Egy PL/SQL kódba (például egy tárolt alprogramba) beágyazhatók SQL utasítások. Ezek ugyanúgy lefordításra kerülnek, mint a procedurális utasítások és futásról futásra ugyanazt a tevékenységet hajtják végre. Ezen utasítások alakja, szövege fordítási időben ismert és utána nem változik meg. Az ilyen utasításokat statikus SQL utasításoknak hívjuk.

A PL/SQL viszont lehetővé teszi azt is, hogy egy SQL utasítás teljes szövege csak futási időben határozódjon meg, tehát futásról futásra változhasson. Ezeket hívjuk dinamikus SQL utasításoknak.

A dinamikus SQL utasítások sztringekben tárolódnak, melyek futási időben kezelhetők. A sztringek tartalma érvényes SQL utasítás vagy PL/SQL blokk lehet, amelyekbe beágyazhatunk ún. helykijelölőket. Ezek nem deklarált azonosítók, melyek első karaktere a kettőspont és a sztring paraméterezésére szolgálnak.

A dinamikus SQL elsősorban akkor használatos, ha

A dinamikus SQL megvalósítására a következő utasítás szolgál:

EXECUTE IMMEDIATE dinamikus_sztring

[INTO {változó [,változó]…|rekord}]

[USING [IN|OUT|IN OUT] kapcsoló_argumentum

[,[IN|OUT|IN OUT] kapcsoló_argumentum]…]

[{RETURNING|RETURN} INTO kapcsoló_argumentum

[,kapcsoló_argumentum]…];

A dinamikus_sztring tartalmazza az SQL utasítást (záró pontosvessző nélkül) vagy a PL/SQL blokkot. A változó egy olyan változó, amely egy leválogatott oszlopot, a rekord pedig egy olyan rekord, amely egy teljes sort tárol. Az INTO utasításrész csak akkor használható, ha a dinamikus_sztring olyan SELECT utasítást tartalmaz, amely egyetlen sort ad vissza. A szolgáltatott sor minden oszlopához meg kell adni típuskompatibilis változót, vagy pedig a rekordban léteznie kell egy ilyen mezőnek.

A dinamikus_sztring az utasítás fenti formájában nem tartalmazhat több sort visszaadó kérdést. Az ilyen kérdés dinamikus végrehajtására az OPEN–FOR, FETCH, CLOSE utasításokat vagy együttes hozzárendelést használunk. Ezeket a fejezet későbbi részében tárgyaljuk.

A USING utasításrész input (IN – ez az alapértelmezés), output (OUT) vagy input-output (IN OUT) kapcsoló_argumentumokat határozhat meg. Ezek IN esetén olyan kifejezések lehetnek, amelyek értéke átkerül a helykijelölőbe. OUT és IN OUT esetén pedig ezek változók, amelyek tárolják az SQL utasítás vagy blokk által szolgáltatott értékeket. A helykijelölők és a kapcsoló argumentumok számának meg kell egyezni (kivéve ha a DML utasításnál szerepel a RETURNING utasításrész), egymáshoz rendelésük a sorrend alapján történik. A kapcsoló_argumentum típusa nem lehet speciális PL/SQL típus (például BOOLEAN). NULL érték a kapcsoló_argumentummal nem adható át, de egy NULL értékű változóval

igen.

A RETURNING INTO utasításrész olyan dinamikus DML utasítások esetén használható, amelyek RETURNING utasításrészt tartalmaznak (BULK COLLECT utasításrész nélkül). Ez az utasításrész határozza meg azokat a kapcsoló változókat, amelyek a DML utasítás által visszaadott értékeket tárolják. A DML utasítás által szolgáltatott minden értékhez meg kell adni egy típuskompatibilis kapcsoló_argumentumot. Ebben az esetben a USING utasításrészben nem lehet OUT vagy IN OUT kapcsoló_argumentum.

Az EXECUTE IMMEDIATE utasítás minden végrehajtásánál a dinamikus_sztring tartalma elemzésre, majd végrehajtásra kerül.

A következő példák a fejezet végén szereplő notesz csomagból valók, és speciálisan az EXECUTE IMMEDIATE utasítás egy-egy tulajdonságát emelik ki:

Több sort szolgáltató kérdések dinamikus feldolgozása

A dinamikus SQL használatánál is kurzorváltozót kell alkalmazni, ha több sort visszaadó SELECT utasítást akarunk kezelni (a kurzorokkal kapcsolatban lásd 8. fejezet).

A dinamikus OPEN–FOR utasítás egy kurzorváltozóhoz hozzárendel egy dinamikus lekérdezést, lefuttatja azt, meghatározza az aktív halmazt, a kurzort az első sorra állítja és nullázza a %ROWCOUNT attribútumot. Alakja:

OPEN kurzorváltozó FOR dinamikus_sztring

[USING kapcsoló_argumentum [,kapcsoló_argumentum]…];

Az utasításrészek jelentése ugyanaz, mint amit fentebb tárgyaltunk.

A betöltés és lezárás ugyanúgy történik, mint statikus esetben.

Dinamikus együttes hozzárendelés

Az együttes hozzárendelés statikus verzióját a 12. fejezetben tárgyaltuk. Nézzük, hogyan alkalmazható dinamikus esetben. Az együttes EXECUTE IMMEDIATE utasítás alakja:

EXECUTE IMMEDIATE dinamikus_sztring

[BULK COLLECT INTO változó[,változó]…]

[USING kapcsoló_argumentum[,kapcsoló_argumentum]…]

[{RETURNING|RETURN}

BULK COLLECT INTO kapcsoló_argumentum[,kapcsoló_argumentum]…];

Egy dinamikus, több sort szolgáltató lekérdezés eredménye a BULK COLLECT INTO utasításrész segítségével úgy kezelhető, hogy az oszlopok értékei egy-egy kollekcióban vagy egy megfelelő rekord elemtípusú kollekcióban kerülnek tárolásra. A több sort visszaadó dinamikus INSERT, DELETE, UPDATE esetén RETURNING BULK COLLECT INTO definiálja az outputot fogadó kollekciókat.

Az együttes FETCH alakja:

FETCH dinamikus_kurzor

BULK COLLECT INTO változó[,változó]…;

Lehetőség van arra, hogy a FORALL (lásd 12. fejezet) ciklus belsejében alkalmazzuk az EXECUTE IMMEDIATE utasítást, ha az nem tartalmaz SELECT utasítást. Ennek alakja:

FORALL index IN alsó_határ..felső_határ

EXECUTE IMMEDIATE dinamikus_sztring

USING kapcsoló_argumentum[(index)]

[,kapcsoló_argumentum[(index)]]…

[{RETURNING|RETURN} BULK COLLECT INTO

kapcsoló_argumentum[,kapcsoló_argumentum]…];

A dinamikus SQL használata – esettanulmány

A következő esettanulmány egy noteszt létrehozó és kezelő csomag, mely a PL/SQL számos eszközét felvonultatja, de talán a legfontosabb azt kiemelni, hogy a csomag az aktuális hívó jogosultságaival fut és natív dinamikus SQL-t használ. A csomag specifikációja a következő:

CREATE OR REPLACE PACKAGE notesz

AUTHID CURRENT_USER

/*

A csomag segítségével időpontokhoz és

személyekhez kötött feljegyzéseket

készíthetünk és kezelhetünk.

A csomag képes létrehozni a szükséges táblákat

natív dinamikus SQL segítségével, mivel a

csomag eljárásai a hívó jogosultságaival futnak le.

*/

AS

/************************************************/

/* A csomag által nyújtott típusok, deklarációk */

/************************************************/

-- Egy feljegyzés formátuma

TYPE t_feljegyzes IS RECORD(

idopont DATE,

szemely VARCHAR2(20),

szoveg VARCHAR2(3000),

torles_ido DATE

);

-- Egy általános REF CURSOR

TYPE t_refcursor IS REF CURSOR;

-- Egy feljegyzes rekord, a %TYPE-okhoz

v_Feljegyzes t_feljegyzes;

-- Kollekciótípusok

TYPE t_feljegyzes_lista IS TABLE OF t_feljegyzes;

TYPE t_szemely_lista IS TABLE OF v_Feljegyzes.szemely%TYPE;

TYPE t_szam_lista IS TABLE OF NUMBER;

-- A hibás argumentum esetén az alprogramok kiválthatják

-- a hibas_argumentum kivételt.

hibas_argumentum EXCEPTION;

PRAGMA EXCEPTION_INIT(hibas_argumentum, -20200);

/******************************************/

/* A csomag által nyújtott szolgáltatások */

/******************************************/

-- A használt séma nevét állíthatjuk át

PROCEDURE init_sema(p_Sema VARCHAR2 DEFAULT USER);

