vorheriges KapitelInhaltsverzeichnisIndexInfoseitenächstes Kapitel



17


SQL-Anweisungen mit SQL generieren

Ziele

Warum man mit SQL andere SQL-Anweisungen generiert

Verschiedene Befehle von SQL*Plus

Zeilen in allen Tabellen zählen

Mehreren Benutzern Systemprivilegien zuteilen

Privilegien Ihrer Tabellen an andere Benutzer übertragen

Tabelleneinschränkungen beim Laden von Daten deaktivieren

Mehrere Synonyme im Block erzeugen

Sichten auf Tabellen erzeugen

Alle Tabellen in einem Schema leeren

Shell-Skripts mit SQL generieren

Tabellen und Indizes rekonstruieren

Zusammenfassung

Fragen & Antworten

Workshop



Ziele

In der heutigen Lektion erfahren Sie, wie man SQL-Anweisungen aus einer Abfrage generiert. Insbesondere lernen Sie ...



Warum man mit SQL andere SQL-Anweisungen generiert

Das Generieren von SQL aus einer anderen SQL-Anweisung bedeutet einfach, daß die Ausgabe der einen SQL-Anweisung eine andere SQL-Anweisung oder einen Befehl bildet. Bis jetzt haben Sie ausschließlich SQL-Anweisungen geschrieben, die entweder eine Manipulation ausführen - beispielsweise die Daten in einer Tabelle zeilenweise aktualisieren - oder eine Art Bericht aus einer Abfrage erzeugen. Heute lernen Sie, wie man eine Abfrage schreibt, deren Ausgabe zu einer anderen Abfrage oder SQL-Anweisung führt.


Warum sollte man überhaupt eine SQL-Anweisung aus einer Abfrage erzeugen? Zunächst einmal ist das eine Sache der Vereinfachung und Effizienz. Man ist natürlich nicht gezwungen, SQL-Anweisungen zu generieren, würde dann aber auf eine der leistungsfähigsten Möglichkeiten von SQL verzichten, und zwar auf ein Merkmal, das ohnehin noch zu wenig bekannt ist.


Normalerweise lassen sich alle SQL-Anweisungen manuell erzeugen und auslösen, was aber in bestimmten Situationen sehr mühselig sein kann. Andererseits kann ein wichtiger Termin das Generieren von SQL-Anweisungen erforderlich machen, wenn zum Beispiel Ihr Chef allen 90 Benutzern in der Marketing-Abteilung den Zugriff auf eine neue Tabelle gewähren möchte (Sie aber nach Hause wollen). Da einige Benutzer dieser Datenbank nicht im Marketing arbeiten, kann man nicht einfach den Zugriff auf die Tabelle öffentlich gewähren. Bei mehreren Benutzergruppen mit unterschiedlichen Zugriffstypen sollte man die Rollensicherheit durchsetzen - eine integrierte Methode, die den Benutzerzugriff auf Daten steuert. In diesem Fall kann man eine SQL-Anweisung schreiben, die GRANT-Anweisungen für alle Personen in der Marketing-Abteilung generiert. Diese Anweisung sorgt also dafür, daß jeder Benutzer die geeignete(n) Rolle(n) erhält.


Man könnte genügend Beispiele anführen, wo es vorteilhaft ist, eine SQL-Anweisung als Ausgabe an eine andere Anweisung zu produzieren. Manchmal muß man viele ähnliche SQL-Anweisungen als Gruppe ausführen oder DDL aus dem Systemkatalog regenerieren. Erzeugt man SQL als Ausgabe von einer anderen Anweisung, erhält man immer die Daten für die Ausgabe entweder vom Systemkatalog oder den Schematabellen in der Datenbank. Abbildung 17.1 verdeutlicht dieses Verfahren.


Wie Abbildung 17.1 zeigt, kann eine SELECT-Anweisung an die Datenbank ausgelöst werden, die ihre Ergebnismenge entweder vom Systemkatalog oder von Anwendungstabellen in der Datenbank bezieht. Die Anweisung kann die abgerufenen Daten in einer oder mehreren SQL-Anweisungen anordnen. Gibt die Anweisung eine Zeile zurück, läßt man eine SQL-Anweisung generieren. Erhält man 100 Zeilen aus der Anweisung zurück, wird man 100 SQL-Anweisungen generieren lassen. Bei erfolgreicher Generierung von SQL-Code aus der Datenbank kann man diesen Code wiederum auf die Datenbank anwenden und damit eine Reihe von Abfragen oder Datenbankaktionen auslösen.


Die Beispiele dieser Lektion zeigen, wie man Ausgaben in der Form von SQL-Anweisungen produziert. Die meisten Informationen kommen dabei aus dem Systemkatalog, so daß Sie sich gegebenenfalls noch einmal mit den gestrigen Themen befassen sollten.


Abbildung 17.1:
Das Generieren von SQL aus der Datenbank

Die heutigen Beispiele arbeiten mit Personal Oracle8. Wie immer sollten Sie die heute behandelten Konzepte auf die Syntax Ihrer konkreten Datenbankimplementierung übertragen.


Verschiedene Befehle von SQL*Plus

In den heutigen Beispielen kommen einige neue Befehle vor. Diese sogenannten SQL*Plus-Befehle sind spezifisch für Personal Oracle8 und steuern das Format der Ausgabe. (Siehe dazu Tag 20.) SQL*Plus-Befehle werden an der Aufforderung SQL> ausgelöst oder können in einer Datei verwendet werden.


