Datenbank-Zugriffsschnittstellen am Beispiel von Oracle und PL/SQL.

Datenbank-Zugriffsschnittstellenam Beispiel von Oracle und PL/SQL

© Prof. T. Kudraß, HTWK Leipzig

Kommunikation mit der Datenbank

Vorbereiten einer SQL-Anweisung

Freigabe der Ressourcen

Verbindungsaufbau

Lesen der Ergebnismenge

Verbindungsabbau

Ausführung einer Anweisung


Überblick Datenbank-Zugriff

Datenbankzugriff mit PL/SQL Server Pages (PSP)

Zugriff auf Metadaten in PL/SQL Dynamisches SQL

– Parametrisierung von Anfragen– Dynamischer Aufbau von WHERE-Bedingungen– Dynamische SELECT- und FROM-Klausel


Entwicklung von PL/SQL Server Pages (PSP)

1. Schreiben der PL/SQL Server Page Standard-Skripttag Spezielle Skripttags:

- Pages- Prozeduren- Parameter- Deklarationen- Expression Blocks- Include- Kommentare

2. Kompilieren der PSP-Datei als Stored Procedure3. Aufruf der PSP im Browser


Standard-Skripttag

Funktion: Begrenzung von PL/SQL-Anweisungen

Inhalt: beliebige PL/SQL-Statements, z.B. Prozeduraufrufe

<% PL/SQL code %><% PL/SQL code %>Syntax


Spezielle Tags:Page Direktive

Funktion: Charakterisierung der PSP Attribute:

– language: verwendete Skriptsprache (PL/SQL Standard)– contentType: Inhaltstyp der Seite; text/html Standard– errorPage: PSP-Seite, die auf auftretenden Fehlern aufgerufen

wird (Standard: keine Datei)

<% page [language=“PL/SQL“][contentType=“content type string“][errorPage=“file.psp“] %>

<% page [language=“PL/SQL“][contentType=“content type string“][errorPage=“file.psp“] %>

Syntax


Spezielle Tags:Procedure & Parameter Direktive

Funktion: Spezifikation von Prozedur und Parameter (alles IN) Attribute:

– procedure: Name des Prozedur– parameter: Name des Parameters– type: Datentyp des Parameters; Standardwert varchar2 (ohne Länge)– default: Standardwert für Parameter

<% plsql procedure=“procedure name“ %><% plsql procedure=“procedure name“ %>Syntax

<% plsql parameter=“parameter name“ %>[type=“PL/SQL datatype“][default=“value“] %>

<% plsql parameter=“parameter name“ %>[type=“PL/SQL datatype“][default=“value“] %>


Spezielle Tags:Declaration Direktive

Funktion: Deklaration von Variablen und Cursor auf der ganzen Seite

Beispiel:

<%! PL/SQL declaration;[PL/SQL declaration;] ... %>

<%! PL/SQL declaration;[PL/SQL declaration;] ... %>

Syntax

<%! cursor prod_cur is select * from products where price between minprice and´maxprice

vc_name varchar2(200):=‘Peter‘; %>

<%! cursor prod_cur is select * from products where price between minprice and´maxprice

vc_name varchar2(200):=‘Peter‘; %>


Spezielle Tags: Expression Block / Print Direktive

Funktion: Ausgabe eines beliebigen PL/SQL-Ausdrucks (String, Zahl, Ergebnis eines Funktionsaufrufs)

Beispiel:

<%= PL/SQL expression %> <%= PL/SQL expression %> Syntax

<%= ‘The employee name is ‘|| emp_rec.ename %>oderThe employee name is <%= emp_rec.ename %>

<%= ‘The employee name is ‘|| emp_rec.ename %>oderThe employee name is <%= emp_rec.ename %>


Spezielle Tags: Include Direktive

Funktion: Einbinden des Quelltexts anderer Dateien in die Seite

Beispiel:

<%@ include file =“path name“ %> <%@ include file =“path name“ %> Syntax

<%@ include file=“header.inc“ %><%@ include file=“header.inc“ %>

Anmerkungen:- Datei darf HTML- und PSP-Skriptelemente enthalten- Einbindung nur einmal zur Übersetzungszeit (mögliche

Alternative: Einbindung durch Prozeduraufruf)


Spezielle Tags: Kommentare

Funktion: erscheint nicht in der generierten Prozedur bzw. in den daraus erstellten HTML-Seiten