-- Létrehozza a táblát az aktuális sémában

PROCEDURE tabla_letrehoz;

-- Törli a táblát az aktuális sémában

PROCEDURE tabla_torol;

-- Bejegyez egy feljegyzést és visszaadja egy rekordban

FUNCTION feljegyez(

p_Idopont v_Feljegyzes.idopont%TYPE,

p_Szoveg v_Feljegyzes.szoveg%TYPE,

p_Torles_ido v_Feljegyzes.torles_ido%TYPE DEFAULT NULL,

p_Szemely v_Feljegyzes.szemely%TYPE DEFAULT USER

) RETURN t_feljegyzes;

-- Megnyitja és visszaadja az adott személy feljegyzéseit a

-- napra. Ha a szemely NULL, akkor az összes feljegyzést megadja.

FUNCTION feljegyzesek(

p_Idopont v_Feljegyzes.idopont%tyPE DEfAULT SYSDATE,

p_Szemely v_Feljegyzes.szemely%TYPE DEFAULT NULL

) RETURN t_refcursor;

-- Visszaadja egy táblában az adott személy feljegyzéseinek számát a

-- napra. Ekkor p_Nevek és p_Szamok egy-egy elemet tartalmaznak.

-- Ha a p_Szemely NULL, akkor az összes feljegyzést visszadja a napra,

-- p_Nevek és p_Szamok megegyező elemszámúak lesznek.

PROCEDURE feljegyzes_lista(

p_Nevek OUT NOCOPY t_szemely_lista,

p_Szamok OUT NOCOPY t_szam_lista,

p_Idopont v_Feljegyzes.idopont%TYPE DEFAULT SYSDATE,

p_Szemely v_Feljegyzes.szemely%TYPE DEFAULT NULL

);

-- Kilistázza az adott személy feljegyzéseit a

-- napra. Ha a szemely NULL, akkor az összes feljegyzést megadja.

PROCEDURE feljegyzesek_listaz (

p_Idopont v_Feljegyzes.idopont%TYPE DEFAULT SYSDATE,

p_Szemely v_Feljegyzes.szemely%TYPE DEFAULT NULL

);

-- Megadja az időben első feljegyzés időpontját

PROCEDURE elso_feljegyzes(

p_Datum OUT DATE

);

-- A paraméterben megadja az első olyan feljegyzés időpontját,

-- amely időpontja nagyobb, mint a p_Datum.

-- NULL-t ad vissza, ha nincs ilyen.

PROCEDURE kovetkezo_feljegyzes(

p_Datum IN OUT DATE

);

-- Megadja az időben utolsó feljegyzés időpontját

PROCEDURE utolso_feljegyzes(

p_Datum OUT DATE

);

-- A paraméterben megadja az első olyan feljegyzés időpontját,

-- amely időpontja kisebb, mint a p_Datum.

-- NULL-t ad vissza, ha nincs ilyen.

PROCEDURE elozo_feljegyzes(

p_Datum IN OUT DATE

);

-- Törli a törlési idővel megjelölt és lejárt feljegyzéseket és

-- visszaadja a törölt elemek számát.

FUNCTION torol_lejart RETURN NUMBER;

-- Törli a megadott személyek összes feljegyzéseit és visszaadja

-- az egyes ügyfelekhez a törölt elemek számát.

PROCEDURE feljegyzesek_torol (

p_Szemely_lista t_szemely_lista,

p_Szam_lista OUT NOCOPY t_szam_lista

);

END notesz;

/

show errors

A csomag törzse:

CREATE OR REPLACE PACKAGE BODY notesz

AS

/**********************/

/* Privát deklarációk */

/**********************/

-- A séma nevét tárolja

v_Sema VARCHAR2(30);

/***************************************/

/* Publikus deklarációk implementációi */

/***************************************/

-- A használt séma nevét állíthatjuk át

PROCEDURE init_sema(p_Sema VARCHAR2 DEFAULT USER) IS

BEGIN

v_Sema := p_Sema;

END init_sema;

-- Létrehozza a táblát az aktuális sémában

PROCEDURE tabla_letrehoz