Die hier behandelten Befehle gehören zwar speziell zu Oracle, in anderen Implementierungen (wie zum Beispiel Transact-SQL) gibt es aber ähnliche Befehle. (Siehe dazu insbesondere Tag 19.)


SET ECHO ON/OFF

Der Befehl SET ECHO ON aktiviert die Anzeige von SQL-Anweisungen, die aus einer Befehlsdatei über den Befehl START ausgeführt werden. Mit SET ECHO OFF schaltet man die Echoanzeige der SQL-Anweisungen aus und läßt nur die Anzeige der eigentlichen Ausgaben zu.


SET ECHO [ ON | OFF ]



SET FEEDBACK ON/OFF

Zeigt die Anzahl der aus einer Abfrage zurückgegebenen Datensätze an. Wenn eine SELECT-Anweisung zum Beispiel 30 Zeilen zurückgibt, lautet die Rückmeldung:


30 Zeilen ausgewählt.


SET FEEDBACK ON schaltet die Anzeige ein; SET FEEDBACK OFF oder SET FEEDBACK blendet den Zeilenzähler von der Ausgabe aus.


SET FEEDBACK [ ON | OFF ]



SET HEADING ON/OFF

Steuert die Anzeige der Spaltenüberschriften in der Ausgabe. SET HEADING ON schaltet die Anzeige ein, SET HEADING OFF blendet die Spaltenüberschriften von der Ausgabe aus.


SET HEADING [ ON | OFF ]



SPOOL Dateiname/OFF

Mit Spooling bezeichnet man die Weiterleitung der Abfrageergebnisse in eine Datei. Mit dem Befehl


SPOOL Dateiname


öffnet man eine Spool-Datei unter dem angegebenen Dateinamen. Der Befehl


SPOOL OFF


schließt die Spool-Datei. Fehlt die Angabe einer Erweiterung in Dateiname, wird .LST als Standard angenommen.



START Dateiname

Die meisten bisher behandelten SQL-Befehle wurden an der Aufforderung SQL> ausgeführt. Man kann die Befehle auch in eine Datei schreiben und diese Datei dann mit dem Befehl


START Dateiname


ausführen. Wenn keine Dateierweiterung angegeben ist, verwendet SQL*Plus die Standarderweiterung .SQL.



EDIT Dateiname

Der Befehl EDIT (Kurzform ED) startet einen Editor und lädt die als Dateiname angegebene Datei zur Bearbeitung. Mit diesem Befehl läßt sich auch der Inhalt der Spool-Datei bearbeiten. EDIT ohne Dateiname schreibt den Inhalt des SQL-Puffers in eine Datei mit dem Standardnamen AFIEDT.BUF, ruft den Editor auf und lädt die Datei AFIEDT.BUF zur Bearbeitung. Ist die Datei AFIEDT.BUF bereits vorhanden, wird sie überschrieben. Wenn die als Dateiname angegebene Datei nicht existiert, erscheint die Frage, ob Sie die Datei neu anlegen möchten. Fehlt die Angabe einer Erweiterung in Dateiname, wird .SQL als Standard angenommen.


ED[IT] Dateiname



Zeilen in allen Tabellen zählen

Das erste Beispiel zeigt, wie man die Spool-Datei bearbeitet, um belanglose Zeilen im generierten Code zu entfernen und Syntaxfehler bei der Ausführung der SQL-Anweisungen zu vermeiden.


Die Bearbeitungsmethoden zeigen wir nur im ersten Beispiel und übergehen diesen Schritt in den übrigen Beispielen der heutigen Lektion. Mittlerweile dürfte Ihnen die grundlegende Syntax der SQL-Anweisungen vertraut sein. Darüber hinaus sind Sie nicht auf einen bestimmten Editor festgelegt, sondern können die Spool-Datei auch mit anderen Werkzeugen bearbeiten.

Als erstes bemühen wir die Funktion zum Zählen aller Zeilen in einer Tabelle: COUNT(*). Sie wissen bereits, wie man einen Zähler für alle Zeilen in einer einzelnen Tabelle auswählt. Dazu folgendes Beispiel:


SQL> SELECT COUNT(*)
2 FROM RECHNUNGEN;


COUNT(*)
---------
10

1 Zeile wurde ausgewählt.


Nehmen wir nun an, daß Sie alle Zeilen in allen Tabellen, die Sie besitzen oder die sich in Ihrem Schema befinden, zählen möchten. Die folgende Liste zeigt als Beispiel alle Tabellen, deren Eigentümer Sie sind.


SELECT * FROM CAT;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
ACCT_PAY TABLE
ACCT_REC TABLE
CUSTOMERS TABLE
EMPLOYEES TABLE
HISTORY TABLE
INVOICES TABLE
ORDERS TABLE
PRODUCTS TABLE
PROJECTS TABLE
VENDORS TABLE

10 Zeilen ausgewählt.


Um die Gesamtzahl der Zeilen in diesen Tabellen zu ermitteln, könnten Sie die Anweisung COUNT(*)auf jede einzelne Tabelle anwenden. Dieses manuelle Verfahren ist recht umständlich.

