Muniqsoft Training

Auswahl  

Bind Variable oder Substitutionsvariable? 

Oracle
SQL
RDBMS 11.2:RDBMS 8.x:RDBMS 9.x:12.1, 12.2
16.10.18 (MP)
11.12.18 (MP)
Bind Variable, Substitutionsvariable

Body

1. Fangen wir mal mit der Substitutionsvariable an:

Wir definieren uns eine Textvariable (etwas anderes gibt es auch nicht)

SQL> DEFINE my_ename='KING'

Diese können wir nun in einem SELECT einsetzen:

SQL> SELECT sal FROM emp
WHERE ename='&my_ename.';
alt   2: WHERE ename='&my_ename.'
neu   2: WHERE ename='KING'
       SAL
----------
      5000

     
Die alt und neu ... stört etwas, aber die bekommen wir weg:

SET VERIFY OFF
SELECT sal FROM emp
WHERE ename='&my_ename.';
       SAL
----------
      5000


      
Mit DEFINE bekommen sie alle gesetzten Variablen angezeigt:

SQL> define
DEFINE _DATE           = "11.10.2018 16:18:05" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "o18c" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1803000000" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1803000000" (CHAR)
DEFINE MY_ENAME        = "KING" (CHAR)      

Die Variable bleibt nun bis zum Sessionende am Leben, es sei denn sie löschen sie wieder:

SQL> UNDEFINE my_ename

Was machen wir, wenn wir denn Wert der Variablen vom Benutzer erfragen möchten:

Lösung 1:

SQL> SELECT sal FROM emp
WHERE ename='&my_ename.';
Geben Sie einen Wert f³r my_ename ein: KING
       SAL
----------
      5000

    
Der Inhalt der Variablen wird nach dem Einsatz gelöscht:

SQL> define my_ename
SP2-0135: Symbol my_ename ist nicht definiert (UNDEFINED)

Lösung 2:

SQL> SELECT sal FROM emp
WHERE ename='&&my_ename.';
Geben Sie einen Wert f³r my_ename ein: KING
       SAL
----------
      5000

Hier wird der Inhalt der Variablen bis zum Sessionende (oder UNDEFINE) aufgehoben:

SQL> define my_ename
DEFINE MY_ENAME        = "KING" (CHAR)

Lösung 3: Der Benutzer soll höflich nach dem Wert gefragt werden:

accept my_ename DEFAULT 'KING' PROMPT "Hätten Sie die Güte, den Namen rauszurücken? "

Hinweis:
1. der DEFAULT Wert ist Optional.
2. wenn hinter das Statement noch HIDE geschrieben wird, ist die Eingabe verdeckt möglich.

Was machen wir, wenn der Wert der Variablen aus einem Ergebnis einer Query zurückkommt?

SQL> COL my_ename NEW_VALUE my_ename
SQL> SELECT ename As my_ename FROM emp
WHERE empno=7839;
MY_ENAME
----------
KING
SQL> define my_ename
DEFINE MY_ENAME        = "KING" (CHAR)

Und nun zu den Bindvariablen:
Diese können nur im Filter einer Query eingesetzt werden, sind dafür aber auch in PL/SQL nutzbar.

Wir definieren die Bindvariable mit einem Datentyp:

SQL> VARIABLE my_ename VARCHAR2(100)

Danach füllen wir die Variable mit einem Wert:

SQL> EXEC :MY_ENAME:='KING';

Wir setzen den Bindvariablen-Wert in einer Query ein:

SQL> SELECT sal FROM emp
     WHERE ename=:MY_ENAME
SQL> /
       SAL
----------
      5000

     
Oder befüllen die Variable direkt aus einer Query

SQL> EXEC SELECT ename INTO :my_ename FROM emp WHERE sal=5000;

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.