Einige SQL- und PL/SQL-Erweiterungen

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