Die folgende SELECT-Anweisung erzeugt weitere SELECT-Anweisungen, um einen Zeilenzähler für alle oben aufgeführten Tabellen zu erhalten.


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL CNT.SQL
SQL> SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'
2 FROM CAT;

SELECT COUNT(*) FROM ACCT_PAY;
SELECT COUNT(*) FROM ACCT_REC;
SELECT COUNT(*) FROM CUSTOMERS;
SELECT COUNT(*) FROM EMPLOYEES;
SELECT COUNT(*) FROM HISTORY;
SELECT COUNT(*) FROM INVOICES;
SELECT COUNT(*) FROM ORDERS;
SELECT COUNT(*) FROM PRODUCTS;
SELECT COUNT(*) FROM PROJECTS;
SELECT COUNT(*) FROM VENDORS;


Zunächst verdichtet man mit den SQL*Plus-Befehlen SET ECHO OFF, SET FEEDBACK OFF und SET HEADING OFF die Ausgabe auf das, was eigentlich auszuwählen ist. Die Ausgabe soll nämlich nicht als Bericht dienen, sondern ausführbare SQL-Anweisungen liefern. Der nächste Schritt leitet mit dem SPOOL-Befehl die Ausgabe in die Datei CNT.SQL um. Die SQL-Anweisung im letzten Schritt produziert Ausgaben in Form anderer Anweisungen. Beachten Sie die Apostrophe für die Auswahl einer literalen Zeichenfolge. Mit Hilfe der Apostrophe und dem Verkettungsoperator (||) lassen sich die eigentlichen Daten mit literalen Strings verbinden, um eine andere SQL-Anweisung zu bilden. Das Beispiel wählt die Daten aus dem Systemkatalog aus.

Bevor Sie die Ausgabedatei ausführen, ist eine Bearbeitung erforderlich, um Korrekturen der Syntax und weitere Anpassungen in der erzeugten Datei vorzunehmen.

SQL> SPOOL OFF
SQL> ED CNT.SQL


SQL> SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'
2 FROM CAT;

SELECT COUNT(*) FROM ACCT_PAY;
SELECT COUNT(*) FROM ACCT_REC;
SELECT COUNT(*) FROM CUSTOMERS;
SELECT COUNT(*) FROM EMPLOYEES;
SELECT COUNT(*) FROM HISTORY;
SELECT COUNT(*) FROM INVOICES;
SELECT COUNT(*) FROM ORDERS;
SELECT COUNT(*) FROM PRODUCTS;
SELECT COUNT(*) FROM PROJECTS;
SELECT COUNT(*) FROM VENDORS;
SQL> SPOOL OFF


Der Befehl SPOOL OFF schließt die Spool-Datei. Mit dem Befehl ED ruft man den Editor auf und öffnet die Datei CNT.SQL - das heißt die gerade erzeugte Datei - zur Bearbeitung. Die obige Ausgabe zeigt den Inhalt dieser Datei, aus der nun alle unnötigen Zeilen zu entfernen sind. Auf jeden Fall ist die SELECT-Anweisung zu löschen, mit der die Ergebnisse realisiert wurden, da die Eingabeaufforderung SQL> zu Fehlern bei der späteren Ausführung der Datei führt. Das gleiche trifft auf das SPOOL OFF am Ende der Datei zu.

Nach der Bearbeitung sollte die Datei das folgende Aussehen haben. Beachten Sie, daß jede Zeile eine gültige SQL-Anweisung darstellt.


SELECT COUNT(*) FROM ACCT_PAY;
SELECT COUNT(*) FROM ACCT_REC;
SELECT COUNT(*) FROM CUSTOMERS;
SELECT COUNT(*) FROM EMPLOYEES;
SELECT COUNT(*) FROM HISTORY;
SELECT COUNT(*) FROM INVOICES;
SELECT COUNT(*) FROM ORDERS;
SELECT COUNT(*) FROM PRODUCTS;
SELECT COUNT(*) FROM PROJECTS;
SELECT COUNT(*) FROM VENDORS;


Führen Sie nun die Datei aus:


SQL> SET ECHO ON
SQL> SET HEADING ON
SQL> START CNT.SQL

SQL> SELECT COUNT(*) FROM ACCT_PAY;

COUNT(*)
---------
7
SQL> SELECT COUNT(*) FROM ACCT_REC;

COUNT(*)
---------
9
SQL> SELECT COUNT(*) FROM CUSTOMERS;

COUNT(*)
---------
5
SQL> SELECT COUNT(*) FROM EMPLOYEES;

COUNT(*)
---------
10

SQL> SELECT COUNT(*) FROM HISTORY;

COUNT(*)
---------
26
SQL> SELECT COUNT(*) FROM INVOICES;

COUNT(*)
---------
0
SQL> SELECT COUNT(*) FROM ORDERS;

COUNT(*)
---------
0
SQL> SELECT COUNT(*) FROM PRODUCTS;

COUNT(*)
---------
10
SQL> SELECT COUNT(*) FROM PROJECTS;

COUNT(*)
---------
16
SQL> SELECT COUNT(*) FROM VENDORS;

COUNT(*)
---------
22
SQL>


Der Befehl SET ECHO ON schaltet die Anzeige der Anweisungen ein, wenn diese aus einer Datei heraus ausgeführt werden. Mit SET HEADING ON aktiviert man die Anzeige der Spaltenüberschrift COUNT(*) für die einzelnen SELECT-Anweisungen. Wenn Sie zusätzlich den Befehl

 

