Database SQL

Danach kann für jede SQLite Datenbank Datei ein neuer Alias benutzt werden. Den gerade angelegten Driver benutzen, bei URL den Pfad zur Datei anhängen, Username und Passwort können frei bleiben.

SQL

SQL Links

SQL Data Retrieval

SELECT

SELECT * FROM foo WHERE myid=42;
SELECT * FROM foo WHERE myid=42 AND myotherid=12;
SELECT foo.myid,bar.otherid FROM foo,bar;
SELECT * FROM foo WHERE mytime BETWEEN
TO_DATE('2007-07-01','yyyy-mm-dd') AND
TO_DATE('2007-07-14 22:09:59','yyyy-mm-dd HH24:MI:SS');
SELECT id, foo FROM bar ORDER BY foo DESC, id ASC;

[$[Get Code]]

Oft will man alle Spalten auswählen und (eine) bestimmte als erstes sehen. Entweder man gibt dann alle Spalten an, oder man verwendet weiterhin * und listet einfach eine der vorhanden Spalten erneut auf. Damit das funktioniert muss man der Tabelle von der man selektiert einen Alias geben und der doppelten Spalte einen neuen Namen

SELECT f.numbers AS numbers2,
f.*
FROM foo f

[$[Get Code]]

SELECT DISTINCT

Mehrfach vorkommende Zeilen werden nur einfach ausgegeben

SELECT DISTINCT myname FROM foo;

[$[Get Code]]

SELECT CASE

CASE ersetzt Gelesenes durch eigene Inhalte, abhängig vom Gelesen.

SELECT foo, CASE
WHEN b.x<0 THEN 'S'
WHEN b.x>0 THEN 'G'
ELSE 'E'
END AS tg
FROM bar b WHERE IS NOT NULL b.x;

[$[Get Code]]

SELECT DECODE

DECODE ersetzt den Inhalt einer Spalte durch die vorgegebene Abbildung

SELECT decode(A,
42, 'Mr Bar',
3, 'Mrs Bar'
) FROM foo

[$[Get Code]]

INTERSECT

Liefert Schnittmenge beider Tabellen

SELECT myname FROM foo INTERSECT SELECT myname FROM bar;

[$[Get Code]]

MINUS

Listet die Werte aus der ersten Tabelle abzüglich der Werte die auch in der zweiten Tabelle sind

SELECT myname FROM foo
MINUS
SELECT myname FROM bar;

[$[Get Code]]

Die Inhalte zweier Tabellen vergleichen

Durch welchen Zeilen unterscheiden sich zwei Tabellen? Hier sogar zwei Tabellen in verschiedenen Datenbanken:

SELECT * FROM
(
SELECT 'MYDB' DB_NAME, d.* FROM
(
SELECT * FROM foo.bar minus
SELECT * FROM foo.bar@OTHERDB
) d
UNION
SELECT 'OTHERDB' DB_NAME, d.* FROM
(
SELECT * FROM foo.bar@OTHERDB minus
SELECT * FROM foo.bar
) d
)

[$[Get Code]]

Alias

SELECT foo.nm "Name", SUM(foo.nr) "Total Number" FROM foo ...;

[$[Get Code]]

In der Ausgabe steht statt "nm" dann "Name" und statt "sum(nr)" "Total Number".

JOIN

Verbindet alle Elemente aus einer Tabelle mit allen Elementen aus einer anderen Tabelle (auf Wunsch auch aus der gleichen). Beispiel mit zwei Tabellen. foo:

myid	name
1	Peter
2	Paul
3	John
Frank

bar:

myid	age
1	22
2	51
33

INNER JOIN

Liefert alle Werte aus der ersten und der zweiten Tabelle bei der die Vergleichsschlüssel übereinstimmen. Ist der Vergleichsschlüssel in einer der beiden Tabellen leer wird er nicht angezeigt. Im obigen Beispiel erscheint also weder Frank, noch John, noch 33 wenn der Vergleichsschlüssel myid ist

NATURAL JOIN

Wie INNER JOIN, nur dass man den Vergleichsschlüssel nicht angeben muss wenn es in beiden Tabellen eine Spalte gleichen Namens gibt (im obigem Beispiel myid).

RIGHT OUTER JOIN

Findet man zu einem Eintrag in der rechten Tabelle keinen Eintrag in der linken Tabelle würde er bei einem normalen JOIN gar nicht angezeigt. Da man dabei Einträge verlieren würde, die man möglicherweise sehen möchte, werden solche Einträge bei einem RIGHT OUTER JOIN angezeigt und die Spalten aus der linken Tabelle werden mit null aufgefüllt. Ein typischer Fall, in dem kein passender Eintrag in der linken Tabelle gefunden werden kann, ist wenn die Vergleichspalte in einer der beiden Tabellen (oder sogar in beiden) null ist.

LEFT OUTER JOIN

Wie RIGHT OUTER JOIN, nur dass Einträge aus der linken Tabelle auch dann angezeigt werden wenn es keinen passenden Eintrag in der rechten Tabelle gibt.

SELECT name FROM foo JOIN bar WHERE foo.myid=bar.myid;
SELECT name FROM foo NATURAL JOIN bar;
SELECT name FROM foo NATURAL JOIN bar WHERE foo.myid=42;
SELECT * FROM foo LEFT OUTER JOIN bar;
SELECT * FROM foo RIGHT OUTER JOIN bar ON foo.myid=bar.myid;

[$[Get Code]]

In Oracle schreibt man statt einem normalen JOIN (INNER JOIN) mit Komma getrennt die Tabellen, NATURAL JOIN schreibt man dagegen wieder, bei einem LEFT oder oder RIGHT OUTER JOIN markiert man mit (+) die Seite die bei einem Vergleich nicht gefunden werden muss und dann mit null aufgefüllt wird.

SELECT a.name, SUM(a.nr) a.nr FROM foo a, bar b WHERE a.name = b.name(+) GROUP BY a.name;
SELECT a.name, SUM(a.nr) a.nr FROM foo a, bar b WHERE a.name(+) = b.name GROUP BY a.name;

[$[Get Code]]

UNION

Der Ausgabe von zwei Selects wird untereinander gemischt. Die Spalten beider Selects müssen die gleiche Datentypen haben. Doppelte Zeilen werden entfernt. Mit UNION ALL erhält man auch die doppelten Einträge.

SELECT myname FROM foo UNION SELECT myname FROM bar;

[$[Get Code]]

ORA-00932: inconsistent datatypes: expected - got CLOB

[$[Get Code]]

Das kann passieren, wenn man ein UNION macht das einen CLOB behinhaltet. Von einem CLOB kann man nicht mit distinct selektieren, ein UNION beinhaltet aber ein distinct. Daher dann UNION ALL benutzen.

LIKE

Alles was mit Peter anfängt bzw. alles was mit Peter anfängt und danach einen beliebigen Buchstaben hat (z.B. "Peters")

SELECT * FROM x WHERE name LIKE 'Peter%';
SELECT * FROM x WHERE name LIKE 'Peter_'

[$[Get Code]]

Wenn man mit LIKE sucht, arbeitet das Zeichen _ auch als Wildcard. Folgendes

SELECT * FROM FOO WHERE x LIKE 'BAR_%'

[$[Get Code]]

sucht also erfolgreich nicht nur nach BAR_X sondern auch nach BARE.

Möchte man das nicht, definiert man sich das Zeichen als Escape Zeichen und nutzt es

SELECT * FROM FOO WHERE x LIKE 'BAR_%' escape '';

[$[Get Code]]

GROUP BY

Zeilen mit gleichem Inhalt in bestimmten Spalten zusammenfassen, z.B. zum Aufaddieren

SELECT MYID1,SUM(MYYESNOCOL) AS MYSUM1 FROM MYTABLE1 WHERE MYTABLE1.MYID1=MYTABLE1.MYID2 GROUP BY MYTABLE1.MYYESNOCOL;

[$[Get Code]]

GROUP BY Strings zusammen fassen

In Oracle kann man den Inhalt mehrerer varchar Zellen in einem GROUP BY aggregieren. Hat man z.B. diese Tabelle

SELECT * FROM FOO;

[$[Get Code]]

ID	LastName
1	Frank
1	Peter
2	John

Und möchte alle Zeilen mit der gleichen ID zusammenfassen, geht das so

SELECT id, LISTAGG(LastName, ' : ') WITHIN GROUP (ORDER BY LastName) FROM FOO GROUP BY id;

[$[Get Code]]

1	Frank : Peter
2	John

HAVING

Gegeben ist folgende Tabelle:

MYID	MYNAME
100	LA
200	SD
150	LA
151	LA
300	SD
500	FR

Jetzt sollen

Reihen mit bestimmten Namen ausgewählt werden (WHERE Bedingung)
alle mit gleichem Namen gruppiert werden (GROUP BY)
pro gruppierten Element eine Summe gebildet werden (SUM)
nur die Summen angezeigt werden die eine bestimmte Größe haben (HAVING)

SELECT myname, SUM(myid) FROM foo WHERE ( myname='ABC' OR myname='DEF' )
GROUP BY myname HAVING SUM(myid) > 300;

[$[Get Code]]

GROUP BY jeweils nur eine beliebige Zeile ausgeben

Gegeben dieses Beispiel:

A	B	C
1	42	44
1	55	11
2	12	18

Es gibt offenbar mehrere Zeilen in denen A gleich ist. Über diese will man gruppieren. Will man mehr als nur Spalte A ausgeben (also z.B. auch B und C) muss man über die anderen Zeilen eine Aggregatfunktion legen (sonst gäbe es ja potentiell mehrere mögliche Werte für B und C die ausgegeben werden müssten). Oft will man aber einfach nur irgendeine der gruppierten Zeile haben.

Gruppiert man z.B. über A und wählt für B und C min()

SELECT a, MIN(b), MIN(c) FROM foo GROUP BY a

[$[Get Code]]

erhält man aber keine der Zeilen der DB sondern eine neue Zeile in der jeweils die Minimalwerte pro Spalte eingetragen sind.

A	B	C
1	42	11
2	12	18

