17
SQL-Anweisungen mit SQL generieren
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
Alle Tabellen in einem Schema leeren
Shell-Skripts mit SQL generieren
Tabellen und Indizes rekonstruieren
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: |
|
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;
|
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
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>
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;
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: 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.
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. |
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.
Markt+Technik Verlag. Elektronische Fassung des Titels: SQL in 21 Tagen, ISBN: 3-8272-2020-3
