Výukový program pre Oracle PL / SQL Dynamic SQL: Okamžité vykonanie & DBMS_SQL

Obsah:

Anonim

Čo je to Dynamic SQL?

Dynamic SQL je programátorská metodika na generovanie a spúšťanie príkazov za behu. Používa sa hlavne na písanie univerzálnych a flexibilných programov, kde budú príkazy SQL vytvárané a vykonávané za behu na základe požiadavky.

V tomto návode sa naučíte

  • Spôsoby zápisu dynamického SQL
  • NDS (natívne dynamické SQL) - okamžité vykonanie
  • DBMS_SQL pre dynamické SQL

Spôsoby zápisu dynamického SQL

PL / SQL poskytuje dva spôsoby zápisu dynamického SQL

  1. NDS - natívne dynamické SQL
  2. DBMS_SQL

NDS (natívne dynamické SQL) - okamžité vykonanie

Natívna dynamická SQL je ľahší spôsob zápisu dynamickej SQL. Používa príkaz „EXECUTE IMMEDIATE“ na vytvorenie a vykonanie SQL za behu. Aby sme to však mohli použiť, je potrebné vopred poznať dátový typ a počet premenných, ktoré sa majú použiť za behu. Poskytuje tiež lepší výkon a menšiu zložitosť v porovnaní s databázou DBMS_SQL.

Syntax 

EXECUTE IMMEDIATE()[INTO][USING ]
  • Vyššie uvedená syntax zobrazuje príkaz EXECUTE IMMEDIATE.
  • Klauzula INTO je voliteľná a používa sa, iba ak dynamický SQL obsahuje príkaz select, ktorý načítava hodnoty. Typ premennej by sa mal zhodovať s typom premennej príkazu select.
  • Klauzula USING je voliteľná a použije sa, iba ak dynamický SQL obsahuje ľubovoľnú premennú väzby.

Príklad 1 : V tomto príklade ideme načítať údaje z tabuľky emp pre emp_no '1001' pomocou príkazu NDS.

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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;/

Výkon

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

Vysvetlenie kódu:

  • Riadok kódu 2-6 : Deklarovanie premenných.
  • Riadok kódu 8 : Rámcovanie SQL za behu. SQL obsahuje premennú väzby v podmienke ': empno'.
  • Riadok kódu 9 : Spustenie zarámovaného textu SQL (ktorý sa vykonáva v riadku kódu 8) pomocou príkazu NDS „EXECUTE IMMEDIATE“
  • Premenné v klauzule 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) sa používajú na uchovanie načítaných hodnôt z dotazu SQL (emp_name, emp_no, plat, manažér)
  • Klauzula „USING“ poskytuje hodnoty premennej väzby v dotaze SQL (: emp_no).
  • Riadok kódu 10-13 : Zobrazenie načítaných hodnôt.

DBMS_SQL pre dynamické SQL

PL / SQL poskytujú balík DBMS_SQL, ktorý vám umožní pracovať s dynamickým SQL. Proces vytvárania a vykonávania dynamického SQL obsahuje nasledujúci proces.

  • OPEN CURSOR : Dynamický SQL sa bude vykonávať rovnakým spôsobom ako kurzor. Aby sme teda mohli vykonať príkaz SQL, musíme otvoriť kurzor.
  • PARSE SQL : Ďalším krokom je analýza dynamického SQL. Tento proces iba skontroluje syntax a udrží dotaz pripravený na vykonanie.
  • ZÁVÄZNÉ VARIABILNÉ hodnoty : Ďalším krokom je priradenie hodnôt pre premenné väzby, ak existujú.
  • DEFINE COLUMN : Ďalším krokom je definovanie stĺpca pomocou ich relatívnych polôh vo výkaze select.
  • EXECUTE : Ďalším krokom je vykonanie analyzovaného dotazu.
  • FETCH VALUES : Ďalším krokom je načítanie vykonaných hodnôt.
  • CLOSE CURSOR : Po načítaní výsledkov by mal byť kurzor zatvorený.

Príklad 1 : V tomto príklade budeme načítať údaje z tabuľky emp pre emp_no '1001' pomocou príkazu DBMS_SQL.

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:/

Výkon

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

Vysvetlenie kódu:

  • Riadok kódu 1-9 : Deklarácia premennej.
  • Riadok kódu 10 : Zarámovanie príkazu SQL.
  • Riadok kódu 11 : Otvorenie kurzora pomocou DBMS_SQL.OPEN_CURSOR. Vráti ID kurzora, ktorý je otvorený.
  • Riadok kódu 12 : Po otvorení kurzora sa analyzuje SQL.
  • Riadok kódu 13 : Viazaná premenná '1001' je priradená k ID kurzora namiesto ': empno'.
  • Riadok kódu 14-17 : Definovanie názvu stĺpca na základe ich relatívnej polohy v príkaze SQL. V našom prípade je relatívna pozícia (1) emp_name, (2) emp_no (3) plat (4) manažér. Takže na základe tejto pozície definujeme cieľovú premennú.
  • Riadok kódu 18 : Vykonanie dotazu pomocou DBMS_SQL.EXECUTE. Vráti počet spracovaných záznamov.
  • Riadok kódu 19-33 : Načítanie záznamov pomocou slučky a ich zobrazenie.
  • Riadok kódu 20: DBMS_SQL.FETCH_ROWS načíta jeden záznam zo spracovaných riadkov. Môže sa vyvolať opakovane, aby sa načítali všetky riadky. Ak nemôže načítať riadky, vráti hodnotu 0, čím opustí slučku.

Zhrnutie

V tejto časti sme diskutovali o dynamickom SQL a spôsoboch vykonávania DYNAMICKÉHO SQL. Videli sme tiež rôzne kroky pri vykonávaní dynamického SQL oboma spôsobmi. Videli sme tiež príklady, v ktorých sa rovnaký scenár spracováva v NDS aj DBMS_SQL spôsoboch vykonávania vykonania za behu.