Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL

Što je Dynamic SQL?

Dinamičan SQL je metodologija programiranja za generiranje i izvođenje izraza u vrijeme izvođenja. Uglavnom se koristi za pisanje programa opće namjene i fleksibilnih programa gdje će se SQL naredbe kreirati i izvršavati tijekom izvođenja na temelju zahtjeva.

Načini pisanja dinamičkog SQL-a

PL/SQL nudi dva načina pisanja dinamičkog SQL-a

1. NDS – izvorni dinamički SQL
2. DBMS_SQL

NDS (Native Dynamic SQL) – Izvrši odmah

Izvorni dinamički SQL lakši je način za pisanje dinamičkog SQL-a. Koristi naredbu 'EXECUTE IMMEDIATE' za stvaranje i izvršavanje SQL-a za vrijeme izvođenja. Ali da biste koristili ovaj način, tip podataka i broj varijable koja će se koristiti u vremenu izvođenja moraju biti poznati prije. Također daje bolje performanse i manju složenost u usporedbi s DBMS_SQL.

Sintaksa

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]

• Gornja sintaksa prikazuje naredbu EXECUTE IMMEDIATE.
• Klauzula INTO nije obavezna i koristi se samo ako dinamički SQL sadrži naredbu odabira koja dohvaća vrijednosti. Tip varijable treba odgovarati tipu varijable izjave select.
• Klauzula USING je izborna i koristi se samo ako dinamički SQL sadrži bilo koju varijablu vezanja.

Primjer 1: U ovom primjeru ćemo dohvatiti podatke iz emp tablice za emp_no '1001' koristeći NDS izjavu.

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;
/

Izlaz

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

Objašnjenje koda:

• Redak koda 2-6: Deklariranje varijabli.
• Kodna linija 8: Uokvirivanje SQL-a tijekom izvođenja. SQL sadrži varijablu vezanja u uvjetu where ':empno'.
• Kodna linija 9: Izvršavanje uokvirenog SQL teksta (što se radi u liniji koda 8) pomoću NDS naredbe 'EXECUTE IMMEDIATE'
• Varijable u klauzuli 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) koriste se za čuvanje dohvaćenih vrijednosti iz SQL upita (emp_name, emp_no, salary, manager)
• Klauzula 'USING' daje vrijednosti varijabli vezanja u SQL upitu (:emp_no).
• Redak koda 10-13: Prikaz dohvaćenih vrijednosti.

DBMS_SQL za dinamički SQL

PL/SQL pruža paket DBMS_SQL koji vam omogućuje rad s dinamičkim SQL-om. Proces kreiranja i izvođenja dinamičkog SQL-a sadrži sljedeći proces.

• OTVORI POKAZOR: Dinamički SQL će se izvršiti na isti način kao a pokazivač. Dakle, da bismo izvršili SQL naredbu, moramo otvoriti kursor.
• PARSE SQL: Sljedeći korak je analiziranje dinamičkog SQL-a. Ovaj proces će samo provjeriti sintaksu i zadržati upit spreman za izvršenje.
• BIND VARIJABLA Vrijednosti: Sljedeći korak je dodjeljivanje vrijednosti za varijable vezanja ako ih ima.
• DEFINIRAJ STUPAC: Sljedeći korak je definiranje stupca pomoću njihovih relativnih položaja u naredbi odabira.
• IZVRŠITI: Sljedeći korak je izvršavanje raščlanjenog upita.
• DOHVATI VRIJEDNOSTI: Sljedeći korak je dohvaćanje izvršenih vrijednosti.
• ZATVORI POKAZOR: Nakon što su rezultati dohvaćeni, kursor bi trebao biti zatvoren.

Primjer 1: U ovom primjeru ćemo dohvatiti podatke iz emp tablice za emp_no '1001' koristeći DBMS_SQL izjavu.

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);

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:
/

Izlaz

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

Objašnjenje koda:

• Redak koda 1-9: Deklaracija varijable.
• Kodna linija 10: Uokvirivanje SQL naredbe.
• Kodna linija 11: Otvaranje kursora pomoću DBMS_SQL.OPEN_CURSOR. Vratit će ID kursora koji je otvoren.
• Kodna linija 12: Nakon što se kursor otvori, SQL se analizira.
• Kodna linija 13: Varijabla povezivanja '1001' dodjeljuje ID kursora umjesto ':empno'.
• Redak koda 14-17: Definiranje naziva stupca na temelju njihovog relativnog položaja u SQL izjavi. U našem slučaju, relativna pozicija je (1) emp_name, (2) emp_no (3) salary (4) manager. Dakle, na temelju ove pozicije definiramo ciljnu varijablu.
• Kodna linija 18: Izvršavanje upita pomoću DBMS_SQL.EXECUTE. Vraća broj obrađenih zapisa.
• Redak koda 19-33: Dohvaćanje zapisa pomoću petlje i prikazivanje istih.
• Redak koda 20: DBMS_SQL.FETCH_ROWS će dohvatiti jedan zapis iz obrađenih redaka. Može se pozivati ​​više puta za dohvaćanje svih redaka. Ako ne može dohvatiti retke, vratit će 0, čime se izlazi iz petlje.

Rezime

U ovom odjeljku raspravljali smo o dinamičkom SQL-u i načinima izvršavanja DYNAMIC SQL-a. Također smo vidjeli različite korake u izvršavanju dinamičkog SQL-a na oba načina. Također smo vidjeli primjere u kojima se istim scenarijem rukuje u NDS i DBMS_SQL načinima za izvođenje u vrijeme izvođenja.