Oracle PL/SQL dinamikus SQL oktatóanyag: Azonnali és DBMS_SQL végrehajtása

By: Fiona Brown Fiona Brown
Hours korszerűsített

Mi az a dinamikus SQL?

Dinamikus SQL egy programozási módszertan utasítások generálására és futtatására futás közben. Főleg általános célú és rugalmas programok írására szolgál, ahol az SQL utasítások létrejönnek és futás közben végrehajtódnak a követelménynek megfelelően.

A dinamikus SQL írásának módjai

A PL/SQL két módot kínál a dinamikus SQL írására

  1. NDS – Natív dinamikus SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) – Azonnali végrehajtás

A natív dinamikus SQL a dinamikus SQL írásának egyszerűbb módja. Az 'EXECUTE IMMEDIATE' parancsot használja az SQL létrehozásához és végrehajtásához futás közben. Ennek használatához azonban korábban ismerni kell az adattípust és a futási időben használandó változó számát. A DBMS_SQL-hez képest jobb teljesítményt és kisebb bonyolultságot biztosít.

Szintaxis 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • A fenti szintaxis az EXECUTE IMMEDIATE parancsot mutatja.
  • Az INTO záradék nem kötelező, és csak akkor használatos, ha a dinamikus SQL értékeket lekérő select utasítást tartalmaz. A változó típusának meg kell egyeznie a select utasítás változótípusával.
  • A USING záradék nem kötelező, és csak akkor használatos, ha a dinamikus SQL bármilyen kötési változót tartalmaz.

Példa 1: Ebben a példában az emp_no '1001' emp táblából fogjuk lekérni az adatokat az NDS utasítás használatával.

NDS – Azonnali végrehajtás

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_manager
USING 1001;
Dbms_output.put_line('Employee Name:‘||lv_emp_name);
Dbms_output.put_line('Employee Number:‘||ln_emp_no);
Dbms_output.put_line(‘Salary:'||ln_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

teljesítmény

Employee Name : XXX 
Employee Number: 1001 
Salary: 15000 
Manager ED: 1000

Kód magyarázata:

  • Kódsor 2-6: Változók deklarálása.
  • 8. kódsor: Az SQL keretezése futásidőben. Az SQL tartalmazza a kötési változót, ahol a feltétel ':empno'.
  • 9. kódsor: A bekeretezett SQL szöveg végrehajtása (ami a 8-as kódsorban történik) az 'EXECUTE IMMEDIATE' NDS paranccsal
  • Az 'INTO' záradék változói (lv_emp_name, ln_emp_no, ln_salary, ln_manager) az SQL-lekérdezésből lekért értékek tárolására szolgálnak (emp_name, emp_no, fizetés, menedzser)
  • A 'USING' záradék megadja az SQL lekérdezés bind változójának értékeit (:emp_no).
  • Kódsor 10-13: A lekért értékek megjelenítése.

DBMS_SQL dinamikus SQL-hez

A PL/SQL biztosítja a DBMS_SQL csomagot, amely lehetővé teszi a dinamikus SQL-lel való munkát. A dinamikus SQL létrehozásának és végrehajtásának folyamata a következő folyamatot tartalmazza.

  • KURSZOR NYITÁSA: A dinamikus SQL ugyanúgy fut le, mint a kurzor. Tehát az SQL utasítás végrehajtásához meg kell nyitnunk a kurzort.
  • SQL ELEMZÉS: A következő lépés a dinamikus SQL elemzése. Ez a folyamat csak a szintaxist ellenőrzi, és készen tartja a lekérdezést a végrehajtásra.
  • BIND VARIABLE Értékek: A következő lépés a kötési változók értékeinek hozzárendelése, ha vannak ilyenek.
  • OSZLOP MEGHATÁROZÁSA: A következő lépés az oszlop meghatározása a relatív pozíciójuk alapján a select utasításban.
  • VÉGREHAJT: A következő lépés az elemzett lekérdezés végrehajtása.
  • ÉRTÉKEK KERESÉSE: A következő lépés a végrehajtott értékek lekérése.
  • KURSZOR ZÁRÁSA: Az eredmények lekérése után a kurzort be kell zárni.

Példa 1: Ebben a példában az emp_no '1001' emp táblából fogjuk lekérni az adatokat a DBMS_SQL utasítás használatával.

DBMS_SQL dinamikus SQL-hez

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50);
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
ln_cursor_id NUMBER;
ln_rows_processed;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,2,ln_emp_no);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,3,ln_salary);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,4,ln_manager);

ln_rows__processed:=DBMS_SQL.EXECUTE(ln_cursor_id);

DBMS_SQL dinamikus SQL-hez

LOOP
IF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0
THEN
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,1,lv_emp_name); 
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,2,ln_emp_no);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,3,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||ln_emp_no); 
Dbms_output.put_line(‘Salary:‘||ln_salary); 
Dbms_output.put_line('Manager ID :‘| ln_manager);
END IF;
END LOOP;

DBMS_SQL.CLOSE_ClIRSOR(ln_cursor_id);

END:
/

teljesítmény

Employee Name:XXX 
Employee Number:1001 
Salary:15000 
Manager ID:1000

Kód magyarázata:

  • Kódsor 1-9: Változó deklaráció.
  • 10. kódsor: Az SQL utasítás keretezése.
  • 11. kódsor: A kurzor megnyitása a DBMS_SQL.OPEN_CURSOR használatával. Visszaadja a megnyitott kurzorazonosítót.
  • 12. kódsor: A kurzor megnyitása után az SQL elemzésre kerül.
  • 13. kódsor: Az '1001' kötési változó a kurzorazonosítóhoz rendeli a ':empno' helyett.
  • Kódsor 14-17: Az oszlopnév meghatározása az SQL utasításban elfoglalt relatív pozíciójuk alapján. Esetünkben a relatív pozíció (1) emp_name, (2) emp_no (3) fizetés (4) menedzser. Tehát ezen pozíció alapján definiáljuk a célváltozót.
  • 18. kódsor: A lekérdezés végrehajtása a DBMS_SQL.EXECUTE használatával. A feldolgozott rekordok számát adja vissza.
  • Kódsor 19-33: A rekordok lekérése hurok segítségével és ugyanazon megjelenítése.
  • 20. kódsor: A DBMS_SQL.FETCH_ROWS egy rekordot kér le a feldolgozott sorokból. Az összes sor lekéréséhez többször is meghívható. Ha nem tudja lekérni a sorokat, akkor 0-t ad vissza, így kilép a ciklusból.

Összegzésként

Ebben a részben a dinamikus SQL-t és a DINAMIKUS SQL végrehajtásának módjait tárgyaltuk. Láttuk a dinamikus SQL végrehajtásának különböző lépéseit is mindkét módon. Láttunk olyan példákat is, amelyekben ugyanazt a forgatókönyvet az NDS és a DBMS_SQL is kezeli a végrehajtás futás közbeni végrehajtására.

Talán tetszene: