Was macht COALESCE?
Verwenden Sie COALESCE, um den ersten Nicht-NULL-Wert in einer Liste zu finden. Es wird kein Ausdruck in der Liste ausgewertet, es sei denn, die vorherigen Werte sind alle NULL.
DECLARE
value1 VARCHAR2(50) := NULL;
value2 VARCHAR2(50) := 'Hello, World!';
result VARCHAR2(50);
BEGIN
result := COALESCE(value1, value2, 'Default Value');
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END;Result: Hello, World!
Worauf sollte ich beim FOR LOOP ITERATOR achten?
- Deklarieren Sie niemals eine Variable mit dem gleichen Namen wie der Iterator der FOR-Schleife. Oracle deklariert sie implizit für Sie - aber nur zur Verwendung innerhalb des Schleifenkörpers.
- Versuchen Sie niemals, den Wert des Iterators innerhalb der Schleife zu ändern.
Der Code für eine einfache Schleife, welche die Zahlen von 1 bis 5 ausgibt?
DECLARE i INTEGER := 10; BEGIN FOR i IN 1 ... 5 LOOP DBMS_OUTPUT.put_line(i); END LOOP; DBMS_OUTPUT.put_line(i); END; /
Worauf muss ich bei EXCEPTION achten?
Verwenden Sie den EXCEPTION-Abschnitt, um Ausnahmen abzufangen, Fehler zu protokollieren und - falls Sie dies wünschen - eine Ausnahme erneut auszulösen, um den umschließenden Block anzuhalten.
Sie können mehrere WHEN-Klauseln haben und sogar mehrere Ausnahmen in einer einzigen WHEN-Klausel mit einem OR kombinieren. Die WHEN OTHERS-Klausel muss die letzte im EXCEPTION-Abschnitt sein.
BEGIN
DBMS_OUTPUT.PUT_LINE(SYSDATE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE)
END;Gibt es implizite Konvertierungen in Oracle?
Oracle führt alle möglichen impliziten Konvertierungen für uns durch, was sehr nützlich sein kann, aber auch zu schlampiger Programmierung führt. Am besten ist es, TO_*-Funktionen (z.B. TO_CHAR oder TO_DATE) zu verwenden, um Konvertierungen wie gewünscht explizit durchzuführen.
Das folgende liefert übrigens das Ergebnis 200, ist durch die Konvertierungen allerdings unschön.
DECLARE
l_number NUMBER := 100;
l_like_a_number VARCHAR2 (10) := '100';
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (l_number) + TO_NUMBER (l_like_a_number));
END;Mit welchen VIEWS kann ich mehr über die Spalten des aktuellen SCHEMAS herausfinden?
USER_TAB_COLUMNS - grundlegende Informationen über Spalten in einer Tabelle, Ansicht oder materialisierten Ansicht
USER_TAB_COLS - ähnlich wie USER_TAB_COLUMNS, unterscheidet jedoch zwischen physischen und virtuellen Spalten (virtuelle Spalten wurden in Oracle Database 11g hinzugefügt)
USER_TAB_COL_STATISTICS - verschiedene “Statistiken” über eine Spalte
Sie können USER auch durch “ALL” ersetzen, um Informationen über alle Datenbankschemas zu erhalten, die Datenbankobjekte enthalten, auf die Ihr aktuell verbundenes Schema Zugriffsrechte hat.
Ist die Aussage richtig oder falsch?Ersetzen Sie alle hart kodierten literalen Werte durch benannte Konstanten oder Funktionsaufrufe.
Richtig
Es ist immer am besten, hart kodierte Literale in Ihrem Code zu vermeiden, und zwar aus zwei Hauptgründen:
1. Wenn sich diese Werte ändern müssen, müssen Sie sie wahrscheinlich an mehr als einer Stelle ändern. Wenn Sie den Wert hinter einem Namen “verstecken”, müssen Sie ihn nur an einer Stelle ändern.
2. Sie verbessern die Lesbarkeit Ihres Codes. In diesem Fall gibt es keinen Grund zu der Annahme, dass sich die Monate, die Januar und Dezember zugeordnet sind, ändern werden. Das Problem ist vielmehr, dass “1” und “12” viele Dinge bedeuten können, nicht unbedingt die Anzahl der Monate. Wenn Sie eine benannte Konstante verwenden, ist die Bedeutung klar, wie Sie in der folgenden Neuformulierung der Prozedur sehen können:
CREATE OR REPLACE PROCEDURE set_pct_of_sales (company_id_in IN INTEGER, percent_in IN NUMBER)
IS
c_january CONSTANT PLS_INTEGER := 1;
c_december CONSTANT PLS_INTEGER := 12;
BEGIN
FOR month_index IN c_january .. c_december
LOOP
UPDATE monthly_sales
SET pct_of_sales = percent_in
WHERE company_id = company_id_in AND month_number = month_index;
END LOOP;
END set_pct_of_sales;Welche Attribute gibt es für explizite Cursor?
Oracle bietet eine Reihe von Attributen für explizite Cursor, die Ihnen helfen, den Zustand des Cursors zu verstehen (ist er offen oder geschlossen, wie viele Zeilen wurden durch den Cursor geholt usw.).
%ROWCOUNT: Gibt die Anzahl der vom Cursor zu diesem Zeitpunkt verarbeiteten Zeilen zurück (diese Zahl kann die Anzahl der abgerufenen oder der geänderten - also gelöschten, aktualisierten oder eingefügten - Zeilen wiedergeben).
%ISOPEN: Ein boolesches Attribut, das TRUE zurückgibt, wenn ein Cursor geöffnet ist.
%FOUND:: Ein boolesches Attribut, das TRUE zurückgibt, wenn der letzte Abruf eine Zeile ergeben hat, und FALSE, wenn nicht.
%NOTFOUND: Ein boolesches Attribut, das TRUE zurückgibt, wenn der letzte Abruf keine Zeile zurückgegeben hat, und FALSE, wenn dies der Fall war.
Bei expliziten Cursors werden sie als Cursorname%Attribut angegeben; zum Beispiel CURS_GET_EMPS%FOUND. Bei impliziten Cursorn werden sie als SQL%Attribut angegeben, z.B. SQL%NOTFOUND.
DECLARE
num_updated NUMBER;
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';
num_updated := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Number of rows updated: ' || num_updated);
END;Number of rows updated: 5
Welche zwei FOR Schleifen gibt es in PL/SQL?
- Numerische FOR-Schleife - Sie geben die niedrigen und hohen ganzzahligen Werte in der IN-Klausel an, um die Anzahl der Ausführungen des Schleifenkörpers zu bestimmen.
- Cursor FOR-Schleife - Sie geben eine SELECT-Anweisung an und der Schleifenkörper wird für jede durch die Abfrage abgerufene Zeile ausgeführt.
CREATE TABLE plch_employees (employee_id INTEGER , last_name VARCHAR2 (100) , salary NUMBER)
BEGIN
FOR emp_rec IN (SELECT last_name FROM plch_employees)
LOOP
DBMS_OUTPUT.put_line (emp_rec.last_name);
END LOOP;
END;Eine Cursor FOR Schleife kann mit impliziten oder expliziten Cursorn verwendet werden.
BEGIN
FOR item IN (
SELECT last_name FROM employees
WHERE job_id LIKE '%CLERK%'
ORDER BY last_name
)
LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name);
END LOOP;
END;Die obige Form der Cursor FOR LOOP-Anweisung verwendet einen impliziten Cursor und wird als implizite Cursor FOR LOOP-Anweisung bezeichnet.
Wenn Sie die SELECT-Anweisung mehr als einmal in derselben PL/SQL-Einheit verwenden, definieren Sie einen expliziten Cursor dafür und geben diesen Cursor in der Cursor FOR LOOP-Anweisung an (die dann explizite Cursor FOR LOOP-Anweisung genannt wird), wie unten gezeigt:
DECLARE
CURSOR c1 IS
SELECT last_name FROM employees
WHERE job_id LIKE '%CLERK%'
ORDER BY last_name;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name);
END LOOP;
END;Ein paar Fakten zum Package UTL_FILE
- Sie können nicht wahllos auf Zeilen in einer Datei zugreifen. Sie müssen die Zeilen der Reihe nach ab der ersten Zeile der Datei lesen.
- Die maximale Größe (Anzahl der Bytes) einer Zeile in einer Datei, die gelesen werden kann, beträgt 32.767 Bytes.
- Dateien werden erst geschlossen, wenn Sie sie explizit mit einem Aufruf von UTL_FILE.FCLOSE oder UTL_FILE.FCLOSE_ALL schließen oder die Sitzung beenden.
- UTL_FILE gibt es schon seit Oracle 7.
Ein Beispiel für das Package UTL_FILE
DECLARE v_file UTL_FILE.FILE_TYPE; v_dir VARCHAR2(100) := 'MY_DIRECTORY'; -- replace with your directory name v_filename VARCHAR2(100) := 'MY_FILE.txt'; -- replace with your file name BEGIN v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'w'); FOR i IN 1..10 LOOP UTL_FILE.PUT_LINE(v_file, 'This is line ' || i); END LOOP; UTL_FILE.FCLOSE(v_file); END;
In diesem Beispiel deklarieren wir eine Variable v_file vom Typ UTL_FILE.FILE_TYPE, die die Datei darstellt, in die wir schreiben werden. Dann geben wir das Verzeichnis und den Dateinamen an, die wir verwenden wollen, indem wir den Variablen v_dir und v_filename Werte zuweisen.
Wir verwenden die Funktion UTL_FILE.FOPEN, um die Datei im Schreibmodus (‘w’) zu öffnen, und weisen v_file das entsprechende Dateimanöver zu. Dann gehen wir in einer Schleife durch einige Daten, in diesem Fall nur die Zahlen 1 bis 10, und verwenden die Funktion UTL_FILE.PUT_LINE, um jede Zeile in die Datei zu schreiben. Schließlich schließen wir die Datei mit UTL_FILE.FCLOSE.
Beachten Sie, dass Sie über die entsprechenden Rechte verfügen müssen, um das in v_dir angegebene Verzeichnis lesen und schreiben zu können. Beachten Sie auch, dass das UTL_FILE-Paket nicht in allen Versionen der Oracle-Datenbank verfügbar ist. Prüfen Sie daher die Dokumentation Ihrer Version.
Was kann ich bei hierarchischen Daten verwenden?
CONNECT BY PRIOR
In meinem Fall waren das die Unterstufen der Konstruktionsstückliste. Mit CONNECT BY PRIOR konnte ich alle jeweiligen Unterstufen bekommen.
SELECT * FROM eng_part_structure_tab WHERE structure_id = 'STD' START WITH part_no = '6GF62003AA10' AND part_rev = 'R03' CONNECT BY PRIOR sub_part_no = part_no AND PRIOR sub_part_rev = part_rev;
Wie oft wird der Hauptteil der folgenden Schleife ausgeführt?
BEGIN
FOR year_index IN 1990 .. 1999
LOOP
IF MOD (year_index, 2) = 0
THEN
DBMS_OUTPUT.put_line (year_index);
END IF;
END LOOP;
END;10
Es gibt 10 Jahre zwischen 1990 und 1999, einschließlich. Und eine numerische FOR-Schleife schließt die Endpunkte in ihre Iterationen ein.
Was wird nach der Ausführung dieses Blocks auf dem Bildschirm angezeigt:
DECLARE
l_selection VARCHAR2 (1) := 'C';
l_value VARCHAR2 (100) := 'Fruit';
BEGIN
CASE l_selection
WHEN 'A'
THEN
l_value := 'Apple';
WHEN 'B'
THEN
l_value := 'Banana';
END CASE;
DBMS_OUTPUT.put_line (NVL (l_value, 'NULL'));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error');
END;Error
- Wenn keine Übereinstimmung für WHEN-Klauseln in einer CASE-Anweisung gefunden wird und diese Anweisung keine ELSE-Klausel enthält, gibt Oracle die Fehlermeldung “ORA-06592: CASE not found while executing CASE statement” aus.
- Sie müssen keine ELSE-Klausel für einen CASE-Ausdruck angeben. Wenn in den WHEN-Klauseln keine Übereinstimmung gefunden wird, gibt Oracle einen NULL-Wert für den Ausdruck zurück.
- Wenn Sie eine CASE-Anweisung schreiben, sollten Sie immer eine ELSE-Klausel einfügen - es sei denn, Sie sind absolut sicher, dass eine der WHEN-Klauseln immer TRUE ergibt. Das ist eine große Annahme.
Ich erstelle die folgende Tabelle
CREATE TABLE plch_stuff
(amount NUMBER , rating INTEGER)
BEGIN
FOR indx IN 1 .. 20
LOOP
INSERT INTO plch_stuff
VALUES (indx, indx * 10);
END LOOP;
COMMIT;
END;Wie kann ich jetzt eine 10 anzeigen lassen?
BEGIN
UPDATE plch_stuff
SET rating = -1 * rating
WHERE MOD (amount, 2) = 0;
/*HOWMANY*/
END;sys.DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
SQL%ROWCOUNT zeigt Ihnen in der Tat an, wie viele Zeilen durch die zuletzt ausgeführte SQL-Anweisung in Ihrer Sitzung geändert wurden. Da 10 Zeilen geändert wurden, wird “10” angezeigt.
Was kann ich mit SAVEPOINT machen?
Verwenden Sie SAVEPOINT, um einen Punkt in der Transaktion Ihrer Sitzung zu definieren, zu dem Sie ein Rollback durchführen können. Mit dieser Funktion können Sie Änderungen in Ihrer Sitzung nur teilweise rückgängig machen.
DECLARE
balance NUMBER := 1000;
withdraw_amount NUMBER := 500;
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting balance: ' || balance);
SAVEPOINT start_transaction;
-- Withdraw 500 from the balance
balance := balance - withdraw_amount;
-- Check if the balance is negative
IF balance < 0 THEN
DBMS_OUTPUT.PUT_LINE('Insufficient balance. Rolling back transaction.');
-- Roll back to the savepoint
ROLLBACK TO start_transaction;
ELSE
DBMS_OUTPUT.PUT_LINE('Withdrawal successful. New balance: ' || balance);
END IF;
END;Sind Deklarationen in einem PL/SQL-Block optional?
Sie brauchen keine Deklaration (und auch keinen Deklarationsabschnitt) in einen Block aufzunehmen, es sei denn, Sie müssen eine lokale Variable, Konstante, einen Typ oder ein anderes Element deklarieren.
Sie können durchaus Blöcke schreiben und ausführen, die keine lokalen Elemente zu deklarieren haben und daher weder Deklarationen noch einen Deklarationsabschnitt enthalten.
Was ist Pragma Autonomous_Transaction und wo muss ich es im Code platzieren?
- Wenn Sie das Pragma AUTONOMOUS_TRANSACTION in Pl/SQL verwenden, wird eine von der Transaktion, in der Sie gerade arbeiten, getrennte Transaktion erstellt.
- Die PRAGMA-Anweisung, die eine Programmeinheit als eigenständige Transaktion definiert, kann an beliebiger Stelle im Deklarationsteil des Unterprogramms stehen. Sie darf nicht in den Kopf des Unterprogramms gestellt werden.
- Seien Sie äußerst vorsichtig, dieses Pragma nicht zu missbrauchen, denn Sie sollten es nur für die Protokollierung verwenden. Wenn Sie ein AUTONOMOUS_TRANSACTION-Pragma nicht nur für die Protokollierung verwenden müssen, ist Ihr Datenbankdesign möglicherweise fehlerhaft.
CREATE OR REPLACE PROCEDURE plch_save_changes (
keys_in IN DBMS_SQL.number_table)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_value plch_table.my_value%TYPE;
BEGIN
FOR indx IN 1 .. keys_in.COUNT
LOOP
l_value := 'Value' || keys_in.count;
UPDATE plch_table
SET my_value = l_value
WHERE my_key = keys_in (indx);
END LOOP;
COMMIT;
END;Was ist der Vorteil bei so einer Verwendung einer Prozedur?
DECLARE
l_number NUMBER;
PROCEDURE initialize
IS
BEGIN
l_number := 100;
END;
BEGIN
initialize;
DBMS_OUTPUT.put_line (l_number);
END;Bei dieser Wahl weise ich in der Deklaration keinen Standardwert zu. Stattdessen definiere ich ein verschachteltes Unterprogramm, das (daher der Name) “alle” Variablen in meinem Block initialisieren soll (in diesem Fall nur eine).
Dann rufe ich initialize als erste Zeile in meinem Ausführungsblock auf.
Für etwas so Triviales wie die Zuweisung von 100 an eine Zahl ist das ein Overkill. In komplexeren Programmen, in denen mehrere Schritte erforderlich sind, um den ausführbaren Teil für das richtige Verhalten “einzurichten”, empfehle ich dringend, ein verschachteltes Unterprogramm für die Initialisierung zu deklarieren und zu verwenden.
Dieser Ansatz hat zwei große Vorteile:
- Wenn die Zuweisung des Standardwerts eine Ausnahme auslöst (in der Regel VALUE_ERROR), können Sie diese innerhalb des Blocks behandeln (wenn die Zuweisung im Deklarationsabschnitt erfolgt, wird die Ausnahme immer unbehandelt weitergegeben).
- Der ausführbare Teil ist jetzt viel sauberer. Es ist einfacher, sich auf den Hauptzweck des Blocks zu konzentrieren und sich nicht von all den Einrichtungsschritten ablenken zu lassen.
Wie kann ich einer Variable den gleichen Datentyp einer Variablen aus einer bestimmten Tabelle geben?
PACKAGE types IS dollar_amount NUMBER(20,2); END;
Das %TYPE-Attribut kann verwendet werden, um den Datentyp einer Variablen oder Konstanten mit einem anderen, zuvor definierten Element (einer Spalte in einer Tabelle, einem Ausdruck in einem Cursor oder einer PL/SQL-Variablen) zu “verankern”.
Einer der schönsten Aspekte verankerter Deklarationen, sowohl mit %TYPE als auch mit %ROWTYPE, besteht darin, dass, wenn sich die Deklaration des referenzierten Elements ändert, sich auch die Deklaration des referenzierenden Elements entsprechend ändert, wenn der Code, in dem es verwendet wird, das nächste Mal kompiliert wird.
DECLARE my_variable types.dollar_amount%TYPE; BEGIN my_variable := 1; END;
Was ist ein Trigger?
- Ein Trigger ist eine benannte Programmeinheit, die ausgeführt wird, wenn ein bestimmtes Ereignis in der Datenbank eintritt. Die häufigste Art von Trigger ist der DML-Trigger, der als Reaktion auf eine Update-, Insert- oder Delete-Operation in einer Tabelle ausgelöst wird.
- Sie können jedoch auch Trigger auf Datenbank- und Schemaebene (Data Definition Language - DDL) definieren, so dass z.B. bei der Ausführung einer CREATE/TRUNCATE/DROP-Anweisung ein Trigger ausgelöst wird.
- Ein Trigger kann ich z.B. so definieren
CREATE OR REPLACE TRIGGER plch_after_ddl
AFTER DDL
ON SCHEMA
BEGIN
DBMS_OUTPUT.put_line ('DDL executed!');
END;Dadurch wird z.B. bei den folgenden Anweisungen der Trigger ausgelöst:
- CREATE TABLE plch_table (col1 NUMBER)
- TRUNCATE TABLE plch_table
- DROP TABLE plch_table
Welche EXCEPTIONS gibt es?
Sie können eine Exception nach ihrem Namen abfangen oder mit WHEN OTHERS abfangen. Wenn Sie wissen, welche Exceptions ausgelöst werden könnten, ist es in der Regel besser, einen Handler für diese spezielle Exception einzubinden, damit Sie auch genauere Informationen darüber protokollieren können, was den Fehler verursacht haben könnte.
Wenn Ihre SELECT-INTO-Anweisung keine Zeilen identifiziert, löst Oracle die Ausnahme NO_DATA_FOUND aus. Sie können diese Ausnahme explizit durch den Namen abfangen:
WHEN NO_DATA_FOUND
oder Sie können sie zusammen mit anderen Fehlern abfangen, indem Sie
WHEN OTHERS
Sie können einen Ausnahmeabschnitt jedoch nicht an eine SQL-Anweisung anhängen, sondern nur an das Ende eines PL/SQL-Blocks.
Was ist an diesem Code falsch?
DECLARE
my_flavor plch_ice_cream.fav_flavor%TYPE;
BEGIN
SELECT fav_flavor
INTO my_flavor
FROM plch_ice_cream
WHERE name = 'Steven Feuerstein'
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No favorite for you!');
END;Sie können einen Exception-Abschnitt nicht zu einer Abfrage hinzufügen, sondern nur zu einem PL/SQL-Block.
