Datenbank-Zugriffsschnittstellen am Beispiel von Oracle und PL/SQL

1
Datenbank-Zugriffsschnittstellenam Beispiel von
Oracle und PL/SQL
2
Kommunikation mit der Datenbank
Verbindungsaufbau
Vorbereiten einer SQL-Anweisung
Ausführung einer Anweisung
Lesen der Ergebnismenge
Freigabe der Ressourcen
Verbindungsabbau
3
Ü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

4
Entwicklung von PL/SQL Server Pages (PSP)

• Schreiben der PL/SQL Server Page
• Standard-Skripttag
• Spezielle Skripttags
• Pages
• Prozeduren
• Parameter
• Deklarationen
• Expression Blocks
• Include
• Kommentare
• Kompilieren der PSP-Datei als Stored Procedure
• Aufruf der PSP im Browser

5
Standard-Skripttag
lt PL/SQL code gt
Syntax

• Funktion Begrenzung von PL/SQL-Anweisungen
• Inhalt beliebige PL/SQL-Statements, z.B.
  Prozeduraufrufe

6
Spezielle TagsPage Direktive
lt page languagePL/SQL contentTypecontent
type string errorPagefile.psp gt
Syntax

• 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)

7
Spezielle TagsProcedure Parameter Direktive
lt plsql procedureprocedure name gt
Syntax
lt plsql parameterparameter name
gt typePL/SQL datatype defaultvalue gt

• 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

8
Spezielle TagsDeclaration Direktive
lt! PL/SQL declaration PL/SQL declaration ...
gt
Syntax

• Funktion Deklaration von Variablen und Cursor
  auf der ganzen Seite
• Beispiel

lt! cursor prod_cur is select from products
where price between minprice andmaxprice
vc_name varchar2(200)Peter gt
9
Spezielle Tags Expression Block / Print
Direktive
lt PL/SQL expression gt
Syntax

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

lt The employee name is emp_rec.ename
gt oder The employee name is lt emp_rec.ename
gt
10
Spezielle Tags Include Direktive
lt_at_ include file path name gt
Syntax

• Funktion Einbinden des Quelltexts anderer
  Dateien in die Seite
• Beispiel

lt_at_ include fileheader.inc gt

• Anmerkungen
• Datei darf HTML- und PSP-Skriptelemente enthalten
• Einbindung nur einmal zur Übersetzungszeit
  (mögliche Alternative Einbindung durch
  Prozeduraufruf)

11
Spezielle Tags Kommentare
lt-- Kommentar --gt
Syntax

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

lt!-- Kommentar --gt
Syntax

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

-- einzeiliger Kommentar / mehrzeiliger
Kommentar /
Syntax

• Funktion Kommentare innerhalb eines
  PL/SQL-Blocks

12
Kompilieren der PL/SQL Server Page
Syntax
loadpsp -replace - user username/password_at_conne
ct_string include_file_name ...
ltpage1gtltpage2gt ...

• Attribute
• replace überschreibt Prozedur gleichen Namens
• username/password_at_connect_string
  Login-Information
• include-file_name Dateien, die mittels include
  eingebunden werden
• page1 ... Name der PSP-Dateien, die kompiliert
  werden sollen

Beispiel
loadpsp -replace - user name/passw_at_ora10glv
timestamp.inc display_cust.psp
13
ParameterverarbeitungGET-Methode

• Beispiel

... ltform methodGET actioncust_order_itemsgt
ltinput typehidden namecust_id
valueltcust_id gt ltinput typetext
nameord_id size10 valueltorder_id
gt ltinput_typesubmit value Order
Detailsgt lt/formgt ...
URL bei Submit
http//abraham.imn.htwk-leipzig.de7777/pls/web/
cust_order_items?ord_id100cust_id100
14
ParameterverarbeitungPOST-Methode

• Beispiel

... ltform methodPOST actioncust_order_itemsgt
ltinput typehidden namecust_id
valueltcust_id gt ltinput typetext
nameord_id size10 valueltorder_id
gt ltinput_typesubmit value Order
Detailsgt lt/formgt ...
URL bei Submit
http//abraham.imn.htwk-leipzig.de7777/pls/web/
cust_order_items
15
Parameterübergabe
lt_at_ plsql procedure "show_detail" gtlt_at_ plsql
parameterp_ID typenumber default0gtlt_at_
plsql parameterp_Name typevarchar2
defaultgt
ltSELECT NAME p_ID size1gtlt for item in
(select ID, Name from Products order by Name)
loop gt ltoption valueltitem.ID gt
ltitem.Name gtlt/optiongt lt end loop
gtlt/SELECTgt
SELECT-Box wird durch Abfrage auf Tabelle
Products mit Werten gefüllt, Zuordnung zum
Parameter p_ID 2. Eingabeparameter p_Name als
Input-Textfeld in HTML-Formular
ltform methodpost actionshow_detailgt
ltpgtEingabe ltinput typetext size50 maxlength50
namep_Namegt ltSELECT NAMEp_ID size1gt
... lt/SELECTgt ltinput typesubmit
valueAbschickengt lt/formgt
16
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)

