Zum Inhalt springen

PL/SQL/ Druckversion

Links hinzufügen
Aus Wikibooks
PL/SQL



Wikibooks

Einleitung

Dieses Buch steht im Regal Programmierung Zur Startseite Grundlegende Elemente


Wer braucht das Buch?

[Bearbeiten]

Dieses Buch wendet sich an Einsteiger in die PL/SQL-Programmierung und an Anwender in der Form eines Nachschlagewerkes. Grundlegende Kenntnisse der Programmierung, des Aufbaues von relationalen Datenbanken und SQL werden für das Verständnis des Buches vorausgesetzt.

Architektur des Buches

[Bearbeiten]

Im ersten Teil des Buches wird die Methode der Syntaxbeschreibung für die PL/SQL-Grammatik, im zweiten Teil die Syntax der PL/SQL-Sprachelemente und im dritten Teil die Anwendung der wichtigsten PL/SQL-Systembibliotheken erläutert. Zu jedem PL/SQL-Element gibt es neben der Syntaxbeschreibung ein kurzes Codebeispiel. Diese Beispiele stellen keine funktionsfähigen Programme dar, sondern nur die Umsetzung der Syntaxbeschreibung in Programmcode. Im letzten Teil werden programmtechnische Aspekte behandelt. Dazu werden mit ausgewählten Systembibliotheken Anwendungsbeispiele erstellt und auf die Verbindung mit anderen Programmiersprachen eingegangen.

Wie funktioniert PL/SQL

[Bearbeiten]

PL/SQL steht für Procedural Language/SQL, ist eine prozedurale Programmiersprache und erweitert die Abfragesprache SQL. Mit PL/SQL kann ausschließlich in Oracle Datenbanken gearbeitet werden, unabhängig vom verwendeten Betriebssystem. Die Übertragung in andere Datenbanksysteme, die Programmierung erlauben, ist ohne manuelle Nacharbeit nicht möglich. Ähnliche Programmiermöglichkeiten gibt es unter anderem in folgenden Datenbanksystemen.

  • PL/pgSQL in der Open-Source-Datenbank PostgreSQL
  • SQL PL für DB2

Der Programmcode wird in einer Datenbanktabelle des Datadictionary in lesbarere Textform gespeichert. Es besteht die Möglichkeit, den Quellcode in ein internes Format umzuwandeln. Dieses Umwandlung wird über den PL/SQL-Wrapper durchgeführt. Der Programmcode wird interpretativ abgearbeitet, das kann sowohl auf der Serverseite innerhalb der Datenbank, als auch auf der Clientseite in der von Oracle zur Verfügung gestellte Oberfläche Oracle Forms erfolgen. Jeder Benutzer der Datenbank hat eine eigene Session. Innerhalb dieser Session läuft für den Benutzer die Interpretation des Programmcodes ab. Der konkrete Ablauf wird im Abschnitt Programmentwicklung erläutert.


Grundlegende Elemente

Dieses Buch steht im Regal Programmierung Einleitung Zur Startseite Typen


Wer ist Herr Backus?

[Bearbeiten]

Für die Beschreibung einer Programmiersprache gibt es verschiedene Möglichkeiten. Grundlegend unterscheidet man zwischen grafischer und verbaler (Metasprache) Beschreibung. In diesem Buch verwende ich eine Metasprache, die der Erweiterten Backus-Naur-Form (EBNF) entspricht. Diese Metasprache geht auf John Warner Backus und Peter Naur zurück. Die EBNF verwendet folgende Elemente zur Darstellung

Verwendung Darstellung
Definition eines Elementes =
implizite Aufzählung einer bekannten, ordinalen Menge ..
alternatives Element |
optionales Element [ ]
optionale Wiederholung des letzten Elementes [..]
Elementgruppierung ( )
Wiederholung der Elementgruppierung (..)
optionale Wiederholung der Elementgruppierung [(..)]
Regelende ;
konstantes Element (Begrenzung) " "

Namen, Zahlen und Co.

[Bearbeiten]

Jede Programmiersprache besteht aus elementaren, nicht mehr teilbaren, Bestandteilen, die als lexikalische Elemente bezeichnet werden.

  • Namen
  • Zahlen
  • Schlüsselworte
  • Operatoren
  • Kommentare

Namen

[Bearbeiten]

Mit einem Namen wird eine Deklaration, die einen Typ, eine Variable in unterschiedlicher Ausprägung oder eine Funktionalität bezeichnet, definiert. Der Name hat folgende Struktur: 

 name = einfacherName | arrayName | globalerName;
   einfacherName = buchstabe [zeichen[..]];
     zeichen     = buchstabe | ziffer | "_";
     buchstabe   = "A".."Z" | "a".."z";
     ziffer      = "0".."9";
   arrayName     = ((einfacherName | globalerName) "[" index "]") [..];
     index       = zahl | name;
   globalerName  = packageName "." name;
     packagename = einfacherName;

Ein Name beginnt immer mit einem Buchstaben und ist mindestens ein Zeichen lang. Damit wären aber nur 26 Namen möglich, da in PL/SQL die Groß-/Kleinschreibung keine Rolle spielt. Dem ersten Buchstaben dürfen weitere Buchstaben, Ziffern oder das Sonderzeichen "_" folgen. Ein Name darf maximal 30 Zeichen lang sein und muss ein Unikat innerhalb seines Gültigkeitsbereiches sein.

Zahlen

[Bearbeiten]

Zahlen werden als Konstanten innerhalb von Anweisungen oder bei Deklarationen verwendet und haben folgende Struktur: 

 zahl = ganzeZahl|reelleZahl;
   
   ganzeZahl       = [vorzeichen] ziffer [ziffer[..]];
   reelleZahl      = [vorzeichen] (vorkommaStellen [nachkommaAnteil]) |
                                   nachkommaAnteil |
                                  (vorkommaStellen [nachkommaAnteil] "E" [vorzeichen] exponent);
   
     vorzeichen      = "+" | "-";
     vorkommaStellen = ziffer [ziffer[..]];
     nachkommaAnteil = "." | ("." nachkommaStellen);
     exponent        = zahl:
   
       nachkommaStellen = ziffer [ziffer[..]];

Eine Zahl kann mit einem Vorzeichen beginne, fehlt es, wird das positive Vorzeichen angenommen. Ganze Zahlen bestehen aus mindestens einer Ziffer. Reelle Zahlen bestehen aus Vorkomma- und Nachkommastellen, die durch einen Punkt getrennt werden, wobei mindestens ein Teil vorhanden sein und dieser Teil aus mindestens einer Ziffer bestehen muss. Über die Genauigkeit und die Typumwandlung wird im Abschnitt Vereinbarungen ausführlich eingegangen. Reelle Zahlen können auch in wissenschaftlicher Darstellung, in Exponentialform, geschrieben werden

Konstanten

[Bearbeiten]