IS

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE ' || v_Sema || '.notesz_feljegyzesek ('

|| 'idopont DATE NOT NULL,'

|| 'szemely VARCHAR2(20) NOT NULL,'

|| 'szoveg VARCHAR2(3000),'

|| 'torles_ido DATE'

|| ')';

END tabla_letrehoz;

-- Törli a táblát az aktuális sémában

PROCEDURE tabla_torol

IS

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE ' || v_Sema || '.notesz_feljegyzesek';

END tabla_torol;

-- Bejegyez egy feljegyzést és visszaadja egy rekordban

FUNCTION feljegyez(

p_Idopont v_Feljegyzes.idopont%TYPE,

p_Szoveg v_Feljegyzes.szoveg%TYPE,

p_Torles_ido v_Feljegyzes.torles_ido%TYPE DEFAULT NULL,

p_Szemely v_Feljegyzes.szemely%TYPE DEFAULT USER

) RETURN t_feljegyzes

IS

rv t_feljegyzes;

BEGIN

IF p_Idopont IS NULL THEN

RAISE_APPLICATION_ERROR(-20200,

'Nem lehet NULL az időpont egy feljegyzésben');

END IF;

EXECUTE IMMEDIATE 'INSERT INTO ' || v_Sema || '.notesz_feljegyzesek '

|| 'VALUES (:1, :2, :3, :4) '

|| 'RETURNING idopont, szemely, szoveg, torles_ido '

|| 'INTO :5, :6, :7, :8 '

USING p_Idopont, p_Szemely, p_Szoveg, p_Torles_ido

RETURNING INTO rv.idopont, rv.szemely, rv.szoveg, rv.torles_ido;

/* A RETURNING helyett lehetne a USING-ot is használni:

USING ...,

OUT rv.idopont, OUT rv.szemely, OUT rv.szoveg, OUT rv.torles_ido;

Az Oracle azonban a RETURNING használatát javasolja.

*/

RETURN rv;

END feljegyez;

-- Megnyitja és visszaadja az adott személy feljegyzéseit a

-- napra. Ha a szemely NULL, akkor az összes feljegyzést megadja.

FUNCTION feljegyzesek(

p_Idopont v_Feljegyzes.idopont%tyPE DEfAULT SYSDATE,

p_Szemely v_Feljegyzes.szemely%TYPE DEFAULT NULL

) RETURN t_refcursor IS

rv t_refcursor;

BEGIN

OPEN rv FOR

'SELECT * FROM ' || v_Sema || '.notesz_feljegyzesek '

|| 'WHERE idopont BETWEEN :idopont AND :idopont + 1 '

|| 'AND (:szemely IS NULL OR szemely = :szemely) '

|| 'ORDER BY idopont'

USING TRUNC(p_Idopont), TRUNC(p_Idopont), p_Szemely, p_Szemely;

RETURN rv;

END feljegyzesek;

-- Visszaadja egy táblában az adott személy feljegyzéseinek számát a

-- napra. Ekkor p_Nevek és p_Szamok egy-egy elemet tartalmaznak.

-- Ha a p_Szemely NULL, akkor az összes feljegyzést visszadja a napra,

-- p_Nevek és p_Szamok megegyező elemszámúak lesznek.

PROCEDURE feljegyzes_lista(

p_Nevek OUT NOCOPY t_szemely_lista,

p_Szamok OUT NOCOPY t_szam_lista,

p_Idopont v_Feljegyzes.idopont%TYPE DEFAULT SYSDATE,

p_Szemely v_Feljegyzes.szemely%TYPE DEFAULT NULL

) IS

v_Ido VARCHAR2(10);

v_Szemely_pred VARCHAR2(100);

v_Datum DATE;

BEGIN

v_Ido := TO_CHAR(p_Idopont, 'YYYY-MM-DD');

v_Szemely_pred := CASE

WHEN p_Szemely IS NULL THEN ''

ELSE 'AND ''' || p_Szemely || ''' = szemely '

END;

EXECUTE IMMEDIATE

'SELECT szemely, COUNT(1) '

|| 'FROM ' || v_Sema || '.notesz_feljegyzesek '

|| 'WHERE idopont BETWEEN '

|| 'TO_DATE(''' || v_Ido || ''', ''YYYY-MM-DD'') '

|| 'AND TO_DATE(''' || v_Ido || ''', ''YYYY-MM-DD'') + 1 '

|| v_Szemely_pred

|| 'GROUP BY szemely'

BULK COLLECT INTO p_Nevek, p_Szamok;

END feljegyzes_lista;

-- Kilistázza az adott személy feljegyzéseit a

-- napra. Ha a szemely NULL, akkor az összes feljegyzést megadja.

PROCEDURE feljegyzesek_listaz(

p_Idopont v_Feljegyzes.idopont%TYPE DEFAULT SYSDATE,

p_Szemely v_Feljegyzes.szemely%TYPE DEFAULT NULL

) IS

v_Feljegyzesek_cur t_refcursor;

v_Feljegyzes t_feljegyzes;

BEGIN

v_Feljegyzesek_cur := feljegyzesek(p_Idopont, p_Szemely);

LOOP

FETCH v_Feljegyzesek_cur INTO v_Feljegyzes;

EXIT WHEN v_Feljegyzesek_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_Feljegyzesek_cur%ROWCOUNT || '. '

|| TO_CHAR(v_Feljegyzes.idopont, 'HH24:MI') || ', '

|| v_Feljegyzes.szemely || ', ' || v_Feljegyzes.szoveg || ', '

|| TO_CHAR(v_Feljegyzes.torles_ido, 'YYYY-MM-DD HH24:MI'));

END LOOP;

CLOSE v_Feljegyzesek_cur;

END feljegyzesek_listaz;

-- Megadja az időben első feljegyzés időpontját

PROCEDURE elso_feljegyzes(

p_Datum OUT DATE

) IS

BEGIN

EXECUTE IMMEDIATE 'SELECT MIN(idopont) '

|| 'FROM ' || v_Sema || '.notesz_feljegyzesek'

INTO p_Datum;

END elso_feljegyzes;

-- A paraméterben megadja az első olyan feljegyzés időpontját,

-- amely időpont nagyobb, mint a p_Datum.

-- NULL-t ad vissza, ha nincs ilyen.

PROCEDURE kovetkezo_feljegyzes(

p_Datum IN OUT DATE

) IS

BEGIN

EXECUTE IMMEDIATE 'SELECT MIN(idopont) '

|| 'FROM ' || v_Sema || '.notesz_feljegyzesek '

|| 'WHERE idopont > :idopont '

INTO p_Datum

USING p_Datum;

END kovetkezo_feljegyzes;

-- Megadja az időben utolsó feljegyzés időpontját

PROCEDURE utolso_feljegyzes(

p_Datum OUT DATE

) IS

BEGIN

EXECUTE IMMEDIATE 'SELECT MAX(idopont) '

|| 'FROM ' || v_Sema || '.notesz_feljegyzesek'

INTO p_Datum;

END utolso_feljegyzes;

-- A paraméterben megadja az első olyan feljegyzés időpontját,

-- amely időpontja kisebb, mint a p_Datum.

-- NULL-t ad vissza, ha nincs ilyen.

PROCEDURE elozo_feljegyzes(

p_Datum IN OUT DATE

) IS

BEGIN

EXECUTE IMMEDIATE 'SELECT MAX(idopont) '

|| 'FROM ' || v_Sema || '.notesz_feljegyzesek '

|| 'WHERE idopont < :idopont '

INTO p_Datum

USING p_Datum;

END elozo_feljegyzes;

-- Törli a törlési idővel megjelölt és lejárt feljegyzéseket és

-- visszaadja a törölt elemek számát.

FUNCTION torol_lejart

RETURN NUMBER IS

BEGIN

EXECUTE IMMEDIATE 'DELETE FROM ' || v_Sema || '.notesz_feljegyzesek '

|| 'WHERE torles_ido < SYSDATE';

RETURN SQL%ROWCOUNT;

END torol_lejart;

-- Törli a megadott személyek összes feljegyzéseit és visszaadja

-- az egyes ügyfelekhez a törölt elemek számát.

PROCEDURE feljegyzesek_torol(

p_Szemely_lista t_szemely_lista,

p_Szam_lista OUT NOCOPY t_szam_lista

) IS

BEGIN

FORALL i IN 1..p_Szemely_lista.COUNT

EXECUTE IMMEDIATE 'DELETE FROM ' || v_Sema || '.notesz_feljegyzesek '

|| 'WHERE szemely = :szemely'

USING p_Szemely_lista(i);

p_Szam_lista := t_szam_lista();

p_Szam_lista.EXTEND(p_Szemely_lista.COUNT);

FOR i IN 1..p_Szam_lista.COUNT LOOP

p_Szam_lista(i) := SQL%BULK_ROWCOUNT(i);

END LOOP;

END feljegyzesek_torol;

/**************************/

/* Csomag inicializációja */

/**************************/

BEGIN

init_sema;

END notesz;

/

show errors