<%-- Kommentar --%> <%-- Kommentar --%> Syntax

<%!-- Kommentar --%> <%!-- Kommentar --%> Syntax

Funktion: Kommentare, die in der HTML-Ausgabe erscheinen (normale HTML-Syntax)

-- einzeiliger Kommentar /* mehrzeiliger Kommentar */

-- einzeiliger Kommentar /* mehrzeiliger Kommentar */

Syntax

Funktion: Kommentare innerhalb eines PL/SQL-Blocks


Kompilieren der PL/SQL Server Page

loadpsp [-replace] - user username/password[@connect_string] [include_file_name ...] <page1>[<page2> ... ]

loadpsp [-replace] - user username/password[@connect_string] [include_file_name ...] <page1>[<page2> ... ]

Syntax

Beispiel

loadpsp -replace - user name/passw@ora10glv timestamp.inc display_cust.psp

loadpsp -replace - user name/passw@ora10glv timestamp.inc display_cust.psp

Attribute:- replace: überschreibt Prozedur gleichen Namens- username/password@connect_string: Login-Information- include-file_name: Dateien, die mittels include eingebunden werden

- page1 ...: Name der PSP-Dateien, die kompiliert werden sollen


ParameterverarbeitungGET-Methode

Beispiel:

http://abraham.imn.htwk-leipzig.de:7777/pls/web/cust_order_items?ord_id=100&cust_id=100

http://abraham.imn.htwk-leipzig.de:7777/pls/web/cust_order_items?ord_id=100&cust_id=100

URL bei Submit:

...<form method=“GET“ action=“cust_order_items“><input type=“hidden“ name=“cust_id“ value=“<%=cust_id %“><input type=“text“ name=“ord_id“ size=“10“ value=“<%=order_id %“><input_type=“submit“ value = “Order Details“></form>...

...<form method=“GET“ action=“cust_order_items“><input type=“hidden“ name=“cust_id“ value=“<%=cust_id %“><input type=“text“ name=“ord_id“ size=“10“ value=“<%=order_id %“><input_type=“submit“ value = “Order Details“></form>...


ParameterverarbeitungPOST-Methode

Beispiel:

http://abraham.imn.htwk-leipzig.de:7777/pls/web/cust_order_items

http://abraham.imn.htwk-leipzig.de:7777/pls/web/cust_order_items

URL bei Submit:

...<form method=“POST“ action=“cust_order_items“><input type=“hidden“ name=“cust_id“ value=“<%=cust_id %“><input type=“text“ name=“ord_id“ size=“10“ value=“<%=order_id %“><input_type=“submit“ value = “Order Details“></form>...

...<form method=“POST“ action=“cust_order_items“><input type=“hidden“ name=“cust_id“ value=“<%=cust_id %“><input type=“text“ name=“ord_id“ size=“10“ value=“<%=order_id %“><input_type=“submit“ value = “Order Details“></form>...


Parameterübergabe

<%@ plsql procedure "show_detail" %><%@ plsql parameter=“p_ID“ type=“number“ default=“0“%><%@ plsql parameter=“p_Name“ type=“varchar2“ default=“‘‘“%>

<SELECT NAME = “p_ID“ size=“1“><% for item in

(select ID, Name from Products order by Name) loop %><option value=“<%=item.ID %>“ <%=item.Name %></option>

<% end loop; %></SELECT>

<SELECT NAME = “p_ID“ size=“1“><% for item in

(select ID, Name from Products order by Name) loop %><option value=“<%=item.ID %>“ <%=item.Name %></option>

<% end loop; %></SELECT>

SELECT-Box wird durch Abfrage auf Tabelle Products mit Werten gefüllt, Zuordnung zum Parameter p_ID2. Eingabeparameter: p_Name als Input-Textfeld in HTML-Formular

<form method=“post“ action=“show_detail“> <p>Eingabe: <input type=text size=50 maxlength=50 name=“p_Name“> <SELECT NAME=“p_ID“ size=“1“> ... </SELECT> <input type=“submit“ value=“Abschicken“> </form>

<form method=“post“ action=“show_detail“> <p>Eingabe: <input type=text size=50 maxlength=50 name=“p_Name“> <SELECT NAME=“p_ID“ size=“1“> ... </SELECT> <input type=“submit“ value=“Abschicken“> </form>


Fehlerbehandlung - Verwendung von Errorpages