SET FEEDBACK ON


einbinden, erscheint nach jeder Zählanweisung


1 Zeile wurde ausgewählt.


in der Ausgabe. In diesem Beispiel wurde das SQL-Skript mit dem START-Befehl von SQL*Plus ausgeführt. Bei lediglich 10 Tabellen ist die Arbeitserleichterung vielleicht noch nicht so deutlich geworden. Stellen Sie sich das Ganze aber einmal mit 50 oder mehr Tabellen vor!

Der richtige Einsatz der Apostrophe (einfache Anführungszeichen) ist bei der Generierung eines SQL-Skripts von entscheidender Bedeutung. Achten Sie darauf, daß Sie alle Elemente einbinden, um die zu generierende Anweisung vollständig zu beschreiben. Im Beispiel schließen die Apostrophe die Komponenten ein, die sich nicht aus einer Tabelle auswählen lassen; in diesem Fall 'SELECT COUNT(*) FROM' und ';'.


Mehreren Benutzern Systemprivilegien zuteilen

Wenn Sie für die Verwaltung von Benutzern verantwortlich sind (etwa als Datenbank-Administrator), werden Sie häufig Anforderungen nach Benutzer-IDs erhalten. Sie müssen den Benutzern nicht nur die geeigneten Privilegien für die Nutzung der Datenbank erteilen, sondern die Privilegien der Benutzer auch modifizieren, um den sich ändernden Anforderungen zu entsprechen. Mit den Angaben aus dem Systemkatalog können Sie nun GRANT-Anweisungen erzeugen, um vielen Benutzern Systemprivilegien oder Rollen zuzuteilen.


SQL> SET ECHO OFF
SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL GRANTS.SQL
SQL> SELECT 'GRANT CONNECT, RESOURCE TO ' || USERNAME || ';'
2 FROM SYS.DBA_USERS
3 WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','RYAN','PO8','DEMO')
4 /


GRANT CONNECT, RESOURCE TO KEVIN;
GRANT CONNECT, RESOURCE TO JOHN;
GRANT CONNECT, RESOURCE TO JUDITH;
GRANT CONNECT, RESOURCE TO STEVE;
GRANT CONNECT, RESOURCE TO RON;
GRANT CONNECT, RESOURCE TO MARY;
GRANT CONNECT, RESOURCE TO DEBRA;
GRANT CONNECT, RESOURCE TO CHRIS;
GRANT CONNECT, RESOURCE TO CAROL;
GRANT CONNECT, RESOURCE TO EDWARD;
GRANT CONNECT, RESOURCE TO BRANDON;
GRANT CONNECT, RESOURCE TO JACOB;


SQL> SPOOL OFF

SQL> START GRANTS.SQL

SQL> GRANT CONNECT, RESOURCE TO KEVIN;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO JOHN;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO JUDITH;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO STEVE;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO RON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO MARY;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO DEBRA;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO CHRIS;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO CAROL;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO EDWARD;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT CONNECT, RESOURCE TO JACOB;

Benutzerzugriff (Grant) wurde erteilt.


In diesem Beispiel haben Sie sich viele mühsame Tastenanschläge gespart, indem Sie alle GRANT-Anweisungen mit Hilfe einer einfachen SQL-Anweisung generiert haben und sie nicht einzeln eintippen mußten.

Dieses und auch die folgenden Beispiele übergehen den Schritt, in dem Sie die Ausgabedatei bearbeiten. Die Dateien werden bereits in der bearbeiteten Form wiedergegeben.


Privilegien Ihrer Tabellen an andere Benutzer übertragen

Die Zuteilung von Privilegien für eine Tabelle an einen anderen Benutzer ist genauso einfach wie das Auswählen eines Zeilenzählers auf einer Tabelle. Möchten Sie aber den Zugriff auf mehrere Tabellen für Rollen oder Benutzer festlegen, können Sie dafür per SQL ein Skript erzeugen lassen (es sei denn, daß Sie alle Anweisungen lieber selbst eintippen wollen).


Wiederholen wir zunächst eine einfache GRANT-Anweisung für eine Tabelle:


SQL> GRANT SELECT ON HISTORY TO BRANDON;


Benutzerzugriff (Grant) wurde erteilt.


Die nächste Anweisung erzeugt nun eine GRANT-Anweisung für alle 10 Tabellen in Ihrem Schema.


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL GRANTS.SQL
SQL> SELECT 'GRANT SELECT ON ' || TABLE_NAME || ' TO BRANDON;'
2 FROM CAT
3 /

GRANT SELECT ON ACCT_PAY TO BRANDON;
GRANT SELECT ON ACCT_REC TO BRANDON;
GRANT SELECT ON CUSTOMERS TO BRANDON;
GRANT SELECT ON EMPLOYEES TO BRANDON;
GRANT SELECT ON HISTORY TO BRANDON;
GRANT SELECT ON INVOICES TO BRANDON;
GRANT SELECT ON ORDERS TO BRANDON;
GRANT SELECT ON PRODUCTS TO BRANDON;
GRANT SELECT ON PROJECTS TO BRANDON;
GRANT SELECT ON VENDORS TO BRANDON;


Für jede Tabelle wurde automatisch eine GRANT-Anweisung vorbereitet. BRANDON erhält SELECT-Zugriff auf alle Tabellen.