17
Benutzerdefinierte Ausnahmebehandlung (Exceptions)
lt_at_ page errorPage"Error_Page.psp" gt -- Angabe
der Fehlerseite...l_exception EXCEPTION --
Deklaration der Ausnahme l_exception...IF
(TO_DATE(arrival_date,'YYYY-MM-DD') lt
SYSDATE)THEN RAISE l_exception -- Datum
liegt vor dem aktuellem DatumEND IFEXCEPTION
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
... lt IF (SQLERRM 'User-Defined Exception')
THEN gt lt l_error_message 'Date values
should be greater than Current Date' gt lt
l_error_message gt -- Ausgabe der
Fehlermeldung lt ELSE gt lt SQLERRM gt --
wenn anderer Fehler, Ausgabe der Fehlermeldung lt
END IF gt ...
18
Zusammenfassendes BeispielAusgabe einer
Kursliste
lttable cellspacing"2" cellpadding"3"
border"0"gt lttrgt ltth class"inner"gtTitellt/thgt
ltth class"inner"gtLeiterlt/thgt ltth
class"inner"gtTaglt/thgt ltth class"inner"gtZeit
vonlt/thgt ltth class"inner"gtZeit bislt/thgt
ltth class"inner"gtOrtlt/thgt ltth
class"inner"gtPlaumltzelt/thgt lt/trgt lt FOR
ds IN (SELECT Kurse., Kl_Name ,
Kl_Vorname AS Kl_Fullname, Kl_EMail
FROM Kurse LEFT JOIN Kursleiter ON
Kurs_Leiter_ID_FKKl_ID ORDER BY Kurs_Name)
LOOPgt
19
Zusammenfassendes BeispielAusgabe einer
Kursliste (Forts.)
lttrgt ltlttd class"inner"gtds.Kurs_Namelt/
tdgtgt ltlttd class"inner"gtlta
href"mailtods.Kl_EMail"gt
ds.Kl_Fullnamelt/agtlt/tdgtgt ltlttd
class"inner"gtds.Kurs_Wochentaglt/tdgtgt
ltlttd class"inner"gtds.Kurs_Zeitvonlt/tdgt
gt ltlttd class"inner"gtds.Kurs_Zeitbislt/t
dgtgt ltlttd class"inner"gtds.Kurs_Ortlt/t
dgtgt lt IF ds.Kurs_AktTeiln lt ds.Kurs_MaxTeiln
THEN gt ltlttd class"inner2"gt
ds.Kurs_AktTeiln/ ds.Kurs_MaxTeilnlt/td
gtgt lt ELSE gt ltlttd class"inner3"gt
ds.Kurs_AktTeiln/ ds.Kurs_MaxTeilnlt/
tdgtgt lt END IF gt lt/trgt lt END
LOOPgt lt/tablegt
20
Ausgabe der PSP-Beispielseite
21
Ü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

22
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 OWNERTHOMAS AND
TABLE_NAMESTUDENTEN ORDER BY column_id
) LOOP htp.prn( Tabellenname
ds.Table_Nameltbrgt) htp.prn(
Spaltenname ds.Column_Nameltbrgt)
htp.prn( Datentyp ds.Data_Typeltbrgt)
htp.prn( Laenge ds.data_lengthltbrgt)
htp.prn( isNullable ds.nullableltbrgt) END
LOOP gt
23
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 gt Version7 verwendet dbms_sql.parse(cu
r,SELECT FROM studenten,DBMS_SQL.native) --
Ausführen der Anfrage i 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)
24
Zugriff auf Metadaten eines Result Set in PL/SQL
(Forts.)

• Ausgabe der Metadaten

-- hole ersten Datensatz rec ds.first IF (rec
is not null) THEN LOOP htp.prn(
Spaltenname ds(rec).col_name ltbrgt)
htp.prn( Datentyp ds(rec).col_type
ltbrgt) htp.prn( Laenge
ds(rec).col_max_lenltbrgt) IF
(ds(rec).col_null_ok) THEN htp.prn(
isNullable true ltbrgt) ELSE htp.prn(
isNullable false ltbrgt) END IF
htp.prn(ltbrgt) -- naechster Datensatz rec
ds.next(rec) EXIT WHEN (rec is null)
END LOOP END IF
25
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
• Beispiel

