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

Što je Dynamic SQL?

Dinamičan SQL je programska metodologija za generiranje i izvršavanje naredbi za vrijeme izvođenja. Uglavnom se koristi za pisanje programa opće namjene i fleksibilnih programa gdje se SQL naredbe kreiraju i izvršavaju za vrijeme izvođenja na temelju zahtjeva, na primjer kada nazivi tablica, popisi stupaca ili WHERE uvjeti nisu poznati dok se program ne pokrene.

Načini pisanja dinamičkog SQL-a

PL/SQL nudi dva načina za pisanje dinamičkog SQL-a:

1. NDS – izvorni dinamički SQL (naredbe EXECUTE IMMEDIATE i OPEN-FOR)
2. DBMS_SQL (isporučeni paket)

Opće pravilo je jednostavno: ako su broj i tipovi podataka ulaznih i izlaznih varijabli poznati u vrijeme kompajliranja, koristite Native Dynamic SQL jer je brži i zahtijeva manje koda. Kada su te informacije poznate tek u vrijeme izvođenja, koristite DBMS_SQL paket.

NDS (Native Dynamic SQL) – Izvrši odmah

Izvorni dinamički SQL je jednostavniji način pisanja dinamičkog SQL-a. Koristi naredbu EXECUTE IMMEDIATE za stvaranje i izvršavanje SQL-a tijekom izvođenja. Da biste koristili ovaj pristup, tip podataka i broj varijabli korištenih tijekom izvođenja moraju biti unaprijed poznati. Također pruža bolje performanse i manju složenost u usporedbi s DBMS_SQL-om.

Sintaksa

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• dinamički_sql_niz: Nizovni izraz (VARCHAR2 ili CHAR, ne NVARCHAR2/NCHAR) koji sadrži jednu SQL naredbu ili PL/SQL blok.
• INTO klauzula: Neobavezno. Koristi se samo kada je dinamički SQL SELECT s jednim redom; vraćane vrijednosti sprema u varijable ili zapis. Svaki odabrani stupac treba varijablu kompatibilnu s tipom.
• UPORABA klauzule: Neobavezno. Navodi varijable vezanja. Zadani način je IN; OUT i IN OUT se koriste za primanje vrijednosti natrag.
• RETURNING INTO klauzula: Koristi se s DML naredbama koje sadrže klauzulu RETURNING za hvatanje vrijednosti pogođenih redaka u argumente povezivanja.

Primjer 1: U ovom primjeru, dohvaćamo podatke iz emp tablice za emp_no '1001' pomoću NDS naredbe s varijablom vezanja.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   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_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

Izlaz

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

Code Objašnjenje:

• Redci 2-6: Deklariranje varijabli.
• Redak 8: Uokviravanje SQL-a za vrijeme izvođenja. SQL sadrži varijablu vezanja ':empno' u uvjetu WHERE.
• Redci 9-11: Izvršavanje uokvirenog SQL-a s EXECUTE IMMEDIATE. Varijable INTO klauzule sadrže dohvaćene vrijednosti, a USING klauzula daje vrijednost za varijablu vezanja :empno.
• Redci 12-15: Prikaz dohvaćenih vrijednosti.

Korištenje dinamičkog SQL-a za DDL

Statički PL/SQL ne može izravno pokrenuti DDL naredbe poput CREATE, ALTER ili DROP. EXECUTE IMMEDIATE rješava ovaj problem tako što naredbu gradi kao niz znakova, što je također korisno kada se naziv objekta navede za vrijeme izvođenja:

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

Imena objekata (tablica, stupac, shema) ne mogu se prenijeti kao varijable vezanja, stoga se moraju spojiti u niz. Uvijek provjerite takav unos, na primjer s DBMS_ASSERT.SIMPLE_SQL_NAME, kako biste izbjegli SQL injekciju.

DBMS_SQL za dinamički SQL

PL/SQL pruža DBMS_SQL paket za rad s dinamičkim SQL-om kada struktura naredbe nije poznata do izvođenja. Proces stvaranja i izvršavanja dinamičkog SQL-a uključuje sljedeće korake:

• OTVORI KURSOR: Dinamički SQL se izvršava kao pokazivačZa izvršavanje SQL naredbe prvo moramo otvoriti kursor.
• PARSIRANJE SQL-a: Analiziraj dinamički SQL. To provjerava sintaksu i održava upit spremnim za izvršenje.
• Vrijednosti POVEZIVANJA VARIJABLE: Dodijelite vrijednosti za varijable vezanja, ako ih ima.
• DEFINIRAJ STUPAC: Definirajte svaki stupac koristeći njegov relativni položaj u naredbi select.
• IZVRŠITI: Izvršite parsirani upit.
• DOHVATI VRIJEDNOSTI: Dohvati izvršene vrijednosti.
• ZATVORI KURSOR: Nakon što se dobiju rezultati, zatvorite kursor.

Primjer 1: U ovom primjeru, dohvaćamo podatke iz emp tablice za emp_no '1001' pomoću DBMS_SQL naredbe. Blok EXCEPTION zatvara kursor čak i ako se dogodi greška.

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 NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   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, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || 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_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

Izlaz

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

Code Objašnjenje:

• Redci 1-8: Deklaracija varijable.
• Redak 10: Uokviravanje SQL naredbe.
• Redak 11: Otvaranje kursora pomoću DBMS_SQL.OPEN_CURSOR, koji vraća ID otvorenog kursora.
• Redak 12: Nakon što se kursor otvori, SQL se parsira.
• Redak 13: Vrijednost vezanja '1001' dodjeljuje se umjesto ':empno'.
• Redci 14-17: Definiranje stupaca prema njihovom relativnom položaju: (1) naziv_zaposlenika, (2) broj_zaposlenika, (3) plaća, (4) voditelj.
• Redak 18: Izvršavanje upita s DBMS_SQL.EXECUTE, koji vraća broj obrađenih zapisa.
• Redci 19-32: Dohvaćanje zapisa u petlji. FETCH_ROWS vraća 0 kada ne ostane nijedan redak, što dovodi do izlaska iz petlje.
• Blok IZUZETKA: Osigurava da je kursor zatvoren kako otvoreni kursori ne bi propuštali podatke ako se pojavi greška.

NDS vs. DBMS_SQL: Kada koji koristiti

Oba pristupa pokreću SQL za vrijeme izvođenja, ali odgovaraju različitim situacijama:

• Koristi izvorni dinamički SQL (IZVRŠI ODMAH / OTVORI ZA) kada su broj i tipovi podataka ulaza i izlaza poznati u vrijeme kompajliranja. Brži je, lakši za čitanje i potrebno je manje koda.
• Koristi DBMS_SQL kada je struktura nepoznata do vremena izvođenja, na primjer upit čiji se broj odabranih stupaca ili vezanih varijabli mijenja, poznat kao dinamički SQL metode 4, ili naredba prevelika da bi stala u jednu varijablu VARCHAR2 od 32K.

Pitanja i odgovori