✔ Oracle 9i, Variablen/Parameter '&param1' ':Param_1'

[tplanitz]

Hallo,

ich habe öfter mit Scripten zu tun die auf eine Oracle DB abgefeuert werden. Kann mir jemand kurz und knapp den unterschied zwischen den Bezeichnern

&param1

und

:Param_1

erklären. Ist meine Vermutung Richtig das das '&' für Sqlplus benötigt wird und der ':' für PLsql Programme?

Vielen dank und Grüße
Thorsten

 

[Exceptionfault]

Genau. Die Variablen mit "&" sind Platzhalter für Scripte, d.h. mit

DEFINE var = 'wert'

kannst du in SQL*Plus die Variable &var definieren. Diese kann dann praktisch in allen Scripten genutzt werden. SQL*Plus ersetzt die Variable VOR der Ausführung. Damit kann man dann auch z.B. direkt in DDL Statements Platzhalter einbauen z.B.

CREATE TABLESPACE &DATA_TBS 
    DATAFILE 'H:\ORACLE\ORADATA\&DB_SID\mydata01.dbf' 
    SIZE &FILESIZE.M REUSE AUTOEXTEND ON 
    NEXT 5M MAXSIZE UNLIMITED 
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Variablen mit dem ":" sind Bind Variablen. Sie stehen für einen Wert, der einem DML Statement übergeben werden kann, aber nicht als textueller Platzhalten wie oben.
Der Unterschied ist, dass SQL*Plus das Statement mit dem Platzhalter an die Datenbank übergibt, diese Parst das Statement und setzt erst nach dem parsen den vom Client übergebenen Wert ein. Das kann z.B. zu einem recht hohen Performancegewinn führen wenn man ein und dasselbe Statement immer wieder ausführt und sich nur der als Bind Variable übergebene Parameter ändert. In dem Fall muss die Datenbank das Statement nur einmal parsen und kann es immer wieder mit dem neu übergebenen Parameter ausführen.

Hoffe das war einigermassen verständlich ?!

 

[tplanitz]

Hallo,

sehr vielen Dank für die Erklärung, das macht einiges deutlich.
Mit Bind Variablen Performance Gewinn, das war mir ja noch gar nicht klar!!

Beste Grüße

Thorsten

 

[Exceptionfault]

Mit Bind Variablen Performance Gewinn, das war mir ja noch gar nicht klar!!

Und wie... hier ein Beispiel:

Ich habe eine Tabelle mit 1.000.000 Sätzen und selektierte in einer Schleife zufällig 5000 Werte, natürlich nacheinander. Im ersten Beispiel baue ich mein SELECT mit einer BIND Variable auf:

declare
	indx		PLS_INTEGER;
	betrag		GELD.BETRAG%TYPE;
	begints		TIMESTAMP;
	endts		TIMESTAMP;
begin

	SELECT	SYSTIMESTAMP
	INTO	begints
	FROM	DUAL;	
	
	for i in 1..5000 loop
		indx := DBMS_RANDOM.VALUE( 1, 1000000 );

		SELECT	BETRAG
		INTO	betrag
		FROM	GELD
		WHERE	BETRAG = indx;

	end loop;

	SELECT	SYSTIMESTAMP
	INTO	endts
	FROM	DUAL;

	DBMS_OUTPUT.put_line( begints - endts );

end;
/

(in einem PL/SQL Block braucht eine Bind Variable kein ":")

Im zweiten Versuch baue ich das SELECT Statement als String zusammen und gebe es dann an Oracle

declare
	indx		PLS_INTEGER;
	betrag		GELD.BETRAG%TYPE;
	begints		TIMESTAMP;
	endts		TIMESTAMP;
begin

	SELECT	SYSTIMESTAMP
	INTO	begints
	FROM	DUAL;	
	
	for i in 1..5000 loop
		indx := DBMS_RANDOM.VALUE( 1, 1000000 );

		EXECUTE IMMEDIATE 'SELECT	BETRAG
						   FROM		GELD
						   WHERE	BETRAG = ' || indx
		INTO betrag;

	end loop;

	SELECT	SYSTIMESTAMP
	INTO	endts
	FROM	DUAL;

	DBMS_OUTPUT.put_line( begints - endts );

end;
/

Vor jeder Auführung rufe ich folgendes auf, um gleiche Bedingungen zu schaffen:

alter system flush shared_pool;
alter system flush buffer_cache;

Und hier die Ergebnisse:

mit  Binds:		00:00:01.627832000
ohne Binds:		00:00:12.997400000

Also ca. Faktor 10

Den Grund für den Unterschied erkennt man, wenn man ein Tracing der Session ausführt:

Mit Binds:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute   5003      0.26       0.26          0          0          0           0
Fetch     5003      0.29       0.24          0      15000          0        5003
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10010      0.56       0.51          0      15000          0        5003

Ohne Binds:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     5022     10.71      14.58          0          0          0           0
Execute   5038      0.50       0.68          0          0          0           0
Fetch     5107      0.43       0.47          0      15196          0        5079
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    15167     11.65      15.74          0      15196          0        5079