Schließen Sie die Ausgabedatei mit dem Befehl SPOOL OFF. Wir gehen davon aus, daß die Datei dann bearbeitet wurde und sich ausführen läßt.


SQL> SPOOL OFF
SQL> SET FEEDBACK ON
SQL> START GRANTS.SQL

SQL> GRANT SELECT ON ACCT_PAY TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON ACCT_REC TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON CUSTOMERS TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON EMPLOYEES TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON HISTORY TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON INVOICES TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON ORDERS TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON PRODUCTS TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON PROJECTS TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.

SQL> GRANT SELECT ON VENDORS TO BRANDON;

Benutzerzugriff (Grant) wurde erteilt.


Mit dem Befehl SET ECHO ON aktivieren Sie das Echo. Durch das Einschalten der Rückmeldung mit SET FEEDBACK ON erscheint Benutzerzugriff (Grant) wurde erteilt. in der Ausgabe. In diesem Beispiel haben Sie mühelos das SELECT-Privileg an BRANDON für alle 10 Tabellen übertragen. Auch hier sollten Sie daran denken, daß man es oft mit weit mehr als zehn Tabellen zu tun hat.


Tabelleneinschränkungen beim Laden von Daten deaktivieren

Bei der Übernahme von Daten in Tabellen muß man manchmal die Einschränkungen (Constraints) auf den Tabellen deaktivieren. Nehmen wir an, daß Sie Ihre Tabellen geleert haben und nun Daten von Grund auf in die Tabellen einfügen. Wahrscheinlich weisen Ihre Tabellen Einschränkungen hinsichtlich der referentiellen Integrität auf (zum Beispiel Fremdschlüssel). Da Ihnen die Datenbank nicht erlaubt, in eine Tabelle Datenzeilen mit Verweisen auf eine andere Tabelle einzufügen (wenn die referenzierte Spalte nicht in der anderen Tabelle existiert), müssen Sie Einschränkungen deaktivieren, um die Initialisierung mit den neuen Daten durchführen zu können. Nach dem erfolgreichen Ladevorgang sind die Einschränkungen natürlich wieder zu aktivieren.


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL DISABLE.SQL
SQL> SELECT 'ALTER TABLE ' || TABLE_NAME ||
2 'DISABLE CONSTRAINT ' || CONSTRAINT_NAME || ';'
3 FROM SYS.DBA_CONSTRAINTS
4 WHERE OWNER = 'RYAN'
5 /


ALTER TABLE ACCT_PAY DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE ACCT_REC DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE CUSTOMERS DISABLE CONSTRAINT FK_CUSTOMER_ID;
ALTER TABLE HISTORY DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE INVOICES DISABLE CONSTRAINT FK_ACCT_ID;
ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ACCT_ID;


Das Ziel besteht in der Generierung einer Folge von ALTER TABLE-Anweisungen, die die Einschränkungen für alle Tabellen, deren Eigentümer RYAN ist, deaktivieren. Das am Ende der Auswahlanweisungen angehängte Semikolon schließt die SQL-Anweisungen ab.

SQL> SPOOL OFF

SQL> SET ECHO OFF
SQL> SET FEEDBACK ON
SQL> START DISABLE.SQL

Tabelle wurde geändert.

Tabelle wurde geändert.

Tabelle wurde geändert.

Tabelle wurde geändert.

Tabelle wurde geändert.

Tabelle wurde geändert.


Beachten Sie, daß das Echo deaktiviert ist. Die einzelnen Anweisungen sind also nicht zu sehen. Da die Rückmeldungen eingeschaltet sind, erscheinen die Ergebnisse:
Tabelle wurde geändert.


Wenn man sowohl Echo als auch Rückmeldung deaktiviert, ist gar nichts zu sehen, und es entsteht einfach eine Pause für die Ausführungszeit der ALTER TABLE-Anweisungen. Dann erscheint wieder die SQL>-Aufforderung.


Nun können Sie die Daten laden, ohne Fehler infolge von Einschränkungen befürchten zu müssen. Einschränkungen sind zwar sinnvoll, können aber auch beim Laden von Daten hinderlich sein. Das gleiche Konzept können Sie auf das Aktivieren der Tabelleneinschränkungen anwenden.



Mehrere Synonyme im Block erzeugen

Eine andere mühselige und aufreibende Aufgabe ist das Erzeugen zahlreicher Synonyme, ob sie nun öffentlich oder privat sind. Öffentliche Synonyme kann nur der DBA erzeugen, private Synonyme kann aber jeder Benutzer vergeben.


Das folgende Beispiel erzeugt öffentliche Synonyme für alle Tabellen, deren Eigentümer RYAN ist.


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL PUB_SYN.SQL
SQL> SELECT 'CREATE PUBLIC SYNONYM ' || TABLE_NAME || ' FOR ' ||
2 OWNER || '.' || TABLE_NAME || ';'
3 FROM SYS.DBA_TABLES
4 WHERE OWNER = 'RYAN'
5 /


CREATE PUBLIC SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;
CREATE PUBLIC SYNONYM ACCT_REC FOR RYAN.ACCT_REC;
CREATE PUBLIC SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;
CREATE PUBLIC SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;
CREATE PUBLIC SYNONYM HISTORY FOR RYAN.HISTORY;
CREATE PUBLIC SYNONYM INVOICES FOR RYAN.INVOICES;
CREATE PUBLIC SYNONYM ORDERS FOR RYAN.ORDERS;
CREATE PUBLIC SYNONYM PRODUCTS FOR RYAN.PRODUCTS;
CREATE PUBLIC SYNONYM PROJECTS FOR RYAN.PROJECTS;
CREATE PUBLIC SYNONYM VENDORS FOR RYAN.VENDORS;