Hier hat man z.B. die 42 aus der 1. Zeile und die 11 aus der 2. Zeile.

Man kann in Oracle aber auch gezielt die erste oder die letzte Zeile mit A=1 auswählen (erste oder letzte bezogen auf ein Sortierkriterium).

KEEP (DENSE_RANK FIRST ORDER BY B,C)

[$[Get Code]]

Damit hat man für zwar sicher für jede Gruppe nur noch höchstens eine Zeile, es wird aber dennoch eine Aggregatsfunktion erwartet. Man kann jetzt einfach min() oder max() nehmen, da wir nur noch höchstens eine Zeile haben macht das keinen Unterschied.

SELECT a,
MIN(b) KEEP (DENSE_RANK FIRST ORDER BY b,c) "B",
MIN(c) KEEP (DENSE_RANK FIRST ORDER BY b,c) "C"
FROM foo
GROUP BY a;

[$[Get Code]]

Damit erhält man das Gewünschte:

A	B	C
1	42	44
2	12	18

Will man statt der ersten die letzte Zeile haben nicht versuchen das mit min() oder max() auszuwählen (ändert nichts weil wir nur noch eine Zeile haben, da ist min()==max()) sondern FIRST gegen LAST austauschen (oder die Sortierung ändern).

Over Partition By

Angenommen man hat eine solche Tabelle:

NAME	AGE	TEAM
John	42	1	Frank	55	1
Peter	55	2	Jane	28	2

Und man möchte Information gruppiert nach Team betrachten. Das geht zwar mit einem group by, aber dabei verliert man Informationen, weil ja dabei Zeilen zusammengefasst werden.

Mit einem

OVER (PARTITION BY ...)

[$[Get Code]]

kann man jetzt Informationen pro Team ermitteln und trotzdem alle Zeilen behalten. Z.B. wie ist das kleinste Alter pro Team:

SELECT name, age, team, MIN(age) OVER (PARTITION BY team) AS min_age_for_one_team FROM f052653.over_the_moon

[$[Get Code]]

NAME	AGE	TEAM	MIN_AGE_FOR_ONE_TEAM		John	42	1	42	Frank	55	1	42	Peter	55	2	28	Jane	28	2	28

Dabei muss die letzte Spalte, die vor dem OVER steht, eine Aggregatsfunktion sein.

Innere Abfrage

Erste Abfrage bezieht sich auf Ergebnis einer zweiten

SELECT SUM(mynr) FROM foo
WHERE myname IN (SELECT myothername FROM bar WHERE myid > 5 );
UPDATE foo SET foo.price=(SELECT bar.price FROM bar WHERE foo.id=bar.id);

[$[Get Code]]

So kann man die Ergebnisse eines Select Statements für ein Updatestatement benutzen (das Select Statement darf höchstens eine Zeile finden)

UPDATE foo f SET (id, name) =
(
SELECT b.number,b.description
FROM bar b
WHERE f.ref = s.ref
)

[$[Get Code]]

Inner Abfrage mit exists

Bei where x in (...) abfragen interessiert man sich ja nur dafür, ob x in der inneren Abfragen überhaupt vorkommt. Es ist also gar nicht notwendig die Werte aus der inneren Abfrage wirklich alle zu ermitteln. Daher kann man die Abfrage auch mit exists umschreiben. Falls Oracle das nicht schon selbst optimiert hat, kann man damit auf eine Performance Verbesserung hoffen.

SELECT SUM(mynr) FROM foo
WHERE EXISTS (SELECT bar.myothername FROM bar WHERE foo.myname=bar.myothername AND bar.myid > 5);

[$[Get Code]]

IN Bedingung an mehrere Spalten gleichzeitig stellen

Man hat eine Bedingung an mehrere Spalten, z.B.

SELECT * FROM foo WHERE ( a=42 AND b=9 ) OR ( a=3 AND b=1)

[$[Get Code]]

Das kann man eigentlich nicht über eine WHERE IN Anweisung machen, mit einem Trick aber doch. Einfach die betroffenen Spalten zu einer zusammenfügen und auf das Zusammengesetzte testen

SELECT * FROM foo WHERE a || '_' || b IN ( '42_9', '3_1' )

[$[Get Code]]

Nutzt man dagegen ein Subselect kann man auch WHERE IN Abfragen mit mehr als einer Spalte durchführen

SELECT * FROM foo WHERE (a, b) IN (SELECT a, b FROM bar)

[$[Get Code]]

In einem CLOB suchen

SELECT * FROM BLOBTEST WHERE MYCLOB LIKE '%Bar%'
SELECT * FROM BLOBTEST WHERE instr(MYCLOB, 'Bar')>0

[$[Get Code]]

Anzahl der Ergebniszeilen beschränken

Nur eine begrenzte Anzahl an Zeilen bekommen

MYSQL

SELECT a FROM foo LIMIT 20;

[$[Get Code]]

ORACLE

SELECT a FROM foo WHERE ROWNUM<=20;

[$[Get Code]]

Verkettete Listen connect by

Man hat eine solche Tabellenstruktur, in der jeder Zeile (hier über NEXTID -> ID) auf eine andere Zeile verweisen kann. Fängt man mit einem Eintrag an, kann man die ganze Kette ablaufen:

ID	VALUE	NEXTID
7	a	13
13	\|k	\|1
1	\|e	\|42
2	x	4
4	y	5
5	z	5
11	S	3
3	n	7

Wie findet man jetzt alle Zeilen die zu einer Startzeile gehören? Die Schwierigkeit dabei ist, dass man vorher nicht wissen kann, wie viele Zeilen das sein werden, mit einem Join kommt man hier daher nicht weit.

SELECT VALUE FROM foo f START WITH f.id=11 CONNECT BY f.id = prior f.nextid;

[$[Get Code]]

VALUE	S	n	a	k	\|e

DML Data Manipulation Language

INSERT, UPDATE, DELETE

INSERT INTO MYTABLE1 (MYCOL1, MYCOL2, ...) VALUES (MYVALUE1, MYVALUE2, ...);
DELETE FROM MYTABLE1 WHERE MYCOL1 = 'MYVALUE1';
UPDATE MYTABLE1 SET MYCOL1='MYVALUE3', MYCOL2='MYVALUE4' WHERE MYCOL2='MYVALUE2';

[$[Get Code]]

Neue Einträge aus vorhandenen erzeugen

INSERT INTO foo (a, b, c)
SELECT a, '42' AS b, c FROM bar WHERE a IS NOT NULL

[$[Get Code]]

MERGE (REPLACE)

Eine Tabelle in eine andere einmischen. Je nachdem ob die aktuelle Zeile schon vorhanden ist oder nicht kann ein anderer Befehl zum Einmischen benutzt werden.

MERGE INTO
TABLE_DEST foo
USING
TABLE_SOURCE bar
ON (FOO.id = bar.id)
WHEN MATCHED THEN UPDATE SET foo.txt = bar.txt
WHEN NOT MATCHED THEN INSERT (id, txt) VALUES (bar.id, bar.txt);

[$[Get Code]]

Ersetzen von String durch andere

So kann man einen String durch einen anderen ersetzen

SELECT REPLACE('TestEn', 'e', 'a') FROM dual

[$[Get Code]]

Dabei ist die Groß- Kleinschreibung aber relevant. Es wird also nur das kleine e ersetzt. Möchte man statt dessen alle Vorkommnisse ersetzen

SELECT REGEXP_REPLACE('TestEn','e','a', 1, 0, 'i') FROM dual

[$[Get Code]]

1 und 0 (fange mit dem 1. Zeichen an und ersetze unbegrenzt viele Zeichen) sind eigentlich optional, hier aber notwendig um den Parameter 'i' angeben zu können.

SELECT REGEXP_REPLACE('TEst','e','x', 1, 0, 'i') FROM dual

[$[Get Code]]

Substrings

Angenommen man hat folgenden String

START:World Hello:END

[$[Get Code]]

Und möchte den Substring zwischen den beiden : erhalten:

SELECT text, regexp_substr(text, ':.*:') FROM
(
SELECT 'Start:World Hello:End' AS text FROM dual
)

[$[Get Code]]

Ausgabe:

:World Hello:

[$[Get Code]]

Die ersten oder letzten Einträge bezüglich einer Sortierung

Angenommen man hat die Spalten a,b. Jetzt möchte man die Tabelle gedanklich nach b sortieren und interessiert sich dann für die erste 3 Zeilen nach dieser Sortierung

SELECT a, b FROM (
SELECT
a,b,
RANK() OVER (ORDER BY b) r
FROM
FOO.BAR
)
WHERE
r<4;

[$[Get Code]]

DDL Data Definition Language

Achtung, DDL Statements müssen nicht per Commit abgesendet werden, sondern werden sofort aktiv.

CREATE

MySQL CREATE TABLE

CREATE DATABASE MYDB1;
CREATE TABLE MYTABLE1 ( MYCOL1 SERIAL, MYCOL2 TEXT NOT NULL);

