Date post: | 05-Apr-2015 |
Category: |
Documents |
Upload: | sigismund-kaufer |
View: | 109 times |
Download: | 0 times |
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
© Prof. T. Kudraß, HTWK Leipzig
Ü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
© Prof. T. Kudraß, HTWK Leipzig
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
© Prof. T. Kudraß, HTWK Leipzig
Standard-Skripttag
Funktion: Begrenzung von PL/SQL-Anweisungen
Inhalt: beliebige PL/SQL-Statements, z.B. Prozeduraufrufe
<% PL/SQL code %><% PL/SQL code %>Syntax
© Prof. T. Kudraß, HTWK Leipzig
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
© Prof. T. Kudraß, HTWK Leipzig
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“] %>
© Prof. T. Kudraß, HTWK Leipzig
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‘; %>
© Prof. T. Kudraß, HTWK Leipzig
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 %>
© Prof. T. Kudraß, HTWK Leipzig
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)
© Prof. T. Kudraß, HTWK Leipzig
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
© Prof. T. Kudraß, HTWK Leipzig
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
© Prof. T. Kudraß, HTWK Leipzig
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>...
© Prof. T. Kudraß, HTWK Leipzig
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>...
© Prof. T. Kudraß, HTWK Leipzig
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>
© Prof. T. Kudraß, HTWK Leipzig
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
© Prof. T. Kudraß, HTWK Leipzig
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; %>...
© Prof. T. Kudraß, HTWK Leipzig
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%>
© Prof. T. Kudraß, HTWK Leipzig
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>
© Prof. T. Kudraß, HTWK Leipzig
Ausgabe der PSP-Beispielseite
© Prof. T. Kudraß, HTWK Leipzig
Überblick Datenbank-Zugriff
Datenbankzugriff mit PL/SQL Server Pages (PSP)
Zugriff auf Metadaten Dynamisches SQL
– Parametrisierung von Anfragen– Dynamischer Aufbau von WHERE-Bedingungen– Dynamische SELECT- und FROM-Klausel
© Prof. T. Kudraß, HTWK Leipzig
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;%>
© Prof. T. Kudraß, HTWK Leipzig
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);
© Prof. T. Kudraß, HTWK Leipzig
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;
© Prof. T. Kudraß, HTWK Leipzig
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 . . .
© Prof. T. Kudraß, HTWK Leipzig
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>");}
© Prof. T. Kudraß, HTWK Leipzig
Überblick Datenbank-Zugriff
Datenbankzugriff mit PL/SQL Server Pages (PSP)
Zugriff auf Metadaten Dynamisches SQL
– Parametrisierung von Anfragen– Dynamischer Aufbau von WHERE-Bedingungen– Dynamische SELECT- und FROM-Klausel
© Prof. T. Kudraß, HTWK Leipzig
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)
© Prof. T. Kudraß, HTWK Leipzig
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
© Prof. T. Kudraß, HTWK Leipzig
Anwendungsbeispiel
© Prof. T. Kudraß, HTWK Leipzig
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
© Prof. T. Kudraß, HTWK Leipzig
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;
© Prof. T. Kudraß, HTWK Leipzig
Realisierung mit DBMS_SQL Package in PL/SQL (3)
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;
© Prof. T. Kudraß, HTWK Leipzig
Realisierung mit DBMS_SQL Package in PL/SQL (4)
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
© Prof. T. Kudraß, HTWK Leipzig
Realisierung mit DBMS_SQL Package in PL/SQL (5)
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);
© Prof. T. Kudraß, HTWK Leipzig
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()