Das Parsing... ;-(

 

[flowerpower]

Hallo,

was hat es dann mit :new.xxx und ld.xxx auf sich.

Muss denn wirklich kein ':' angegeben werden?

Hatte überall diese Darstellung bisher gelesen.

Danke und Gruß. flowerpower

 

[Exceptionfault]

:new und ld sind Sonderfälle. Durch sie kann in Triggern auf die Werte von Feldern VOR, bzw. NACH
einem Update zugegriffen werden. Wenn ich also einen BEFORE UPDATE Trigger auf eine Tabelle legen
stehen mir mit :new und ld der neue und der alte Wert zur Verfügung. Hier wird ein ":" auch
innerhalb eines PL/SQL Blocks benötigt.

Hier kurz der Unterschied zwischen Bind Variablen innerhalb und außerhalb eines PL/SQL Blocks:

--innerhalb
declare
   my_bind_var  PLS_INTEGER;
begin

    my_bind_var := 23;

    UPDATE  APP_USERS
    SET     LAST_LOGIN = SYSDATE
    WHERE   APPUSERID  = my_bind_var;
    
    COMMIT;
    
end;
/


--ausserhalb ( geht nur in SQL*Plus )
VARIABLE my_bind_var NUMBER
begin
    :my_bind_var := 23;
    
    UPDATE  APP_USERS
    SET     LAST_LOGIN = SYSDATE
    WHERE   APPUSERID  = :my_bind_var;
    
    COMMIT;
end;
/

Schreibe ich also ein Script, welches ich mit Variablen ausstatten möchte die ich öfter brauche, dann externe Bind Variablen. Das macht mich halt von SQL*Plus abhängig. Schreibe ich Prozeduren, Funktionen etc., also "richtige" PL/SQL Anwendungen, dann habe ich lokale Bind Variablen bzw. Funktionsparameter und brauche keine ":"

 

[flowerpower]

create table spiel (
name varchar2(20),
datum date,
raum varchar2(2),
dauer number);

CREATE OR REPLACE TRIGGER tr_eingabe
BEFORE
INSERT
ON SPIEL
FOR EACH ROW
BEGIN
IF roundnew.datum - sysdate) < 1
then
RAISE_APPLICATION_ERROR(-20500,'Eingaben dürfen erst für nächsten Tag eingegeben werden! ');
END IF;
IF :new.dauer <= 20
then
:new.dauer := 20;
end if;
END;
/

sorry, ich muss noch mal nachfragen, da es mir nicht ganz klar ist, was heisst hier innerhalb und ausserhalb.

wenn ich also :new bzw. ld im PLSQL-Code (wie im obigen Beispiel) verwende, dann muss ich ein ':' verwenden (ist das ':' dann eine Art Deklaration ähnlich einer Zuweisung oder missverstehe ich das).

Das sind dann aber keine Bindevariablen oder doch? Verstehe zwar, anhand deines Beispiels, dass die Abarbeitung schneller ist, aber sonst verschliesst sich mir noch etwas der Nutzen.

Danke, wie immer, für deine kompetente Hilfe.

Gruß. flowerpower

 

[Exceptionfault]

In deinem Beispiel wird ein Trigger verwendet. Das ist im Grunde nichts anderes als eine PL/SQL
Prozedur die zu einem bestimmten Ereignis ausgeführt wird. In diesem Fall hast du interne
Bind Variablen. D.h. es sind Variablen die von der PL/SQL Engine, also quasi in Datenbank
definiert werden.

Externe Bind Variablen kannst du im Grunde wie Konsolenparameter bei einem Schript oder einer EXE
sehen. Sie definierst du ausserhalb der Applikation und gibst sie beim starten mit. Bei uns heisst
dies: Wir definieren die Variablen in der SQL*Plus Session und nutzen sie in fertigen Prozeduren.

Dein Trigger feuert beim einfügen eines neuen Satzes in die Tabelle "Spiel". Oracle stellt dir in
einem Trigger automatisch ein paar Variablen zur Verfügung mit denen du im Trigger arbeiten kannst.
Dazu gehören u.a. die Werte die in die neue Zeile eingefügt werden sollen. In einem UPDATE Trigger
bekommst du sogar die Werte die vorher in der Zeile drin standen UND die Werte die jetzt rein sollen.

Die Variablen heissen genauso wie die Spalten der Tabelle, wie alsu nun den neuen vom alten
unterscheiden? => Mit :new.SPALTENNAME und ld.SPALTENNAME.

In einem INSERT Trigger kann es natürlich nur :NEW geben, in einem DELETE TRIGGER nur :OLD. UPDATE
kennt sowohl :new, als auch ld. Wie schon gesagt sind :new und ld Sonderfälle und haben nix
mit "externen" Bind Variablen aus SQL*Plus zu tun.

Dein Trigger weisst der Variablen DAUER einen neuen Wert zu. sobald dieser größer als 20 wird.
Somit ist es in der Tabelle nicht möglich Spiele mit einer Dauer > 20 zu speichern. Man kann zwar
INSERTEN was man will, aber es wird vom Trigger immer auf 20 gesetzt, sobald die Zahl größer wird.
Nebenbei: hier wäre auch ein CHECK Constraint denkbar.

 

[flowerpower]

Danke Dir.

(Allerdings sollen Spiele mit einer Dauer unter bzw. gleich 20 min auf 20 min gesetzt werden, aber das hast Du sicher auch so gemeint.)

Vielen Dank nochmal.

Gruß. flowerpower