ORACLE und PL/SQL
Werbung
2 Deklaration von Variablen 2.1 2.2 2.3 2.4 2.5 2.6 2.7 Variablendeklarationen................................................................... 2-2 Variablentypen ............................................................................... 2-4 Skalare Datentypen ........................................................................ 2-6 Neue Datentypen seit 10g ............................................................ 2-10 Datentyp-Konvertierung................................................................ 2-12 2.5.1 Explizite Datentyp-Konvertierung..................................... 2-12 2.5.2 Implizite Datentyp-Konvertierung..................................... 2-14 Implizite Variablendeklarationen mit %TYPE ................................. 2-16 Gültigkeitsbereiche von Variablen ................................................ 2-18 1.2.066 / 4053 2-1 2 2 Deklaration von Variablen Deklaration von Variablen Unter der Deklaration von PL/SQL-Variablen versteht man die Angabe von Namen und Datentyp einer Variablen. Jede Variable, die im Ausführungs- oder Fehlerteil referenziert wird, muss im Deklarationsteil des gleichen Blocks oder eines übergeordneten Blocks deklariert werden. Durch die Deklaration wird für die Variable Speicherplatz reserviert, und durch den Namen der Variablen kann der ihr zugeordnete Speicherplatz referenziert werden. 2.1 Variablendeklarationen Syntax: variablenname [CONSTANT] datentyp [NOT NULL] [:= | DEFAULT wert]; Für Variablennamen gelten die üblichen Namenskonventionen: • maximal 30 Zeichen lang • müssen mit einem Buchstaben beginnen • Zahlen sind erlaubt, an Sonderzeichen sind nur _, $ und # erlaubt • reservierte Wörter sind verboten (außer in doppelten Hochkommata, aber nicht zu empfehlen) • Groß/Kleinschreibung ist unwichtig • Tabellen- und Spaltennamen sollten nicht als Variablennamen verwendet werden Eine Variable kann auch bereits bei der Deklaration initialisiert werden. Dies geschieht entweder mit dem Zuweisungsoperator (:=) oder durch das Schlüsselwort DEFAULT. Eine nicht initialisierte Variable hat den Wert NULL. Durch das Schlüsselwort CONSTANT wird eine Konstante deklariert. Sie muss initialisiert werden; ihr Wert kann später nicht mehr geändert werden. NOT NULL bedeutet, dass die Variable einen definierten Wert haben muss. Wird einer solchen Variablen im Programm der Wert NULL zugewiesen, so löst dies einen vordefinierten Fehler aus (VALUE_ERROR). NOT NULL-Variablen müssen daher initialisiert werden. 2-2 1.2.066 / 4053 Deklaration von Variablen 2 Variablendeklaration 2 Deklaration: Name und Datentyp es gelten die üblichen Namenskonventionen CONSTANT: Konstante, muss initialisiert werden NOT NULL: muss initialisiert werden := bzw. DEFAULT: Initialisierung Beispiele: v_nr v_name v_status v_ort c_pi NUMBER(8); VARCHAR2(25); BOOLEAN NOT NULL := FALSE; VARCHAR2(25) DEFAULT 'Boston'; CONSTANT NUMBER(3,2) := 3.14; www.unilog.integrata.de www.unilog-integrata.de 1.2.066 / 4053 4053 / 1.2.036 Folie 2 2-3 2 2.2 Deklaration von Variablen Variablentypen Bei PL/SQL-Variablen unterscheidet man folgende Kategorien von Datentypen: • Skalar: Halten einen einzelnen Wert. • Zusammengesetzt: Halten eine Gruppe zusammengehöriger Werte • Referenz: Halten einen Zeiger (Pointer), welche auf die Elemente anderer Programme zeigen • LOB (Large Objects): Halten Locator, die den Speicherort großer Objekte bezeichnen Daneben können in PL/SQL auch SQL*Plus-Austauschvariablen (durch ein ‘&‘ vor dem Variablennamen) und Nicht-PL/SQL-Variablen (durch einen ‘:‘ vor dem Variablennamen) referenziert werden. Unter NichtPL/SQL-Variablen versteht man Bind- und Hostvariablen. Bei Verwendung von SQL*Plus-Austauschvariablen wird der Benutzer einmalig beim Kompilieren des Programms aufgefordert, einen Wert für die Variable einzugeben. Ab dann ist die Variable bei jedem Start des Programms fest mit diesem Wert belegt. 2-4 1.2.066 / 4053 Deklaration von Variablen 2 Variablentypen 2 PL/SQL-Variable: Referenzierung: variablenname Skalar Zusammengesetzt Referenz LOB Nicht-PL/SQL-Variable: Referenzierung: :variablenname Bind- und Hostvariablen SQL*Plus-Austauschvariable: Referenzierung: &variablenname www.unilog.integrata.de www.unilog-integrata.de 1.2.066 / 4053 4053 / 1.2.036 Folie 3 2-5 2 2.3 Deklaration von Variablen Skalare Datentypen Alle unter SQL verfügbaren skalaren Datentypen sind auch unter PL/SQL-verfügbar, aber teilweise mit anderem Wertebereich (z. B. VARCHAR2). Die wichtigsten unter ihnen sind: VARCHAR2, NUMBER und DATE. Im Folgenden sind die wichtigsten skalaren Basistypen, die unter PL/SQL zur Verfügung stehen, aufgelistet. VARCHAR2(n): Zeichenkette mit variabler Länge bis 32.767 Bytes. Maximallänge n muss angegeben werden (VARCHAR2-Datenbankspalten fassen nur 4000 Bytes). NVARCHAR2(n): Nationale Zeichenkette mit variabler Länge bis 32.767 Bytes. Maximallänge n muss angegeben werden NUMBER[(m, n)]: Basistyp für Fest- und Fließkommazahlen, wobei m (max. 38 Stellen) die Ziffernstellen und n (von -84 bis 127) die Nachkommastellen angibt. Ein negatives n sorgt dafür, dass auf entsprechend viele Zehnerpotenzen gerundet wird. DATE: Basistyp für Datum und Uhrzeit. Beinhaltet die Tageszeit in Sekunden seit Mitternacht. Wertebereich von 4712 v.Chr. bis 9999 n. Chr. TIMESTAMP[(n)]: er ermöglicht die Angabe von Sekundenbruchteilen; n liegt dabei zwischen 0...9, Default-Wert ist 6; der entsprechende Initialisierungs-Parameter lautet NLS_TIMESTAMP_FORMAT. TIMESTAMP[(n)] WITH TIME ZONE: er beinhaltet zusätzlich die Abweichung von UTC (Coordinated Universal Time); zwei Werte werden dabei als gleich erachtet, wenn sie der gleichen Zeitzone angehören; der entsprechende Initialisierungs-Parameter lautet NLS_TIMESTAMP_TZ_FORMAT. TIMESTAMP[(n)] WITH LOCAL TIME ZONE: er beinhaltet ebenfalls die Abweichung von UTC, jedoch wird diese nicht explizit in der Datenbank gespeichert; stattdessen wird der Wert auf die Zeitzone der Datenbank umgerechnet und als solche eingetragen; ein SELECT liefert die lokale Session-Zeit. INTERVAL YEAR[(n)] TO MONTH: damit lassen sich Zeitintervalle in Jahren und Monaten angeben; die Anzahl an Stellen der Jahreszahl kann optional mit angegeben werden; Default-Wert ist 2; INTERVAL DAY[(n)] TO SECOND: damit lassen sich Zeitintervalle in Tagen, Stunden, Minuten, Sekunden und Sekundenbruchteilen angeben; die Anzahl an Stellen für den Tag kann optional mit angegeben werden; n liegt dabei zwischen 0..9, der Default-Wert ist 2. 2-6 1.2.066 / 4053 Deklaration von Variablen 2 CHAR[(n)]: Zeichenkette mit fester Länge bis 32.767 Bytes. (CHARDatenbankspalten fassen nur 2000 Bytes). Default für n ist 1. LONG(n): Zeichenkette mit variabler Länge bis 32.760 Bytes. (LONGDatenbankspalten fassen 2GByte). BOOLEAN: TRUE, FALSE oder NULL BINARY_INTEGER: +2147483647. Ganze Zahlen zwischen –2147483647 und BINARY_FLOAT und BINARY_DOUBLE: (seit 10g) Gleitkommazahlen zur Basis 2 nach IEEE754. PLS_INTEGER: Ganze Zahlen zwischen –2147483647 und +2147483647. Erfordern weniger Speicherplatz und sind schneller als NUMBER und BINARY_INTEGER, da sie direkt vom Prozessor verarbeitet werden. RAW: Binärdaten bis 32767 Bytes (RAW-Datenbankspalten fassen jedoch nur 2000 Bytes). ROWID: Entspricht der Adresse eines Datensatzes der Datenbank Hinweis: NCHAR, NVARCHAR und NCLOB haben seit Version 9i grundsätzlich das UNICODE-Format, da für den NATIONAL CHARACTER SET nur noch UNICODE-Zeichensätze zulässig sind. String-Funktionen können mit jeder beliebigen Kombination aus CHAR/ VARCHAR2/ NCHAR/ NVARCHAR2 umgehen, auch innerhalb ein und derselben Funktion. Intervalle können zu DateTime-Werten addiert oder von ihnen abgezogen werden und es ergibt sich wieder ein DateTime-Wert. Sie können aber auch miteinander addiert, voneinander subtrahiert, mit einer Zahl multipliziert oder durch eine dividiert werden, es ergibt sich immer wieder ein Intervall. 1.2.066 / 4053 2-7 2 Datentyp Char Varchar2 Deklaration von Variablen Sub-Typ character string varchar nvarchar2 Long Number decimal double precision float integer numeric real smallint Beispiel v_text1 char(50); v_text2 varchar2(50); v_text3 varchar2(50); v_var nvarchar2(10); v_long long; v_nu number(10,2); v_nu decimal; v_nu double precision; v_nu v_nu v_nu v_nu v_nu binary_float binary_double Boolean v_b Date timestamp Interval v_date date; v_ts timestamp(2); v_dauer interval year(2) to month; v_i raw; v_i rowid; v_i mlslabel; natural positiv raw rowid mlslabel 2-8 Länge: 1 .. 32767 Bytes Länge: 1 .. 32760 Bytes Werte: ±1e-130 .. 10e125 float; integer; numeric; real; smallint; v_int pls_integer; v_i binary_integer; v_i natural; v_i positiv; v_b binary_float; v_b binary_double; pls_integer binary_integer Gültigkeitsbereich Länge: 1 .. 32767 Bytes Länge: 1 .. 32767 Bytes boolean; Werte: -(231)...+231 -1 Werte: -(231)...+231 –1 0... 231 –1 1... 231 –1 Werte: -3.4e38 .. 3.4e38 Werte: -1.79e308 .. 1.79e308 Werte: True, false, null 4712 v. Chr - 9999 n.Chr wie date unterschiedlich Länge: 1 .. 32767 bytes 256 bytes 2..5 bytes 1.2.066 / 4053 Deklaration von Variablen 2 Skalare Datentypen Datentyp Sub-Typ Beispiel char character v_text1 char(50); Länge: 1 .. 32767 Bytes varchar2 string varchar v_text2 varchar2(50); v_text3 varchar2(50); Länge: 1 .. 32767 Bytes nvarchar2 v_var nvarchar2(10); Länge: 1 .. 32767 Bytes long v_long long; Länge: 1 .. 32760 Bytes number v_nu number(10,2); v_nu decimal; v_nu double precision; Werte: ±1e-130 .. 10e125 decimal double precision float integer numeric real smallint v_nu v_nu v_nu v_nu v_nu Gültigkeitsbereich float; integer; numeric; real; smallint; pls_integer v_int pls_integer; Werte: -(231)...+231 -1 binary_integer v_i v_i v_i binary_integer; natural; positiv; Werte: -(231)...+231 –1 0... 231 –1 1... 231 –1 v_b binary_float; Werte: -3.4e38 .. 3.4e38 natural positiv binary_float www.unilog.integrata.de www.unilog-integrata.de 4053 / 1.2.036 Folie 4 Skalare Datentypen (f) Datentyp Sub-Typ Beispiel v_b binary_double; boolean v_b boolean; Werte: -1.79e308 .. 1.79e308 Werte: True, false, null 4712 v. Chr - 9999 n.Chr date v_date date; timestamp v_ts timestamp(2); wie date interval v_dauer interval year(2) to month; v_i raw; v_i rowid; v_i mlslabel; unterschiedlich www.unilog.integrata.de www.unilog-integrata.de 1.2.066 / 4053 4053 / 1.2.036 2 Gültigkeitsbereich binary_double raw rowid mlslabel 2 Länge: 1 .. 32767 bytes 256 bytes 2..5 bytes Folie 5 2-9 2 2.4 Deklaration von Variablen Neue Datentypen seit 10g Die Datenbank wurde um zwei neue numerische Datentypen für IEEEGleitkommazahlen erweitert. Diese zwei Datentypen sind: • BINARY_FLOAT (32 Bit) • BINARY_DOUBLE (64 Bit) Die Gleitkommazahlen haben folgendes Format: Gleitkommazahl = Mantisse * Basis ^ Exponent Die binären ORACLE Gleitkommazahlen verwenden die Basis 2 (im Gegensatz zu Basis 10 bei den normalen Gleitkommazahlen). Dadurch wird eine größere Genauigkeit bei Berechnungen ermöglicht. 2-10 1.2.066 / 4053 Deklaration von Variablen 2 Neue Datentypen 10g 2 BINARY_FLOAT (32 Bit) BINARY_DOUBLE (64 Bit) Format: Gleitkommazahl = Mantisse * Basis ^ Exponent ORACLE Eingabeformat Bsp.: 0.00002 1.1e-23 2.5e+22 ORACLE 10g verwendet Basis 2 (im Gegensatz zu Basis 10 bei den normalen Gleitkommazahlen) Î größere Genauigkeit www.unilog.integrata.de www.unilog-integrata.de 1.2.066 / 4053 4053 / 1.2.036 Folie 6 2-11 2 2.5 Deklaration von Variablen Datentyp-Konvertierung Sollte es notwendig werden, den Wert einer Variablen in einen anderen Datentyp zu konvertieren, so besteht unter PL/SQL die Möglichkeit der expliziten und der impliziten (automatischen) Konvertierung. 2.5.1 Explizite Datentyp-Konvertierung Mit Hilfe vorhandener Funktionen ist es möglich, eine Konvertierung von Datentypen manuell vorzunehmen. Die erforderlichen Funktionen und Typ-Konvertierungsmöglichkeiten, die auch unter SQL zur Verfügung stehen, sind in der folgenden Tabelle zusammengestellt. Die wichtigsten Funktionen sind: TO_CHAR TO_DATE TO_NUMBER TO_LOB Beispiele: v_date:=to_date(’01#02#2002 18:59:21’,’DD#MM#YYYY HH24:MI:SS’); v_num:=to_number(substr(‘BMW 330’,5,3))+200; -- v_num ist 530 v_chr:=to_char(sysdate,’MM/YY’); --v_chr ist z. B. 01/02 Eine weitere Konvertierung soll hier noch ergänzend erwähnt werden: nvl(wert,ersetzung). Bei dieser Funktion wird geprüft, ob wert NULL ist, wenn ja wird er durch den zweiten Parameter (ersetzung) ersetzt. nach 2-12 von CHAR DATE NUMBER RAW ROWID CHAR -- TO_DATE TO_NUMBER HEXTORAW CHARTOROWID DATE TO_CHAR -- NUMBER TO_CHAR TO_DATE RAW RAWTOHEX ROWID ROWIDTOCHAR ---- 1.2.066 / 4053 Deklaration von Variablen 2 Konvertierungsfunktionen 2 Konvertierungsfunktionen: TO_BINARY_DOUBLE(expr[, fmt[, nlsparam]]) Wandelt eine Zahl in einen BINARY_DOUBLE um. TO_BINARY_FLOAT(expr[, fmt[, nlsparam]]) Wandelt eine Zahl in einen BINARY_FLOAT um. www.unilog.integrata.de www.unilog-integrata.de 4053 / 1.2.036 Folie 7 Konvertierungsfunktionen (f) 2 Explizite Datentypkonvertierung: nach www.unilog.integrata.de www.unilog-integrata.de 1.2.066 / 4053 von CHAR DATE NUMBER RAW ROWID CHAR -- TO_DATE TO_NUMBER HEXTORAW CHARTOROWID DATE TO_CHAR -- NUMBER TO_CHAR TO_DATE RAW RAWTOHEX ROWID ROWIDTOCHAR 4053 / 1.2.036 -- -- -- Folie 8 2-13 2 Deklaration von Variablen Neue Konvertierungen ab Version 10g TO_BINARY_DOUBLE(expr[, fmt[, nlsparam]]) Wandelt eine Zahl in einen BINARY_DOUBLE um. TO_BINARY_FLOAT(expr[, fmt[, nlsparam]]) Wandelt eine Zahl in einen BINARY_FLOAT um. 2.5.2 Implizite Datentyp-Konvertierung Eine implizite Typkonvertierung wird in manchen Fällen automatisch von PL/SQL ausgeführt, sobald ein übergebener Wert nicht den erwarteten Typ aufweist. Ist eine solche Konvertierung nicht möglich, wird das PL/SQL Programm mit einem Fehler abgebrochen. Es ist ebenfalls nur eine erfolgreiche Konvertierung möglich, wenn der zu konvertierende Wert dem Wertebereich des Zieldatentyps entspricht. Implizite Konvertierungen sollten allerdings vermieden werden, soweit dies möglich ist, da explizite Konvertierungen performanter sind. In der folgenden Tabelle sind die möglichen Konvertierungen beschrieben: nach Von BINARY_ INTEGER CHAR DATE LONG NUMBER RAW ROWID VARCHAR2 BINARY_ INTEGER -- Ja Nein Ja Ja Nein Nein Ja CHAR Ja -- Ja Ja Ja Ja Ja Ja DATE Nein Ja -- Ja Nein Nein Nein Ja LONG Nein Ja Nein -- Nein Ja Nein Ja Ja Ja Nein Ja -- Nein Nein Ja RAW Nein Ja Nein Ja Nein -- Nein Ja ROWID Nein Ja Nein Nein Nein Nein -- Ja Ja Ja Ja Ja Ja Ja Ja -- NUMBER VARCHAR2 Hinweis: Seit ORACLE 10g gibt es eine implizite Konvertierung zwischen CLOB und NCLOB, die natürlich ebenso sparsam eingesetzt werden sollte. 2-14 1.2.066 / 4053 Deklaration von Variablen 2 Implizite Datentypkonvertierung 2 nach von BINARY_ INTEGER BINARY_ INTEGER -- CHAR DATE LONG Ja Nein Ja NUMBER Ja RAW ROWID Nein VARCHAR2 Nein Ja Ja CHAR Ja -- Ja Ja Ja Ja Ja DATE Nein Ja -- Ja Nein Nein Nein Ja LONG Nein Ja Nein -- Nein Ja Nein Ja NUMBER Ja Ja Nein Ja -- Nein Nein Ja RAW Nein Ja Nein Ja Nein -- Nein Ja ROWID Nein Ja Nein Nein Nein Nein -- Ja Ja Ja Ja Ja Ja Ja Ja -- VARCHAR2 www.unilog.integrata.de www.unilog-integrata.de 1.2.066 / 4053 4053 / 1.2.036 Folie 9 2-15 2 2.6 Deklaration von Variablen Implizite Variablendeklarationen mit %TYPE Des Weiteren hat man unter PL/SQL noch die Möglichkeit, implizite Datentypen zu benutzen. Bei der impliziten Typzuordnung wird der Variablen oder Konstanten zur Kompilierungszeit der Datentyp einer Tabellenspalte oder einer anderen, bereits deklarierten Variablen zugewiesen. Der Programmentwickler muss somit den eigentlichen Datentyp einer Variablen nicht wissen, da der aktuelle Datentyp zur Laufzeit aus dem Data-Dictionary ermittelt wird. Auch bei einer Änderung des Datentyps (ALTER TABLE) braucht das Programm nicht umgeschrieben zu werden, eine erneute Kompilierung reicht. Syntax: variablenname Tabellenname.Spaltenname%TYPE; bzw.: variablenname variablenname2%TYPE; Beispiel: Es soll der Variablen v_name die Spalte ename aus der Tabelle emp zugewiesen werden. Eine zweite Variable v_name2 soll den gleichen Datentyp erhalten 2-16 Ö v_name emp.ename%TYPE; Ö v_name2 v_name%TYPE; 1.2.066 / 4053 Deklaration von Variablen Implizite Variablendeklaration mit %TYPE Variablendeklaration mit 2 2 %TYPE: Datentyp muss zur Entwicklungszeit nicht bekannt sein Bei Änderung des Datentyps reicht erneute Kompilierung des Programms Beispiele: v_nachname v_gehalt emp.ename%TYPE; emp.sal%TYPE; v_dummy v_nachname%TYPE; v_min_gehalt v_gehalt%TYPE; www.unilog.integrata.de www.unilog-integrata.de 1.2.066 / 4053 4053 / 1.2.036 Folie 10 2-17 2 2.7 Deklaration von Variablen Gültigkeitsbereiche von Variablen Eine Variable ist grundsätzlich nur in dem Block gültig, in dem sie deklariert wurde (und in allen Unterblöcken). Beispiel: <<Block_A>> DECLARE var_A NUMBER; BEGIN .... <<BLOCK_B>> DECLARE var_B NUMBER; BEGIN .... END; END; In dem Beispiel ist die Variable var_A in den Blöcken A und B gültig, die Variable var_B hingegen nur im Block B, da sie in diesem Block deklariert wurde. var_B kann somit nicht im Block A angesprochen werden. Würde im Block B noch einmal eine Variable var_A deklariert, so würde var_A aus Block A in Block B unsichtbar, denn es gilt immer die nähergelegene Deklaration. Das heißt, eine Änderung von var_A im Block B betrifft nur var_A aus Block B. Ist Block B abgearbeitet, steht in var_A der ursprüngliche Inhalt aus Block A. Es kann jedoch auch auf eine unsichtbare Variable aus dem übergeordneten Block zugegriffen werden, indem ein Label verwendet wird und das Blocklabel mit einem trennenden Punkt der Variablen vorangestellt wird. Beispiel: <<Block_A>> DECLARE var_A NUMBER; BEGIN .... <<BLOCK_B>> DECLARE var_A NUMBER; BEGIN var_A := BLOCK_A.var_A; .... END; END; 2-18 1.2.066 / 4053 Deklaration von Variablen 2 Gültigkeitsbereiche von Variablen 2 Beispiel: <<Block_A>> DECLARE var_A NUMBER; BEGIN ... <<BLOCK_B>> DECLARE var_B NUMBER; BEGIN ... END; END; www.unilog.integrata.de www.unilog-integrata.de 4053 / 1.2.036 Folie 11 Gültigkeitsbereiche von Variablen (f) 2 Sichtbarmachen einer Variable innerhalb eines fremden Blocks: <<Block_A>> DECLARE var_A BEGIN NUMBER; .... <<BLOCK_B>> DECLARE var_A NUMBER; BEGIN var_A := BLOCK_A.var_A; ... END; END; www.unilog.integrata.de www.unilog-integrata.de 1.2.066 / 4053 4053 / 1.2.036 Folie 12 2-19 2 2-20 Deklaration von Variablen 1.2.066 / 4053