erwartete vs. unerwartete Fehler erwarteter Fehler: NO_DATA_FOUND unerwarteter Fehler: 2 Produkte mit der gleichen ID Verwendung von ErrorPage in Page-Direktive zur Behandlung

unerwarteter Fehler Nachteil: keine Parameterübergabe möglich (z.B.

Fehlerzeitpunkt, Eingabeparameter)

EXCEPTION WHEN OTHERS THENhtp.init;error; END; -- error: Name der Fehlerseite


Benutzerdefinierte Ausnahmebehandlung (Exceptions)

<%@ page errorPage="Error_Page.psp" %> -- Angabe der Fehlerseite...l_exception EXCEPTION; -- Deklaration der Ausnahme l_exception...IF (TO_DATE(arrival_date,'YYYY-MM-DD') <= SYSDATE)THEN RAISE l_exception; -- Datum liegt vor dem aktuellem DatumEND IF;EXCEPTION WHEN l_exception THEN -- Test, ob es der vom Benutzer ausgelöste Fehler ist RAISE; -- Weiterleitung des Fehlers an übergeordnete Fehler- END; -- behandlung in Error_Page

...<% IF (SQLERRM = 'User-Defined Exception') THEN %> <% l_error_message := 'Date values should be greater than Current Date'; %> <%= l_error_message %> -- Ausgabe der Fehlermeldung<% ELSE %> <%= SQLERRM %> -- wenn anderer Fehler, Ausgabe der Fehlermeldung<% END IF; %>...


Zusammenfassendes Beispiel:Ausgabe einer Kursliste

<table cellspacing="2" cellpadding="3" border="0"> <tr> <th class="inner">Titel</th> <th class="inner">Leiter</th> <th class="inner">Tag</th> <th class="inner">Zeit von</th> <th class="inner">Zeit bis</th> <th class="inner">Ort</th> <th class="inner">Plätze</th> </tr> <% FOR ds IN (SELECT Kurse.*, Kl_Name ||’, ’ || Kl_Vorname AS Kl_Fullname, Kl_EMail FROM Kurse LEFT JOIN Kursleiter ON Kurs_Leiter_ID_FK=Kl_ID ORDER BY Kurs_Name) LOOP%>

<table cellspacing="2" cellpadding="3" border="0"> <tr> <th class="inner">Titel</th> <th class="inner">Leiter</th> <th class="inner">Tag</th> <th class="inner">Zeit von</th> <th class="inner">Zeit bis</th> <th class="inner">Ort</th> <th class="inner">Plätze</th> </tr> <% FOR ds IN (SELECT Kurse.*, Kl_Name ||’, ’ || Kl_Vorname AS Kl_Fullname, Kl_EMail FROM Kurse LEFT JOIN Kursleiter ON Kurs_Leiter_ID_FK=Kl_ID ORDER BY Kurs_Name) LOOP%>


Zusammenfassendes Beispiel:Ausgabe einer Kursliste (Forts.)

<tr> <%=’<td class="inner">’||ds.Kurs_Name||’</td>’%> <%=’<td class="inner"><a href="mailto:’||ds.Kl_EMail||’">’ ||ds.Kl_Fullname||’</a></td>’%> <%=’<td class="inner">’||ds.Kurs_Wochentag||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Zeitvon||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Zeitbis||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Ort||’</td>’%> <% IF ds.Kurs_AktTeiln < ds.Kurs_MaxTeiln THEN %> <%=’<td class="inner2">’ || ds.Kurs_AktTeiln||’/’ ||ds.Kurs_MaxTeiln||’</td>’%> <% ELSE %> <%=’<td class="inner3">’ ||ds.Kurs_AktTeiln||’/’ ||ds.Kurs_MaxTeiln||’</td>’%> <% END IF; %> </tr> <% END LOOP;%></table>

<tr> <%=’<td class="inner">’||ds.Kurs_Name||’</td>’%> <%=’<td class="inner"><a href="mailto:’||ds.Kl_EMail||’">’ ||ds.Kl_Fullname||’</a></td>’%> <%=’<td class="inner">’||ds.Kurs_Wochentag||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Zeitvon||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Zeitbis||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Ort||’</td>’%> <% IF ds.Kurs_AktTeiln < ds.Kurs_MaxTeiln THEN %> <%=’<td class="inner2">’ || ds.Kurs_AktTeiln||’/’ ||ds.Kurs_MaxTeiln||’</td>’%> <% ELSE %> <%=’<td class="inner3">’ ||ds.Kurs_AktTeiln||’/’ ||ds.Kurs_MaxTeiln||’</td>’%> <% END IF; %> </tr> <% END LOOP;%></table>