Führen Sie nun die Datei aus.


SQL> SPOOL OFF
SQL> ED PUB_SYN.SQL
SQL> SET ECHO ON
SQL> SET FEEDBACK ON
SQL> START PUB_SYN.SQL

SQL> CREATE PUBLIC SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM ACCT_REC FOR RYAN.ACCT_REC;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM HISTORY FOR RYAN.HISTORY;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM INVOICES FOR RYAN.INVOICES;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM ORDERS FOR RYAN.ORDERS;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM PRODUCTS FOR RYAN.PRODUCTS;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM PROJECTS FOR RYAN.PROJECTS;

Synonym wurde angelegt.

SQL> CREATE PUBLIC SYNONYM VENDORS FOR RYAN.VENDORS;

Synonym wurde angelegt.


Fast augenblicklich haben alle Datenbankbenutzer Zugriff auf ein öffentliches Synonym für alle Tabellen, deren Eigentümer RYAN ist. Ein Benutzer braucht nun nicht die Tabelle qualifizieren, wenn er eine SELECT-Operation ausführt. (Qualifizieren bedeutet die Kennzeichnung des Tabelleneigentümers wie in RYAN.VENDORS.)

Nehmen wir nun an, daß es keine öffentlichen Synonyme gibt. BRANDON hat SELECT-Zugriff auf alle Tabellen, deren Eigentümer RYAN ist, und möchte nun private Synonyme erzeugen.


SQL> CONNECT BRANDON
Kennwort eingeben: *******
Connect durchgeführt.

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL PRIV_SYN.SQL
SQL> SELECT 'CREATE SYNONYM ' || TABLE_NAME || ' FOR ' ||
2 OWNER || '.' || TABLE_NAME || ';'
3 FROM ALL_TABLES
4 /

CREATE SYNONYM DUAL FOR SYS.DUAL;
CREATE SYNONYM AUDIT_ACTIONS FOR SYS.AUDIT_ACTIONS;
CREATE SYNONYM USER_PROFILE FOR SYSTEM.USER_PROFILE;
CREATE SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS;
CREATE SYNONYM ORDERS FOR RYAN.ORDERS;
CREATE SYNONYM PRODUCTS FOR RYAN.PRODUCTS;
CREATE SYNONYM INVOICES FOR RYAN.INVOICES;
CREATE SYNONYM ACCT_REC FOR RYAN.ACCT_REC;
CREATE SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY;
CREATE SYNONYM VENDORS FOR RYAN.VENDORS;
CREATE SYNONYM EMPLOYEES FOR RYAN.EMPLOYEES;
CREATE SYNONYM PROJECTS FOR RYAN.PROJECTS;
CREATE SYNONYM HISTORY FOR RYAN.HISTORY;


SQL> SPOOL OFF
SQL>

SQL> SET ECHO OFF
SQL> SET FEEDBACK ON
SQL> START PRIV_SYN.SQL

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.

Synonym wurde angelegt.


Ohne großen Aufwand verfügt BRANDON jetzt über Synonyme für alle Tabellen, deren Eigentümer RYAN ist, und BRANDON muß die Tabellennamen nicht mehr qualifizieren.


Sichten auf Tabellen erzeugen

Wenn man Sichten auf eine Gruppe von Tabellen erzeugen möchte, kann man etwa wie im folgenden Beispiel vorgehen:


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL VIEWS.SQL
SQL> SELECT 'CREATE VIEW ' || TABLE_NAME || '_VIEW AS SELECT * FROM ' ||
2 TABLE_NAME || ';'
3 FROM CAT
4 /


CREATE VIEW ACCT_PAY_VIEW AS SELECT * FROM ACCT_PAY;
CREATE VIEW ACCT_REC_VIEW AS SELECT * FROM ACCT_REC;
CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;
CREATE VIEW EMPLOYEES_VIEW AS SELECT * FROM EMPLOYEES;
CREATE VIEW HISTORY_VIEW AS SELECT * FROM HISTORY;
CREATE VIEW INVOICES_VIEW AS SELECT * FROM INVOICES;
CREATE VIEW ORDERS_VIEW AS SELECT * FROM ORDERS;
CREATE VIEW PRODUCTS_VIEW AS SELECT * FROM PRODUCTS;
CREATE VIEW PROJECTS_VIEW AS SELECT * FROM PROJECTS;
CREATE VIEW VENDORS_VIEW AS SELECT * FROM VENDORS;


SQL> SPOOL OFF
SQL> SET ECHO OFF
SQL> SET FEEDBACK ON
SQL> START VIEWS.SQL

View wurde angelegt.

View wurde angelegt.

View wurde angelegt.

View wurde angelegt.

View wurde angelegt.

View wurde angelegt.

View wurde angelegt.

View wurde angelegt.

View wurde angelegt.

View wurde angelegt.


