SELECT-Abfragen mit regulären Ausdrücken

Veröffentlicht: von

findet man hier:
SELECT   REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[A-Za-z]+‘) Adress FROM DUAL;

SELECT   REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[[:digit:]]+‘) Adress FROM DUAL;

SELECT   REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[e]$‘) Adress FROM DUAL;

SELECT   REGEXP_SUBSTR (‚(101)202-3300‘, ‚\([[:digit:]]{3}\)‘) telefonnummer  FROM DUAL;

SELECT   REGEXP_SUBSTR (‚(101)202-3300‘, ‚\([[:digit:]]{3}\)[[:digit:]]{3}‘) telefonnummer  FROM DUAL;

SELECT   REGEXP_SUBSTR (‚(101)202-3300‘, ‚\([[:digit:]]{3}\)[[:digit:]]{3}-[[:digit:]]{4}‘) telefonnummer  FROM DUAL;

SELECT   Nachname, REGEXP_SUBSTR (Nachname, ‚(a|r|e)$‘) AS testen
FROM   angestellte;

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚^[[:alpha:] ]+‘)
AS testen
FROM   angestellte;

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[[:alpha:] ]+‘)
AS testen
FROM   angestellte;

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[[:alpha:] ]{8}‘)
AS testen
FROM   angestellte;

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚^(Pa)‘)
AS testen
FROM   angestellte;

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚(er)$‘)
AS testen
FROM   angestellte;

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[a-z]+a‘)
AS testen
FROM   angestellte;

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[[:alpha:] ]+e‘)
AS testen
FROM   angestellte;

—Frage: {} funktionieren nicht…, Namen mit genau einem e

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[[:alpha:] ]+e {2,}‘)
AS testen
FROM   angestellte;

—Frage: {} funktionieren nicht…, Namen mit mehr als 2 e’s

SELECT   Vorname,
Nachname,
REGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚e‘)
AS testen
FROM   angestellte;

SELECT   REGEXP_SUBSTR (‚(101)202-3300‘, ‚[1-3]{3}‘) telefonnummer  FROM DUAL;

SELECT   REGEXP_SUBSTR (‚Weber‘, ‚e.e*e‘) test  FROM DUAL;

SELECT nachname, REGEXP_SUBSTR (nachname, ‚[^Mü.r$]‘) FROM angestellte order by nachname;

SELECT nachname, REGEXP_SUBSTR (nachname, ‚[r$]‘) FROM angestellte order by nachname;
— alle Angestellten, deren Nachname mit r aufhört.

SELECT nachname, REGEXP_SUBSTR (nachname, ‚[B^].+‘) FROM angestellte order by nachname;
— alle Angestellten, deren Nachname mit B anfängt.

Besonderheiten bei den SINGLE ROW Funktionen

Veröffentlicht: von

Für mich ist das ein ziemlich ätzendes Kapitel , aber sei’s drum…

  • DECODE, CASE, NVL  und NULLIF können als Fallunterscheidung im SELECT angewendet werden.
  • NVL2 kann benutzet werden, um NULL-Werte und NOT NULL WErte zu unterscheiden (sieheNVL2 )
  • Die Funktion Coalesce gibt den ersten von NULL verschiedenen Wert aus einer Liste von Werten zurück. Sind alle Werte Null, wird NULL zurückgegeben.
  • PAD heißt auffüllen, dementsprechend gibt es LPAD und RPAD
  • TRIM heißt abschneiden, daher auch RTRIM und LTRIM in SQL
  • INITCAP wandelt den ersten Buchstaben in einen Großbuchstaben um
  • Es gibt eine automatische Datentyp-Konvertierung, die z.B. TEXT || Zahl in Text verwandelt.
  • Der Datentyp TIMESTAMP  WITH DATA TIME ZONE bezieht sich auf den Server, TIMESTAMP  WITH LOCAL TIME ZONE auf den Client , von dem die Datenbank aufgerufen wird. Auf TIMESTAMP  WITH LOCAL  TIME ZONE kann ein Primary Key definiert werden , auf TIMESTAMP  WITH DATA TIME ZONE nicht.
  • DATE ist das einzige Datumsformat, dass keine Sekundenbruchteile abbildet.
  • LAST_DAY gibt den letzten Tag des Monats aus
  • Die Zeitzonen können mit den Funktionen DBTIMEZONE und SESSIONTIMEZONE abgerufen werden.
  • MOD und REMAINDER sind Varianten des Teilens mit Rest, wobei REMAINDER auch negative Zahlen liefern kann. Dies ist dann der Fall, wenn die nächste ganze Zahl,  die geteilt wird, größer als der Divisor ist. Also: MOD(11,3) = 2 und REMAINDER(11,3) = -1.
  • Single Row Functions können beliebig geschachtelt werden, wenn die Wertebereiche zueinander passen.