Ausgabe der PSP-Beispielseite




Zugriff auf Metadaten Dynamisches SQL



Zugriff auf Metadaten in PL/SQL

Zugriff auf Metadaten über Views des Oracle Data Dictionary = Sichten auf zugrundeliegende Systemtabellen, z.B. all_tab_columns, all_objects, all_tables

Definierte Schnittstelle unabhängig von der DBMS-Version

FOR ds IN ( SELECT table_name, column_name, data_type,data_length, nullable

FROM all_tab_columns WHERE OWNER=’THOMAS’ AND TABLE_NAME=’STUDENTEN’ ORDER BY column_id )

LOOP htp.prn(’ Tabellenname: ’||ds.Table_Name||’<br>’); htp.prn(’ Spaltenname: ’||ds.Column_Name||’<br>’); htp.prn(’ Datentyp: ’||ds.Data_Type||’<br>’); htp.prn(’ Laenge: ’||ds.data_length||’<br>’); htp.prn(’ isNullable: ’||ds.nullable||’<br>’);END LOOP;%>


Zugriff auf Metadaten eines Result Set in PL/SQL

Nutze das Package DBMS_SQL Parsen der SQL-Anweisung

cur := dbms_sql.open_cursor;-- Parsen der Anfrage, DMBS_SQL.native wird für alle Oracle--- Datenbanken > Version7 verwendetdbms_sql.parse(cur,’SELECT * FROM studenten’,DBMS_SQL.native);-- Ausführen der Anfragei := dbms_sql.execute(cur);

Zugriff auf Metadaten des Resultset

dbms_sql.describe_columns(cur, spaltenanzahl, ds);

type desc_rec is record ( col_type BINARY_INTEGER := 0, col_max_len BINARY_INTEGER := 0, col_name VARCHAR2(32) := ’’, col_null_ok BOOLEAN := TRUE);


Zugriff auf Metadaten eines Result Set in PL/SQL (Forts.)

Ausgabe der Metadaten

-- hole ersten Datensatzrec := ds.first;IF (rec is not null) THEN LOOP htp.prn(’ Spaltenname: ’||ds(rec).col_name ||’<br>’); htp.prn(’ Datentyp: ’ ||ds(rec).col_type ||’<br>’); htp.prn(’ Laenge: ’ ||ds(rec).col_max_len||’<br>’); IF (ds(rec).col_null_ok) THEN htp.prn(’ isNullable: true <br>’); ELSE htp.prn(’ isNullable: false <br>’); END IF; htp.prn(’<br>’);-- naechster Datensatz rec := ds.next(rec); EXIT WHEN (rec is null); END LOOP;END IF;


Zugriff auf Metadaten in JDBC(Datenbank)

Klasse DatabaseMetaData enthält Informationen über das spezifische DBMS, z.B.

– Datenbank-Version– maximale Zeichenkettenlänge für Bezeichner– unterstützte Funktionen (z.B. EXISTS-Subquery)– unterstützte Datentypen

BeispielDatabaseMetaData md = conn.getMetaData();ResultSet rset = md.getTables(null,"THOMAS",null, null);while (rset.next()) { out.println("Schema: "+rset.getString("TABLE_SCHEMA")+", "); out.println("Tabellenname: "+ rset.getString("TABLE_NAME")+"<br>");}

Schema: THOMAS, Tabellenname: KURSESchema: THOMAS, Tabellenname: KURSLEITER . . .


Zugriff auf Metadaten in JDBC(ResultSet)

Klasse ResultSetMetaData enthält Informationen über das Ergebnis einer Anfrage, z.B.

– Anzahl Spalten– Spaltenname und Datentyp– Eigenschaften wie Zulässigkeit von Nullwerten

BeispielStatement stmt = conn.createStatement ();ResultSet rsetQuery = stmt.executeQuery ("SELECT * FROM Kurse ORDER BY Kurs_Name");

ResultSetMetaData rsmetadata = rsetQuery.getMetaData();int spalten = rsmetadata.getColumnCount();