CREATE TABLE IPAdressen (IPid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,IPadr VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE Email (EMAILid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,EMAILadr VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE URL (URLid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,URLadr VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE PATH (PATHid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PATHtxt VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE PUBTYPE (PUBTYPEid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PUBTYPEtxt VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE LANG (LANGid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,LANGtxt VARCHAR(2) NOT NULL UNIQUE);
CREATE TABLE PAGEID (PAGEIDid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PAGEIDtxt VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE UAGENT (UAGENTid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,UAGENTtxt VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE HTMLERRLOG (HTMLERRLOGid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY, PATHid BIGINT UNSIGNED NOT NULL,
URLid BIGINT UNSIGNED NOT NULL, IPid BIGINT UNSIGNED NOT NULL, HTMLERR BIGINT UNSIGNED NOT NULL, PAGEIDid BIGINT UNSIGNED NOT NULL,
COUNT BIGINT UNSIGNED NOT NULL DEFAULT 1, LASTMOD TIMESTAMP );

[$[Get Code]]

Man kann aus einem Select auch per DDL Statement eine neue Tabelle erzeugen. Geht viel schneller als per Insert und Subselect.

CREATE TABLE foo AS SELECT * FROM bar WHERE id>42;

[$[Get Code]]

Eine Tabelle anlegen und gleichzeitig einen Constraint vergeben

CREATE TABLE mytable (
id NUMERIC(10) NOT NULL,
name VARCHAR2(50) NOT NULL,
age NUMERIC(3) NOT NULL,
CONSTRAINT constr_id_name UNIQUE (id, name)
)

[$[Get Code]]

Er kann aber auch im Nachhinein einen Contstraint vergeben

ALTER TABLE mytable ADD CONSTRAINT constr_id_name PRIMARY KEY (ID)

[$[Get Code]]

Siehe auch

Eigene Datentypen erzeugen

So kann man sich (in Oracle) einen eigenen Datentyp deklarieren

CREATE OR REPLACE TYPE address AS OBJECT
(
street CHAR(20),
city CHAR(20)
)

[$[Get Code]]

den man dann als Spaltentyp benutzen kann

CREATE TABLE foo
(
myname CHAR(20),
myaddress address)

[$[Get Code]]

So füllt man diese Tabelle dann

INSERT INTO foo VALUES
(
'John',
address('Main Street',
'NY'
)
)

[$[Get Code]]

So liest man daraus

SELECT f.myaddress.street FROM foo f

[$[Get Code]]

(an dieser Stelle ist das f. wohl nicht optional)

Info zum Datentypen anzeigen

DESC address

[$[Get Code]]

VIEWS

Aus vorhandenen Spalten eine virtuelle Tabelle machen

CREATE VIEW v_myview AS
SELECT myname, myid FROM foo;

[$[Get Code]]

Geht auch über mehrere Tabellen

CREATE VIEW v_myview AS
SELECT foo.myname, bar.myothername FROM foo,bar
WHERE foo.myid = bar.myid GROUP BY foo.myid;

[$[Get Code]]

Vorsicht: Wenn man eine View mit einem * select erzeugt:

CREATE VIEW v_myview AS
SELECT * FROM foo;

[$[Get Code]]

Wird das während der Erzeugung der View ausgewertet und ist danach statisch, das heißt, kommen in der Tabelle auf das sich das View bezieht neue Spalten hinzu sind diese nicht automatisch in der View enthalten.

Oracle Inlineviews

In Oracle kann man eine Subquery über eine With Clause so definieren, dass sie wie eine Inline View erscheint. Das macht Statements mit vielen Subqueries übersichtlicher und leichter zu pflegen

WITH HotEntries AS
(
SELECT * FROM a WHERE STATUS=’active’
),
SmallEntries AS
(
SELECT * FROM b WHERE x<100
)
SELECT f.id, f.name, b.name FROM HotEntries f, SmallEntries b WHERE f.id=b.id

[$[Get Code]]

Invalide Objekte

Hat man z.B. eine View A, die auf eine andere View B zeigt, und die View B wird verändert, ist View A invalide. Man kann nicht wissen ob die View B überhaupt noch die Spalten anbietet, die View A nutzt.

Alle invalide Objekte auflisten

SELECT * FROM ALL_OBJECTS a WHERE a.status <> 'VALID'

[$[Get Code]]

Valide werden views indem man entweder einfach von ihnen liest, oder aber durch ein COMPILE.

ALTER VIEW foo.bar COMPILE

[$[Get Code]]

So erhält man automatisch Statements für alle invaliden Views

SELECT 'ALTER VIEW ' || o.owner || '.' || o.OBJECT_NAME || ' COMPILE;' FROM all_objects o WHERE status<>'VALID' AND OBJECT_TYPE='VIEW'

[$[Get Code]]

Alle invaliden Synonyme auflisten

SELECT * FROM dba_synonyms s WHERE table_owner NOT IN('SYSTEM','SYS') AND db_link IS NULL AND NOT EXISTS
(SELECT 1 FROM dba_objects o WHERE s.table_owner=o.owner AND s.table_name=o.object_name)

[$[Get Code]]

Materialized Views

Wie normale Views, nur dass nicht bei jedem Zugriff auf die View die Daten aus den Quelltabellen bezogen wird. Statt dessen werden die Daten nur manuell bezogen und danach werden zwischengespeicherte Werte ausgeliefert. Anlegen

CREATE MATERIALIZED VIEW foo.bar AS
SELECT * FROM blub.mytable

[$[Get Code]]

Relevante Rechte:

GRANT CREATE MATERIALIZED VIEW TO foo;
GRANT CREATE ANY MATERIALIZED VIEW TO foo;
GRANT ALTER ANY MATERIALIZED VIEW TO foo;
GRANT DROP ANY MATERIALIZED VIEW TO foo;
GRANT EXECUTE ON dbms_snapshot TO foo;
GRANT SELECT ANY TABLE TO foo;
GRANT CREATE ANY TABLE TO foo;
GRANT ALTER ANY snapshot TO foo;

[$[Get Code]]

Die manuelle Aktualisierung geht so

EXEC DBMS_MVIEW.REFRESH('foo.bar', 'C', NULL, FALSE, TRUE, 1,2, NULL, TRUE);

[$[Get Code]]

NULL Spalten und ihr Datentyp

Angenommen man hat ein select in dem eine Spalte für alle Zeilen null ist

SELECT a,b,c,NULL,d FROM foo

[$[Get Code]]

Dann ist der Datentyp dieser Spalte nicht automatisch zu ermitteln. Das kann z.B. Probleme machen, wenn dieses Select Teil einer View werden soll Mit einigen Tricks kann man den Datentyp aber beeinflussen. So erhält man z.B. als Datentype eine numerische null Spalte

SELECT a,b,c,(NULL + 0),d FROM foo

[$[Get Code]]

SELECT a,b,c,to_date(NULL),d FROM foo

[$[Get Code]]

Mit einem cast kann man sogar beliebige Datentypen erreichen. Ein eingeschobenes decode, welches nie greift verhindert dass der Trick vom cast bemerkt wird

SELECT a,b,c,CAST(decode('A', 'A', NULL) AS varchar2(255)),d FROM foo

[$[Get Code]]

DROP

Tabelle löschen

DROP TABLE foo;

[$[Get Code]]

TRUNCATE

Tabelle leeren

TRUNCATE TABLE foo;

[$[Get Code]]

ALTER

Tabellenspalten hinzufügen oder umbenennen

ALTER TABLE foo ADD myname VARCHAR;
ALTER TABLE foo CHANGE mynaame myname CHAR(50);
ALTER TABLE foo.bar RENAME COLUMN blub TO newblub;
ALTER TABLE foo.bar ADD blub varchar2(1);
ALTER TABLE foo.bar DROP COLUMN blub;

[$[Get Code]]

Eine Tabelle umbennen

ALTER TABLE USER.bar RENAME TO foo;

[$[Get Code]]

Beim Zielnamen ist das Schema nicht anzugeben.

Das Schema (den aktiven User) wechseln

ALTER SESSION SET CURRENT_SCHEMA = foo

[$[Get Code]]

Constraints

So kann man erreichen, dass bestimmte Spalten immer bestimmte Bedingungen erfüllen müssen

CREATE foo
(
a NUMBER,
b NUMBER,
c NUMBER,
CHECK (a > b)
);

[$[Get Code]]

Kann man auch für eine vorhandene Tabelle nachreichen

ALTER TABLE foo ADD CONSTRAINT foo_check_1 CHECK (a > b);

[$[Get Code]]

Constraint der erzwingt dass der Wert in einer Spalte mit to_date() umgewandelt werden kann. Der Vergleich wird so gewählt, dass er immer erfüllt wird.

ALTER TABLE foo.bar ADD CONSTRAINT constr_to_date_possible CHECK
(
to_date(foo,'dd.mm.yyyy')>to_date('01.01.1900', 'dd.mm.yyyy')
);

[$[Get Code]]

RENAME

ALTER TABLE foo RENAME COLUMN mynaame TO myname;

[$[Get Code]]

Disable

So kann man einen Constraint deaktivieren, aktivieren, entfernen (vor dem Constraint Namen darf das Schema nicht wiederholt werden)

ALTER TABLE foo.bar DISABLE CONSTRAINT my_constraint;
ALTER TABLE foo.bar ENABLE CONSTRAINT my_constraint;
ALTER TABLE foo.bar DROP CONSTRAINT my_constraint;

[$[Get Code]]

DCL Data Control Language

GRANT

Benutzer Rechte auf Datenbank gewähren

GRANT SELECT, INSERT ON customers TO webuser;

[$[Get Code]]

(MySQL)

GRANT ALL PRIVILEGES ON MYDB1.* TO 'USER1'@'www1.EXAMPLE.COM' IDENTIFIED BY 'MYSECRETPASSWORD' WITH GRANT OPTION;

[$[Get Code]]

Spaltenweise Rechte vergeben

GRANT UPDATE (age) ON foo.people TO bar;

[$[Get Code]]

With Grant Option

Wenn ein man eine Tabelle in einem Schema hat, und in einem anderen Schema soll eine View Zugriff auf diese Tabelle erhalten, dann braucht sie dafür entsprechende Rechte. Wenn diese View die Daten ihrerseits wieder an andere Schemata weiterreichen soll, braucht man dafür WITH GRANT OPTION. Folgendes Beispiel

So kann man PUBLIC select auf eine Tabelle erlauben und ihm auch erlauben, selbst andere zu berechtigen (with grant option). Folgendes erlaubt PUBLIC nicht nur den Zugriff auf foo.bar, sondern erlaubt es PUBLIC selbst wieder Zugriff auf Daten aus der Tabelle an andere zu gewähren.

GRANT SELECT ON foo.bar TO public WITH GRANT OPTION;

[$[Get Code]]

REVOKE

REVOKE ... FROM foouser;

[$[Get Code]]

Atomare Operationen / Transaktionen

Mehrer SQL Anweisung können zu einer atomaren Operation verbunden werden

BEGIN; SELECT ...; UPDATE ...; END;

[$[Get Code]]

In Oracle nutzt man immer automatisch eine Transaktion (kein BEGIN notwendig), man muss COMMIT benutzten um die Änderungen zu übernehmen. Mit ROLLBACK kommt man zu Anfang der Transaktion oder zum letzten SAVEPOINT zurück.

Flashback

In Oracle gibt es neben dem Rollback noch eine leistungsfähigere Möglichkeit, ungewollte Änderungen zurückzurollen. Angenommen man hat versehentlich eine Tabelle gelöscht

DROP TABLE foo.bar

[$[Get Code]]

Kann der DBA sie so wieder herstellen

flashback TABLE foo.bar TO BEFORE DROP

[$[Get Code]]

Eingebaute Funktionen

SUBSTR

Den Namen ab der Stelle 3 und dann die nächsten 2 Zeichen

SELECT SUBSTR(name, 3,2) FROM foo WHERE name = 'Bar';

[$[Get Code]]

Die letzten 2 Stellen abschneiden

SELECT SUBSTR(foo, 1, LENGTH(foo)-2) AS foo2 FROM bar;

[$[Get Code]]

Den Text zwischen dem 2. und 3. Komma ausschneiden

SELECT SUBSTR(s,
instr(s, ',',1,2)+2,
instr(s, ',',1,3)-instr(s, ',',1,2)-2
)
FROM
(
SELECT 'AAA, BBB, CCC, DDD, EEE' s FROM dual
)

[$[Get Code]]

CONCAT (||)

Macht aus 2 Spalten eine durch zusammensetzten der beiden Spalten

SELECT CONCAT(name,id) FROM foo WHERE name = 'Bar';

[$[Get Code]]

SUM

Werte aufsummieren.

COUNT

Vorkommen zählen.

Aktuelles Datum beziehen

SELECT SYSDATE FROM dual;

[$[Get Code]]

Datum letzter Montag / letzter Freitag

SELECT
next_day(trunc(sysdate, 'DDD'),'FRI')-11 AS start_monday_last_week,
next_day(trunc(sysdate, 'DDD'),'FRI')-6 AS end_friday_last_week
FROM dual;

[$[Get Code]]

Das letzte Quartal ermitteln

SELECT
TRUNC(SYSDATE, 'Q') AS q_prev,
add_months(TRUNC(SYSDATE, 'Q'), -3) AS q
FROM dual;

[$[Get Code]]

Datum letzter Monat

SELECT
add_months(TRUNC(sysdate, 'Month'), -1) AS s,
add_months(TRUNC(sysdate, 'Month'), 0) AS e
FROM dual;

[$[Get Code]]

Teile eines Datums extrahieren

SELECT EXTRACT(YEAR FROM sysdate) FROM dual;
SELECT EXTRACT(DAY FROM sysdate) FROM dual;

[$[Get Code]]

Oder aber mit to_char

Spracheinstellungen und Wochentage

Aufgepasst, die Datumsfunktionen in Oracle reagieren je nach Spracheinstellungen anders!

Tag heißt anders

SELECT
to_char (sysdate, 'FmDay', 'nls_date_language=german'), -- Dienstag
to_char (sysdate, 'FmDay', 'nls_date_language=english') -- Tuesday
FROM dual;

[$[Get Code]]

Wochen fangen an anderen Tagen an

-- 2
ALTER SESSION SET nls_territory = 'UNITED KINGDOM';
SELECT to_char(sysdate, 'd') FROM dual;

-- 3
ALTER SESSION SET nls_territory = 'AMERICA';
SELECT to_char(sysdate, 'd') FROM dual;

[$[Get Code]]

Aber so kann man erzwingen dass die Wochentage immer gleich geliefert werden und auch der Wochenbegin vorhersagbar ist

-- Get the day of the week in a numeric and predicable form
SELECT
CASE to_char (sysdate, 'FmDay', 'nls_date_language=english')
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'sunday' THEN 7
END dow
FROM dual

[$[Get Code]]

Ein Datum runden

Auf Mitternacht auf oder abrunden

SELECT round(sysdate, 'DDD') FROM dual

[$[Get Code]]

Abrunden auf Mitternacht

SELECT trunc(sysdate, 'DDD') FROM dual

[$[Get Code]]

4 Stunden vor Mitternacht

SELECT trunc(sysdate, 'DDD')-(4/24) FROM dual

[$[Get Code]]

Mitternacht letzter Sonntag

SELECT trunc(sysdate, 'DAY') FROM dual

[$[Get Code]]

Mitternacht letzter Tag des vergangenen Jahres SELECT trunc(sysdate, 'IYYY') FROM dual

to_char

Mit to_char kann man Zahlen und Datumsangaben formatieren.

SELECT to_char(42, '99.99') FROM dual -- 42.00
SELECT to_char(42, '$99.99') FROM dual -- $42.00
SELECT to_char(42, '000099') FROM dual -- 000042
SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM dual -- 2008-01-19 18:38:51

[$[Get Code]]

Achtung, to_char reserviert auch bei positiven Zahlen ein Zeichen für ein mögliches Vorzeichen. Damit erhalten damit konvertierte positive Zahlen immer ein führendes Leerezeichen, was zu Problemen führen kann. Mit FM kann man dieses Verhalten steuern

SELECT
LENGTH('123') AS A,
LENGTH(to_char(123, '000')) AS B1,
LENGTH(to_char(123, 'FM000')) AS B2
LENGTH(to_char(-123,'000')) AS c
FROM dual

[$[Get Code]]

Nicht numerische Werte in einer Spalte finden

SELECT DISTINCT foo FROM bar WHERE UPPER(foo)!=LOWER(foo);

[$[Get Code]]

Nachkommastellen einer Zahl

Die Nachkommastellen einer Zahl ermitteln

SELECT REGEXP_REPLACE(to_char(12.3456),'^[^.]*[.]*','') FROM dual;

[$[Get Code]]

Die Nachkommastellen einer Zahl zählen

SELECT nvl(LENGTH(REGEXP_REPLACE(to_char(12.3456),'^[^.]*[.]*','')),0) FROM dual;

[$[Get Code]]

Spalten mit Nachkommastellen

ORA-01438: VALUE larger than specified PRECISION allowed FOR this COLUMN

[$[Get Code]]

Mit number(a,b) erzeugt man eine nummerische Spalte mit b Nachkommastellen und a-b Stellen vor dem Komma. Mit number(9,6) z.B. hat man 6 Nachkommastellen und 3 Stellen vor dem Komma.

CREATE TABLE mytable (id NUMBER PRIMARY KEY, mynumericcolumn NUMBER(9,6));

INSERT INTO mytable (id, mynumericcolumn) VALUES (1, 1.0);
INSERT INTO mytable (id, mynumericcolumn) VALUES (4, 123.456789);
INSERT INTO mytable (id, mynumericcolumn) VALUES (5, 1000.456789);
-- Error: ORA-01438: value larger than specified precision allowed for this column

ALTER TABLE mytable MODIFY mynumericcolumn NUMBER(10,6);
INSERT INTO mytable (id, mynumericcolumn) VALUES (5, 1000.456789);

[$[Get Code]]

TRIM

SELECT LTRIM(' FOO ') FROM dual;

[$[Get Code]]

LPAD

Einen String links mit Buchstaben auffüllen, bis er eine vorgegeben Gesamtlänge erreicht.

lpad('7', 3, '0')

[$[Get Code]]

Datenbanken und Tabellen Anlegen und Verwalten

SHOW DATABASES;
SHOW TABLES;
DESCRIBE MYTABLE1;

[$[Get Code]]

Schlüssel

Es kann nur einen Primary Key geben, aber mehrer Unique Keys.

Primärschlüssel

Eine Spalte als Primärschlüssel definieren: Oracle

CREATE TABLE Customer (SID INTEGER PRIMARY KEY, Last_Name VARCHAR(30));

[$[Get Code]]

Andere

CREATE TABLE Customer (SID INTEGER, Last_Name VARCHAR(30));
ALTER TABLE Customer ADD PRIMARY KEY (SID);

[$[Get Code]]

Vorhandene Tabelle soll technischen Schlüssel bekommen

Eine neue Spalte hinzufügen und mit eindeutigen Werten initialisieren

ALTER TABLE foo.bar ADD myid NUMBER UNIQUE;
UPDATE foo.bar x SET x.myid=rownum;

[$[Get Code]]

Foreign Key

Eine Spalte in einer Tabelle verweist auf einen Primärschlüssel in eineren anderen Tabelle Oracle:

CREATE TABLE ORDERS (Order_ID INTEGER PRIMARY KEY, Order_Date DATE, Customer_SID INTEGER REFERENCES CUSTOMER(SID), Amount DOUBLE);

[$[Get Code]]

Mysql:

FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER(SID));

[$[Get Code]]

Nachträglich: Oracle:

ALTER TABLE ORDERS ADD CONSTRAINT fk_orders1 FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

[$[Get Code]]

Andere:

ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

[$[Get Code]]

Indizes

Aus einer oder mehrern Spalten einen Index erzeugen um schnelleres Suchen nach einzelnen Reihen zu erreichen

CREATE INDEX IDX_CUSTOMER_LOCATION ON CUSTOMER (City, Country);

[$[Get Code]]

Unter Oracle kann man so sehen, auf welche Spalten ein bestimmter Index geht

SELECT * FROM all_ind_columns WHERE INDEX_NAME='FOO'

[$[Get Code]]

Worauf sich der Index beziehen sollte

Index geht auf eine Spalte

Man greift mit der where Bedingung nur auf diese eine Spalte zu und erwartet (im Vergleich zur Gesamtanzahl) relativ wenig Zeilen für diese where Bedingung.

Mehrere Indizes, die jeweils nur auf eine Spalte gehen

Man hat mehrere einzelne Statements, die jeweils nur eine der Spalten abfragen (siehe Index geht auf eine Spalte).

Index über mehrere Spalten

Man hat Abfragen die where Bedingungen über mehrere Spalten beinhalten. Das gilt besonders dann, wenn die Teile der where Bedingungen, die sich auch eine Spalte beziehen die Auswahl noch nicht sehr stark einschränken, also viele Zeilen liefern würden. Hier verhindert der Index dass erst mal sehr viele Zeilen gelesen werden müssen.

Trigger

(Oracle lastig) Trigger: Trigger in Oracle

Trigger sind eventgesteuerte Prozeduren, die automatisch bei bestimmten Ereignissen durchgeführt werden. Es gibt 3 Auslöser

INSERT
UPDATE
DELETE

Zusätzlich kann noch der Ausführungszeitpunkt bestimmt werden

BEFORE	Vor der Änderung
AFTER	Nach der Änderung

Ein weiteres Kriterium ist wie oft der Trigger gestartet werden soll

Name	Befehl	Aktion	Anwendung
ROW-Trigger	FOR EACH ROW	Werden pro geänderter Zeile ausgeführt	z.B. für die Protokollierung von Änderungen
Statement-Trigger	FOR EACH STATEMENT	werden pro ausgeführtem Statement ausgeführt, egal wieviele Zeilen betroffen sind	z.B. für erweiterten Zugriffsschutz

Trigger mit BEFORE, FOR EACH ROW und aktiv bei INSERT, UPDATE und DELETE

CREATE OR REPLACE TRIGGER mytrigger BEFORE INSERT OR UPDATE OR DELETE
ON foo FOR EACH ROW
WHEN (NEW.id>5)
DECLARE
...
BEGIN
IF INSERTING THEN
...
END IF;
IF UPDATING THEN
...
END IF;
IF DELETING THEN
...
END IF;
EXCEPTION
...
END mytrigger;

[$[Get Code]]

Nur in ROW-Triggern werden die Alten (old) und Neuen (new) Werte der Tabelle zur Verfügung gestellt
Bei BEFORE-Triggern besteht die Möglichkeit die NEW-Werte zu ändern

Kleines Beispiel, vor jedem Einfügen wird noch schnell ein LPAD auf die neuen Einträge gemacht

CREATE OR REPLACE TRIGGER foo.mytrigger
BEFORE INSERT OR UPDATE ON foo.mytable
FOR each ROW BEGIN
SELECT lpad(:NEW.mycolumn,3,'0')
INTO :NEW.mycolumn
FROM dual;
END;

[$[Get Code]]

Meta Informationen auslesen

(sehr Oracle lastig)

Alle Tabelle auflisten

Oracle

SELECT * FROM tab;
SELECT TABLE_NAME FROM user_tables;
SELECT TABLE_NAME FROM all_tables;
SELECT TABLE_NAME FROM dba_tables;
SELECT object_name FROM user_objects WHERE object_type = 'TABLE';
SELECT object_name FROM all_objects WHERE object_type = 'TABLE';
SELECT object_name FROM dba_objects WHERE object_type = 'TABLE';

[$[Get Code]]

Man kann sich auch den Quellcode aller FUNCTION, PACKAGE und PROCEDURE Objekte ansehen (und mit LIKE darin suchen)

SELECT * FROM ALL_SOURCE

[$[Get Code]]

Abhängige Objecte

Welche Objekte hängen vom Objekt FOO ab?

SELECT * FROM all_dependencies WHERE referenced_name='FOO'

[$[Get Code]]

Reservierte Oracle Namen als Spaltennamen

War jemand so unvorsichtig einen reservierten Oracle Namen als Spaltennamen zu verwenden?

SELECT * FROM v$reserved_words WHERE KEYWORD IN (SELECT column_name FROM dba_tab_columns WHERE owner = 'FOO')

[$[Get Code]]

Aktuelle Datenbankinstanz

<code>select * from global_name</code>

MySQL

SHOW TABLES;
sp_databases;
sp_tables;

[$[Get Code]]

[$[Get Code]]

Alle Spalten einer Tabellen auflisten

SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE OWNER='FOO' AND TABLE_NAME='BAR'

[$[Get Code]]

Quelltext anzeigen

SELECT * FROM DBA_Source WHERE TYPE='FUNCTION' AND name='MYFOOFUNCTION' ORDER BY owner,name, TYPE, line

[$[Get Code]]

Constraint anzeigen

SELECT * FROM ALL_CONSTRAINTS i, ALL_CONS_COLUMNS c WHERE i.CONSTRAINT_NAME='FOO' AND c.CONSTRAINT_NAME=i.CONSTRAINT_NAME

[$[Get Code]]

Index anzeigen

SELECT * FROM all_indexes i, all_ind_columns c WHERE i.index_name='FOO' AND c.INDEX_NAME=i.INDEX_NAME

[$[Get Code]]

Sequenzen

CREATE SEQUENCE foo.bar START WITH 1 INCREMENT BY 1 MINVALUE 0

[$[Get Code]]

Eine Sequenz um eine bestimmte Anzahl erhöhen

Die Sequenz foo würde als nächstes 100 ausgeben. Sie soll aber 225 ausgeben

ALTER SEQUENCE foo INCREMENT BY 124;
SELECT foo.nextval FROM dual;
ALTER SEQUENCE foo INCREMENT BY 1;

[$[Get Code]]

Ein möglicher Cache auf die Sequenz könnte aber dazu führen, dass auch dann noch von einigen Prozessen (vorübergehend) Zahlen kleiner 225 aus der Sequence (bzw. deren Cache) gelesen werden können.

So kann man den Cache übrigens aktivieren und deaktivieren

ALTER SEQUENCE seq_cache NOCACHE;
ALTER SEQUENCE seq_cache 20; -- default

[$[Get Code]]

Gegenseitig blockierende Zugriffe

So kann man unter Oracle sehen, welche zwei Zugriffe sich gegenseitig blockieren

SELECT l1.sid, l2.sid FROM v$lock l1, v$lock l2 WHERE
l1.block =1 AND l2.request > 0 AND l1.id1=l2.id1 AND l1.id2=l2.id2

[$[Get Code]]

Mit diesen beiden IDs kann man dann noch mehr Informationen ermitteln

SELECT * FROM v$session WHERE sid=FOO1 OR sid=FOO2

[$[Get Code]]

Auf der DB ausgeführte Befehle ermitteln

So sieht man unter Oracle welche Statements ausgeführt wurden

SELECT * FROM v$sqlarea ORDER BY FIRST_LOAD_TIME DESC

[$[Get Code]]

Man sieht allerdings nur die Statements, die neu auswertet werden musste. Prepared Statements tauchen also nur einmalig auf, auch wenn sie mehrfach ausgeführt werden.

Tablespace

In Oracle gibt es:

Dateien auf dem Dateisystem
Aus einer oder mehreren Dateien entsteht ein tablespace
Ein Schema (oder mehrere) liegen auf einem (oder mehreren) tablespace(s)
In einem Schema liegen dann u.a. die Tabellen

Das sind die Dateien auf dem Dateisystem (mit Größe)

SELECT * FROM dba_data_files ORDER BY bytes DESC

[$[Get Code]]

So sieht man alle Tablespaces:

SELECT * FROM v$tablespace ORDER BY name
SELECT * FROM DBA_TABLESPACES ORDER BY tablespace_name

[$[Get Code]]

So sieht man, welche Objekte auf dem Tablespace FOO noch Platz belegen:

SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'FOO' ORDER BY bytes DESC

[$[Get Code]]

Welcher Tablespace ist zur Zeit wie voll?

SELECT TABLESPACE_NAME, SUM(BYTES) free FROM dba_free_space GROUP BY TABLESPACE_NAME
ORDER BY free DESC

[$[Get Code]]

SELECT * FROM (
SELECT a.TABLESPACE_NAME,
a.SIZE_BYTES/1024/1024/1024 AS SIZE_GB,
b.FREE_BYTES/1024/1024/1024 AS FREE_GB,
(round(((a.SIZE_BYTES-b.FREE_BYTES)/a.SIZE_BYTES)*100.0*100.0)/100.0) percent_used
FROM
(SELECT TABLESPACE_NAME, SUM(BYTES) SIZE_BYTES FROM dba_data_files GROUP BY TABLESPACE_NAME) a,
(SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES FROM dba_free_space GROUP BY TABLESPACE_NAME) b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME
) ORDER BY percent_used DESC

[$[Get Code]]

Gibt es Dateien die gar nicht mehr benutzt werden?

SELECT f.file_name, f.file_id, f.TABLESPACE_NAME, f.bytes/1024/1024/1024 AS
Size_GB, COUNT(s.SEGMENT_NAME)
FROM dba_data_files f, DBA_SEGMENTS s
WHERE f.TABLESPACE_NAME=s.TABLESPACE_NAME(+)
GROUP BY f.file_name,f.file_id,f.TABLESPACE_NAME,f.bytes
HAVING COUNT(s.SEGMENT_NAME)=0
ORDER BY Size_GB DESC

[$[Get Code]]

Wieviel Platz belegt welches Schema auf den jeweiligen Tablespaces

SELECT owner, TABLESPACE_NAME, SUM(bytes)/1024/1024/1024 AS SUM_SIZE_GB FROM
DBA_SEGMENTS GROUP BY owner, tablespace_name ORDER BY sum_size_gb DESC

[$[Get Code]]

Wieviel Platz belegt welches Schema insgesamt?

SELECT owner, SUM(bytes)/1024/1024/1024 AS SUM_SIZE_GB FROM DBA_SEGMENTS GROUP
BY owner ORDER BY sum_size_gb DESC

[$[Get Code]]

Gibt es Tabellen oder Indizes auf einem Tablespace:

SELECT * FROM DBA_TABLES i WHERE i.TABLESPACE_NAME='FOO';
SELECT * FROM DBA_INDEXES i WHERE i.TABLESPACE_NAME='FOO';

[$[Get Code]]

Liste aller Partitionen

SELECT * FROM dba_ind_partitions

[$[Get Code]]

SQL Statements auf die Elemente beschränken, die sich in einer bestimmten Partition befinden

SELECT * FROM foo.bar PARTITION(partition_001) WHERE ...

[$[Get Code]]

Oracle

Oracle Links

Installation

Von der Oracle Webseite herunterladen und installieren, z.B. oracle-xe-universal_10.2.0.1-1.0_i386.deb (Paketname oracle-xe-universal) oder alternativ

deb http://oss.oracle.com/debian unstable main non-free

[$[Get Code]]

in die sources.list übernehmen.

Probleme:

/var/lib/dpkg/info/oracle-xe-universal.postinst: line 57: bc: command NOT found

[$[Get Code]]

apt-GET install bc
apt-GET remove --purge oracle-xe-universal
apt-GET install oracle-xe-universal

[$[Get Code]]

# /etc/init.d/oracle-xe configure
Specify the HTTP port that will be used FOR Oracle Application Express [8080]:
Specify a port that will be used FOR the DATABASE listener [1521]:
Specify a password TO be used FOR DATABASE accounts. Note that the same
password will be used FOR SYS AND SYSTEM. Oracle recommends the USE OF
different passwords FOR each DATABASE account. This can be done after
initial configuration: admin
Confirm the password: admin

[$[Get Code]]

Einloggen: Bei mir ging das Passwort das bei der Installation gesetzt wurde nicht. Also user oracle geht es auch ohne:

cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
. ./oracle_env.sh
sqlplus / AS sysdba

SQL> ALTER USER SYS IDENTIFIED BY admin;

[$[Get Code]]

Webfrontend für alle freigeben:

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

[$[Get Code]]

Jetzt konnte ich auch das Webfrontend benutzten.

Einloggen nach der Installation:

# su - oracle
# cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
# . ./oracle_env.sh
# sqlplus / AS sysdba

[$[Get Code]]

Einmal:

SQL> CREATE ROLE conn;
ROLE created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SYNONYM TO conn;
GRANT succeeded.

SQL> CREATE USER foo IDENTIFIED BY bar DEFAULT tablespace users TEMPORARY tablespace temp;
USER created.

SQL> ALTER USER foo QUOTA UNLIMITED ON users;
USER altered.

SQL> GRANT conn TO foo;
GRANT succeeded.

SQL> CONNECT foo

Enter password: bar

Connected.

[$[Get Code]]

SQL*Plus

sqlplus username/passwort@foo.bar.de
sqlplus -S username/passwort@foo.bar.de @myscript.sql > myoutput.txt 2>&1

[$[Get Code]]

(Der Name hinter dem @ ist kein Rechnername sondern kommt aus der tnsnameora)

Statements werden in sqlplus ausgeführt sobald ein ";" gelesen wird. Da es innerhalb eines ps/sql Skripts mehrere ";" geben kann ohne dass das Skript zu Ende wäre, wird in diesem Fall ein "/" am Ende des Skripts erwartet.

Allgemeine Hinweise

Befehle

define_editor=emacs

[$[Get Code]]

NULL

In Oracle ist der Vergleich mit NULL immer falsch. Das kann überraschende Auswirkungen haben.

a	b
2	5
4	4
9	null

Mit diesem Statement

SELECT * FROM foo WHERE b<>4

[$[Get Code]]

würde man vielleicht die erste und dritte Zeile als Ergebnis erwarten (schließlich sind 5 und null nicht gleich 4). Allerdings liefern alle Vergleiche mit null stets falsch, weshalb auch der Vergleich ob 4 ungleich null ist mit falsch beantwortet wird. Möchte man die null Werte auch erhalten kann geht das z.B. so

SELECT * FROM foo.bar WHERE lnvl(<>4)

[$[Get Code]]

bind_variables

Statt

SELECT * FROM foo WHERE myid=5;

[$[Get Code]]

lieber

variable x NUMBER
EXEC :x := 5
SELECT * FROM foo WHERE myid=:x;
EXEC :x := 42
SELECT * FROM foo WHERE myid=:x;

[$[Get Code]]

oder

DECLARE l_myvalue NUMBER;
...
EXECUTE IMMEDIATE
'INSERT INTO foo VALUES (:x1, :x2)'
USING l_myvalue, l_myvalue;

[$[Get Code]]

schreiben dann muss Oracle den Ausdruck nicht jedesmal neu parsen nur weil man nach einer anderen id sucht.

MTS

Statt pro Client einen Prozess zu starten gibt es einen Pool von Prozessen die sich die Clients teilen.

Read

Read Uncommitted

Erlaubt dirty reads während andere Clients gerade schreiben

Read Committed

Beim Lesen wird angehalten wenn die zu lesende Zeile gerade von einer anderen Transaktion bearbeitet wird. Ist aber auch unsicher:

Prozess A soll eine Spalte aufaddieren. Er liest Zeile 1,2,3,4.
Jetzt startet Transaktion die von Zeile 1 eine Zahl abzieht und auf 5 draufaddiert.
Der lesende Zugriff auf Zeile 5 wird jetzt blockiert bis der Schreibvorgang abgeschlossen ist (hier geht also nichts schief
Jetzt liest Prozess A weiter Zeile 5,6,7,8,...

Jetzt hat Prozess A aber Zeile 1 vor der Änderung gelesen und Zeile 5 nach der Änderung. Die festgestellte Summe hat aber so nie existiert.

Repeatable Read

Der zu lesensende Bereich wird während dem kompletten Vorgang gegenüber Änderungen gesichert. Die Ergebnisse sind also reproduzierbar.

In Oracle hat man immer Repeatable Read weil man auf virtueller Kopie der Daten liest.

Wenn man lesen will um das Gelesen zum Schreiben zu benutzen durch FOR UPDATE die betroffenen Bereiche sperren. Falls das nicht möglich ist warten oder fehlschlagen lassen (NOWAIT).

SELECT x,y FROM foo FOR UPDATE NOWAIT;

[$[Get Code]]

isolation_level

Es ist nicht möglich dass mehrere Änderungen gleichzeitig stattfinden. Falls doch gibt es einen Fehler.

isolation_level=serializable

[$[Get Code]]

SET TRANSACTION

Angaben über die aktuelle Transaktion. Z.B. aktuelle Transaktion schreibt nichts:

...
COMMIT;

SET TRANSACTION READ ONLY NAME 'foo';
SELECT * FROM bar WHERE ...;
COMMIT;

...

[$[Get Code]]

Es gibt:

READ ONLY
READ WRITE
ISOLATION LEVEL SERIALIZABLE
ISOLATION LEVEL READ
ISOLATION LEVEL READ COMMITTED
USE ROLLBACK SEGMENT foo
NAME bar

Datenbanktabellentypen

Typ	Beschreibung
Heap	Die Zeilen werde im Speicher da abgelegt wo sie gerade hinpassen. Die Reihenfolge der Zeile ist folglich nicht vorhersehbar, Suchen nach einzelnen Elementen langsam, komplettes Einlesen aller Zeilen aber sehr schnell.
Index	Index der auf einer oder mehreren Spalten basiert. Suche nach einzelnen Zeilen über diese Spalten ist sehr schnell, komplettes Einlesen ohne Index schneller (Full Scan). Dann kann man

SELECT /* FULL(foo) */ myname FROM foo;

[$[Get Code]]

benutzen um Oracle einen Hint zu geben dass das sinnvoller sein könnte.||

Clustered	Man hat mehrere Tabellen die über gemeinsamen Schlüssel verbunden sind. Statt die Tabellen hintereinander zu speichern werden die Zeilen aus den verschiedenen Tabellen die den gleichen Schlüssel haben zusammen gespeichert. Macht NATURAL JOINS schnell
Hash Clustered	Wie Clustered, nur dass statt ein Hash als Index benutzt wird.

Indizes

Index	Beschreibung
B* Tree Index	Index ist in einer Baumstruktur abgelegt (B-Baum, B*-Baum)
B* Clustered Key	Siehe Clustered, das ist der Index dazu
Descendending Index	Wie B* nur dass in den Baumknoten absteigend sortiert ist
Reverse Index	Aus Konten 1234 wird Knoten 4321 (auf Bitebene). Indizes die nahe zusammenliegen werden so weit voneinder entfernt abgelegt. Hilfreich bei konkurrierenden Änderungen.
Bitmap Index	Man hat einen Index auf etwas mit sehr wenigen Ausprägungen wie z.B. das Geschlecht mit M, W, X (unbekannt oder keine natürliche Person). Normalerweise tausende verschiedenen Zeile mit tausend verschiedenen Indizies (die aber alle M, W, X sind). Bitmap macht das effizienter:

Zeile Nr	1	2	3	4	...
Geschlecht M	Ja	Nein	Ja	Nein	...
Geschlecht W	Nein	Ja	Nein	Nein	...
Geschlecht X	Nein	Nein	Nein	Ja	...

Jetzt ist es sehr effizient zu zählen wieviele Männer es in der Tabelle gibt (vor allem weil man dafür nur den Index braucht und die Zeile nicht gesucht werden muss). Schreibt man oft ist es aber sehr langsam weil dann immer die ganze Zeile im Index blockiert werden muss.

Function-Based-Index	Man schreibt eine Funktion die aus einer oder mehreren Spalten selbst einen Index erstellt
Applikation-Domain-Index	Man schreibt eine Erweiterung für Oracle (z.B. um Bilder in einer Datenbank speichern zu können) und will seinen eigenen Index mitliefern (z.B. basierend auf der Anzahl der Farben in einem Bild).

Hinweis: NULL in der Spalte auf der der Index basiert führt dazu dass der Index diese Zeile nicht erfasst. Kann ein Problem werden aber auch trickreich genutzt werden. Wenn sehr viele Zeilen normalerweise den gleichen Index hätten und nur wenige einen davon unterschiedlichen, den häufig vorkommenden auf NULL setzten, dann werden diese Zeilen nicht vom Index erfasst und man hat einen sehr viel kleineren Index (FIXME: kann sein dass man dafür 2 Spalten als Indexgrundlage braucht und nur eine NULL sein darf).

Hints

Index ignorieren bzw. erzwingen

SELECT /*+ FULL(foo) */ bar FROM foo WHERE id=42;
SELECT /*+ INDEX(my_alias idx_bar) */ bar FROM foo.bar AS my_alias WHERE id=42;

[$[Get Code]]

So kann man mehr als einen Index angeben

SELECT /*+ INDEX(a ind1) INDEX (b ind2) */ a.*, b.* FROM tabx a, taby b

[$[Get Code]]

Hints funktionieren so allerdings nicht mehr (zuverlässig?), wenn man nicht direkt von einer Tabelle liest, sondern über eine View zugreift.

SELECT /*+ INDEX(my_alias idx_bar) */ bar FROM foo.my_view_on_bar AS my_alias WHERE id=42;

[$[Get Code]]

Das Problem ist, dass die View mit dem Alias my_alias ja selbst gar kein Index idx_bar hat. Es ist daher notwendig, einen Alias zu erstellen, mit dem man durch die View auf die zugrundeliegende Tabelle zugreifen kann. Das funktioniert mit einem Query Block Identifier. Diese werden automatisch generiert und können dem Explain Plan entnommen werden (Spalte Object Alias).

EXPLAIN PLAN FOR
SELECT bar FROM foo.my_view_on_bar AS my_alias WHERE id=42;

[$[Get Code]]

SELECT * FROM TABLE( dbms_xplan.display(NULL, NULL, 'typical alias -rows -bytes -cost' ));
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1 / MY_ALIAS@SEL$2

[$[Get Code]]

Ein typischer Name ist wie hier z.B. MY_ALIAS@SEL$2 Mit diesem Query Block Indentifier kann man dann wieder einen Hint benutzen.

SELECT /*+ INDEX(MY_ALIAS@SEL$2 idx_bar) */ bar FROM foo.my_view_on_bar AS my_alias WHERE id=42;

[$[Get Code]]

Besser ist es natürlich, den Query Block Indentifier selbst festzulegen, damit man sicher sein kann, dass der Name sich auch nicht ändert.

CREATE OR REPLACE VIEW foo.my_view_on_bar AS
(
SELECT /*+ QB_NAME (MyEvenNicerName) */ * FROM foo.bar
);

[$[Get Code]]

Jetzt hat man einen vorhersehbaren Namen, den man nutzen kann um einen Hint zu benutzen

Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
1 - SEL$6BE3C2D9 / BAR@MYEVENNICERNAME

[$[Get Code]]

Nutzt man in einer View einen Join über mehrere Quellen scheint das nicht mehr zu funktionieren. Da kann es helfen die für jede Quelle ein Inner Select zu erstellen.

Performance

Explain Plan

SET TIMING ON;
SET AUTOTRACE ON EXPLAIN;
SET SERVEROUTPUT ON;

[$[Get Code]]

Nach größeren Änderungen kann es notwendig werden, die Statistiken zu aktualisieren

ANALYZE TABLE foo COMPUTE STATISTICS;
ANALYZE TABLE foo COMPUTE STATISTICS FOR ALL INDEXES;
ANALYZE TABLE foo ESTIMATE STATISTICS SAMPLE 20 PERCENT;

[$[Get Code]]

Alternativ

dbms_stats.gather_table_stats(ownname => 'MYUSER',
tabname => 'MYTABLE',
method_opt => 'FOR ALL INDEXED COLUMNS',
degree => 8,
granularity => 'ALL',
cascade => TRUE,
no_invalidate => FALSE
);

[$[Get Code]]

Überprüfen ob die Statistiken aktuell sind

SELECT TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM ALL_TAB_COL_STATISTICS WHERE TABLE_NAME = 'FOO'
SELECT INDEX_NAME, LAST_ANALYZED FROM ALL_INDEXES WHERE TABLE_NAME = 'POSCHECKDETAIL'

[$[Get Code]]

Welche Statements haben in der nahen Vergangenheit Resourcen verbraucht (AWR Report) AWR Report erzeugen

Lang laufende Statements

Welche lang laufende Statements sind zur Zeit noch aktiv?

SELECT
i.opname,
i.start_time,
i.last_update_time,
i.time_remaining,
i.message,
i.username,
s.sql_text
FROM
v$session_longops i,
gv$sqlarea s
WHERE
i.sql_address = s.address
AND i.time_remaining>0;

[$[Get Code]]

Datenbank Link

Einen Link zu einer anderen Datenbank aufbauen und abfragen

CREATE DATABASE LINK mylink CONNECT TO mrfoo IDENTIFIED BY mysecretpasswd USING 'XE'
SELECT * FROM bar@mylink

[$[Get Code]]

Alle Datenbanklinks anzeigen

SELECT * FROM ALL_DB_LINKS

[$[Get Code]]

Tools

EXP

Daten exportieren. Auch nützlich um die gesetzten Optionen herauszufinden.

IMP

Daten die exportiert wurden wieder importieren.

SQLLDR

Daten in von der Kommandozeile in eine Datenbank einlesen.

PL/SQL

Einführung in die PL/SQL-Programmierung

PL/SQL Beispiele

SET serveroutput ON
DECLARE
CURSOR c_mycursor1 IS SELECT DISTINCT foo FROM sys.bar;
v_myid sys.bar.foo%TYPE;

BEGIN
dbms_output.enable(100000);

dbms_output.put_line('Hello World.');
dbms_output.put_line('============');

-- iterate over input from cursor
FOR c_current IN c_mycursor1
LOOP
dbms_output.put('Debug: '); dbms_output.put_line(c_current.foo);

v_myid:=NULL;

SELECT MIN(ids) INTO v_myid FROM public.allids;

IF v_myid IS NULL THEN
dbms_output.put_line('Error');
ELSE
dbms_output.put('Min: '); dbms_output.put_line(v_myid);
END IF;

END LOOP;
END;
/

[$[Get Code]]

Eine function with no parameter

CREATE OR REPLACE FUNCTION i_have_no_parameter
RETURN NUMBER
IS
BEGIN
RETURN 42;
END;
/

[$[Get Code]]

Aufruf

SELECT i_have_no_parameter FROM dual;

[$[Get Code]]

SET serveroutput ON format wrapped

CREATE OR REPLACE FUNCTION MyFunction
(
p_MyParameter IN varchar2,
p_MyOtherParameter IN varchar2,
p_AndAnotherParameter IN varchar2
)
RETURN varchar2
IS
myresult varchar2(32767);
BEGIN

myresult:=p_MyParameter;
myresult:=REGEXP_REPLACE(myresult, '([^A-Z_])' || p_MyOtherParameter ||'([^A-Z_])', '1' || p_AndAnotherParameter || '2', 1, 0, 'i');

RETURN myresult;

END;
/

[$[Get Code]]

Aufruf

SELECT MyFunction('Some long String', 'long', 'really long') FROM dual;

[$[Get Code]]

Mit PL/SQL aus der Datenbank lesen

CREATE OR REPLACE FUNCTION foo
(
p_myparameter IN NUMBER
)
RETURN varchar2
IS
myresult varchar2(255);

CURSOR c_mycursor1 IS SELECT * FROM dual WHERE dummy=p_myparameter;

BEGIN

FOR c_current IN c_mycursor1
LOOP
myresult:=myresult+c_current.dummy;
END LOOP;
RETURN myresult;

END;
/

SHOW errors;

[$[Get Code]]

Ein to_date() das keine Fehler wirft sondern im Fehlerfall einen alternativ Wert liefert

CREATE OR REPLACE FUNCTION to_date_no_exceptions
(
p_MaybeDate IN varchar2, -- the string which to_date should convert into a date
p_Format_mask IN varchar2, -- the format you expect the string to have, see the Oracle doc for to_date. An example may be 'dd.mm.yyyy'
p_OnErrorValue IN DATE -- if to_date fails this function retunrs this value (must be of format date). An example may be null or any valid date like to_date('01.1.1900', 'dd.mm.yyyy')
)
RETURN DATE
IS
BEGIN
RETURN to_date(p_MaybeDate, p_Format_mask); -- this may fail if your p_MaybeDate is not a valid date
EXCEPTION
WHEN others THEN -- on any error we return the alternative date value you provided
RETURN p_OnErrorValue;
END;

[$[Get Code]]

CREATE OR REPLACE FUNCTION concat_text_up_to_a_limit
(
p_id IN NUMBER
)
RETURN varchar2
IS
myresult varchar2(4000):='';
max_length NUMBER:=4000;
current_length NUMBER:=0;
total_length NUMBER:=0;
rest_length NUMBER;
CURSOR c_mycursor1 IS SELECT text FROM foo.bar v WHERE v.ID=p_id AND text IS NOT NULL ORDER BY id DESC;
BEGIN
FOR c_current IN c_mycursor1
LOOP
myresult:= SUBSTR(CONCAT (myresult, c_current.remark), 0, max_length-4);
END LOOP;
IF (LENGTH(myresult)=(max_length-4)) THEN
myresult:=CONCAT(myresult, ' ...');
END IF;
RETURN myresult;
END;

[$[Get Code]]

Wie oft wird eine Function aufgerufen

Man sollte keinerlei Annahmen darüber treffen, wie oft Oracle eine Funktion aufruft, die in einem SQL vorkommt. Beispiel:

SELECT * FROM my_table WHERE my_table.my_value=my_function();

[$[Get Code]]

Die Funktion könnten einmal, einmal pro Zeile in der Tabelle oder zweimal aufgrund eines Index Range Scans aufgerufen werden, oder ... You cannot rely on how many times or when or if SQL will call your function

PL/SQL Collections als Datentyp

In SQL kann man neben den Basisdatentypen auch Collections benutzen PL/SQL Collections and Records

Index-by tables / associative arrays

Ein Schlüssel -> Wert Container, wie eine Hashtable

CREATE OR REPLACE FUNCTION mycollectiondemo
RETURN varchar2
IS
-- define a Associative Arrays / Index-By Tables type
TYPE number2string_type IS TABLE OF varchar2(255) INDEX BY BINARY_INTEGER;

-- define a variable with this type (this is a Hashtable)
employees number2string_type;

current_pos binary_integer;
myresult varchar2(255);
BEGIN
myresult:='';
-- enter some entries in our hashtable
employees(1235):='Mickey';
employees(42):='Tom';
employees(2221):='Alf';
-- fill the hashtable with the one line result of a SQL select
SELECT name INTO employees(2241) FROM some_table WHERE id=2;
-- fill the hashtable with all rows a select returns
FOR one_row IN ( SELECT id, name FROM some_table )
loop
employees(one_row.id) := one_row.name;
END loop;
-- iterate through the hashtable and build the result
current_pos:=employees.FIRST;
WHILE (current_pos IS NOT NULL)
LOOP
myresult:=myresult || current_pos || ': ' || employees(current_pos) || ', ' ;
current_pos:=employees.NEXT(current_pos);
END LOOP;
RETURN myresult;
END;
/

[$[Get Code]]

SELECT mycollectiondemo FROM dual;

[$[Get Code]]

Nested tables

Wie eine einspaltige Tabelle

CREATE OR REPLACE FUNCTION mycollectiondemo
RETURN varchar2
IS
-- define a Nested Table type
TYPE varchartable_type IS TABLE OF VARCHAR2(255);

-- have a variable for it and fill it with values
random_names varchartable_type:=varchartable_type('Frank', 'John');

myresult varchar2(255);
BEGIN
myresult:='';

-- add a new element
random_names.extend;
random_names(random_names.last):='Jane';

-- remove an element
random_names.delete(2);

-- iterate through nested table but watch out if it is sparse
FOR i IN random_names.FIRST .. random_names.LAST
LOOP
-- you have to skip holes
IF random_names.exists(i) THEN
myresult:=myresult || i || ':' || random_names(i) || ';';
END IF;
END LOOP;

RETURN myresult;
END;
/

[$[Get Code]]

Wenn man den nested table type außerhalb des PL/SQL, also auf Schema Ebene definiert

CREATE OR REPLACE TYPE foo.varchartable_type IS TABLE OF VARCHAR2(255);

[$[Get Code]]

kann man sogar normale SQL Selects gegen die nested Table fahren

CREATE OR REPLACE FUNCTION foo.mycollectiondemo
RETURN varchar2
IS
-- have a variable for it and fill it with values
random_names foo.varchartable_type:=foo.varchartable_type('Frank', 'John');
myresult varchar2(400);
BEGIN
myresult:='';
-- add a new element
random_names.extend;
random_names(random_names.last):='Jane';
SELECT MAX(column_value) INTO myresult FROM TABLE(random_names) WHERE column_value LIKE 'J%';
RETURN myresult;
END;
/

[$[Get Code]]

SELECT foo.mycollectiondemo FROM dual;

[$[Get Code]]

Definiert man den Typ versehentlich innerhalb des PL/SQL Blocks erhält man

PL/SQL: ORA-22905: cannot access ROWS FROM a non-nested TABLE item
PLS-00642: LOCAL collection types NOT allowed IN SQL statements

[$[Get Code]]

Varrays

Wie ein Array, Größe vorgegeben, Reihenfolge fix, keine Löcher

-- Collection example
CREATE OR REPLACE FUNCTION mycollectiondemo
RETURN varchar2
IS
-- define a varray
TYPE varray_type IS VARRAY (3) OF VARCHAR2(100);
random_names varray_type:=varray_type();
myresult varchar2(255);
BEGIN
myresult:='';
-- add a new element
random_names.extend(2);
random_names(1):='Jane';
random_names.extend;
random_names(2):='Tim';
-- iterate through nested table but watch out if it is sparse
FOR i IN random_names.FIRST .. random_names.LAST
LOOP
myresult:=myresult || i || ':' || random_names(i) || ';';
END LOOP;
RETURN myresult;
END;
/

[$[Get Code]]

Führende Leerzeichen verschwinden

Falls führende Leerzeichen beim dbms_output.put_line verschwinden hilft das hier

SET serveroutput ON format wrapped

[$[Get Code]]

Anzeige der Anzahl der veränderten Zeilen

Falls man auch in PL/SQL sehen möchte, wieviele Zeile eine update betroffen hat geht das so

UPDATE foo.bar SET x=y;
dbms_output.put_line('Numer of changed entries:' || SQL%ROWCOUNT);

[$[Get Code]]

postgresql

PostgreSQL Dokumentation

Mit postgresql Datenbank verbinden

psql -l
psql -h HOSTNAME DATABASENAME
l
d

[$[Get Code]]

Eine Datenbank exportieren und wieder importieren

pg_dump FOO -t BAR -d -D -F t -f /tmp/foo.tar

[$[Get Code]]

pg_restore --list /tmp/foo.tar > /tmp/foo.list
pg_restore --use-list=/tmp/foo.list --dbname=FOO -v /tmp/foo.tar

[$[Get Code]]

MySQL

SQLite

SQLite ist eine sehr kleine in C geschriebene Datenbankbibliothek, die ohne Serverinstanz auskommt und direkt ins das jeweilige Programm integriert wird, welches eine Datenbank benötigt. Siehe auch SQLite Wikipedia. Für andere Programmiersprachen gibt es SQLite Wrapper. Das ist die unterstützte SQlite SQL Syntax und hier die unterstüzten SQLite Datentypen.

In SQLite erhalten alle Tabellen automatisch einen mehr oder weniger unsichtbare Spalte

ROWID / _ROWID_ / OID

[$[Get Code]]

die jede Zeile dauerhaft eindeutig beschreibt. Erzeugt man eine Tabelle mit einer Spalte vom Typ

INTEGER PRIMARY KEY

[$[Get Code]]

zeigt diese intern auf die ROWID Spalte. Wenn man neue Zeilen einfügt und die Spalte nicht befüllt wird sie automatisch mit einer eindeutigen ID gefüllt. Siehe auch SQLite Autoincrement.

Auf der SQLite Download Seite gibt es z.B. eine Command-line Shell, mit der man SQLlite Datenbanken anlegen kann und über eine Shell direkt mit ihnen interagieren kann.

SQLite Shell

Eine neue Datenbank in der Datei MyFirstSQLiteDatabase.db im lokalen Ordner anlegen

sqlite3.exe MyFirstSQLiteDatabase.db

[$[Get Code]]

In der Shell kann man dann auch gleich Tabellen anlegen <pre>sqlite> create table cities (id INTEGER PRIMARY KEY ASC, name TEXT, postcode INTEGER); sqlite> insert into cities (name, postcode) values ("Berlin", 10115); sqlite> select * from cities; 1|Berlin|10115sqlite> create table people (id INTEGER PRIMARY KEY ASC, name TEXT, city_ref INTEGER REFERENCES cities (id)); sqlite> insert into people (name, city_ref) values ("John Doe", 1); sqlite> select * from people p, cities c where p.city_ref=c.id; 1|John Doe|1|1|Berlin|10115sqlite> .quit</pre> Hier ist ein und hier ist ein und so funktioniert .

Microsoft SQL Server

MS SQL Server Management Studio Express

Wenn man nicht alle Fenster geschlossen hat schlägt die Installation vielleicht fehlt

SQL server setup has encountered the following error invoke OR begininvoke cannot be called ON a control until the window has been created...

[$[Get Code]]

Vor einem erneuten Versuch Windows neu starten

<h3>MS SQL JDBC</h3> <a href=” http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx”>Microsoft JDBC Driver for SQL Server</a>

Falls dieser Fehler kommt

Could NOT find class com.microsoft.jdbc.sqlserver.SQLServerDriver IN neither the Java class path nor the Extra class path OF the Microsoft MSSQL Server JDBC Driver driver definition:
java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver

[$[Get Code]]

Die JDBC Klasse ändern auf

com.microsoft.sqlserver.jdbc.SQLServerDriver

[$[Get Code]]

JDBC String

jdbc:sqlserver://your.db.server.name.example.com:1433;databaseName=MyDataBase

[$[Get Code]]

Allerdings ist dieser JDBC Treiber besser als der von MS

JTDS

In Squirrel das hier auswählen

jTDS Microsoft SQL

[$[Get Code]]

und JDBC Treiber von deren Webseite laden.

Windows Authentifizierung statt Username / Passwort: Die Datei README.SSO lesen. Kurzfassung: ntlmauth.dll so ablegen, dass SQL client sie findet. Squirrel: Squirrel Installationsordner. Danach einfach Username / Passwort leer lassen.

Falls er die DLL nicht findet kommt das

java.lang.RuntimeException: java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library NOT loaded. CHECK the java.library.path system property.

[$[Get Code]]

Thorsten Gunkel

Database SQL

Tools

Squirrel

Squirrel SQLite

SQL

SQL Links

SQL Data Retrieval

SELECT

SELECT DISTINCT

SELECT CASE

SELECT DECODE

INTERSECT

MINUS

Die Inhalte zweier Tabellen vergleichen

Alias

JOIN

UNION

LIKE

GROUP BY

GROUP BY Strings zusammen fassen

HAVING

GROUP BY jeweils nur eine beliebige Zeile ausgeben

Over Partition By

Innere Abfrage

Inner Abfrage mit exists

IN Bedingung an mehrere Spalten gleichzeitig stellen

In einem CLOB suchen

Anzahl der Ergebniszeilen beschränken

Verkettete Listen connect by

DML Data Manipulation Language

INSERT, UPDATE, DELETE

Neue Einträge aus vorhandenen erzeugen

MERGE (REPLACE)

Ersetzen von String durch andere

Substrings

Die ersten oder letzten Einträge bezüglich einer Sortierung

DDL Data Definition Language

CREATE

Eigene Datentypen erzeugen

VIEWS

Oracle Inlineviews

Invalide Objekte

Materialized Views

NULL Spalten und ihr Datentyp

DROP

TRUNCATE

ALTER

Constraints

RENAME

Disable

DCL Data Control Language

GRANT

Spaltenweise Rechte vergeben

With Grant Option

REVOKE

Atomare Operationen / Transaktionen

Flashback

Eingebaute Funktionen

SUBSTR

CONCAT (||)

SUM

COUNT

Aktuelles Datum beziehen

Teile eines Datums extrahieren

Spracheinstellungen und Wochentage

Ein Datum runden

to_char

Nicht numerische Werte in einer Spalte finden

Nachkommastellen einer Zahl

Spalten mit Nachkommastellen

TRIM

LPAD

Datenbanken und Tabellen Anlegen und Verwalten

Schlüssel

Primärschlüssel

Vorhandene Tabelle soll technischen Schlüssel bekommen

Foreign Key

Indizes

Worauf sich der Index beziehen sollte