Fallstricke Kapitel 18

Veröffentlicht: von

  1. GRANT ALL PRIVELIGES to user ist sehr gefährlich, REVOKE ALL PRIVELIGES FROM USER entfernt genau ALL PRIVELIGES wieder, allerdings nur, wenn diese mit GRANT ALL PRIVELIGES vergeben wurden.( Seite  684/685).
  2. GRANT CREATE ANY TABLE to Public ist sehr gefährlich
  3. Bedeutung des Schlüsselwortes ANY bei GRANT
  4. Option : WITH ADMIN –OPTION beachten bzw. WITH GRANT OPTION, beide werden im revoke nicht benutzt
  5. Wenn ein System Privileg gelöscht wird, kaskadiert das nicht. Also wenn ein user1 einem anderen user2  ein Recht weiter gegeben hat und user1 das Recht entzogen wird, hat user2 es noch.
  6. Damit man sich das besser merken kann, ist das bei Objektpriviliegien anders: Die kaskadieren beim Löschen nämlich. Ist das ein Bug oder ein Feature?
  7. Unterschied PUBLIC Synonym und privat SYNONYM beim GRANT
  8. Rollen können System Priviliegien, Object Privilegien und wieder Rollen beinhalten. Sie werden mit CREATE role vergeben.
  9. Eine Rolle kan auch mit GRANT rolle to PUBLIC ; an alle Datenbankbenutzer vergeben werden.

Fallstricke, Kapitel 11

Veröffentlicht: von

ALTER TABLE

  1. Bei einem ALTER-Table MODIFY Spalte gibt es keine automatische Datentypkonvertierung, d.h. das Ändern einer Spalte wird abgewiesen, wenn die Spalte Daten enthält und der Datentyp nicht passt oder die Länge , z.B. VARCHAR(100) nicht ausreicht.
  2. ALTER Table MODIFY Spalte kann nur ausgeführt werden, wenn die zugrunde liegenden  CONSTRAINTS erfüllt bleiben.
  3. Das Schlüsselwort COLUMNS kommt in ALTER TABLE ADD nicht vor: ALTER TABLE Test ADD Spaltenname; ist korrekt.  Aber um die Verwirrung zu erhöhen ist ALTER TABLE Test RENAME COLUMN Spalte to Spalte_neu ; korrekt.
  4. Ein CONSTRAINT löscht man mit einem ALTER TABLE DROP CONSTRAINT, nicht mit DROP CONSTRAINT.
  5. Alle DDL-Befehle, wie ALTER, CREATE oder DROP beinhalten ein automatisches COMMIT.
  6. Man kann eine Spalte in einer Tabelle mit ALTER TABLE XYZ DROP Spalte löschen, alternativ kann man eine Spalte auch auf UNUSED setzen: ALTER TABLE XYZ SET UNUSED  Spalte;.  Allerdings ist die Spalte auch nach einem UNUSED nicht mehr verfügbar (RECOVER) , der Befehl dient nur dazu, die Performance zu erhöhen.
  7. Mit ALTER TABLE table_name DROP UNUSED COLUMNS; werden alle UNUSED COLUMNS gelöscht.
  8. Ein ALTER TABLE DROP PRIMARY KEY oder ALTER TABLE DROP UNIQUE löscht auch den zugrunde liegenden Index.
  9. Ein  CREATE TABLE oder ein ALTER TABLE kann nur eine USING-Index-Klausel benutzen, die sich auf einen Primärschlüssel oder einen UNIQUE-Index bezieht (Frage 10, Seite 481).

FLASHBACK

  1. Mit diesem ORACLE-Befehl kann man komplette Tabellen rekonstruieren, Daten wiederherstellen oder SELECTs auf einem Datenbestand in der Vergangenheit ausführen.
  2. Dazu muss der RECYCLEBIN mit ALTER SESSION SET recyclebin ON; eingestellt sein.
  3. PURGE Table TEST;  oder DROP TABLE Test PURGE;  löscht die Tabelle TEST  aus dem Papierkorb. danach ist kein FLASHBACK mehr möglich.
  4. Man kann mit FLASHBACK eine Tabelle auch auf einen früheren Zeitpunkt (SCN, TIMESTAMP oder RESTORE POINT) zurückspielen, allerdings nur, wenn die Tabelle mit ALTER TABLE test_f ENABLE ROW MOVEMENT; darauf vorbereitet wurde.
  5. Die SCN ist eine Art numerischer Schlüssel für Transaktionen, der vom Oracle-System automatisch mit jeder Transaktion vergeben wird.
  6. Flashbacks können nur ausgeführt werden, wenn vorher kein ALTER TABLE auf die Tabelle ausgeführt wurde.
  7. FLASHBACKS fahren nur den letzten DROP-Befehl zurück, wenn Tabellen zweimal glöscht wurden.