for (int i=1;i<=spalten;i++) { out.println("Spaltenname: "+rsmetadata.getColumnName(i)+"<br>"); out.println("Datentyp: "+rsmetadata.getColumnType(i)+"<br>"); out.println("Laenge: "+rsmetadata.getPrecision(i)+"<br>"); out.println("isNullable: "+rsmetadata.isNullable(i)+"<br>");}




Zugriff auf Metadaten Dynamisches SQL



Unterstützung dynamischer Anfragen

Dynamische Belegung von Werten in der WHERE-Klausel (durch Parametrisierung)

Dynamischer Aufbau der WHERE-Bedingung (d.h. variable Spalten)

Dynamischer Aufbau der zu selektierenden Spalten (SELECT-Liste) bzw. Tabellen (FROM-Klausel)


Dynamische Zusammenstellung einer Anfrage

Ansatz 1: Nutzung von Zeichenkettenfunktionen in der jeweiligen Sprache

Ansatz 2:Verwendung des Oracle-Pakets DBMS_SQL

Anwendungsbeispiel:Dynamische Anzeige von Studentendaten mit vorgegebener Matrikel-Nr. und vom Benutzer wählbarer Spalten


Anwendungsbeispiel


Realisierung mit DBMS_SQL Package in PL/SQL

[...]<input type=hidden name="spalten" value="stud_matrikel"><table border="1" cellpadding="0" cellspacing="0"><tr> <td>Matrikelnummer:</td> <td><INPUT name="matrikel" size="10" maxlength="7" value=""></td></tr><tr> <td>Name</td> <td><input type=checkbox name="spalten"

value="stud_name"></td></tr>[...]

Liste der Checkboxen in HTML


Realisierung mit DBMS_SQL Package in PL/SQL (2)

<%@ plsql procedure="psp_dynamic" %><%@ plsql parameter="matrikel" type="NUMBER" %><%@ plsql parameter="spalten" type="owa_util.ident_arr" %>

Erstellung und Ausführung der dynamischen SQL-Anweisung in PSP Programm

Speichere die Namen aller selektierten Spalten in einer ZeichenketteFOR i IN 1..spalten.count LOOP IF (i=spalten.count) THEN spalten_all := spalten_all || spalten(i); ELSE spalten_all := spalten_all || spalten(i) || ’,’; END IF;END LOOP;



c := dbms_sql.open_cursor;

Öffnen des Cursors für weitere Ausführung der dynam. Anfrage

DBMS_SQL.PARSE(c, ’SELECT ’||spalten_all||’ FROM studentenWHERE stud_matrikel = :matrikel’, dbms_sql.native);

DBMS_SQL.BIND_VARIABLE(c, ’:matrikel’, matrikel);

Parsen der Anfrage und Parameterbindung

Definiere eine Spalte für jede selektierte Spalte im Cursor

FOR i IN 1..spalten.countLOOP DBMS_SQL.DEFINE_COLUMN(c, i, value_string, 30);END LOOP;



DBMS_SQL.execute(c);[...]FOR i IN 1..spalten.count LOOP htp.print(’<th class="inner">’||spalten(i)||’</th>’); END LOOP;

Ausführung der Anfrage – Ausgabe des Tabellenkopfes

Hinweis:Neben DBMS_SQL auch native dynamic SQL in Oracle verfügbar



Lese jede Ergebniszeile mittels (DBMS_SQL.FETCH_ROWS) Pro Zeile: Lese alle Spaltenwerte (Out-Param value_string) LOOP IF DBMS_SQL.FETCH_ROWS(c)>0 THEN htp.print(’<tr>’); FOR i IN 1..spalten.count LOOP DBMS_SQL.COLUMN_VALUE(c, i, value_string); htp.print(’<td class="inner">’||value_string||’</td>’); END LOOP; htp.print(’</tr>’); ELSE exit; END IF;END LOOP;-- Nach Ergebnisausgabe: Cursor schliessenDBMS_SQL.CLOSE_CURSOR(c);


Transaktionsunterstützung

Autocommit-Modus– bedeutet: Jedes SQL-Statement als individuelle

Transaktion behandelt– neue Connections sind im Autocommit-Modus– Ausschalten des Autocommit-Modus

JDBC: conn.setAutoCommit(false)– Transaktionskontrolle ohne Autocommit-Modus

JDBC: conn.commit() conn.rollback()

Datenbank-Zugriffsschnittstellen am Beispiel von Oracle und PL/SQL.

Documents

Transcript of Datenbank-Zugriffsschnittstellen am Beispiel von Oracle und PL/SQL.