PL/SQL Deklaration von Variablen
Werbung
Inhalt • Erkennen der PL/SQL Blockstruktur PL/SQL • Beschreiben von Variablen in PL/SQL Deklaration von Variablen • Unterschied zwischen PL/SQL und nicht-PL/SQL Variablen • Deklaration von PL/SQL Variablen • Ausführen eines PL/SQL Blocks Copyright © Oracle Corporation, 1998. All rights reserved. PL/SQL Block Struktur SQL2 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. PL/SQL Block Struktur DECLARE – Optional – Variable, Cursor, benutzerdefinierte Ausnahmen BEGIN - Befehle – SQL Statements – PL/SQL Statements – Ausnahmen – Optional – Aktionen, wenn Fehler auftreten END; DECLARE BEGIN / EXCEPTION END; SQL3 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. DECLARE v_variable VARCHAR2(5); BEGIN SELECT column_name INTO v_variable FROM table_name; EXCEPTION WHEN exception_name THEN DECLARE ... END; BEGIN / /* Prozedur mit "/" beenden*/ EXCEPTION END; SQL4 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Programm Konstrukte Block Typen Anonyme [DECLARE] Prozeduren PROCEDURE name IS Funktionen BEGIN --statements BEGIN --statements [EXCEPTION] [EXCEPTION] FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END; / END; / END; / SQL5 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Verwendung von Variablen Verwendung von Variablen für • Temporäres Speichern von Daten • Verändern gespeicherter Werte • Wiederverwendung • Bequeme Handhabung Anonymous block DECLARE Application trigger BEGIN EXCEPTION Database trigger Stored procedure/ function Application procedure/ function END; Packaged procedure/ function SQL6 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Verwendung von Variablen in PL/SQL • Deklaration und Initialisierung Variabler im Deklarationsteil. • Zuweisen neuer Werte zu den Variablen im Ausführungsteil. • Wertübergabe in den PL/SQL Block durch Parameter. • Ergebnisse durch Output-Variable ansehen . SQL7 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. SQL8 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Typen von Variablen • PL/SQL Variable – einfache – zusammengesetzte – Referenzen – LOB (large objects) • Nicht-PL/SQL Variable – Bind und Hostvariable SQL9 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Namensregeln • Zwei Variable können in unterschiedlichen Blöcken denselben Namen haben. • Der Variablenname (Identifier) darf nicht derselbe sein wie der Name einer Tabellenspalte im Block. DECLARE empno NUMBER(4); BEGIN SELECT empno INTO empno FROM emp WHERE ename = 'SMITH'; END; SQL11 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Deklaration von PL/SQL Variablen Syntax identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Beispiele Declare v_hiredate v_deptno v_location c_ comm DATE; NUMBER(2) NOT NULL := 10; VARCHAR2(13) := 'Atlanta'; CONSTANT NUMBER := 1400; SQL10 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Werte zu Variablen zuweisen Syntax identifier := expr; Beispiele Setze ein vordefiniertes Einstellungsdatum für einen neuen Angestellten v_hiredate := '31-DEC-98'; Der Name des Angestellten sei “Maduro.” v_ename := 'Maduro'; SQL12 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Variablen-Initialisierung und Schlüsselworte Verwendung Skalare Basisdatentypen • VARCHAR2 (maximum_length) • NUMBER [(precision, scale)] • := Zuweisungsoperator • DATE • DEFAULT • CHAR [(maximum_length)] • NOT NULL • LONG • LONG RAW • BOOLEAN • BINARY_INTEGER • PLS_INTEGER (bringt bei Überlauf Fehlermeldung) SQL13 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Deklaration skalarer Variabler Das %TYPE Attribut • Deklariere eine Variable passend zu: Beispiele v_job v_count v_total_sal v_orderdate c_tax_rate v_valid SQL14 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. VARCHAR2(9); BINARY_INTEGER := 0; NUMBER(9,2) := 0; DATE := SYSDATE + 7; CONSTANT NUMBER(3,2) := 8.25; BOOLEAN NOT NULL := TRUE; ... – einer Datenbank-Spaltendefinition – anderen vorher definierten Variablen • Vor %TYPE steht: – Die Datenbank-Tabelle und -Spalte – Der Name der vorher deklarierten Variablen SQL15 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. SQL16 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Beispiele Deklaration von BOOLEAN Variablen ... v_ename v_balance v_min_balance ... emp.ename%TYPE; NUMBER(7,2); v_balance%TYPE := 10; SQL17 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Zusammengesetzte Datentypen • haben nur die Werte TRUE, FALSE und NULL. • sind verbunden durch die logischen Operatoren AND, OR oder NOT. SQL18 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. LOB-Datentyp-Variable (> 4 Gigabyte) CLOB (char) Typen • PL/SQL Tabellen BLOB (bin) • PL/SQL RECORDS BFILE (Binärfile) NCLOB File mit nationalen Zeichen SQL19 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. SQL20 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Nicht-PL/SQL Variable Bind Variable Deklarieren auf Kommandoebene mit Variable Variable g_monthly_sal number; Variable g_ename varchar2(20) Speichere das Jahresgehalt in eine SQL*Plus Hostvariable. Bind Variable :g_monthly_sal := v_sal / 12; Server • Verwende die nicht-PL/SQL Variable als Host Variable. • Gekennzeichnet durch einen davor gestellten Doppelpunkt (:). SQL21 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. SQL22 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Zusammenfassung Zusammenfassung • PL/SQL Blöcke sind zusammengesetzt aus fogenden Teilen: • PL/SQL Variablennamen: – Deklaration (optional) DECLARE – Sind im Deklarationsteil definiert – Ausführung (erforderlich) BEGIN – Können einfach, zusammengesetzt, – Ausnahmebehandlung (optional) • Ein PL/SQL Block kann ein anonymer Block, eine Prozedur oder eine Funktion sein. SQL23 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. EXCEPTION END; Referenzen oder LOB Datentypen sein – Können zu anderen Variablen oder Datenbank-Objekten gehören – Können initialisiert werden. SQL24 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. PL/SQL Block Syntax • Statements können über mehrere Zeilen gehen Ausführbare Statements • Folgende Elemente gibt es: – Trennzeichen – Namen – Literale – Kommentare Copyright © Oracle Corporation, 1998. All rights reserved. SQL26 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. PL/SQL Block Syntax PL/SQL Block Syntax Namen (Identifiers) • Können bis zu 30 Zeichen enthalten • Dürfen keine reservierten Worte und keine doppelten Anführungszeichen enthalten Literale • Zeichen- und Datums-Literale müssen in einfache Apostrophe eingeschlossen sein. v_ename := 'Henderson'; • Müssen mit einem Buchstaben beginnen • Dürfen nicht den gleichen Namen wie eine Spalte in der Datenbank haben SQL27 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. • Zahlen können einfach oder in wissenschaftlicher Notation dargestellt sein. SQL28 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Kommentare • Kommentare, die nach einer Zeile enden, werden mit zwei Querstrichen eingeleitet (- -). • Kommentare über mehrere Zeilen werden in /* und */ eingeschlossen. Example ... v_sal NUMBER (9,2); BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal := v_sal * 12; END; -- This is the end of the transaction SQL29 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. PL/SQL Funktionen Beispiele • Erzeuge eine Adressliste für eine Firma v_mailing_address := v_name||CHR(10)|| v_address||CHR(10)||v_state|| CHR(10)||v_zip; • Konvertiere die Angestelltennamen in Kleinbuchstaben v_ename := LOWER(v_ename); SQL Funktionen in PL/SQL • Möglich: – Ein Zeichen numerisch – Ein Zeichen Character – Datentyp konvertieren – Date • Nicht möglich: – GREATEST – LEAST – DECODE – Gruppenfunktionen SQL30 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Datentypkonvertierung • Konvertieren von Daten in vergleichbare Typen. • Mischen von Datentypen kann Fehler zur Folge haben und verringert die Perfomance. • Konvertierungsfunktionen: – TO_CHAR – TO_DATE – TO_NUMBER SQL31 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Gleich wie in SQL BEGIN SELECT TO_CHAR(hiredate, FROM END; 'MON. DD, YYYY') emp; SQL32 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Datentypkonvertierung Operatoren in PL/SQL Dieses Statement ist fehlerhaft • Logische v_comment := USER||': '||SYSDATE; • Arithmetische das gleiche wie in SQL • Verkettung So ist es richtig v_comment := USER||': '||TO_CHAR(SYSDATE); • Klammerung zur Steuerung der Reihenfolge der Operationen • Exponential Operator (**) SQL33 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Operatoren in PL/SQL Beispiele • Inkrementieren des Index in der Schleife. v_count := v_count + 1; • Wert für ein Boolean Flag setzen. v_equal := (v_n1 = v_n2); • Abfragen, ob eine Personalnummer vorhanden ist. v_valid := (v_empno IS NOT NULL); SQL35 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. SQL34 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Verschachtelte Blöcke und Variablen-Gültigkeit Beispiel: ... x BINARY_INTEGER; BEGIN ... DECLARE y NUMBER; BEGIN ... END; ... END; Gültigkeit von x Gültigkeit von y SQL36 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Verwendung von Bind Variablen Wenn eine Bind-Variable in PL/SQL verwendet wird, muss der Name das Prefix Doppelpunkt (:) erhalten. Beispiel DECLARE v_sal BEGIN SELECT INTO FROM WHERE :salary END; emp.sal%TYPE; sal v_sal emp empno = 7369; := v_sal; SQL37 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Beispiel SQL> SQL> 2 3 4 5 6 7 variable a varchar2(20) declare ab varchar2(20); begin ab:='Das ist ein Test'; :a:= ab; end; / PL/SQL-Prozedur wurde erfolgreich abgeschlossen. SQL> print a A ------------------------------------------------------Das ist ein Test SQL39 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved. Code Namen Konventionen Vermeide Gleichheiten: • Die Namen von lokalen Variablen und formalen Parameter haben Vorrang vor den Namen der Datenbanktabellen. • Spaltennamen haben Vorrang vor den Namen lokaler Variabler. SQL38 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved.