Die obige SQL-Anweisung hat die Datei VIEWS.SQL generiert. Diese Ausgabedatei fungiert als Befehlsdatei und enthält SQL-Anweisungen, die Sichten auf allen angegebenen Tabellen erzeugen. Die Ausführung von VIEWS.SQL bringt lediglich die Ausgabe, daß die Sichten angelegt wurden.


Alle Tabellen in einem Schema leeren

Insbesondere in einer Entwicklungsumgebung muß man häufig Tabellen in den Urzustand bringen und die Daten erneut einlesen, damit man die Laderoutinen für Daten effektiv entwickeln und die Leistung von SQL-Anweisungen testen kann. In diesem Prozeß lassen sich Fehler erkennen und beseitigen, bevor man die zu entwickelnde oder zu testende Anwendung in eine Produktionsumgebung überführt.


Das folgende Beispiel leert alle Tabellen in einem angegebenen Schema.


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL TRUNC.SQL
SQL> SELECT 'TRUNCATE TABLE ' || TABLE_NAME || ';'
2 FROM ALL_TABLES
3 WHERE OWNER = 'RYAN'
4 /


TRUNCATE TABLE ACCT_PAY;
TRUNCATE TABLE ACCT_REC;
TRUNCATE TABLE CUSTOMERS;
TRUNCATE TABLE EMPLOYEES;
TRUNCATE TABLE HISTORY;
TRUNCATE TABLE INVOICES;
TRUNCATE TABLE ORDERS;
TRUNCATE TABLE PRODUCTS;
TRUNCATE TABLE PROJECTS;
TRUNCATE TABLE VENDORS;


Starten Sie nun das Skript, wenn Sie auf die Daten in den Tabellen verzichten oder diese wiederherstellen können.


SQL> SPOOL OFF
SQL> SET FEEDBACK ON
SQL> START TRUNC.SQL

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.

Tabelle mit TRUNCATE geleert.


In einfachster Weise haben Sie hiermit alle Daten aus den Tabellen des Eigentümers RYAN entfernt. Dieses Verfahren bietet sich an, wenn Sie im Entwicklungsprozeß die Tabellen mit neuen Daten füllen möchten.

Bevor Sie eine Operation wie das Leeren von Tabellen in einem Schema ausführen, sollten Sie immer eine gute Sicherungskopie der betreffenden Tabellen anlegen, selbst wenn Sie genau wissen, daß Sie diese Daten niemals wieder brauchen. (Wenn alles gelöscht ist, kommt garantiert jemand, der auf die alten Datenbestände nicht verzichten kann.)


Shell-Skripts mit SQL generieren

Mit SQL lassen sich auch andere Formen von Skripts generieren - beispielsweise Shell-Skripts. Ein Oracle-RDBMS-Server kann zum Beispiel in einer Unix-Umgebung laufen, die normalerweise größer als die Umgebung eines PC-Betriebssystems ist. Demzufolge muß die Dateiverwaltung in Unix höheren Ansprüchen gerecht werden. Wenn Sie Shell-Skripts mit Hilfe von SQL erstellen, lassen sich die Datenbankdateien leicht verwalten.


Das folgende Szenario löscht Tabellenbereiche in einer Datenbank. Obwohl man Tabellenbereiche direkt per SQL löschen kann, muß man die eigentlichen Datendateien, die mit diesen Tabellenbereichen verbunden sind, separat über das Betriebssystem entfernen.


Der erste Schritt besteht im Erstellen eines SQL-Skripts, das die Tabellenbereiche löscht.


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL DROP_TS.SQL
SQL> SELECT 'DROP TABLESPACE ' || TABLESPACE_NAME || ' INCLUDING CONTENTS;'
2 FROM SYS.DBA_TABLESPACES
3 /


DROP TABLESPACE SYSTEM INCLUDING CONTENTS;
DROP TABLESPACE RBS INCLUDING CONTENTS;
DROP TABLESPACE TEMP INCLUDING CONTENTS;
DROP TABLESPACE TOOLS INCLUDING CONTENTS;
DROP TABLESPACE USERS INCLUDING CONTENTS;


Nachdem die Tabellenbereiche gelöscht wurden, ist als nächstes ein Shell-Skript zu generieren, das die Datendateien auf Betriebssystemebene entfernt.


SQL> SPOOL OFF
SQL> SPOOL RM_FILES.SH
SQL> SELECT 'RM -F ' || FILE_NAME
2 FROM SYS.DBA_DATA_FILES
3 /

rm -f /disk01/orasys/db01/system0.dbf
rm -f /disk02/orasys/db01/rbs0.dbf
rm -f /disk03/orasys/db01/temp0.dbf
rm -f /disk04/orasys/db01/tools0.dbf
rm -f /disk05/orasys/db01/users0.dbf
SQL> SPOOL OFF
SQL>


Nachdem Sie beide Skripts erstellt haben, können Sie mit dem ersten Skript die Tabellenbereiche löschen und dann das Shell-Skript für das Betriebssystem ausführen, um die zugehörigen Dateien zu entfernen. Für die Verwaltung von Dateien und das Generieren von SQL-fremden Skripts werden Sie noch zahlreiche andere Einsatzmöglichkeiten finden.


Tabellen und Indizes rekonstruieren

Selbst wenn viele CASE-Werkzeuge die Rekonstruktion von Tabellen und Indizes erlauben, kann man für diesen Zweck immer reines SQL verwenden. Aus dem Systemkatalog lassen sich alle erforderlichen Informationen abrufen, um Tabellen und Indizes neu aufzubauen. Ohne eine prozedurale Sprache wie PL/SQL oder ein Shell-Skript läßt sich das aber nicht effizient realisieren.


