Einige SQL- und PL/SQL-Erweiterungen
Werbung
Einige SQL- und PL/SQL-Erweiterungen Dr. Gudrun Pabst Einige SQL- und PL/SQL-Erweiterungen > Ø UTL_COMPRESS Ø UTL_MAIL Ø Case- und Akzent-unabhängige Suche und Sortierung Ø Reguläre Ausdrücke (SQL, PL/SQL) Ø Erweitertes CONNECT BY Ø Expressions und Expression Filter EVALUATE Agenda Für besseren Durchblick. Einige SQL- und PL/SQL-Erweiterungen n2n © 2004 UTL_COMPRESS Ø Stellt die ZIP- und UNZIP-Funktionalität unter PL/SQL zur Verfügung Ø Benutzt den Lempel-Ziv-Algorithmus Ø Packt und entpackt RAW bytestreams Ø Kann auch LOBs verarbeiten Ø Kann Daten in mehreren Teilen zu einem Archiv hinzufügen und wieder extrahieren Einige SQL- und PL/SQL-Erweiterungen n3n © 2004 UTL_COMPRESS - Unterprogramme Ø UTL_COMPRESS.LZ_COMPRESS > Zum Packen von Daten > Daten können als RAW BLOB BFILE übergeben und als RAW BLOB zurückgegeben werden. > Kompressionsstufen 1-9 (1=schnellste Kompression, 9=höchste Kompression, Default: 6) Ø Für stückweises Packen: UTL_COMPRESS.LZ_COMPRESS_ADD Einige SQL- und PL/SQL-Erweiterungen n4n © 2004 UTL_COMPRESS - Unterprogramme Ø UTL_COMPRESS.LZ_UNCOMPRESS > Zum Entpacken von Daten > Daten können als RAW BLOB BFILE übergeben und als RAW BLOB zurückgegeben werden. Ø Zum stückweisen Entpacken: UTL_COMPRESS.LZ_UNCOMPRESS_EXTRACT Einige SQL- und PL/SQL-Erweiterungen n5n © 2004 Einige SQL- und PL/SQL-Erweiterungen > Ø UTL_COMPRESS Ø UTL_MAIL Ø Case- und Akzent-unabhängige Suche und Sortierung Ø Reguläre Ausdrücke (SQL, PL/SQL) Ø Erweitertes CONNECT BY Ø Expressions und Expression Filter EVALUATE Agenda Für besseren Durchblick. Einige SQL- und PL/SQL-Erweiterungen n6n © 2004 UTL_MAIL Ø Bisher nur: UTL_SMTP > Wissen über SMTP-Protokoll nötig à komplexe Bedienung Ø Jetzt zusätzlich: UTL_MAIL > Oracle Database New Features Guide: „It requires only the normal mental model of a user of the GUI email client, rather than an understanding of the underlying protocol (SMTP) features.“ Ø Nicht defaultmäßig installiert, da der SMTP_OUT_SERVER konfiguriert werden muss und dies ggf. zu Sicherheitsproblemen führt Einige SQL- und PL/SQL-Erweiterungen n7n © 2004 UTL_MAIL Ø Installation: sqlplus sys/<pwd> SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb Ø Setup: Setzen des init.ora-Parameters SMTP_OUT_SERVER als <server>:<port> ABER: Dieser Parameter kann nicht über alter system gesetzt werden! Der Parameter muss in der init.ora eingetragen werden, dann muss aus dem PFILE wieder ein SPFILE erzeugt werden! Einige SQL- und PL/SQL-Erweiterungen n8n © 2004 UTL_MAIL - Unterprogramme Ø UTL_MAIL.SEND > Senden einer Mail ohne Anhang > Angabe von Sender, Empfänger, CC, BCC, Subject, Message und ggf. MimeType und Priorität Ø UTL_MAIL.SEND_ATTACH_RAW > Senden einer Mail mit binärem Anhang > Angabe von Sender, Empfänger, CC, BCC, Subject, Message und ggf. MimeType und Priorität > Zusätzlich Angabe des Attachments als RAW, Inline oder nicht, ggf. MimeType und File-Name des Attachments Ø UTL_MAIL.SEND_ATTACH_VARCHAR2 > Senden einer Mail mit Text-Anhang > Wie UTL_MAIL.SEND_ATTACH_RAW, aber mit Anhang vom Typ VARCHAR2 Einige SQL- und PL/SQL-Erweiterungen n9n © 2004 Einige SQL- und PL/SQL-Erweiterungen > Ø UTL_COMPRESS Ø UTL_MAIL Ø Case- und Akzent-unabhängige Suche und Sortierung Ø Reguläre Ausdrücke (SQL, PL/SQL) Ø Erweitertes CONNECT BY Ø Expressions und Expression Filter EVALUATE Agenda Für besseren Durchblick. Einige SQL- und PL/SQL-Erweiterungen n 10 n © 2004 Case- und Akzent-unabhängige Suche und Sortierung Ø Häufige Wünsche > Suche unabhängig von Groß- und Kleinschreibung > Suche unabhängig von Akzenten und Umlauten (äàáã) > Sortierte Ausgabe von Zeichenketten nach den Regeln des Dudens Ø Lösungsansätze vor Oracle 10g > Zweites Feld in Tabelle zum Speichern der Werte in Grossbuchstaben und ohne Akzente à Platz- und I/O-Verschwendung > Suchen bzw. Sortieren: » Groß- und Kleinschreibung: Mit upper() und Function Based Indexes performant möglich » Akzenten, Umlaute: ?? Einige SQL- und PL/SQL-Erweiterungen n 11 n © 2004 Case- und Akzent-unabhängige Suche und Sortierung Ø 10g : Erweiterung von NLS_SORT > Linguistisches Verhalten wird durch "linguistische Sortiernamen" erreicht. > An die bekannten Werte werden dabei folgende Endungen angehängt: » _CI für Case-unabhängiges Verhalten » _AI für Case- und Akzent-unabhängiges Verhalten > Setzen auf Instance-, Session- oder Befehlsebene möglich Ø Beispiele: ALTER SYSTEM SET NLS_SORT=GENERIC_M_CI SCOPE=SPFILE; ALTER SESSION SET NLS_SORT=XGERMAN_AI; SELECT * FROM sortierung ORDER BY NLSSORT(text,'NLS_SORT=BINARY_CI'); Einige SQL- und PL/SQL-Erweiterungen n 12 n © 2004 Case- und Akzent-unabhängige Suche und Sortierung Ø Verhalten bei Sortierung: BINARY BINARY_CI BINARY_AI A a Á Ab A ä a ab Ä ab Ab a x x A Á Á á Ä á ab á ä Ab ä Ä x Einige SQL- und PL/SQL-Erweiterungen n 13 n © 2004 Case- und Akzent-unabhängige Suche und Sortierung Ø Verhalten bei Suche: alter session set nls_sort=GERMAN_CI; select srt_text from sortierung where srt_text = 'a'; SRT_TEXT -----------------------------a select srt_text from sortierung where regexp_like ( srt_text , '^a$' ); SRT_TEXT -----------------------------a A Ø NLS_SORT wird nur bei Vergleich mit regulären Ausdrücken beachtet, nicht jedoch beim Standard-Vergleich! Einige SQL- und PL/SQL-Erweiterungen n 14 n © 2004 Einige SQL- und PL/SQL-Erweiterungen > Ø UTL_COMPRESS Ø UTL_MAIL Ø Case- und Akzent-unabhängige Suche und Sortierung Ø Reguläre Ausdrücke (SQL, PL/SQL) Ø Erweitertes CONNECT BY Ø Expressions und Expression Filter EVALUATE Agenda Für besseren Durchblick. Einige SQL- und PL/SQL-Erweiterungen n 15 n © 2004 Reguläre Ausdrücke Ø Von diversen Betriebssystemen und Programmiersprachen schon seit geraumer Zeit bekannt Ø Nun auch in Oracle verfügbar Ø Erleichtert das Suchen, Validieren und Ersetzen an vielen Stellen erheblich, ohne z.B. innerhalb eines SQL-Befehls auf PL/SQL zurückgreifen zu müssen Ø Oracle hält sich an den POSIX Standard (Extended Regular Expression - ERE) Ø http://www.regular-expressions.info Einige SQL- und PL/SQL-Erweiterungen n 16 n © 2004 Reguläre Ausdrücke in der Datenbank Ø Die Datenbank kennt folgende Funktionen zur Auswertung regulärer Ausdrücke: > REGEXP_LIKE Prüfung, ob der reguläre Ausdruck im String auftritt (TRUE/FALSE) > REGEXP_INSTR Stelle, an der der reguläre Ausdruck im String vorkommt > REGEXP_SUBSTR Ausschneiden des Stringteils, der einem regulären Ausdruck entspricht > REGEXP_REPLACE Ersetzen des Stringteils, der einem regulären Ausdruck entspricht, durch einen vorgegebenen String Ø Die Funktionen können in SQL (Selects, Check-Constraints) und in PL/SQL verwendet werden. Ø Ausdrücke können mit Function-Based Indexen indexiert werden, dadurch hervorragende Performance bei Suchoperationen möglich Einige SQL- und PL/SQL-Erweiterungen n 17 n © 2004 Reguläre Ausdrücke − REGEXP_LIKE Ø Test, ob regulärer Ausdruck eingehalten wird Ø Z.B. E-Mail-Adressen: enthalten @, enden auf .com oder .de : SQL> SELECT address 2 FROM email 3 WHERE regexp_like (address, '.*@.*\.(com|de)'); ADDRESS [email protected] [email protected] [email protected] Ø Dritter Parameter: 'i' für case-unabhängige Suche Einige SQL- und PL/SQL-Erweiterungen n 18 n © 2004 Reguläre Ausdrücke − REGEXP_LIKE Ø Gut verwendbar in Check-Constraints auf Tabellen Ø Beispiel: Validierung einer Artikel-Nummer > Format x99.999.999 mit > x = Grossbuchstabe zwischen A und F > 9 = Zahl CREATE TABLE parts (partno VARCHAR2(11) NOT NULL CONSTRAINT check_part# CHECK (regexp_like( partno, '[A-F][[:digit:]]{2}\.[[:digit:]]{3}\.[[:digit:]]{3}' ) ) ) Einige SQL- und PL/SQL-Erweiterungen n 19 n © 2004 Reguläre Ausdrücke − REGEXP_REPLACE Ø Ändern von Strings ? von [email protected] [email protected] nach [email protected] [email protected] SELECT substr(address, instr (address, '.') + 1, instr (address, '@') - instr (address, '.') - 1 ) || '.' || substr (address, 1, instr (address, '.') - 1) || substr (address, instr (address, '@')) FROM email Ø In 10g einfacher mittels REGEXP_REPLACE : SELECT address, regexp_replace (address, '(.*)\.(.*)@(.*)', '\2.\1@\3') FROM email Einige SQL- und PL/SQL-Erweiterungen n 20 n © 2004 Reguläre Ausdrücke − REGEXP_REPLACE Ø Auch komplexere Ausdrücke sind möglich: > Es ist nicht sicher, ob zwischen Name und Vorname wirklich ein "." ist. SQL> SELECT address, 2 regexp_replace (address, 3 '([[:alnum:]]*)\.?([[:upper:]])(.*)@(.*)', 4 '\2\3.\1@\4' 5 ) adress_neu 6 FROM email; ADDRESS [email protected] [email protected] [email protected] Einige SQL- und PL/SQL-Erweiterungen ADRESS_NEU [email protected] [email protected] [email protected] n 21 n © 2004 Einige SQL- und PL/SQL-Erweiterungen > Ø UTL_COMPRESS Ø UTL_MAIL Ø Case- und Akzent-unabhängige Suche und Sortierung Ø Reguläre Ausdrücke (SQL, PL/SQL) Ø Erweitertes CONNECT BY Ø Expressions und Expression Filter EVALUATE Agenda Für besseren Durchblick. Einige SQL- und PL/SQL-Erweiterungen n 22 n © 2004 Erweitertes Connect-By Neue Funktionalitäten: Ø CONNECT_BY_ROOT <spalten_name> gibt den Wert der Spalte des Root-Datensatzes an Ø SYS_CONNECT_BY_PATH ( <spalten_name> , <trennzeichen> ) gibt den „Pfad“ zum Root-Datensatz aus Ø CONNECT_BY_ISLEAF gibt 1 aus, wenn der Datensatz ein „Blatt“ des hierarchischen Baums ist; andernfalls wird 0 zurückgegeben Ø CONNECT_BY_ISCYCLE gibt 1 aus, wenn der Datensatz einen „Kind-Datensatz“ besitzt, der auch ein „Vorfahre“ ist; andernfalls wird 0 zurückgegeben nur mit CONNECT BY NOCYCLE <Bedingung> Einige SQL- und PL/SQL-Erweiterungen n 23 n © 2004 CONNECT_BY_ISLEAF , SYS_CONNECT_BY_PATH Ø Die Abfrage SELECT rpad (' ', LEVEL * 2 - 2, ' ') || last_name last_name, decode (connect_by_isleaf, 1, 'Yes', 'No') leaf, sys_connect_by_path (last_name, '/') "Path" FROM hr.employees START WITH last_name = 'King' AND department_id = 90 CONNECT BY PRIOR employee_id = manager_id; liefert LAST_NAME -------------------King Russell Tucker Bernstein ... Partners King ... Einige SQL- und PL/SQL-Erweiterungen LEAF ---No No Yes Yes Path -----------------------------/King /King/Russell /King/Russell/Tucker /King/Russell/Bernstein No Yes /King/Partners /King/Partners/King n 24 n © 2004 Einige SQL- und PL/SQL-Erweiterungen > Ø UTL_COMPRESS Ø UTL_MAIL Ø Case- und Akzent-unabhängige Suche und Sortierung Ø Reguläre Ausdrücke (SQL, PL/SQL) Ø Erweitertes CONNECT BY Ø Expressions und Expression Filter EVALUATE Agenda Für besseren Durchblick. Einige SQL- und PL/SQL-Erweiterungen n 25 n © 2004 Expression-Filter Ø Bedingungen können in Tabellen-Spalten abgelegt, ausgewertet und mit Index (nur Enterprise Edition) versehen werden Ø Sowohl in Standard Edition (außer Indexe) als auch in Enterprise Edition verfügbar Ø Bedingungen können in der Tabelle geändert werden, ohne die Applikation zu verändern Ø Benutzung von SQL-Funktionen in den Bedingungen ohne Deklaration möglich, benutzerdefinierte Funktionen müssen deklariert werden Ø Abfrage über EVALUATE in der SQL-Where-Bedingung, daher Möglichkeit, zwei Tabellen über die Bedingungs-Spalte zu joinen Ø à sehr flexible Auswertungen möglich Einige SQL- und PL/SQL-Erweiterungen n 26 n © 2004 Expression-Filter 4 Schritte, um eine Expression-Spalte zu definieren: Ø Erstellen eines Attribute Set Ø Festlegen der Expression-Spalte in der (leeren) Tabelle durch Zuweisung des Attribute Sets Ø Einfügen der Daten und der Bedingungen in die Tabelle Ø Auswerten der Bedingungen in Abfragen über SQL EVALUATE Einige SQL- und PL/SQL-Erweiterungen n 27 n © 2004 Expression-Filter – Erstellen des Attribute Set Ø Erstellen eines Attribute Set und Hinzufügen von Attributen: exec dbms_expfil.create_attribute_set ('PKW_ATTRS') exec dbms_expfil.add_elementary_attribute ( 'PKW_ATTRS' , 'PKW_MARKE' , 'VARCHAR2(30)' ) Ø oder aus einem Object Type: create type pkw_attrs as object ( PKW_MARKE VARCHAR2(30) ); / exec dbms_expfil.create_attribute_set ('PKW_ATTRS','YES') Ø Bei Verwendung von benutzerdefinierten Funktionen müssen diese auch im Attribute Set hinterlegt werden: exec dbms_expfil.add_functions ('PKW_ATTRS','FU_DEMO') Einige SQL- und PL/SQL-Erweiterungen n 28 n © 2004 Expression-Filter – Festlegen der Expression-Spalte Ø Erstellen der gewünschten Tabelle mit einer Spalte für die Bedingungen Ø Festlegen der Bedingungs-Spalte als Expression-Spalte: exec dbms_expfil.assign_attribute_set ( 'PKW_ATTRS' , 'KUNDEN' , 'KND_INTERESSE' ) Ø Füllen der Spalte mit den Bedingungen: insert into kunden ( knd_id , knd_interesse ) values ( 1 , 'PKW_MARKE=''Xyz''' ); Einige SQL- und PL/SQL-Erweiterungen n 29 n © 2004 Expression-Filter – EVALUATE Ø Auswerten der Bedingungs-Spalte in der Where-Bedingung mit EVALUATE Ø Wichtig: Übergeben von Werten für jedes Attribut des Attribute Set Ø Beispiel: select * from kunden where evaluate ( KUNDEN.KND_INTERESSE , 'PKW_MARKE => ''Xyz'') = 1; KND_ID KND_INTERESSE -------- -----------------------------1 PKW_MARKE='Xyz' Einige SQL- und PL/SQL-Erweiterungen n 30 n © 2004 Expression Filter – User-Views USER_EXPFIL_ASET_FUNCTIONS Liste der Funktionen und Packages der Attribute Sets USER_EXPFIL_ATTRIBUTES USER_EXPFIL_ATTRIBUTE_SETS Liste der elementaren Attribute der Attribute Sets Liste der Attribute Sets USER_EXPFIL_DEF_INDEX_PARAMS Liste der Default Index-Parameter USER_EXPFIL_EXPRESSION_SETS Liste der Expression Sets USER_EXPFIL_EXPRSET_STATS USER_EXPFIL_INDEX_PARAMS Liste der Statistiken der Prädikate der Expression Sets Liste der Index-Parameter zum Expression Set USER_EXPFIL_INDEXES Liste der Expression-Filter-Indexe USER_EXPFIL_PREDTAB_ATTRIBUTES Liste der gespeicherten und indizierten Attribute für die Indexe USER_EXPFIL_PRIVILEGES Einige SQL- und PL/SQL-Erweiterungen Liste aller Expression-Privilegien des aktuellen Users n 31 n © 2004 SQL und PL/SQL ? ? ? ? ? Fragen? ? ? ? http://www.trivadis.com >Consulting >Individualentwicklung >Projektmanagement >Systems Management >Training Einige SQL- und PL/SQL-Erweiterungen ? n 32 n ? ? ? © 2004