Syntax für flashback

CREATE TABLE Test_f (NR Number);
INSERT into Test_f VALUES (2);
COMMIT;
DROP TABLE test_f;
FLASHBACK TABLE TEST_F TO BEFORE DROP;
SELECT * FROM test_F;

Externe Tabellen

  1. Eine externe Tabelle ist eine Tabelle, deren Metadaten in der Datenbank abgespeichert ist, während die Daten selber außerhalb der Datenbank liegen. Sie werden dazu eingesetzt, Dateien, die normalerweise nicht in der Datenbank gespeichert sind, für einen SELECT zugänglich zu machen. Sie ersetzen damit Werkzeuge wie SQL*LOADER oder Data Pump.
  2. Die Daten einer externen Tabelle liegen in einem Verzeichnis , dass auf dem Datenbankserver mit dem Befehl CREATE DIRECTORY angelegt werden muss.
  3. Externe Tabellen können mit einem SELECT abgefragt werden, aber es kann keine andere DML-Operation darauf angewendet werden.
  4. Externe Tabellen können keine Spalten vom Typ BLOB, CONSTRAINT oder UNUSED Spalten besitzen.

Directory

  1. Ein CREATE DIRECTORY NAME AS ‚Verzeichnis‘, erzeugt einen Zeiger im Dateisystem des Datenbankservers.
  2. DIRECTORY werden für BFiles und externe Tabellen verwendet.
  3. Das Directory ist nur ein Zeiger und prüft nicht, ob das Verzeichnis tatsächlich existiert und beschreibbar ist.
  4. CREATE DIRECTORY erfordert ein eigenen SYSTEM-GRANT und sollte normalerweise nur von Systemadministrator ausgeführt werden.
  5. Lesender /Schreibender Zugriff wird durch ein GRANT READ | WRITE on  directory to user;  vergeben.

Besonderheiten beim Lesen von Daten aus mehreren Tabellen

Veröffentlicht: von

  • Die verschiedenen JOIN -Typen sind schon ziemlich ausführlich im DB-Wiki beschrieben. Ich hoffe mal, alles korrekt, bitte prüfen :-.)
  • USING und ON können nicht bei NATURAL joins verwendet werden.
  • USING braucht keinen Tablealias, auch nicht in der SELECT-List der Spalte , die in using vorkommt.
  • Es kann USING oder ON verwendet werden, aber nicht beides.
  • Fehlt das Wort INNER bzw. OUTER, handelt es sich um einen INNER Join
  • Table alias werden nicht dauerhaft in der Datenbank gespeichert und sind notwendig, wenn Spalten in verschiedenen Tabellen gleich heißen.
  • Es gibt nur einen LEFT OUTER JOIN, einen RIGHT OUTER JOIN und einen FULL OUTER JOIN, OUTER Join,  ohne Vorwort gibt es nicht.
  • Die Schlüsselworte INNER und OUTER sind optional
  • Über JOINS können beliebig viele Tabellen nacheinander verknüpft werden.

Zusammenfassung Kapitel 9

Veröffentlicht: von

Eine Subquery ist ein SELECT-Statement, welches in einem anderen SELECT-Statement existiert. Suqueries können in SELECT-, INSERT-, UPDATE- oder DELETE-Statements eingefügt werden. Subqueries können in der WHERE-Klausel von SELECT-, UPDATE und DELETE-Statements verwendet werden. Sie können in UPDATE..SET Klauseln und in der INSERT Liste verwendet werden.

Typen:

  • Single-Row (gibt eine Zeile zurück)
  • Multiple-Row (kann mehrere Zeilen zurückgeben)
  • Multiple-Column (gibt zwei oder mehr Spalten zurück)
  • Scalar (gibt ein Wert einer Spalte zurück)
  • Correlated

Fallstricke, Kapitel 10, Teil 2

Veröffentlicht: von

Wann sind Views änderbar?

  1. Die zurückliegenden Constraints (z.B. not null) müssen erfüllt sein, d.h. z.B. alle not null Spalten in der View enthalten sein und auch der Primary Key. Das gilt nicht für die zugrunde liegenden Where-Klausel, d.h.Daten, die der Where-Klausel der view nicht entsprechen, sind zwar nach einem insert in die View nicht in der View  selber sichtbar, aber in der zugrunde liegenden Tabelle.
  2. Die View darf weder ein GROUP BY, noch ein DISTINCT noch einen Subselect enthalten.
  3. Die View darf keine hierarchische Abfrage (connect-by) enthalten
  4. Die View darf keinen ALias oder eine konkatenierte Spalte enthalten.
  5. siehe auch Carski
  6. Wenn eine View den Status ‚INVALID‘ hat, kann sie mit ALTER VIEW … COMPILE; wieder übersetzt werden.
  7. Spalten in views, die durch eine Funktion berechnet werden, müssen mit einem Spaltennamen versehen werden.: CREATE VIEW TEST as SELECT to_CHAR(XYZ) Spaltename  FROM Tabelle; der Spaltenname ist obligatorisch.