DatabaseMetaData md conn.getMetaData() ResultSe
t rset md.getTables(null,"THOMAS",null,
null) while (rset.next()) out.println("Schema
"rset.getString("TABLE_SCHEMA")", ")
out.println("Tabellenname "
rset.getString("TABLE_NAME")"ltbrgt")
Schema THOMAS, Tabellenname KURSE Schema
THOMAS, Tabellenname KURSLEITER . . .
26
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
• Beispiel

Statement stmt conn.createStatement
() ResultSet rsetQuery stmt.executeQuery
("SELECT FROM Kurse ORDER BY Kurs_Name") Resul
tSetMetaData rsmetadata rsetQuery.getMetaData()
int spalten rsmetadata.getColumnCount() for
(int i1iltspalteni) out.println("Spaltenn
ame "rsmetadata.getColumnName(i)"ltbrgt")
out.println("Datentyp "rsmetadata.getColumnType(
i)"ltbrgt") out.println("Laenge
"rsmetadata.getPrecision(i)"ltbrgt")
out.println("isNullable "rsmetadata.isNullable(i
)"ltbrgt")
27
Ü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

28
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)

29
Dynamische Zusammenstellung einer Anfrage

• Ansatz 1 Nutzung von Zeichenkettenfunktionen in
  der jeweiligen Sprache
• Ansatz 2Verwendung des Oracle-Pakets DBMS_SQL
• AnwendungsbeispielDynamische Anzeige von
  Studentendaten mit vorgegebener Matrikel-Nr. und
  vom Benutzer wählbarer Spalten

30
Anwendungsbeispiel
31
Realisierung mit DBMS_SQL Package in PL/SQL
Liste der Checkboxen in HTML
... ltinput typehidden name"spalten"
value"stud_matrikel"gt lttable border"1"
cellpadding"0" cellspacing"0"gt lttrgt
lttdgtMatrikelnummerlt/tdgt lttdgtltINPUT
name"matrikel" size"10" maxlength"7"
value""gtlt/tdgt lt/trgt lttrgt lttdgtNamelt/tdgt
lttdgtltinput typecheckbox name"spalten"
value"stud_name"gtlt/tdgt lt/trgt ...
32
Realisierung mit DBMS_SQL Package in PL/SQL (2)
Erstellung und Ausführung der dynamischen
SQL-Anweisung in PSP Programm
lt_at_ plsql procedure"psp_dynamic" gt lt_at_ plsql
parameter"matrikel" type"NUMBER" gt lt_at_ plsql
parameter"spalten" type"owa_util.ident_arr" gt
Speichere die Namen aller selektierten Spalten in
einer Zeichenkette
FOR i IN 1..spalten.count LOOP IF
(ispalten.count) THEN spalten_all
spalten_all spalten(i) ELSE
spalten_all spalten_all spalten(i) ,
END IF END LOOP
33
Realisierung mit DBMS_SQL Package in PL/SQL (3)
Öffnen des Cursors für weitere Ausführung der
dynam. Anfrage
c dbms_sql.open_cursor
Parsen der Anfrage und Parameterbindung
DBMS_SQL.PARSE(c, SELECT spalten_all FROM
studenten WHERE stud_matrikel matrikel,
dbms_sql.native) DBMS_SQL.BIND_VARIABLE(c,
matrikel, matrikel)
Definiere eine Spalte für jede selektierte Spalte
im Cursor
FOR i IN 1..spalten.count LOOP
DBMS_SQL.DEFINE_COLUMN(c, i, value_string,
30) END LOOP
34
Realisierung mit DBMS_SQL Package in PL/SQL (4)
Ausführung der Anfrage Ausgabe des
Tabellenkopfes
DBMS_SQL.execute(c) ... FOR i IN
1..spalten.count LOOP htp.print(ltth
class"inner"gtspalten(i)lt/thgt) END LOOP
HinweisNeben DBMS_SQL auch native dynamic SQL
in Oracle verfügbar
35
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)gt0 THEN
htp.print(lttrgt) FOR i IN 1..spalten.count
LOOP DBMS_SQL.COLUMN_VALUE(c, i,
value_string) htp.print(lttd
class"inner"gtvalue_stringlt/tdgt) END
LOOP htp.print(lt/trgt) ELSE exit END
IF END LOOP -- Nach Ergebnisausgabe Cursor
schliessen DBMS_SQL.CLOSE_CURSOR(c)
36
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-ModusJDBC
  conn.commit() conn.rollback()