Mit Konstanten werden Werte festgelegt, die in Ausdrücken verarbeitet werden. 

 konstanten = numerische_konstanten | boolsche_konstanten | char_konstanten;
   numerische_konstanten = zahl;
   boolsche_konstanten   = "TRUE" | "FALSE";
   char_konstanten       = zeichenkette;
     zeichenkette = zeichenkettenbegrenzer zeichen[..] zeichenkettenbegrenzer;
       zeichenkettenbegrenzer = """;

Der Zeichenkettenbegrenzer ist das Zeichen ", deshalb wird es in sich selbst in der Regel dargestellt.

Schlüsselworte

[Bearbeiten]

Fest vorgegebene Zeichenketten in einer Programmzeile werden als Schlüsselworte bezeichnet. 

 name = buchstabe [zeichen[..]];

Schlüsselworte sind reservierte Worte und dürfen nicht als Namen verwendet werden. Im Anhang befindet sich eine Auflistung aller Schlüsselwort in PL/SQL.

Was ist NULL?

[Bearbeiten]

Verschiedene Programmiersprachen, dazu zählt PL/SQL, besitzen die Möglichkeit, den Zustand, dass eine Variable definiert, aber nicht mit einem Wert belegt ist, auszuwerten. Dazu wird in der Variable ein bestimmtes, dem Programmierer nicht bekantes, Bitmuster hinterlegt, welches als NULL definiert ist. An dieser Stelle muss betont werden, dass NULL einen Zustand und keinen Wert darstellt. Auf den Zustand NULL kann nicht mit Vergleichsoperatoren, sondern nur mit NULL-Operatoren verglichen werden.

Operationswerkzeuge

[Bearbeiten]

Mit Operationszeichen werden einzelne Operanden, das können Variablen, Konstanten oder Ausdrücke sein, verknüpft. Je nach Typ der Operanden unterscheidet man zwischen

  • arithemetische,
  • booleschen,
  • Character-,
  • Mengen-,
  • Vergleichsoperationszeichen und
  • NULL-Operationszeichen.
 opz = arithmetische_opz | boolsche_opz | char_opz | mengen_opz | vgl_opz | null_opz;

Arithmetische Operationszeichen

[Bearbeiten]

Mit arithmetischen Operationszeichen werden numerische Ausdrücke verbunden. 

 arithmetische_opz = "+" | "-" | "*" | "/" | "**";

Arithmetische Operationszeichen liefern als Ergebnis numerische Werte. Ob die mathematische Operation, die mit dem Operationszeichen verbunden ist, auch erklärt ist (z.B. Division durch 0), wird erst bei der Laufzeit erkannt. Höhere arithmetische Operationen (Winkelfunktion u.a.m.) werden durch Standardfunktionen abgebildet.

Boolsche Operationszeichen

[Bearbeiten]

Mit boolschen Operationszeichen werden logische Ausdrücke verbunden. 

 boolsche_opz = "AND" | "OR" | "NOT";

Das Ergebnis eines logischen Ausdrucks, dessen Operanden mit "AND" verbunden sind, ist TRUE, wenn alle Operanden TRUE als Ergebnis liefern. Mit "OR" verknüpfte Operanden liefern TRUE als Ergebnis, wenn mindestens ein Operand TRUE als Ergebnis hat. "NOT" negiert das Ergebnis eines Operanden.

Characteroperationszeichen

[Bearbeiten]

Mit Characteroperationszeichen werden Zeichenketten bearbeitet. 

 char_opz = "||"

Mit diesem Operationszeichen werden zwei Zeichenketten verknüpft. Alle anderen Operationen mit Zeichenketten (Substring, InString usw.) werden durch Standardfunktionen abgebildet.

Mengenoperationszeichen

[Bearbeiten]

PL/SQL kennt nur zwei Mengenoperationszeichen, die als Ergebnis der Operation TRUE oder FALSE liefern. 

 mengen_opz = "IN" | "NOT IN" | "BETWEEN" | "NOT BETWEEN";

Mengenoperationszeichen liefern als Ergebnis boolsche Werte. "IN" liefert TRUE, wenn der Operand in der aufgezählten Menge ist. "NOT IN" kehrt die Auswertung um, der Operand darf nicht in der aufgezähten Menge sein. Mit "BETWEEN" wird ein Operand auf Zugehörigkeit, einschließlich der angegebenen Grenzen, zur aufgezählten Menge überprüft. Das Ergebnis ist TRUE, wenn er zur Menge gehört. "NOT BETWEEN" überprüft, die Nichtzugehörigkeit zur Menge und liefert TRUE für diesen Fall.

Vergleichsoperationszeichen

[Bearbeiten]

Vergleichsoperatoren liefern boolsche Werte als Ergebnis. Verglichen werden können nur Operanden gleichen Typs (numerische, char usw.) 

 vgl_opz = "<" | "<=" | "=" | ">=" | ">" | "!=" | "<>";

Die Operationszeichen sind selbsterklärend, außer die letzten beiden, die die Operanden auf Ungleichheit vergleichen.

NULL-Operationszeichen

[Bearbeiten]

Um den Zustand NULL zu erkennen, gibt es zwei Operationszeichen, 

 null_opz = "IS NULL" | "IS NOT NULL";

Die Verknüpfung eines Operanden mit den NULL-Operationszeichen liefert einen boolschen Wert zurück. Die Verwendung aller anderen Operationszeichen auf einen Operanden der den Zustand NULL hat ist undefiniert.

Wie kommentiert man? Kann man schreiben, wie man will?

[Bearbeiten]

Jeder Quellcode sollte nicht nur fehlerfrei sein, sondern auch verständlich. Neben der Strukturierung des Programmablaufes, dienen Kommentare dazu, den Programmablauf verständlich zu machen. PL/SQL hat zwei Möglichkeiten, Kommentare in den Quellcode einzufügen 

 kommentar = einzeiliger_kommentar|mehrzeiliger_kommentar
   einzeiliger_kommentar  = "--" text zeilenvorschub
   mehrzeiliger_kommentar = "/*" textzeile[..] "*/"
     textzeile = text zeilenvorschub
     text      = zeichen[..]

Das Regelelement "zeilenvorschub" ist vom Betriebssystem abhängig. Eine Verschachtelung von ein- und mehrzeiligen Kommentaren ist möglich, sollte aber vermieden werden.

PL/SQL ist, im Gegensatz zu Programmiersprachen wie JAVA, nicht case-sensitiv. Der Name ABC ist identisch mit aBc. Ebenfalls ist es ohne Bedeutung,

  • wieviele Leerzeichen zwischen zwei lexikalischen Elementen stehen,
  • wieviele Leerzeichen am Zeilenbeginn eingerückt werden und
  • wie lang die Zeile ist.

Leerzeichen werden, außer in Characterkonstanten, bei der Quelltextinterpretation ignoriert. Zur besseren Übersichtlichkeit werden alle Schlüsselworte in diesem Buch groß geschrieben. In PL/SQL wird in den meisten Fällen kommerzielle Software geschrieben. In Softwarefirmen gibt aus vielen Gründen (Programmentwicklung im Team, Wartbarkeit der Software, Qualitätssicherung u.a.m.) Vorschriften,

  • wie Quelltexte zu strukturien sind,
  • welche Namen zu vergeben sind,
  • welche Sprachelemente nicht verwendet werden dürfen (z.B. Exceptionhandling zur Programmsteuerung) und
  • wie Sprachelemente verwendet werden dürfen (z.B. keinen impliziten Cursor).

Auf diese Details kann in einem Wikibook natürlich nicht eingegangen werden, sondern nur auf die Möglichkeiten, die PL/SQL insgesamt bietet.


Typen

Dieses Buch steht im Regal Programmierung Grundlegende Elemente Zur Startseite Deklarationszwang


PL/SQL kennt die folgenden vordefinierten Typen. Das sind Typen, deren Struktur vom Programmierer nicht mehr verändert werden kann.

Skalare Typen

[Bearbeiten]

Skalare Typen haben keine weiteren, internen Strukturen, die vom Programmierer verändert werden können. 

  skalareType = numerischeType | characterType | boolscheType | pointerType | datetimeType;

Numerische Typen

[Bearbeiten]

Numerische Typen definieren ganzzahlige, reelle und numerische Aufzählungstypen unterschiedlicher Genauigkeit und Wertebereiche. 

 numerischeType           = binaryType | numberType;
   binaryType             = "PLS_INTEGER" | "BINARY_INTEGER" | binaryIntegerSubtype;
     binaryIntegerSubtype = "NATURAL" | "NATURALN" | "POSITIVE" | "POSITIVEN" | "SIGNTYPE";
   numberType             = ("NUMBER" [laengenBeschreibung]) | numberSubType;
     laengenBeschreibung  = gesamtlaenge[("," nachkommaanteil)]  
       gesamtlaenge       = 1..38;
       nachkommaanteil    = -84..127;
     numberSubType        = "DEC" | "DECIMAL" | "NUMERIC" | "DOUBLE PRECISION" | "FLOAT" | "REAL" | "INTEGER" | "INT" | "SMALLINT";

Binäre, ganzzahlige Typen

[Bearbeiten]

Die binären Typen stellen ganzzahlige Zahlen, wie der Name schon sagt, intern in binärer Form dar. Sie sind in der Berechnung besonders schnell und werden hauptsächlich als Index verwendet, wobei der Typ PLS_INTEGER am schnellsten ist. Die Subtypen dieser Typklasse unterscheiden sich nur im Wertebereich,

Wertebereiche 

Typ                minimaler Wert      maximaler Wert
BINARY_INTEGER     -2.147.483.648      2.147.483.647
PLS_INTEGER        -2.147.483.648      2.147.483.647
NATURAL                         1      2.147.483.647
POSITIVE                        1      2.147.483.647
NATURALN                        0      2.147.483.647
POSITIVEN                       0      2.147.483.647
SIGNTYPE                       -1                  1

Der Type SIGNTYPE hat nur die drei Werte -1, 0 und 1.

Reelle Typen

[Bearbeiten]

Für reelle Typen gibt verschiedene Darstellungsmöglichkeiten, die sich grundlegend in Floatingpoint und Fixedpoint unterscheiden. Der Wertebereich geht von 10**-130 bis 10**125. Der Typ NUMBER gestattet die größte Gestaltungsmöglichkeit durch die optionale Längendefinition. Floatingpoint wird durch NUMBER ohne Längenbeschreibung definiert und Fixedpoint wird mit einer Längenbeschreibung definiert. Es gelten folgende Regeln bei der Festlegeung der Längenbeschreibung.

  • nur Gesamtlänge bzw. Nachkommaanteil ist 0

Die Längendefinition ist identisch mit 0 als Nachkommaanteil und es wird nur der ganzzahlige Anteil der reellen Zahl verwendet.

  • Nachkommaanteil < 0

Auch diese Variante ist möglich, obwohl sie im ersten Moment widersprüchlich ist. Mit einem negativen Wert im Nachkommaanteil werden Vorkommaanteil 0 gesetzt. -1 setzt die Einerstelle auf 0; es können also nur die Zahlen 10, 20, 30 usw. verarbeitet werden. -2 setzt die Einer- und Hunderterstelle auf 0, es können also nur die Zahlen 100, 200, 300 usw. verarbeitet werden. Damit sollte die Verwendung von negativen Werten als Nachkommaanteilen verständlich sein.

  • Nachkommaanteil > 0

Mit einem Nachkommaanteil > 0 wird die Genauigkeit der reellen Zahl festgelegt. 1 als Nachkomaanteil verarbeitet nur Zehntelwerte, 2 Zehntel- und Hunderstelwert.

Bei der Längenfestlegung ist zu beachten, dass die Gesamtlänge größer als der Nachkommaanteil sein muss. Die Subtypen DEC, DECIMAL und NUMERIC definieren eine 38-stellige, reelle Zahl; DOUBLE PRECISION und FLOAT definieren eine 128 Bit und REAL eine 63 Bit lange, reelle Zahl.

Ganzzahlige Typen

[Bearbeiten]

Die Typen INTEGER, INT und SMALLINT stellen eine 38 Zeichen lange, ganze Zahl dar.

Character-Typen

[Bearbeiten]

Character-Typen definieren alphanumerische Werte, auf die in ihrer Gesamtheit, aber auch auf ihre einzelnen Elemente, zugegriffen werden kann. 

 characterType         = ("CHAR" [laengenFestlegung [struktur]]) | characterSubtype | longType | rawType | rowidType | varcharType | nationalCharType;
   characterSubtype    = "CHARACTER";
   lonType             = "LONG" | "LONG RAW";
   rawType             = "RAW" laengenFestlegung;
   stuktur             = ["CHAR" | "BYTE";
   rowidType           = "ROWID" | "UROWID";
   varcharType         = ("VARCHAR2" | "VARCHAR" | "STRING") [laengenFestlegung [struktur]]);
   nationalCharType    = ("NCHAR" | "NVARCHAR2") [laengenFestlegung];
     laengenFestlegung = "(" 1..32767 ")";

CHAR-Typen

[Bearbeiten]

CHAR ist ein Typ zur Speicherung, mit festgelegter Länge, von Zeichen. Die maximal mögliche Länge beträgt 32767. Mit der Festlegung der Struktur wird definiert, auf was sich die Längenangabe bezieht - auf ein Byte oder ein Zeichen. Das ist besonders wichtig, wenn mit UTF8-Codezeichen gearbeitet wird. Eine Länge von 32767 und die Struktur BYTE ermöglicht bei der Speicherung von 2-Byte-UTF8-Codezeichen nur 16383 Zeichen zu speichern. Die Standardannahme für die Länge ist 1 und als Struktur wird die Festlegung des Parameters NLS_LENGTH_SEMANTICS der benutzenden Datenbankinstanz verwendet. Der Typ CHARACTER ist identisch mit dem Typ CHAR und ist nur aus Kompatibilitätsgründen eingeführt wurden.

LONG-Typen

[Bearbeiten]

Dieser Typ hat eine konstante Länge von 32760 Bytes und dient zur Speicherung von Zeichen (LONG, LONG RAW) oder binären Daten (LONG RAW). Ab der Version Oracle 9i gibt es ((PL_SQL: LOB-Typen|LOB-Tyen)) zur Speicherung von binären Daten und Zeichen bis 2GB. , die die LONG Die Chractertypen haben unterschiedliche Bedeutung und sind teilweise auf Abwärtskompatibilität zurück zu führen.

RAW-Typen

[Bearbeiten]

Der Typ RAW ist identisch mit den Typ LONG RAW, der einzige Unterschied ist die maximale Lange von 32767 Bytes.

ROWID-Typen

[Bearbeiten]

Ein ROWID ist die eindeutige Adresse innerhalb eines Datensatzes in einer Tabelle. Jede Tabelle hat das ROWID als Pseudospalte. Es wird in physische und logische ROWIDs unterschieden, wobei der Typ ROWID nur physische ROWIDs und der Typ UROWID (universell ROWID) physische und logische ROWIDs darstellen kann. Physische ROWIDs haben folgenden Aufbau 

 rowid              = dataobjektNumber fileNumber blockNumber rowNumber;
   dataobjektNumber = zeichen;
   fileNumber       = zeichen;
   blockNumber      = zeichen;
   rowNumber        = zeichen;

Das Element haben folgende Längen in Byte. 

 dataobjectNumber 6
 fileNumber       3
 blockNumber      6
 rownumber        3

VARCHAR2-Typen

[Bearbeiten]

Dieser Typ ist fast identisch mit dem CHAR-Typ. Die Länge und die Struktur (Byte oder Char) kann festgelegt werden. Der wesentliche Unterschied besteht darin, dass VARCHAR2 speicher- und performanceorientiert arbeitet und nur die tatsächlich benötigte Anzahl von Speicherplätzen belegt. CHAR hingegen belegt den Speicher immer mit der vereinbarten Länge. Die Subtypen STRING und VARCHAR sind identisch zu VARCHAR2 und existieren aus Kompatibilitätsgründen.

Nationale CHAR-Typen

[Bearbeiten]

Um erweiterte Zeichensätze, die mehr als 256 Zeichen beinhalten, zu ermöglichen. Dieses ist vorallem bei asiatischen Sprachen notwendig. Die Datenbank arbeitet mit zwei Zeichensätzen, den Datenbankzeichensatz, mit dem alle Informationen über die Strukturen und der PL/SQL-Programmcode und dem Nationalen Zeichensatz mit dem alle Informationen in den Tabellen gespeichert werden. Der Nationale Zeichensatz wird im UNI-Code dargestellt, der 1, 2 oder 3 Byte pro Zeichen lang ist. Zeichensätze, die für jedes Zeichen immer 2 Byte benötigen, werden als AL16UTF16 bezeichnet. Zeichensätze, die für jedes Zeichen 1, 2 oder 3 Byte benötigt, werden als UTF8 bezeichnet. Die Längenangaben in der Typdefinition ist in Byte, das heißt, bei Speicherung in UTF-8 ist die Anzahl der maximal möglichen Zeichen 32787/3 und bei AL16UTF16 32767/2. Der Unterschied zwischen den beiden NCHAR und NVARCHAR2 besteht darin, dass NCHAR immer den Speicherplatz laut Längenbeschreibung belegt, unabhängig davon, wieviel Speicher tatsächlich benötigt wird. NVARCHAR2 belegt nur soviel Platz, wie tatsächlich benötigt wird.

Boolsche Typen

[Bearbeiten]

Dieser Typ ist sehr einfach beschrieben. 

 boolscheType := "BOOLEAN";

Pointertypen

[Bearbeiten]

Datums- und Zeittypen

[Bearbeiten]

Mit diesen Typen werden Datums- und Zeitangaben definiert. Die einzelnen Komponenten des Datums und der Zeit unterliegen folgenden Beschränkungen bzw. Wertebereichen. 

 jahr          = -4712..-1 | 1..9999;
 monat         = 1..12; 
 tag           = 1..31;
 stunde        = 0..23;
 minute        = 0..59;
 sekunde       = 0..59 ["," sekundenTeile];
 sekundenTeile = zahl;

Zur Darstellung von Zeitzonen sind folgende Wertebereiche möglich. 

 zeitzoneStunde = -12..14;
 zeitzoneMinute = 0..59;

Die Region der Zeitzonen ist dem Parameter V$TIMEZONE_NAMES der aktuellen DB-Instanz zu entnehmen. Folgende Datums- und Zeittypen sind definiert. 

 datetimeType    = "DATE" |
                   "TIMESTAMP" [precision] |
                   "TIMESTAMP" [precision] "WITH TIME ZONE" | 
                   "TIMESTAMP" [precision] "WITH LOCAL TIME ZONE" |
                   "INTERVAL YEAR" [precisionYear] "TO MONTH" |
                   "INTERVAL DAY" precisionDay] "TO SECOND" [precisionSecond];
 precision       = zahl;
 precisionYear   = 0..4;
 precisionDay    = 0..9;
 precisionSecond = zahl;

Typ DATE

[Bearbeiten]

Dieser Typ definiert ausschließlich das Datum. Oracle kann das Datum im Bereich vom 1.1.4712 v.Chr. bis 31.12.9999 darstellen. Es besteht die Möglichkeit das Datum als Zahl der Tage seit dem Startdatum darzustellen.

Typ TIMESTAMP

[Bearbeiten]

Timestamp stellt Datum und Uhrzeit in einer rellen Zahl dar, wobei der ganzzahlige Anteil das Datum und der Nachkommaanteil die Zeit seit Mitternacht ist. der Parameter precision gibt die Genauigkeit der Sekundenanteile an, wobei 1 Zehntelsekunden, 2 Hunderstelsekunden usw, sind. Der Zusatz WITH TIME ZONE speichert das Datum in der lokalen Zeitzone mit einem Displacment in Stunden und Minuten der lokalen Zeitzone zu UTC oder den Namen der Zeitzone. Mit dem Zusatz WITH LOCAL TIME ZONE erfolgt bei Speicherung eine Umrechnung von der lokalen Zeitzone in die Zeitzone der Datenbank und dem Displacment der umgerechneten Zeit zu UTC.

Typ INTERVAL

[Bearbeiten]

Mit dem INTERVAL-Typ werden Zeitdifferenzen definiert. Der Parameter precisionYear definiert bei INTERVAL YEAR die Anzahl der Ziffern bei der Jahresangabe Der Standard ist 2, möglich ist 0. Die Differenz wird als Zeichenkette definiert, wobei Jahr und Monat getrennt, als auch zusammen festgelegt werden können. Beispiel für zeitdiff als Variable vom Typ INTERVAL YEAR (3) TO MONATH: 

 zeitdiff = INTERVAL '10-2' YEAR TO MONTH; -- Zeitdifferenz von 10 Jahren und 2 Moanten
 zeitdiff = INTERVAL '10' YEAR; Zuweisung der Jahre in der Zeitdifferenz
 zeitdiff = INTERVAL '3' MONTH; -- Zuweisung der Monate in der Zeitdifferenz
 zeitdiff = '10-2'; -- implizite Zeitdifferenzzuweisung

Der Parameter precisionDay legt die Anzahl der Ziffern bei der Tagesangabe fest, Standard ist 2 und der Parameter precisionSecond legt die Anzahl der Ziffern bei der Sekundenangabe fest, Standard ist 6. Die Zuweisung erfolgt analog wie im Beispiel bei INTERVAL YEAR TO MONTH.

LOB-Typen

[Bearbeiten]

Die Abkürzung LOB steht für Large Object. Mit LOB-Typen werden, aus der Sicht von PL/SQL, unstrukturierte, binäre Daten mit einer Größe von maximal 4 Gigabyte definiert. Es kann sich dabei um

  • Texte,
  • Files,
  • Bilder,
  • Videos u.a.m.

handeln. Es handelt sich um Objekte, die, wie in der objektorientierten Programmierung, für ihre Bearbeitung Methoden haben. Die Definition eines LOB-Types stellt einen Pointer zur Verfügung, der auf den referenzierten Speicherplatz zeigt. Eine Pointerverarbeitung, wie zum Beispiel in C, ist nicht möglich; es gibt keine Möglichkeit, die Adresse an sich unter PL/SQL auszulesen und damit in Form einer Pointerarithmetik zu arbeiten. Aus der Sicht des Programmierers erfolgt der Zugriff statisch auf einen reservierten Speicherbereich. Zur Bearbeitung von LOB-Typen steht das Package DBMS_LOB zur Verfügung. 

 lobType         = fileLobType |
                   binaryLobType |
                   charLobType |
                   numLobType  |
                   xmlLobType;
   fileLobType   = "BFILE";
   binaryLobType = "BLOB";
   charLobType   = "CLOB" | "NCLOB";
   xmlLobType    = "XMLType";

BFILE

[Bearbeiten]

Mit dem LOB-Typ BFILE wird der Zugriff auf ein File aus dem Filesystem ermöglicht. Es muss gewährleistet sein, das der User, der aus der Sicht des Betriebssystem Oracle gestartet hat, auf das File lesende Zugriffsrechte hat. Es ist nur ein lesender Zugriff auf das File über den BFILE-Type möglich. Des weitren ist zu berücksichtigen, dass der BFILE-Type nicht der Transaktionssteuerung der Datenbank unterliegt. Es ist nicht möglich unbegrenzt viele Files duch die Definition von Variablen mit dem Typ BFILE in einer Session zu bearbeiten. Die maximale Anzahl wird durch den Datenbankparameter SESSION_MAX_OPEN_FILES festgelegt. Dabei muss beachtet werden, dass es noch andere Möglichkeiten gibt, Files durch die Datenbank (Package UTL_FILE) zu bearbeiten.

BLOB

[Bearbeiten]

Mit dem LOB-Typ BLOB werden binäre Daten mit einer Größe von maximal 4 GB definiert. Dieser Typ unterliegt der Transaktionssteuerung.

CLOB und NCLOB

[Bearbeiten]

Mit dem LOB-Typ CLOB und NCLOB werden Characterdaten mit einer Größe von maximal 4 GB definiert. Diese beiden Typen unterliegen der Transaktionssteuerung. CLOB ist auf den Typ Characters vom Typ VARCHAR und NCLOB auf Characters vom Typ NVARCHAR zurückzuführen.

XMLType

[Bearbeiten]

Im Gegensatz zu den bisher betrachteten LOB-Typen handelt es sich beim LOB-Type XMLType um ein Objekt. Dieser Typ besteht aus einem Datenbereich, der das XML-Dokument enthält, sowie aus einer Vielzahl von Methoden, mit der das XML-Dokument bearbeitet werden kann. Ausführlich wird im Abschnitt XML auf die Erstellung und Verarbeitung von XML-Dokumenten eingegangen.

Typkonvertierung

[Bearbeiten]

PL/SQL gestattet eine implizite Typkonvertierung über Zuweisungen. Die explizite Typkonvertierung wird über Funktionen realisiert, bei denen teilweise Formatierungsvorschriften vorgegeben werden können.

Implizite Typkonvertierung

[Bearbeiten]

Implizit können folgende Typen konvertiert werden

von/nach Character Number Datetypen Rowid
Character eingeschränkt eingeschränkt immer
Number immer nein immer
Date immer immer nein
Rowid immer immer nein

Einschränkung

Die Umwandlung von

  • Character in Number ist nur möglich, wenn es sich bei dem String um eine Zahl handelt,
  • Character in Datetypen ist nur möglich, wenn der String einem Datum gemäß dem im Parameter NLS_DATE_FORMAT und NLS_DATE_LANGUAGE festgelegten Format entspricht.

Explizite Typkonvertierung

[Bearbeiten]

Die explizite Typkonvertierung ist extrem komplex und hat eine Vielzahl von Parametern, die hier im Einzelnen erläutert werden sollen.

Konvertierung in numerische Datentypen

[Bearbeiten]

Die Konvertierung von Characterwerten in numerische Werte ermöglicht eine implizite Formatierung oder eine individuelle Formatsteuerung des Ausgangswertes für die Umwandlung. 

 konvertierungsfunktion           = numberkonvertierungsfunktion "(" numberparameterliste ")";
   numberkonvertierungsfunktion   = "TO_NUMBER";
   numberparameterliste           = (name | zahl) ["," formatparameter ["," nlsnumberparameterliste]];
     formatparameter              = formatangabe;
       formatangabe               = ziffernformatierung | 
                                    vorzeichenformatierung | 
                                    trennzeichenformatierung | 
                                    waehrungsformatierung |
                                    zahlenformatierung   
         ziffernformatierung      = "0" | "9" | "FM";
         vorzeichenformatierung   = "PR" | "MI" | "S";
         trennzeichenformatierung = "D" | "G" | "." | ",";
         waehrungsformatierung    = "$" | "C" | "L" | "U";
         zahlenformatierung       = "EEEE" | "RN" | "rn" | "TM9" | "TME" | "V" | "X";
     nlsnumberparameterliste      = "'" nlsnumberparameter [..] "'";
       nlsnumberparameter         = "NLS_NUMERIC_CHARACTERS = " "" dezimaltrenner tausendertrenner "" |
                                    "NLS_CURRENCY = " "" text "" |
                                    "NLS_ISO_CURRENCY = " isocurrencycode;
         dezimaltrenner           = "." | ",";
         tausendertrenner         = "." | ",";
         text                     = zeichenkette;
         isocurrencycode          = zeichenkette;

Formatierungen

Formatierungszeichen Bedeutung Beispiel
0 Zeichen ist eine Vornull '0123','0999' --> 123
9 Zeichen ist eine Ziffer '123','999' --> 123
FM Entfernen alle Blanks und Vornullen ' 123 ','FM999' --> 123
PR positives Vorzeichen '+123','PR999' --> 123
MI negatives Vorzeichen '123-','999MI' --> -123
S Vorzeichen '+123','S999' --> 123
D oder , Dezimaltrenner '123.1','999D9' --> 123,1
G oder . Tausendertrenner '1.234','9G999' --> 1234
$ Währungszeichen '123$','999$' --> 123
C internationales Währungszeichen '123$','999C' --> 123
L lokales Währungszeichen '123€','999L' --> 123
U Währungssymbol im DB-Parameter NLS_DUAL_CURRENCY '123€','123U' --> 123
EEEE wissenschaftliche Notation '1.23E+02','9.99EEEE' --> 123
TM9 oder TME minimale Textdarstellung '123','TM9' --> 123
RN oder rn römische Zahlendarstellung (groß/klein) 'XII','RM' --> 12
V Zehnerpotenzdarstellung '1.23x45','9.99V99' --> 123
X hexadezimale Darstellung 'FF','XX' --> 128

Die Formatierung muss die Zeichenfolge der umzuwandelnden Zeichenkette exakt beschreiben. Es erklärt sich von selbst, dass es nicht mögliche Kombinationen gibt, die hier aber nicht weiter erläutert werden sollen. Ebenso soll an dieser Stelle darauf hingewiesenw erden, dass alle Vorzeichenformatierungen nur am Anfang oder Ende der Formatbeschreibung stehen dürfen.

Der 'text' ist eine Zeichenkette, die als Währungsbezeichnung ausgegeben wird und der 'isocurrencycode' das Währungskürzel. Die Parameter überschreiben während der Ausführung der Funktion die Werte der Parameter der DB.

Konvertierung in Datetypen

[Bearbeiten]

Die Konvertierung von Characterwerten in Datetypen ermöglicht eine implizite Formatierung oder eine individuelle Formatsteuerung des Ausgangswertes für die Umwandlung. 

 konvertierungsfunktion           = (datekonvfunktion "(" dateparameterliste ")")|(dateintervallkonvfunktion "(" dateintervallparameterliste ")");
   datekonvfunktion               = "TO_DATE" | "TO_TIMESTAMP" | "TO_TIMESTAMP_TZ";
   dateintervallfunktion          = "TO_DSINTERVAL" | "TO_TMINTERVAL" ;
   dateparameterliste             = (name | date) ["," dateparameter ["," nlsnumberparameterliste]];
     dataparameter                = dateangabe;
       formatangabe               = freietextformatierung | 
                                    dateformatierung | 
                                    timeformatierung
         freietextformatierung    = "-"|","|"."|";"|":"|freiertext;
           freiertext             = """|zeichen[..]|"""; 
         dateformatierung         = [jahresformatierung] [monatsformatierung] [wochenformatierung] [tagesformatierung];
           jahresformatierung     = "AD" | "A.D."| "AM" | "A.M." | "BC" | "B.C." | "BC" | "CC" | "BCC" | "Q" | 
                                    "YYYY" | "YYY" | "YY" | "Y" | "SYYYY" | "YEAR" | "SYEAR" | "Y,YYY";
           monatsformatierung     = "MM" | "MON" | "MONTH" | "RM";
           wochenformatierung     = "W" | "WW" | "IW";
           tagesformatierung      = "DD" | "DDD" | "D" | "DAY" | "DY";

Formatierungen

Formatierungszeichen Bedeutung Beispiel
AD oder A.D.
AM oder A.M.
BC oder B.C.
CC oder BCC Jahrhundert zweistellig 1999,'CC' --> 20
Q Quartal 10.05.2012,'Q' --> 2
YYYY oder YEAR Jahr vierstellig 1999,'YYYY' --> 1999
YYY Jahr dreistellig 1999,'YYY' --> 999
YY Jahr zweistellig 1999,'YY' --> 99
Y Jahr einstellig 1999,'Y' --> 9
SYYYY oder SYEAR Jahr mit Vorzeichen 1999,'SYYYY' --> 1999
Y,YYY Jahr mit Tausendertrenner 1999,'Y,YYY' --> 1,999
MM Monat zweistellig 10,'MM' --> 10
MON Monat dreibuchstabig 1,'MON' --> JAN
MONTH Monat mit 9 Characters (rechts Blanks) 1,'MONTH' --> Januar
RM Monat in römischen Zahlen 10,'RM' --> X
W Woche im Monat
WW Woche im Jahr
IW Industriewoche im Jahr
D Tag in der Woche
DD Tag im Monat
DDD Tag im Jahr
DAY Wochentag mit 9 Characters (rechts Blanks)
DY Tag dreibuchstabig

Die Typen aus der Datenbank

[Bearbeiten]

Es ist in PL/SQL möglich, Typen auf

  • Tabellen und Views,
    • in der Gesamtheit der Tabellen- oder Viewstruktur oder
    • eines einzelnen Spaltenelementes (ordinale Typdefinition in Bezug auf den Typ der Spalte),
  • Typdefinitionen in Packages und
  • Typdefinitionen in der DB

zurückzuführen. Der Vorteil dieser Art der Typzuweisung ist, dass die Programmstruktur sofort invalid wird, wenn das Datenbankobjekt geändert wird. Dadurch werden potentielle Fehlerquellen, die durch Strukturänderungen der Tabellen oder Views entstehen, vermieden. 

 dbobjektType          = tableType | packageType | dbType;
   tableType           = rowtypeType | spaltenType;
     rowtypeType       = [ownername](tabellenname | viewname)[dblinkname] "%rowtype";
       ownername       = name ".";
       dblinkname      = "@" name;
       tabellenname    = name;
       viewname        = name;
     spaltenType       = [ownername](tabellenname | viewname)[dblinkname] "." spaltename "%type";
       spaltename      = name;
   packageType         = [ownername]packagename[dblinkname] "." typDeklaration;
     packagename       = name;
       ownername       = name ".";
       dblinkname      = "@" name;
     typDeklaration    = recordType | subType | dbType | plsqltableType;
   dbType              = name;

Den eigenen Wunschrecord festlegen

[Bearbeiten]

Neben Strukturdefinitionen, die auf Tabellen oder Views zurückgeführt werden, kann ein freier Record als Typ definiert werden. Diese Art der Typdefinition kann beliebige viele, auch weitere Recorddefinitionen enthalten. Zugriff auf die Recordelemente erfolgt über qualifizierte Namen. 

 recordType            = "type" recordtypename "is record" recordelementliste;
   recordtypename      = name;
   recordelementliste  = "(" recordelement [,..] ")";
      recordelement    =  elementname typedefinition;
        elementname    = name;
        typedefinition = numerischeType | characterType | boolscheType | datetimeType | lobType | tableType | recordType

Die Subtypen

[Bearbeiten]

Die Tabellensammlung im Speicher

[Bearbeiten]

Es gibt mehrere Möglichkeiten, Tabellenstrukturen im Speicher zu definieren. Diese Strukturen entsprechen Arrays in Programmiersprachen wie C oder Pascal.

Index By

[Bearbeiten]

Die erste Variante ist eine Tabellenstruktur, die folgende Struktur hat. 

 plsqltableType = "type" typename "is table of" {dbobjektType | recordType | plsqltableType} "index by binary_integer";
   typename     = name;

V(ariable) Array

[Bearbeiten]

Nested Table

[Bearbeiten]

Deklarationszwang

Dieses Buch steht im Regal Programmierung Typen Zur Startseite Anweisungen


In PL/SQL gibt es einen Deklarationszwang. Jede Variable muss vor ihrer ersten Verwendung deklariert werden. Eine Deklaration besteht aus dem Namen der Variablen und dem Typ der Variablen. Optional ist eine Initialbelegung möglich. Eine FORWARD-Deklaration gibt es nicht.

Die Veränderlichen – Variablen

[Bearbeiten]

Die Variablendeklaration hat folgenden Aufbau 

 variablenDeklaration = variablenName typdeklaration [initialbelegung];
   variablenName      = einfachername;
   typdeklaration     = skalareType | lobType | dbobjekttype | subType | plsqltableType;
   initialbelegung    = term;

In ihrem Gültigkeitsbereich darf jeder Variablenname nur einmal verwendet werden.

Die Unveränderlichen – Konstanten

[Bearbeiten]

Die Konstantendeklaration hat folgenden Aufbau 

 konstantenDeklaration = konstantenName "constant" typdeklaration initialbelegung;
   konstantenName      = einfacherName;    
   typdeklaration      = skalareType | lobType | dbobjektType | subType | plsqltableType;
   initialbelegung     = term;

In ihrem Gültigkeitsbereich darf jeder Konstantenname nur einmal verwendet werden. Konstanten können nur bei ihrer Deklaration einen Wert zugewiesen bekommen.

Die Indizierten – PL/SQL-Tabellen

[Bearbeiten]

PL/SQL-Tabellendeklaration hat folgenden Aufbau 

 plsqltableDeklaration = plsqltableName typdeklaration;
   plsqltableName      = einfacherName;
   typdeklaration      = plsqltableType;

In ihrem Gültigkeitsbereich darf jede PL/SQL-Tabledeklaration nur einmal verwendet werden. Eine PL/SQL-Table Typdeklaration ist ein Objekt und beinhaltet neben den Daten eine Vielzahl von Methoden.

Die Selektierten – Cursor

[Bearbeiten]

Ein Cursor ist eine Datenmenge, die durch ein SELECT-Statement erzeugt wird. Die Datenmenge kann eine leere Menge sein. Ein Cursor wird wie folgt deklariert. 

 cursorDeklaration         = "cursor" cursorName [cursorParameterliste] "is" selectStatement;
   cursorname              = einfacherName;
   cursorParameterliste    = "(" cursorParameter [..] ")"
     cursorParameter       = (cursorParametername typdeklaration) [,..]
       cursorParametername = einfacherName;
       typDeklaration      = skalareType | dbobjektType ;
   selectStatement         = sqlStatement;

Ausführliche Erläuterungen zum SELECT-Statement sind an hier zu finden.

Die Fehlerhaften – Exception

[Bearbeiten]

Bei der Abarbeitung auftretende Fehler, damit sind keine ORACLE-Fehler (Fehler beim Zugriff auf die Datenbank) gemeint, sondern ausschließlich Fehler, die anwenderspezifisch sind, müssen deklariert werden, um diese abzufangen und zu behandeln. Eine Exceptiondeklaration wird wie folgt definiert. 

 exceptionDeklaration = exceptionName "exception" [pragmaDeklaration];
   exceptionName      = einfacherName;
   pragmaDeklaration  = "pragma" pragmaName "(" exceptionname ",-" exceptionNummer ")";
     pragmanName      = einfacherName;
     exceptionNummer  = zahl;

Mit der PRAGMA-Deklaration kann der Exception eine negative, ganze Zahl, die kleiner oder gleich -20000 sein muss, zugewiesen werden.

Gültigkeitsbereich und Sichtbarkeit

[Bearbeiten]

Der Gültigkeitsbereich gliedert sich in folgende Ebenen, begonnen mit der kleinsten.

  • lokal innerhalb einer Prozedur/Funktion, definiert im Deklaraionsteile der Prozedur
  • lokal innerhalb einer Prozedur/Funktion, definiert in der Parameterliste
  • global innerhalb eines Packages, definiert im Deklarationsteil des Packages
  • global innerhalb der Datenbanksession, definiert im Packageheader
 create or replace package pa_1 is
 global_1 number;                                                                                              * global_1
                                                                                                               |
   create or replace package body pa_1 is                                                                      |
   global_2 number;                                                                                * global_2  |
                                                                                                   |           |
     procedure p1 (para_lokal_1 varchar2) is                                       * para_lokal_1  |           |
     lokal_1 number;                                       * lokal_1   * lokal_2   |               |           |
     lokal_2 varchar2(19);                                 |           |           |               |           |
                                                           |           |           |               |           |
       function f1 is                                      |           |           |               |           |
       lokal_1 number;                          * lokal_1  #           |           |               |           |
       begin                                    |          #           |           |               |           |
         ...                                    |          #           |           |               |           |
       end;                                     *          #           |           |               |           |
                                                           |           |           |               |           |
     begin                                                 |           |           |               |           |
     ...                                                   |           |           |               |           |
     end;                                                  *           *           *               |           |
                                                                                                   |           |
   end;                                                                                            *           |
                                                                                                               | 
 end;                                                                                                          *

Die Variable global_1 ist in der gesamten Session gültig und sichtbar. Zugegriffen wird auf diese Variable mit einem globalen Namen. Es ist möglich, mit dem Namen der Variablen zu zugrefen, wenn dieser im Header des eigenen Packages deklariert wurde. Aus Gründen der Übersichtlichkeit sollte aber auch in diesem Fall der globale Name verwendet werden. Die Variable global_2 ist nur im eigenen Package gültig und sichtbar. Die Parametervariable para_lokal_1 ist im Deklarationsteil und Ausführungsteil der Prozedur p1 gültig und sichtbar. Die lokale Variable lokal_1 der Prozedur p1 ist ab ihrer Deklaration bis zum Ende der Prozedur gültig und in der Prozedur p1 sichtbar. In der Funktion f1 ist diese Variable gültig, aber nicht sichtbar, da sie durch die lokale Variable lokal_1 der Funktion f1 in ihrer Sichtbarkeit überschrieben wird. Der Wert der lokalen Variablen lokal_1 der Prozedur p1 wird in der Funktion f1 aber nicht überschrieben, sonder bleibt erhalten. Die lokale Variable lokal_2 der Prozedur p1 ist in der Prozedur p1 und der Funktion f1 gültig und sichtbar. Die lokale Variable lokal_1 der Funktion f1 ist nur in dieser Funktion gültig und sichtbar.

Bei Auftreten einer Exception, unabhängig davon, ob von der Datenbank oder dem Anwenderprogramm ausgelöst, gehen alle lokalen Variablen verloren, aber alle globalen Variablen bleiben mit ihrem Inhalt erhalten. Dieser Aspekt ist zu berücksichtigen, wenn die Exception nach ihrer Behandlung eine Weiterarbeit ermöglicht. Sollen dabei die globalen Variablen neu initialisiert werden, muß das im Exceptionhandling ausprogrammiert werden.


Anweisungen

Dieses Buch steht im Regal Programmierung Deklarationszwang Zur Startseite Strukturierungen


Ergibt sich aus – Ergibtanweisung

[Bearbeiten]

Die Ergibtanweisung wird zur Wertzuweisung an Variablen verwendet. Der grundlegende Aufgabe hat folgend Form. 

 ergibtAnweisung   := ergebnisName ":=" operand [opz operand [..]];

Der Name der Variablen, der ein Wert zugewiesen wird kann eine einfache Variable oder Konstante, eine Struktur oder ein Strukturelement sein. 

   ergebnisName    := variablenName | constantenName | strukturname;
      strukturName := plsqltableName "[" index "]" ["." plsqltableElementname] | 
                      recordName ["[" index "]" ["." recordElementname]] |
                      rowtypeName ["." rowtypeElementname] |
                      cursorName ["." cursorElementName]      
        index      := term;

Der Operand besteht aus den Elementen

  • Variablennamen,
  • Funktionsausfrufen und
  • Konstanten

die durch Operationszeichen verbunden und Klammerungen strukturiert werden. Die Elemente können unterschiedliche Datentypen haben; entweder erfolgt eine implizitr Typkonvertierung oder die Konvertierung wird durch Konvertierungsfunktionen vorgegeben. 

 operand := "NULL" | ["("[..]] (variablenName | constantenName | strukturName | funktionsName) [")"[..]]);

Wenn ein Operand NULL ist, ist automatisch das Ergebnis NULL.

Nichts tun – NULL-Anweisung

[Bearbeiten]

Eine besondere Ergibtanweisung ist die NULL-Anweisung. 

 nullAnweisung := "NULL";

Diese Anweisung wird benötigt, wenn eine Programmstruktur zwingend mindestens eine Programmzeile erfordert. Das ist besonders während der Entwicklungsphase von Programmen von Bedeutung, wenn die Programmstruktur schon vorgegeben ist, aber der eigentliche Programminhalt noch fehlt.


Strukturierungen

Dieses Buch steht im Regal Programmierung Anweisungen Zur Startseite


Grundlegener Aufbau

[Bearbeiten]

Für ein PL/SQL-Programm gibt es einen grundlegenden Aufbau 

 plsqlProgramm := [declaration] programm;
   declaration := "declare" declarationsblock;
     declarationsblock := konstantenDeklaration|variablenDeklaration|plsqltableDeklaration|cursorDeklaration|exceptionDeklaration[..];
   programm    := "begin" programmBlock [exception] "end;";
   programmBlock     := anweisung[..];
   exception         := "exception" exceptionBlock[..];
     exceptionBlock  := "when" exception programmBlock;
     exception       := exceptionName
     exceptionName   := name|"others"|sqlExceptioName;

Anonymer Block

[Bearbeiten]

Ein anonymer Block ist die kleinste Programmeinheit und wird häufig in Entwicklungsumgebungen verwendet um Programmteile zu testen. 

 anonymerBlock = [declaration]programm;

Die One-Way-Rückgebenden – Funktionen

[Bearbeiten]

Die Funktion als Struktur dient dazu, einen Wert als Returnwert zurückzugeben und hat folgende Struktur. 

 function := "function" functionName ["(" parameterliste ")"] "is" [declarationsblock] programm;
   functionName := name;
   parameterliste := parameterElement ",";
     parameterElement := (parameterName [parameterAttribute] paramertType)[..];
       parameterName    := name;
       parameterAttribute := "in"|"out"|"in out"|["nocopy"];
       parameterType      := typVereinbarung;


Prozedur

Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Einstieg

[Bearbeiten]

Prozeduren sind nützlich, um regelmäßig wiederkehrende Arbeitsabläufe zu automatisieren z.B. um Installationsarbeiten durchzuführen.

Als Einstieg soll eine Prozedur dienen, die nur aus zwei Befehlen besteht. 

CREATE OR REPLACE PROCEDURE spins IS
BEGIN
   INSERT INTO tdept (deptno, deptname, mgrno, admrdept)
   VALUES ('A00', 'SPIFFY COMPUTER DIV.', '000010', 'A00');
   INSERT INTO tdept (deptno, deptname, mgrno, admrdept)
   VALUES ('B01', 'PLANNING            ', '000020', 'A00');
END;
/

Die Procedur wird im SQLPLUS aufgerufen mit dem Befehl: 

EXECUTE spins;

Innerhalb eines PL/SQL-Scripts wird die Procedur nur durch Angabe ihres Namens aufgefufen. 

BEGIN
   spins;
END;
/

Parameterübergabe

[Bearbeiten]

Wenn man der Prozedur Parameter übergeben will, dann wird unterschieden in:

  • IN: Die aufrufende Umgebung übergibt einen Wert an die Prozedur. Dieser Parameter kann innerhalb der Verarbeitung nicht verändert werden. IN ist der default Parameter-Modus
  • OUT: Die aufrufende Umgebung übergibt eine Variable an die Prozedur, die innerhalb der Prozedur als nicht initialisiert betrachtet wird. Wenn innerhalb der Verarbeitung diesem Parameter ein Wert zugewiesen wird, dann wird dieser an die aufrufende Umgebung zurückgegeben.
  • IN OUT: Die Aufrufende Umgebung übergibt eine Variable an die Prozedur. Diese kann innerhalb der Prozedur verwendet werden und auch geändert werden. Der geänderte Wert wird der aufrufenden Umgebung mitgeteilt.


Einfache Prozedur, die einen Parameter übergeben bekommt. Dieser Parameter wird durch den Befehl put_line wieder ausgegeben. Um die Ausgabe zu aktivieren, muss erst der SQL*Plus - Befehl 'set serveroutput on' ausgeführt werden. 

set serveroutput on
CREATE OR REPLACE PROCEDURE myfirstproc(v IN VARCHAR2)
IS
BEGIN
   dbms_output.put_line(v);
END;
/

Es ist auch möglich, Werte von Prozeduren verändern zu lassen: 

CREATE OR REPLACE PROCEDURE mysecondproc(param1 IN OUT NUMBER)
IS
BEGIN
   Param1 := Param1 + 42;
END;
/

Wird dieses zweite Beispiel verwendet, liefert der entsprechende Test: 

set serveroutput on
DECLARE
  x NUMBER := 13;
BEGIN
   DBMS_OUTPUT.PUT_LINE( x );
   mysecondproc( x );
   DBMS_OUTPUT.PUT_LINE( x );
END;
/

wie zu erwarten, werden die Werte 13 und 55 ausgegeben.


Bedingungen


Das Buch PL-SQL ist zurzeit in Überarbeitung! Änderungen auf dieser Seite bitte nur in Absprache mit mir.

Allgemeines

[Bearbeiten]

Ein Cursor ist eine Art Zeilenvariable oder -zeiger, mit dem die Ergebnismenge einer SELECT-Anweisung zeilenweise (mit Hilfe der FETCH-Anweisung oder per CURSOR-FOR-LOOP) durchschritten wird.

Syntax

[Bearbeiten]
 cursor [NAME] is
 select [SPALTEN,*] from [TABELLE] 
 where  [BEDINGUNG] ....;

Cursor Schleifen

[Bearbeiten]
LOOP
FETCH [CURSOR] INTO [TYPE];
.....
EXIT WHEN [BEDINGUNG]
END LOOP;



FOR [i] IN [CURSOR]
LOOP
.....
EXIT;


Schleifen


Das Buch PL-SQL ist zurzeit in Überarbeitung! Änderungen an dieser Seite nur in Absprache mit mir.

Wie in jeder Sprache unterstützt auch PL/SQL Schleifen. Um richtig in die Programmierung einzusteigen, wollen wir hier zuallererst zwischen kopf- und fußgesteuerten Schleifen unterscheiden.

  • Loop --- Until
  • While
  • For - Schleifen

Beispiele LOOP --- UNTIL:

(PL/SQL kennt keine REPEAT/UNTIL Konstruktion, stattdessen kann mit Hilfe der EXIT-Anweisung eine Schleife an beliebiger Stelle abgebrochen werden.) 

Declare
  x NUMBER := 1;
Begin
  Loop
     DBMS_OUTPUT.put_line( x );
     X := X + 1;
     EXIT WHEN x > 10;
  End Loop;
End;

Die Schleife wird solange ausgeführt, bis die Abbruchbedingung erfüllt ist. Die EXIT Bedingung kann an jeder beliebigen Stelle innerhalb der Schleife verwendet werden. Es ist zu beachten, daß bei unkorrekter Verwendung eine Endlosschleife ausgeführt wird. Wird die EXIT Bedingung am Beginn der Schleife verwendet, entspricht es einer WHILE Schleife.

Lesbarer ist allerdings die 'echte' WHILE Schleife: 

Declare
  x NUMBER := 1;
Begin
  While X < 11 Loop
     DBMS_OUTPUT.put_line( x );
     X := X + 1;
  End Loop;
End;

Hier ein Beispiel für eine einfache FOR Schleife: 

Declare
  -- Indexvariablen müssen nicht deklariert werden;
  -- bei X handelt es sich immer um eine ganze Zahl.
Begin
  For X IN 1 .. 10 Loop
    DBMS_OUTPUT.put_line( x );
  End Loop;
End;

Die Schleife wird genau 10 mal ausgeführt.



Cursor


Das Buch PL-SQL wird zurzeit überarbeitet. Änderungen an dieser Seite bitte nur in Absprache mit mir.

Cursor sind Zeiger

[Bearbeiten]

Cursor ist englisch und bedeutet so viel wie "Zeiger". Im allgemeinen Gebrauch mit dem Computer hat das Wort vor allem zwei Bedeutungen: Einerseits bezeichnet man den Mauszeiger als Cursor und zum anderen den Strich, welcher die Eingabestelle bei Eingebeprogrammen (überall, wo man etwas schreiben kann) markiert.

Im Zusammenhang mit PL/SQL hat er noch eine dritte Bedeutung. Laut Wikipedia steht der Begriff hier eventuell als Abkürzung für Current Set of Records. Das bedeutet, er ist ein temporärer Bereich im Arbeitsspeicher, welcher Informationen über eine spezielle PL/SQL-Anweisung und ihre Ausgabe-Daten speichert und diese auch manipulieren kann. (Als Anweisung bezeichnet man einen Bereich zwischen zwei Semikola.) Wird ein Cursor in der Anweisung wieder geschlossen, gehen auch die in ihm enthaltenen Daten verloren.

In PL/SQL unterscheidet man, je nach Unterscheidungkriterium, insgesamt vier verschiedene Cursor: Das sind einerseits die statischen bzw. dynamischen und andererseits die expliziten bzw. impliziten Cursor. Die Anweisungen eines dynamischen Cursors werden erst zur Laufzeit festgelegt und können für jeden gültigen SQL-Anweisungstyp genutzt werden. Implementiert werden sie über EXECUTE IMMEDIATE-Anweisungen. Ein statischer Cursor hingegen wird bereits zur Kompilierzeit festgelegt und nur für DML-Anweisungen (also SELECT, INSERT, UPDATE, DELETE, MERGE oder SELECT FOR UPDATE) verwendet. Auftauchen können sie in zweierlei Formen: als explizit deklarierte oder implizit eingebettete Cursor.

Implizierter Cursor

[Bearbeiten]

Implizite Cursor werden weder deklariert noch per Befehl geöffnet, bearbeitet oder geschlossen. Sie tauchen erst an der Stelle im Ausführungsteil auf, an der sie benötigt werden, und werden danach wieder geschlossen, wodurch die in ihnen enthaltenen Daten wieder gelöscht werden.

Hier ein Beispiel eines impliziten Cursors, mit dem ein einziger Datensatz gelesen werden kann:

Beispiel in einem anonymen Block: 

declare
    v_user_id users.id%TYPE;
    v_username users.username%type;
begin
    -- implicit cursor
    SELECT id, username
    INTO v_user_id, v_username
    FROM users
    WHERE username='test';
    
    dbms_output.PUT_LINE('----------------------------');
    dbms_output.PUT_LINE('User-ID: '||v_user_id);
    dbms_output.PUT_LINE('Username: '||v_username);
    dbms_output.PUT_LINE('----------------------------');
end;

Wenn man dabei ein SELECT-Statement angibt, das mehr als einen Satz als Ergebnis hat, wird eine Fehlermeldung ausgegeben, da nicht klar ist, welcher der gefundenen Datensätze in die Variable geschrieben werden soll.

Für SELECT-Statements, die mehr als einen Ergebnis-Satz liefern, kann die folgende Konstruktion verwendet werden: 

begin
    -- implicit cursor mit FOR
    FOR rec_users in ( SELECT id, username, firstname, lastname FROM users ) LOOP
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Expliziter Cursor

[Bearbeiten]

Explizite Cursor sind, wie der Name bereits erahnen lässt, im Deklarationsabschnitt deklariert. Dies kann ohne Parameter, mit Argumenten als Parameterliste oder auch mit einer RETURN-Klausel geschehen. Im Ausführungs- bzw. Exceptionabschnitt operiert man mit ihnen mithilfe der Befehle OPEN, FETCH und CLOSE zum Öffnen, Auslesen und Schließen. Dabei sind explizite Cursor die einzigen, die auch mehrzeilige Ausgaben machen können.

Hier ein Beispiel dazu in einem anonymen Block: 

declare  
    CURSOR cur_users IS
        SELECT id, username, firstname, lastname
        FROM users;
    rec_users cur_users%ROWTYPE;
begin
    -- explicit cursor
    OPEN cur_users;
    LOOP
        FETCH cur_users INTO rec_users;
        EXIT WHEN cur_users%NOTFOUND;
    
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Statusvariablen für Cursor

[Bearbeiten]

In einer Cursor-FOR-Schleife kann man die Anzahl der bereits gelesenen Sätze mit der Statusvariablen ROWCOUNT abfragen. Diese Variable ist jedoch nur im Inneren der Schleife gültig. Wenn man sie außerhalb der Schleife verwendet, dann wird ein Syntaxfehler ausgegeben.

Beispiel: 

SET SERVEROUTPUT ON
DECLARE
CURSOR c IS
SELECT table_name 
FROM all_tables
WHERE ROWNUM <= 10
;
BEGIN
dbms_output.put_line('start');
-- gibt Fehler:
-- dbms_output.put_line('vor for i cursor rowcount=' || to_char(c%rowcount));
FOR i IN c LOOP
   dbms_output.put_line('cursor rowcount=' || to_char(c%rowcount));
END LOOP;
-- gibt Fehler:
-- dbms_output.put_line('nach end loop cursor rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
END;
/
SHOW ERRORS

Das Programm hat folgende Ausgabe: 

start
cursor rowcount=1
cursor rowcount=2
cursor rowcount=3
cursor rowcount=4
cursor rowcount=5
cursor rowcount=6
cursor rowcount=7
cursor rowcount=8
cursor rowcount=9
cursor rowcount=10
fertig
PL/SQL procedure successfully completed.
No errors.
Rowcount bei einem Cursor ausgeben

Die auskommentierten Zeilen ergeben eine Fehlermeldung, wenn sie ausgeführt werden sollen. 

set serveroutput on
declare
cursor c is
select table_name 
from all_tables
where rownum <= 1
;
name varchar2(100);
begin
dbms_output.put_line('start');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
open c;
dbms_output.put_line('open c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
close c;
dbms_output.put_line('close c');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
end;
/
show errors

Das Programm hat folgende Ausgabe: 

start
c%isopen=FALSE
open c
c%found=FALSE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=0
fetch c
c%found=TRUE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
close c
c%isopen=FALSE
fertig
PL/SQL procedure successfully completed.
No errors.


Exception-Handling


Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Vordefinierte Exceptions

[Bearbeiten]

Eine Auswahl an Exceptions. Eine vollständige Liste steht unter [1] zur Verfügung.

Name Fehlercode Beschreibung Vorhanden ab
CURSOR_ALREADY_OPEN ORA-06511 Cursor ist bereits geöffnet Oracle 7
DUP_VAL_ON_INDEX ORA-00001 INSERT oder UPDATE eines Wertes, der bereits vorhanden ist, in einer als UNIQUE deklarierten Spalte Oracle 7
INVALID_CURSOR ORA-01001 Der Cursor ist nicht deklariert (existiert nicht) oder wurde nicht geöffnet Oracle 7
INVALID_NUMBER ORA-01722 Fehler bei impliziter Typkonvertierung, z.B. String enthält keinen numerischen Wert wird aber in NUMBER Spalte/Variable eingefügt bzw. übergeben Oracle 7
LOGIN_DENIED ORA-01017 Benutzername oder Passwort falsch Oracle 7
NO_DATA_FOUND ORA-01403 SELECT liefert kein Ergebnis: keine Daten wurden durch einen impliziten Cursor gefunden oder ein Index in einem Assoziativen Array existiert nicht Oracle 7
NOT_LOGGED_ON ORA-01012 Keine Verbindung zur Datenbank Oracle 7
PROGRAM_ERROR ORA-06501 Interner PL/SQL - Fehler Oracle 7
ROWTYPE_MISMATCH ORA-06504 Strukturvariablen inkompatibel Oracle 7
STORAGE_ERROR ORA-06500 Speicherprobleme Oracle 7
TIMEOUT_ON_RESOURCE ORA-00051 Datenbank-Sperre Oracle 7
TOO_MANY_ROWS ORA-01422 SELECT liefert mehr als eine Zeile Oracle 7
VALUE_ERROR ORA-06502 Fehler beim Arbeiten mit "Werten" in Variablen/Tabellen/Prozeduren, z.B. zu große Werte, Verletzung von Constraints, Fehler bei Typkonvertierungen (außer INVALID_NUMBER - Exception) Oracle 7
ZERO_DIVIDE ORA-01476 bei Division durch 0 Oracle 7
ACCESS_INTO_NULL ORA-06530 Zuweisung an ein nicht initialisiertes Objekt Oracle 8
ACCESS_INTO_NULL ORA-06531 Zugriff auf eine nicht initialisierte Collection (TABLE Variable oder VARRAY) Oracle 8
SELF_IS_NULL ORA-030625 Methode eines nicht initialisierten Objekts wird aufgerufen Oracle 8
SUBSCRIPT_BEYOND_COUNT ORA-06533 Index für Zugriff auf eine Collection (TABLE Variable oder VARRAY) ist nicht vorhanden Oracle 8
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 ungültiger Index bei Zugriff auf eine Collection (TABLE Variable oder VARRAY) Oracle 8
SYS_INVALID_ROWID ORA-01410 Zuweisung eines inkompatiblen/nicht konvertierbaren Datentyps zu einem ROWID-Element Oracle 8
CASE_NOT_FOUND ORA-06592 In einem CASE konnte kein WHEN ausgewählt werden und ELSE existiert nicht Oracle 9i



Exceptions gelten immer für den Block in dem sie deklariert sind. Da innerhalb eines Blocks auch mehrere Exceptions auftreten können, ist es möglich, diese hintereinander abzufragen. Mit "others" werden alle bis dahin unbehandelten Exceptions abgefangen.

Beispiel: 

  declare
  ..
  begin
  ..
  exception
  when ZERO_DIVIDE then
  ... -- Fehler behandeln
  when others then
  ... -- Fehler behandeln
  end;


mit RAISE können Exceptions erzeugt werden oder auch aus dem aktuellen Block an den umschließenden Block weitergeleitet werden. 

  declare
  ..
  begin
  ..
    declare
      lv_zahl NUMBER (10);
    begin
      select into lv_zahl 99 from dual;
      IF lv_zahl = 99
        RAISE INVALID_NUMBER
      END IF;
    exception
      when INVALID_NUMBER
         DBMS_OUTPUT.PUT_LINE("99 gilt nicht!");
         RAISE;
    end;
  ..
  exception
    when INVALID_NUMBER
      DBMS_OUTPUT.PUTLINE (sqlcode||sqlerrm);
  end;

Es wird im inneren Block die Exception abgefangen, mit einer Meldung behandelt und danach an den äußeren Block weitergeleitet. Dort wird durch sqlcode und sqlerrm sowohl die Exception-Nummer als auch der Fehlertext der vordefinierten Exception ausgegeben.

Referenzen

[Bearbeiten]
  1. ↑ http://download.oracle.com/docs/cd/E11882_01/server.112/e10880/toc.htm


Variablen


ORACLE Built-in Datentypen

[Bearbeiten]

Text

[Bearbeiten]
Bezeichnung Beschreibung Deklarationsbeispiel Anwendungsgebiet Kommentar
CHAR(size) Speicherung von Texten fester Länge CHAR(8) Codes oder Abkürzungen gleicher Länge Maximale Größe 32767 Bytes. Default und Minimum sind 1 Byte. Beim Speichern wird der übergebene String mit Leerzeichen bis zur deklarierten Länge aufgefüllt.
NCHAR(size) Speicherung von Texten fester Länge NCHAR(8) Codes oder Abkürzungen gleicher Länge Ähnlich CHAR in Unicode, maximal 32767/2 bei AL16UTF16 encoding, und 32767/3 bei UTF8.
VARCHAR2(size) Speicherung von Texten variabler Länge VARCHAR2(200) Zeichenketten bis maximal 32767 Byte Häufig verwendeter Datentyp für Texte - Basisdatentyp. Bei der Deklaration muss eine Längenangabe erfolgen, dies ist die maximale Größe. Bei Vergleichsoperationen wird nur der tatsächlich gespeicherte String verglichen.
NVARCHAR2(size) Speicherung von Texten variabler Länge NVARCHAR2(200) Zeichenketten bis maximal 32767 Byte Ähnlich VARCHAR2 in Unicode, maximal 32767/2 bei AL16UTF16 encoding, und 32767/3 bei UTF8.
LONG Speicherung von langen Texten LONG Texte bis zu 4 GB oder 231 -1 Bytes. LONG sollte nicht mehr verwendet werden. Oracle empfiehlt statt dessen LOB zu verwenden, da LONG möglicherweise in Zukunft nicht mehr supported wird.
CLOB Speicherung von langen Texten CLOB Texte bis zu (4 GiBytes - 1)*Blocksize (8, 16, 32, 64 oder 128 TiByte) Character Large Object speichert lange Texte im Datenbankzeichensatz.
NCLOB Speicherung von langen Texten NCLOB Texte bis zu (4 GiBytes - 1)*Blocksize (8, 16, 32, 64 oder 128 TiByte) Character Large Object für Unicodezeichen.

Zahlen

[Bearbeiten]
Bezeichnung Beschreibung Deklarationsbeispiel Anwendungsgebiet Kommentar
NUMBER(p,s) Speicherung von Zahlen mit fest definierter Genauigkeit(p) und Anzahl der Nachkommastellen(s) NUMBER(22,2) Währung Sehr häufig verwendeter Datentyp für alle Zahlenformate - Basisdatentyp.

1 <= p <= 38, -84 <= s <= 127.
Wertebereich 1.0 x 10-130 bis 9.9...9 x 10125.
Integer können mit NUMBER(p) definiert werden, Fließkommazahlen mit NUMBER.

Datum

[Bearbeiten]
Bezeichnung Beschreibung Deklarationsbeispiel Anwendungsgebiet Kommentar
DATE Speicherung von Datum und Zeit DATE Datum und Zeit (in Sekundengenauigkeit) Erlaubtes Intervall von 1.1.4712 v.Chr. 31.12.9999 n.Chr..
TIMESTAMP (fractional_seconds_precision) Zeitstempel TIMESTAMP(6) z.B. Audit des Erstellungszeitpunkts eines Datensatzes Datum mit Tag, Uhrzeit und den spezifizierten Sekundenbruchteilen (0-9)
TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE Zeitstempel TIMESTAMP(6)   wie TIMESTAMP mit Abweichung der Zeitzone
TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE Zeitstempel TIMESTAMP(6)   wie vorhergehendes mit folgenden Ausnahmen: Beim Speichern werden die Daten auf die Zeitzone der Datenbank normalisiert, beim Auslesen auf die Zeitzone des Anwenders.
INTERVAL YEAR (year_precision) TO MONTH Zeitintervall INTERVAL YEAR (2) TO MONTH   Zeitintervall in Jahren und Monaten, wobei year_precision die Zahl der Stellen im Jahresfeld des Datums ist (0-9).
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision) Zeitintervall INTERVAL DAY (2) TO SECOND (5)   Zeitintervall in Tagen, Stunden, Minuten und Sekunden, wobei day_precision die Maximale Zahl der Ziffern bei den Tagen angibt (0-9). fractional_seconds_precision ist der Sekundenbruchteil (0-9).

Binärdaten

[Bearbeiten]
Bezeichnung Beschreibung Deklarationsbeispiel Anwendungsgebiet Kommentar
RAW (size) Binärdaten RAW(256)   Binärdaten mit spezifizierter Länge, maximal 2000 Bytes.
LONG RAW Binärdaten LONG RAW   Binärdaten mit variabler Länge. Maximale Größe 2 GB. Oracle empfiehlt statt LONG RAW besser BLOB zu verwenden, da die Verarbeitung weniger Restriktionen unterliegt.
BLOB Binärdaten BLOB z.B. in der Datenbank gespeicherte Worddateien Maximale Größe bis zu (4 GiBytes - 1)*Blocksize (8, 16, 32, 64 oder 128 TiByte)
BFILE Verweis auf eine Binärdatei BFILE z.B. Verweis auf externe Videodateien Verweis auf eine Binärdatei, die Außerhalb der Datenbank abgelegt ist. Maximale Größe 8 Terabyte

Sonstige

[Bearbeiten]
Bezeichnung Beschreibung Deklarationsbeispiel Anwendungsgebiet Kommentar
ROWID Eindeutige Adresse einer Zeile in einer Tabelle ROWID z.B. temporäre Kennung eines Datensatzes ohne eindeutige ID Nicht portable, interne Adresse für einen Datensatz; erhält man beim SELECT durch Angabe der Pseudospalte ROWID.
UROWID [(size)] Logische Adresse einer Zeile in einer index-organized table. UROWID   Maximale Größe 4000 Bytes.

PL/SQL Datentypen

[Bearbeiten]

Neben den Built-in Datentypen, die auch für die Definition von Tabellen benutzt werden können, können in der Sprache PL/SQL weitere Datentypen verwendet werden.

Zahlen

[Bearbeiten]
Bezeichnung Beschreibung Deklarationsbeispiel Anwendungsgebiet Kommentar
PLS_INTEGER Ganzzahlen mit einer Größe von -2.147.483.647 bis +2.147.483.647 PLS_INTEGER[()] Normale Zahlen, Iterationen Sehr häufig verwendeter Datentyp für Rechenoperationen (Rechnen mit Ganzzahlen), seit Version 10g identisch mit BINARY_INTEGER

Hinzu kommen noch Datentypen, die der Benutzer selbst definieren kann. Hierzu gehören Objekte, die wie in der objektorientierten Programmierung eigene Methoden und Attribute haben, Arrays und Collections.

Datentyp über Spalte festlegen

[Bearbeiten]

Variablenname Tabellenname.Spaltenname%type;

Definiert eine Variable des Typs der angegebenen Spalte.

Datentyp über Tabelle festlegen

[Bearbeiten]

Variablenname Tabellenname%rowtype;

Definiert eine Variable des Typs der angegebenen Tabelle.

Beispiel : 

   CURSOR cursor_name IS
       SELECT *
       FROM tabelle;
   
   variable tabelle%rowtype;
   
   ...
   
   FOR i IN cursor_name LOOP
   
       variable := i;
       andere_variable := variable.SPALTENNAME;
   
   END LOOP;

Benutzerdefinierte Datentypen

[Bearbeiten]

Benutzerdefinierte Datentypen werden mit Hilfe von "type datentyp is record(feld1 typ1 :=xyz, feld2 typ2 :=xyz, ..., feldn typn :=xyz);" definiert.

declare
    type t_adresse is record(
        hausname adresse.hausname%type,
        strasse adresse.strasse%type,
        hausnummer adresse.hausnummer%type,
        postleitzahl adresse.postleitzahl%type);
    v_adresse t_adresse;
begin
    select hausname,strasse, hausnummer, postleitzahl into v_adresse from adresse where rownum = 1;
end;

Das Beispielprogramm definiert einen eigenen Datentyp mit Namen t_adresse welcher die Felder hausname, strasse, hausnummer und postleitzahl enthält.

Mit diesem Datentyp wird eine Variable v_adresse definiert, welche mit einem Datensatz aus der Tabelle adresse gefüllt wird.

Mittels Punktnotation kann auf die Attribute zurückgegriffen werden "v_adresse.hausname := 'Nollenburgerweg 115';"


DML-Befehle


Im Anweisungsteil eines PL/SQL-Programms können DML-Befehle ausgeführt werden.

SELECT

[Bearbeiten]

Mit dem Select-Befehl werden Daten aus einer Tabelle oder View gelesen. Die Ergebnisse werden in PL/SQL-Variablen gespeichert. Dabei sind grundsätzlich zwei Fälle zu unterscheiden:

1. Es wird ein Select-Befehl verwendet, der entweder keinen oder maximal einen Satz als Ergebnis liefern kann.

2. Ein Select-Befehl kann auch mehrere Sätze als Ergebnis liefern.

Select-Befehl, der maximal einen Satz liefern kann

[Bearbeiten]

Select-Befehle, die maximal einen Datensatz lesen, sind meistens Zugriffe über den Primärschlüssel einer Tabelle. Dabei kann man entweder für jede Spalte des Ergebnissatzes eine einzelne Variable angeben oder eine Struktur angeben, in die die gelesenen Werte eingetragen werden.

Beispiel für die Verwendung einzelner Variablen:

Für jede Spalte, die durch das Select-Statement gelesen wird, muss eine Variable bereitgestellt werden. 

SET SERVEROUTPUT ON
DECLARE 
nachname varchar2(100);
beruf varchar2(100);
gehalt number;
BEGIN
   SELECT ename, job, sal
   INTO nachname, beruf, gehalt
   FROM scott.emp
   WHERE empno = 7566
   ;
   dbms_output.put_line('Nachname=' || nachname);
   dbms_output.put_line('Beruf=' || beruf);
   dbms_output.put_line('Gehalt=' || to_char(gehalt));
END;
/
SHOW ERRORS

Das Programm gibt folgendes Ergebnis aus: 

Nachname=JONES
Beruf=MANAGER
Gehalt=2975

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SHOW ERRORS
Keine Fehler.

Beispiel für die Verwendung einer Struktur: 

SET SERVEROUTPUT ON
DECLARE 
emp_rec scott.emp%ROWTYPE;
BEGIN
   SELECT *
   INTO emp_rec
   FROM scott.emp
   WHERE empno = 7566
   ;
   dbms_output.put_line('Ename=' || emp_rec.ename);
   dbms_output.put_line('Job=' || emp_rec.job);
   dbms_output.put_line('Sal=' || to_char(emp_rec.sal));
END;
/
SHOW ERRORS

Das Programm gibt folgendes Ergebnis aus: 

Ename=JONES
Job=MANAGER
Sal=2975

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> show errors
Keine Fehler.

Wenn kein Satz gefunden werden kann, dann wird eine Fehlermeldung ausgegeben.

Select-Befehl, der mehrere Sätze liefern kann

[Bearbeiten]

Wenn ein Select-Befehl verwendet wird, der auch mehr als einen einzigen Satz lesen kann, dann gibt es mehrere Möglichkeiten.

1. kann man mit einem Cursor arbeiten. Siehe Kapitel Cursor.

2. man kann auch alle Sätze in eine PL/SQL-Struktur einlesen

Beispiel für einen Bulk-Select

...

INSERT, UPDATE, DELETE

[Bearbeiten]

Angenommen, es existiert eine Tabelle t_test mit folgender Definition:

CREATE TABLE t_test (i NUMBER);

Dann kann man die Daten, die in dieser Tabelle gespeichert werden, mit dem folgenden PL/SQL-Programm verändern: 

BEGIN
INSERT INTO t_test VALUES(1);
INSERT INTO t_test VALUES(2);
UPDATE t_test SET i = 3;
DELETE FROM t_test;
COMMIT;
END;
/
SHOW ERRORS

Durch den Befehl COMMIT werden die Änderungen endgültig und die geänderten Sätze können auch von anderen Sessions gelesen oder weiter verändert werden.

Man hat auch die Möglichkeit, mit dem Befehl ROLLBACK die letzten Änderungen wieder zurückzunehmen. Das geht aber nur dann, wenn die Änderungen noch nicht mit COMMIT freigegeben wurden.

Vordefinierte Statusvariablen

[Bearbeiten]

Es kann sein, dass ein DML-Befehl keinen Satz findet der gelesen, geändert oder gelöscht werden kann. Um sich zu vergewissern, ob ein Satz gefunden wurde, gibt es die vordefinierte Variable sql%rowcount. 

SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line('start');
INSERT INTO t_test values(1);
dbms_output.put_line('insert rowcount=' || to_char(sql%rowcount));
INSERT INTO t_test values(1);
dbms_output.put_line('insert rowcount=' || to_char(sql%rowcount));
UPDATE t_test SET i = 3;
dbms_output.put_line('update rowcount=' || to_char(sql%rowcount));
DELETE FROM t_test;
dbms_output.put_line('delete rowcount=' || to_char(sql%rowcount));
COMMIT;
dbms_output.put_line('fertig');
END;
/
SHOW ERRORS

Das Programm ergibt folgende Ausgabe 

start
insert rowcount=1
insert rowcount=1
update rowcount=2
delete rowcount=2
fertig
PL/SQL procedure successfully completed.
No errors.


Schleifen


Das Buch PL-SQL ist zurzeit in Überarbeitung! Änderungen an dieser Seite nur in Absprache mit mir.

Wie in jeder Sprache unterstützt auch PL/SQL Schleifen. Um richtig in die Programmierung einzusteigen, wollen wir hier zuallererst zwischen kopf- und fußgesteuerten Schleifen unterscheiden.

  • Loop --- Until
  • While
  • For - Schleifen

Beispiele LOOP --- UNTIL:

(PL/SQL kennt keine REPEAT/UNTIL Konstruktion, stattdessen kann mit Hilfe der EXIT-Anweisung eine Schleife an beliebiger Stelle abgebrochen werden.) 

Declare
  x NUMBER := 1;
Begin
  Loop
     DBMS_OUTPUT.put_line( x );
     X := X + 1;
     EXIT WHEN x > 10;
  End Loop;
End;

Die Schleife wird solange ausgeführt, bis die Abbruchbedingung erfüllt ist. Die EXIT Bedingung kann an jeder beliebigen Stelle innerhalb der Schleife verwendet werden. Es ist zu beachten, daß bei unkorrekter Verwendung eine Endlosschleife ausgeführt wird. Wird die EXIT Bedingung am Beginn der Schleife verwendet, entspricht es einer WHILE Schleife.

Lesbarer ist allerdings die 'echte' WHILE Schleife: 

Declare
  x NUMBER := 1;
Begin
  While X < 11 Loop
     DBMS_OUTPUT.put_line( x );
     X := X + 1;
  End Loop;
End;

Hier ein Beispiel für eine einfache FOR Schleife: 

Declare
  -- Indexvariablen müssen nicht deklariert werden;
  -- bei X handelt es sich immer um eine ganze Zahl.
Begin
  For X IN 1 .. 10 Loop
    DBMS_OUTPUT.put_line( x );
  End Loop;
End;

Die Schleife wird genau 10 mal ausgeführt.



Cursor


Das Buch PL-SQL wird zurzeit überarbeitet. Änderungen an dieser Seite bitte nur in Absprache mit mir.

Cursor sind Zeiger

[Bearbeiten]

Cursor ist englisch und bedeutet so viel wie "Zeiger". Im allgemeinen Gebrauch mit dem Computer hat das Wort vor allem zwei Bedeutungen: Einerseits bezeichnet man den Mauszeiger als Cursor und zum anderen den Strich, welcher die Eingabestelle bei Eingebeprogrammen (überall, wo man etwas schreiben kann) markiert.

Im Zusammenhang mit PL/SQL hat er noch eine dritte Bedeutung. Laut Wikipedia steht der Begriff hier eventuell als Abkürzung für Current Set of Records. Das bedeutet, er ist ein temporärer Bereich im Arbeitsspeicher, welcher Informationen über eine spezielle PL/SQL-Anweisung und ihre Ausgabe-Daten speichert und diese auch manipulieren kann. (Als Anweisung bezeichnet man einen Bereich zwischen zwei Semikola.) Wird ein Cursor in der Anweisung wieder geschlossen, gehen auch die in ihm enthaltenen Daten verloren.

In PL/SQL unterscheidet man, je nach Unterscheidungkriterium, insgesamt vier verschiedene Cursor: Das sind einerseits die statischen bzw. dynamischen und andererseits die expliziten bzw. impliziten Cursor. Die Anweisungen eines dynamischen Cursors werden erst zur Laufzeit festgelegt und können für jeden gültigen SQL-Anweisungstyp genutzt werden. Implementiert werden sie über EXECUTE IMMEDIATE-Anweisungen. Ein statischer Cursor hingegen wird bereits zur Kompilierzeit festgelegt und nur für DML-Anweisungen (also SELECT, INSERT, UPDATE, DELETE, MERGE oder SELECT FOR UPDATE) verwendet. Auftauchen können sie in zweierlei Formen: als explizit deklarierte oder implizit eingebettete Cursor.

Implizierter Cursor

[Bearbeiten]

Implizite Cursor werden weder deklariert noch per Befehl geöffnet, bearbeitet oder geschlossen. Sie tauchen erst an der Stelle im Ausführungsteil auf, an der sie benötigt werden, und werden danach wieder geschlossen, wodurch die in ihnen enthaltenen Daten wieder gelöscht werden.

Hier ein Beispiel eines impliziten Cursors, mit dem ein einziger Datensatz gelesen werden kann:

Beispiel in einem anonymen Block: 

declare
    v_user_id users.id%TYPE;
    v_username users.username%type;
begin
    -- implicit cursor
    SELECT id, username
    INTO v_user_id, v_username
    FROM users
    WHERE username='test';
    
    dbms_output.PUT_LINE('----------------------------');
    dbms_output.PUT_LINE('User-ID: '||v_user_id);
    dbms_output.PUT_LINE('Username: '||v_username);
    dbms_output.PUT_LINE('----------------------------');
end;

Wenn man dabei ein SELECT-Statement angibt, das mehr als einen Satz als Ergebnis hat, wird eine Fehlermeldung ausgegeben, da nicht klar ist, welcher der gefundenen Datensätze in die Variable geschrieben werden soll.

Für SELECT-Statements, die mehr als einen Ergebnis-Satz liefern, kann die folgende Konstruktion verwendet werden: 

begin
    -- implicit cursor mit FOR
    FOR rec_users in ( SELECT id, username, firstname, lastname FROM users ) LOOP
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Expliziter Cursor

[Bearbeiten]

Explizite Cursor sind, wie der Name bereits erahnen lässt, im Deklarationsabschnitt deklariert. Dies kann ohne Parameter, mit Argumenten als Parameterliste oder auch mit einer RETURN-Klausel geschehen. Im Ausführungs- bzw. Exceptionabschnitt operiert man mit ihnen mithilfe der Befehle OPEN, FETCH und CLOSE zum Öffnen, Auslesen und Schließen. Dabei sind explizite Cursor die einzigen, die auch mehrzeilige Ausgaben machen können.

Hier ein Beispiel dazu in einem anonymen Block: 

declare  
    CURSOR cur_users IS
        SELECT id, username, firstname, lastname
        FROM users;
    rec_users cur_users%ROWTYPE;
begin
    -- explicit cursor
    OPEN cur_users;
    LOOP
        FETCH cur_users INTO rec_users;
        EXIT WHEN cur_users%NOTFOUND;
    
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Statusvariablen für Cursor

[Bearbeiten]

In einer Cursor-FOR-Schleife kann man die Anzahl der bereits gelesenen Sätze mit der Statusvariablen ROWCOUNT abfragen. Diese Variable ist jedoch nur im Inneren der Schleife gültig. Wenn man sie außerhalb der Schleife verwendet, dann wird ein Syntaxfehler ausgegeben.

Beispiel: 

SET SERVEROUTPUT ON
DECLARE
CURSOR c IS
SELECT table_name 
FROM all_tables
WHERE ROWNUM <= 10
;
BEGIN
dbms_output.put_line('start');
-- gibt Fehler:
-- dbms_output.put_line('vor for i cursor rowcount=' || to_char(c%rowcount));
FOR i IN c LOOP
   dbms_output.put_line('cursor rowcount=' || to_char(c%rowcount));
END LOOP;
-- gibt Fehler:
-- dbms_output.put_line('nach end loop cursor rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
END;
/
SHOW ERRORS

Das Programm hat folgende Ausgabe: 

start
cursor rowcount=1
cursor rowcount=2
cursor rowcount=3
cursor rowcount=4
cursor rowcount=5
cursor rowcount=6
cursor rowcount=7
cursor rowcount=8
cursor rowcount=9
cursor rowcount=10
fertig
PL/SQL procedure successfully completed.
No errors.
Rowcount bei einem Cursor ausgeben

Die auskommentierten Zeilen ergeben eine Fehlermeldung, wenn sie ausgeführt werden sollen. 

set serveroutput on
declare
cursor c is
select table_name 
from all_tables
where rownum <= 1
;
name varchar2(100);
begin
dbms_output.put_line('start');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
open c;
dbms_output.put_line('open c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
close c;
dbms_output.put_line('close c');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
end;
/
show errors

Das Programm hat folgende Ausgabe: 

start
c%isopen=FALSE
open c
c%found=FALSE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=0
fetch c
c%found=TRUE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
close c
c%isopen=FALSE
fertig
PL/SQL procedure successfully completed.
No errors.


Exception-Handling


Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Vordefinierte Exceptions

[Bearbeiten]

Eine Auswahl an Exceptions. Eine vollständige Liste steht unter [1] zur Verfügung.

Name Fehlercode Beschreibung Vorhanden ab
CURSOR_ALREADY_OPEN ORA-06511 Cursor ist bereits geöffnet Oracle 7
DUP_VAL_ON_INDEX ORA-00001 INSERT oder UPDATE eines Wertes, der bereits vorhanden ist, in einer als UNIQUE deklarierten Spalte Oracle 7
INVALID_CURSOR ORA-01001 Der Cursor ist nicht deklariert (existiert nicht) oder wurde nicht geöffnet Oracle 7
INVALID_NUMBER ORA-01722 Fehler bei impliziter Typkonvertierung, z.B. String enthält keinen numerischen Wert wird aber in NUMBER Spalte/Variable eingefügt bzw. übergeben Oracle 7
LOGIN_DENIED ORA-01017 Benutzername oder Passwort falsch Oracle 7
NO_DATA_FOUND ORA-01403 SELECT liefert kein Ergebnis: keine Daten wurden durch einen impliziten Cursor gefunden oder ein Index in einem Assoziativen Array existiert nicht Oracle 7
NOT_LOGGED_ON ORA-01012 Keine Verbindung zur Datenbank Oracle 7
PROGRAM_ERROR ORA-06501 Interner PL/SQL - Fehler Oracle 7
ROWTYPE_MISMATCH ORA-06504 Strukturvariablen inkompatibel Oracle 7
STORAGE_ERROR ORA-06500 Speicherprobleme Oracle 7
TIMEOUT_ON_RESOURCE ORA-00051 Datenbank-Sperre Oracle 7
TOO_MANY_ROWS ORA-01422 SELECT liefert mehr als eine Zeile Oracle 7
VALUE_ERROR ORA-06502 Fehler beim Arbeiten mit "Werten" in Variablen/Tabellen/Prozeduren, z.B. zu große Werte, Verletzung von Constraints, Fehler bei Typkonvertierungen (außer INVALID_NUMBER - Exception) Oracle 7
ZERO_DIVIDE ORA-01476 bei Division durch 0 Oracle 7
ACCESS_INTO_NULL ORA-06530 Zuweisung an ein nicht initialisiertes Objekt Oracle 8
ACCESS_INTO_NULL ORA-06531 Zugriff auf eine nicht initialisierte Collection (TABLE Variable oder VARRAY) Oracle 8
SELF_IS_NULL ORA-030625 Methode eines nicht initialisierten Objekts wird aufgerufen Oracle 8
SUBSCRIPT_BEYOND_COUNT ORA-06533 Index für Zugriff auf eine Collection (TABLE Variable oder VARRAY) ist nicht vorhanden Oracle 8
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 ungültiger Index bei Zugriff auf eine Collection (TABLE Variable oder VARRAY) Oracle 8
SYS_INVALID_ROWID ORA-01410 Zuweisung eines inkompatiblen/nicht konvertierbaren Datentyps zu einem ROWID-Element Oracle 8
CASE_NOT_FOUND ORA-06592 In einem CASE konnte kein WHEN ausgewählt werden und ELSE existiert nicht Oracle 9i



Exceptions gelten immer für den Block in dem sie deklariert sind. Da innerhalb eines Blocks auch mehrere Exceptions auftreten können, ist es möglich, diese hintereinander abzufragen. Mit "others" werden alle bis dahin unbehandelten Exceptions abgefangen.

Beispiel: 

  declare
  ..
  begin
  ..
  exception
  when ZERO_DIVIDE then
  ... -- Fehler behandeln
  when others then
  ... -- Fehler behandeln
  end;


mit RAISE können Exceptions erzeugt werden oder auch aus dem aktuellen Block an den umschließenden Block weitergeleitet werden. 

  declare
  ..
  begin
  ..
    declare
      lv_zahl NUMBER (10);
    begin
      select into lv_zahl 99 from dual;
      IF lv_zahl = 99
        RAISE INVALID_NUMBER
      END IF;
    exception
      when INVALID_NUMBER
         DBMS_OUTPUT.PUT_LINE("99 gilt nicht!");
         RAISE;
    end;
  ..
  exception
    when INVALID_NUMBER
      DBMS_OUTPUT.PUTLINE (sqlcode||sqlerrm);
  end;

Es wird im inneren Block die Exception abgefangen, mit einer Meldung behandelt und danach an den äußeren Block weitergeleitet. Dort wird durch sqlcode und sqlerrm sowohl die Exception-Nummer als auch der Fehlertext der vordefinierten Exception ausgegeben.

Referenzen

[Bearbeiten]
  1. ↑ http://download.oracle.com/docs/cd/E11882_01/server.112/e10880/toc.htm


Prozedur

Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Einstieg

[Bearbeiten]

Prozeduren sind nützlich, um regelmäßig wiederkehrende Arbeitsabläufe zu automatisieren z.B. um Installationsarbeiten durchzuführen.

Als Einstieg soll eine Prozedur dienen, die nur aus zwei Befehlen besteht. 

CREATE OR REPLACE PROCEDURE spins IS
BEGIN
   INSERT INTO tdept (deptno, deptname, mgrno, admrdept)
   VALUES ('A00', 'SPIFFY COMPUTER DIV.', '000010', 'A00');
   INSERT INTO tdept (deptno, deptname, mgrno, admrdept)
   VALUES ('B01', 'PLANNING            ', '000020', 'A00');
END;
/

Die Procedur wird im SQLPLUS aufgerufen mit dem Befehl: 

EXECUTE spins;

Innerhalb eines PL/SQL-Scripts wird die Procedur nur durch Angabe ihres Namens aufgefufen. 

BEGIN
   spins;
END;
/

Parameterübergabe

[Bearbeiten]

Wenn man der Prozedur Parameter übergeben will, dann wird unterschieden in:

  • IN: Die aufrufende Umgebung übergibt einen Wert an die Prozedur. Dieser Parameter kann innerhalb der Verarbeitung nicht verändert werden. IN ist der default Parameter-Modus
  • OUT: Die aufrufende Umgebung übergibt eine Variable an die Prozedur, die innerhalb der Prozedur als nicht initialisiert betrachtet wird. Wenn innerhalb der Verarbeitung diesem Parameter ein Wert zugewiesen wird, dann wird dieser an die aufrufende Umgebung zurückgegeben.
  • IN OUT: Die Aufrufende Umgebung übergibt eine Variable an die Prozedur. Diese kann innerhalb der Prozedur verwendet werden und auch geändert werden. Der geänderte Wert wird der aufrufenden Umgebung mitgeteilt.


Einfache Prozedur, die einen Parameter übergeben bekommt. Dieser Parameter wird durch den Befehl put_line wieder ausgegeben. Um die Ausgabe zu aktivieren, muss erst der SQL*Plus - Befehl 'set serveroutput on' ausgeführt werden. 

set serveroutput on
CREATE OR REPLACE PROCEDURE myfirstproc(v IN VARCHAR2)
IS
BEGIN
   dbms_output.put_line(v);
END;
/

Es ist auch möglich, Werte von Prozeduren verändern zu lassen: 

CREATE OR REPLACE PROCEDURE mysecondproc(param1 IN OUT NUMBER)
IS
BEGIN
   Param1 := Param1 + 42;
END;
/

Wird dieses zweite Beispiel verwendet, liefert der entsprechende Test: 

set serveroutput on
DECLARE
  x NUMBER := 13;
BEGIN
   DBMS_OUTPUT.PUT_LINE( x );
   mysecondproc( x );
   DBMS_OUTPUT.PUT_LINE( x );
END;
/

wie zu erwarten, werden die Werte 13 und 55 ausgegeben.


Funktion

Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Neben den vielen Funktionen, die die SQL-Sprache schon bietet, kann man eigene Funktionen definieren. Funktionen haben immer einen oder mehrere Input-Parameter und genau einen Ergebniswert.

Hier ein ganz einfaches Beispiel einer Funktion, die einen String als Input erwartet und einen String als Ergebnis liefert. Die Verarbeitung der Funktion besteht darin, den Input-Parameter um ein 'x' zu erweitern. 

CREATE OR REPLACE FUNCTION myfunction (in_parm VARCHAR2) RETURN VARCHAR2 IS
BEGIN
   RETURN in_parm || 'x';
END;
/

Diese Funktion kann man nun genauso verwenden wie die anderen bereits vordefinierten Funktionen. Man muss nur darauf achten, dass man die richtige Anzahl an Parametern angibt und die passenden Datentypen wählt. Beispielaufruf: 

SELECT loc, myfunction(loc) 
FROM scott.dept;

Ergebnis: 

LOC           MYFUNCTION(LOC)
------------- ---------------
NEW YORK      NEW YORKx
DALLAS        DALLASx
CHICAGO       CHICAGOx
BOSTON        BOSTONx


In dem Anweisungsteil der Funktion können beliebig komplexe PL/SQL-Anweisungen angegeben werden. Ein Beispiel für eine etwas komplexere Funktion: 

CREATE OR REPLACE FUNCTION anzma (abteilung VARCHAR2)
RETURN INTEGER IS
h_anzahl INTEGER;
BEGIN
   SELECT COUNT(*) INTO h_anzahl
   FROM ben01.templ
   WHERE workdept = abteilung;
   RETURN h_anzahl;
END;
/

Funktionen kann man gut testen, indem man sie in einem SELECT einsetzt, der aus der Tabelle dual liest. Diese Tabelle enthält genau einen Satz mit einer Spalte. Eigentlich ist man aber an diesem Satz gar nicht interessiert, sondern man will durch diesen SELECT nur bewirken, dass die Funktion genau einmal aufgerufen wird. Aufrufbeispiel: 

SELECT anzma('A00') 
FROM dual;

Ergebnis: 

ANZMA('A00')
------------
           3


Hinweise:

  • Die 1. Funktion ist deterministisch d.h. wenn man die Funktion zwei mal mit dem selben Eingabe-Parameter aufruft, dann liefert sie immer den selben Output.
  • Die 2. Funktion ist nicht-deterministisch d.h. ihr Ergebnis ist auch von anderen Informationen abhängig, als nur den übergebenen Parametern. Wenn jemand die Sätze in der Tabelle ben01.templ ändert, dann kann die Funktion anzma bei gleichem Eingabe-Parameter ein anderes Ergebnis liefern.


Einführung1


Mit der Oracle Funktion DBMS_OUTPUT.PUT_LINE können Zeichenketten ausgegeben werden, die nicht das Ergebnis einer SQL-Abfrage sind. Allerdings dürfen die Zeichenketten nicht länger als 255 Zeichen sein, sonst kommt es zu einem Abbruch. Wir werden noch eine Funktion vorstellen, die auch längere Zeichenketten ausgeben kann, aber vorerst müssen wir mit 255 Zeichen vorlieb nehmen. In der Oracle 10g Version sollte die Funktion auch mehr Zeichen ausgeben können. Als Einstieg wollen wir nun in SQL*PLUS unsere erste Ausgabe erhalten.

Vorher nicht vergessen in SQL*PLUS auf 

SET SERVEROUTPUT ON

zu stellen. Das ist einer der häufigsten Dinge, die vergessen werden und einen beim Testen zur Verzweiflung bringen.

In PL*SQL würde unser Beispiel 'Hello World' ungefähr so aussehen

Listing: HelloWorld.SQL

[Bearbeiten]
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World') ;
END;

Das war doch gar nicht so schwer, oder?

Vorgehensweise in SQL*PLUS

[Bearbeiten]

Es empfiehlt sich die Anlage einer Datei z.B. mit dem Namen helloworld.plsql: 

  create or replace PROCEDURE helloworld is
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
  END;
  /

Ein Aufruf von sqlplus nach dem Schema: 

  sqlplus user/password @helloworld.plsql

kompiliert die Prozedur. Eventuelle Fehlermeldungen können in SQL*PLUS mit 

  SQL> show err;

eingesehen werden.

Der Aufruf der Prozedur kann dann mit 

  SQL> set serverout on;
  SQL> begin helloworld; end;
  SQL> /

erfolgen.


Literatur


andere Wikibooks zu verwandten Themen

[Bearbeiten]

Infos in Wikipedia

[Bearbeiten]
[Bearbeiten]
[Bearbeiten]


Grundlegender Aufbau

Dieses Buch steht im Regal Programmierung Strukturierungen Zur Startseite


Grundlegener Aufbau

[Bearbeiten]

Für ein PL/SQL-Programm gibt es einen grundlegenden Aufbau 

 plsqlProgramm := [declaration] programm;
   declaration := "declare" declarationsblock;
     declarationsblock := konstantenDeklaration|variablenDeklaration|plsqltableDeklaration|cursorDeklaration|exceptionDeklaration[..];
   programm    := "begin" programmblock "end;";
   programmblock     := anweisung[..];


Anonyme Blöcke1

Dieses Buch steht im Regal Programmierung Deklarationszwang Zur Startseite


Anonyme Blöcke

[Bearbeiten]

Die anonymen Blöcke sind die kleinste Programmeinheit, die es in PL/SQL gibt. 

 anonymerBlock   := ["declare" declarationsblock] "begin" programmblock "end;";
   declarationsblock := konstantenDeklaration|variablenDeklaration|plsqltableDeklaration|cursorDeklaration|exceptionDeklaration[..];
   programmblock     := anweisung[..];
     anweisung       := ergibtAnweisung|sqlKommando|schleifenAnweisung|steuerAnweisung


Funktion

Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Neben den vielen Funktionen, die die SQL-Sprache schon bietet, kann man eigene Funktionen definieren. Funktionen haben immer einen oder mehrere Input-Parameter und genau einen Ergebniswert.

Hier ein ganz einfaches Beispiel einer Funktion, die einen String als Input erwartet und einen String als Ergebnis liefert. Die Verarbeitung der Funktion besteht darin, den Input-Parameter um ein 'x' zu erweitern. 

CREATE OR REPLACE FUNCTION myfunction (in_parm VARCHAR2) RETURN VARCHAR2 IS
BEGIN
   RETURN in_parm || 'x';
END;
/

Diese Funktion kann man nun genauso verwenden wie die anderen bereits vordefinierten Funktionen. Man muss nur darauf achten, dass man die richtige Anzahl an Parametern angibt und die passenden Datentypen wählt. Beispielaufruf: 

SELECT loc, myfunction(loc) 
FROM scott.dept;

Ergebnis: 

LOC           MYFUNCTION(LOC)
------------- ---------------
NEW YORK      NEW YORKx
DALLAS        DALLASx
CHICAGO       CHICAGOx
BOSTON        BOSTONx


In dem Anweisungsteil der Funktion können beliebig komplexe PL/SQL-Anweisungen angegeben werden. Ein Beispiel für eine etwas komplexere Funktion: 

CREATE OR REPLACE FUNCTION anzma (abteilung VARCHAR2)
RETURN INTEGER IS
h_anzahl INTEGER;
BEGIN
   SELECT COUNT(*) INTO h_anzahl
   FROM ben01.templ
   WHERE workdept = abteilung;
   RETURN h_anzahl;
END;
/

Funktionen kann man gut testen, indem man sie in einem SELECT einsetzt, der aus der Tabelle dual liest. Diese Tabelle enthält genau einen Satz mit einer Spalte. Eigentlich ist man aber an diesem Satz gar nicht interessiert, sondern man will durch diesen SELECT nur bewirken, dass die Funktion genau einmal aufgerufen wird. Aufrufbeispiel: 

SELECT anzma('A00') 
FROM dual;

Ergebnis: 

ANZMA('A00')
------------
           3


Hinweise:

  • Die 1. Funktion ist deterministisch d.h. wenn man die Funktion zwei mal mit dem selben Eingabe-Parameter aufruft, dann liefert sie immer den selben Output.
  • Die 2. Funktion ist nicht-deterministisch d.h. ihr Ergebnis ist auch von anderen Informationen abhängig, als nur den übergebenen Parametern. Wenn jemand die Sätze in der Tabelle ben01.templ ändert, dann kann die Funktion anzma bei gleichem Eingabe-Parameter ein anderes Ergebnis liefern.
Druckversion des Buches Name des Buches
  • Dieses Buch umfasst derzeit etwa ? DIN-A4-Seiten einschließlich Bilder.
  • Wenn Sie dieses Buch drucken oder die Druckvorschau Ihres Browsers verwenden, ist diese Notiz nicht sichtbar.
  • Zum Drucken klicken Sie in der linken Menüleiste im Abschnitt „Drucken/exportieren“ auf Als PDF herunterladen.
  • Mehr Informationen über Druckversionen siehe Hilfe:Fertigstellen/ PDF-Versionen.
  • Hinweise:
    • Für einen reinen Text-Ausdruck kann man die Bilder-Darstellung im Browser deaktivieren:
      • Internet-Explorer: Extras > Internetoptionen > Erweitert > Bilder anzeigen (Häkchen entfernen und mit OK bestätigen)
      • Mozilla Firefox: Extras > Einstellungen > Inhalt > Grafiken laden (Häkchen entfernen und mit OK bestätigen)
      • Opera: Ansicht > Bilder > Keine Bilder
    • Texte, die in Klappboxen stehen, werden nicht immer ausgedruckt (abhängig von der Definition). Auf jeden Fall müssen sie ausgeklappt sein, wenn sie gedruckt werden sollen.
    • Die Funktion „Als PDF herunterladen“ kann zu Darstellungsfehlern führen.

Dieser Text ist sowohl unter der „Creative Commons Attribution/Share-Alike“-Lizenz 3.0 als auch GFDL lizenziert.

Eine deutschsprachige Beschreibung für Autoren und Weiternutzer findet man in den Nutzungsbedingungen der Wikimedia Foundation.


Abgerufen von „https://de.wikibooks.org/w/index.php?title=PL/SQL/_Druckversion&oldid=799140“