CONSTRAINTS und Synonyme

  1. Wenn man eine Tabelle löscht, werden alle CONSTRAINTS und Indizes, die an der Tabelle hängen gelöscht. Die Views und Synonyme dagegen bleiben erhalten und werden nur in der Data Dictionary-View user_objects mit dem Status ‚INVALID‘ gekennzeichnet.
  2. Zu den Synonymen gibt es schon einen edb-Onlien-Lexikon-Eintrag:Synonyme im Datenbankwiki.. Dort werden auch die verschiedenen Namensräume unter Oracle beschrieben.

Indexe und deren Gebrauch

  1. Ein Index wird genutzt, wenn die Spalte in einer WHERE-Klausel oder einer ORDER-BY-Klausel auftaucht.
  2. Am schnellsten ist ein Zugriff über „=“, größer als, kleiner funktioniert (in Abhängigkeit von den Daten) nicht immer, <> kann durch den Index nie ausgewertet werden.
  3. LIKE involviert den Index im allgemeinen :-(, wenn der Like-Ausdruck nicht mit einem Wildcard  LIKE  ‚%muster ‚  beginnt.
  4. Eine Funktion wie abs(spalte) setzt den Index außer Kraft, es sein denn, es existiert ein funktionsbasierter INDEX z.B. : CREATE INDEX name on UPPER(spalte);
  5. Seit Oracle 9 gibt es ein automatisches SKIP SCANNING, das dafür sorgt, dass bei einem zusammengesetzten Index (combined Index) mit mehr als einer Spalte der Index auch genutzt wird, wenn die erste Spalte in der WHERE-Klausel nicht enthalten ist. Das SKIP SCANNING ist immer automatisch eingeschaltet. (siehe: SKIP Scannning )
  6. Ein UNIQE INDEX ignoriert NULL -Werte, d. h. eine Spalte, auf der ein UNIQUE INDEX liegt, darf NULL-Werte haben.

Fallstricke zu Kapitel 13

Veröffentlicht: von

  1. zu ROLLUP, CUBE etc. ist einiges im Datenbank-Wiki enthalten:  siehe CUBE und andere.
  2. Wenn in einer GROUPING SET -Klausel ein NULL steht, werden die Aggregation über die komplette Tabelle ausgegeben.
  3. In der ROLLUP-Klausel spielt die Reihenfolge eine Rolle:  Unterschiedliche Ergebnisse haben:

SELECT Abt_nr, Beruf , SUM(Gehalt)
FROM Angestellte
GROUP BY ROLLUP(Abt_nr, Beruf);

SELECT Abt_nr, Beruf , SUM(Gehalt)
FROM Angestellte
GROUP BY ROLLUP(Beruf, Abt_nt);

Beim ersten SELECT werden die Zwischensummen je Abt_nr, beim zweiten die Zwischensummen über die Berufe gebildet. (FAHRRAD/FAHRRAD)

siehe auch: Oracle-SQL-Reference_Rollup und SQL-Reference-Cube

Teilnehmerliste und Organisatorisches zum 15.10.10

Veröffentlicht: von

Große Ereignisse werfen ihren Schatten voraus, in dem Fall die Oracle SQL-Expert-Zertifizierung:-). Ein paar Termine:

  • Der Zertifizieurngstermin/Uhrzeit liegen nun fest: Die Zertifizierung wird am 15.10.10 ab 14 Uhr stattfinden. Gut wäre es, wenn wir uns ca. 13.30 Uhr im Oracle-Schulungszentrum in Düsseldorf einfinden würden
  • Hier ist eine Wegbeschreibung: http://www.oracle.com/global/de/education/maps/ddorf.html. Gesucht ist insbesondere noch ein Fahrzeug, das 4 Studierende von Gummersbach aus mit nimmt. Ich selber werde eher von Norden dazukommen und bin dann auch um 13.30 Uhr vor Ort. Bitte den Blog bzw. diesen Artikel zum Verabreden nutzen.
  • Bitte bei PearsonVue anmelden, wie in der vorigen E-Mail beschrieben
    Achtung: Wer bis zum 11.10.12 nicht angemeldet ist, kann am Test nicht teilnehmen!!!!
  • Teilnehmerliste im Anhang, Fahrgemeinschaften erwünscht, sonst NRW-Ticket
  • Letztes Treffen zur Vorbereitung am Mittwoch, den 13.10.10 ab 14 Uhr in Raum 2.224
  • Frohes Lernen:-)

teilnehmer_2010_10