In einem Shell-Skript verwenden wir normalerweise eingebettetes SQL. Die Funktionen der prozeduralen Sprachen sind um die erforderlichen Syntaxelemente wie zum Beispiel Kommas zu ergänzen. Das Skript muß über eine gewisse »Intelligenz« verfügen, damit zum Beispiel nach der letzten Spalte kein Komma gesetzt wird und die Klammern an der richtigen Stelle stehen. Suchen Sie sich die Werkzeuge zusammen, die für das Regenerieren von Objekten aus dem Systemkatalog verfügbar sind, gleichgültig, ob Sie mit C, Perl, Shell-Skripts, Cobol oder PL/SQL arbeiten.



Zusammenfassung

Wenn man Anweisungen direkt aus der Datenbank generieren läßt, spart man sich oft das mühsame Kodieren von SQL-Anweisungen und hat mehr Zeit für die Arbeit an anderen Teilen eines Projekts.


Auch wenn die heute behandelten Grundlagen ausschließlich an einer Oracle-Datenbank demonstriert wurden, können Sie die Konzepte auf andere relationale Datenbanken übertragen. Machen Sie sich in der bei Ihnen eingesetzten Implementierung mit den Syntaxvarianten und der Struktur des Systemkatalogs vertraut. Wenn Sie neuen Dingen aufgeschlossen gegenüberstehen, finden Sie ständig Möglichkeiten zur Generierung von SQL-Skripts - angefangen bei einfachen Anweisungen bis hin zum komplexen Systemmanagement.



Fragen & Antworten

Frage:

Wann sollte ich Anweisungen manuell ausführen, und wann ist es angebracht, SQL-Anweisungen per SQL generieren zu lassen?

Antwort:

Beantworten Sie sich die folgenden Fragen:

Frage:

Aus welchen Tabellen kann ich auswählen, um SQL-Anweisungen zu generieren?

Antwort:

Sie können aus allen Tabellen auswählen, auf die Sie Zugriff haben - Tabellen, die Sieselbst besitzen, oder Tabellen des Systemkatalogs. Denken Sie auch daran, daß man aus allen gültigen Objekten in der Datenbank auswählen kann, beispielsweise Sichten oder Snapshots.

Frage:

Gibt es irgendwelche Einschränkungen hinsichtlich der Anweisungen, die ich mit SQL generieren kann?

Antwort:

Prinzipiell läßt sich jede Anweisung, die Sie manuell schreiben können, auch mittels SQL generieren. Untersuchen Sie Ihre Implementierung nach speziellen Optionen für die Umleitung der Ausgabe in eine Datei und das Formatieren der Ausgabe in der gewünschten Weise. Die generierten Anweisungen können Sie im nachhinein noch modifizieren, da die Ausgabe in eine Datei umgeleitet wird.


Workshop


Kontrollfragen

1. Aus welchen zwei Quellen kann man SQL-Skripts generieren?


2. Funktioniert die folgende SQL-Anweisung? Ist die generierte Ausgabe funktionsfähig?


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL CNT.SQL
SQL> SELECT 'COUNT(*) FROM ' || TABLE_NAME || ';'
2 FROM CAT
3 /


3. Funktioniert die folgende SQL-Anweisung? Ist die generierte Ausgabe funktionsfähig?


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL GRANT.SQL
SQL> SELECT 'GRANT CONNECT DBA TO ' || USERNAME || ';'
2 FROM SYS.DBA_USERS
3 WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')
4 /


4. Funktioniert die folgende SQL-Anweisung? Ist die generierte Ausgabe funktionsfähig?


SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'
2 FROM SYS.DBA_USERS
3 WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')
4 /


5. Richtig oder falsch: Wenn man SQL-Anweisungen generiert, sollte man am besten die Rückmeldungen aktivieren (SET FEEDBACK ON).


6. Richtig oder falsch: Beim Generieren von SQL leitet man immer die Ausgabe in eine Protokolldatei um, damit man die Ereignisse nachvollziehen kann.


7. Richtig oder falsch: Bevor man Tabellen mit generierten SQL-Anweisungen leert, sollte man immer eine Sicherungskopie der Tabellen anlegen.


8. Was bedeutet der Befehl ED?


9. Was bewirkt der Befehl SPOOL OFF?



Übungen

1. Verwenden Sie die Sicht SYS.DBA_USERS (Personal Oracle8) und erzeugen Sie eine SQL-Anweisung, die eine Folge von GRANT-Anweisungen für fünf neue Benutzer generiert: John, Kevin, Ryan, Ron und Chris. Verwenden Sie die Spalte namens USERNAME. Gewähren Sie den Benutzern SELECT-Zugriff auf die Tabelle HISTORY_TBL.


2. Orientieren Sie sich an den Beispielen dieses Kapitels und erzeugen Sie SQL-Anweisungen, die andere ausführbare SQL-Anweisungen generieren.



Ein Imprint des Markt&Technik Buch- und Software-Verlag GmbH.
Elektronische Fassung des Titels: SQL in 21 Tagen, ISBN: 3-8272-2020-3


vorheriges KapitelTop Of PageInhaltsverzeichnisIndexInfoseitenächstes Kapitel