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

Obsah:

Anonim

Co je to Dynamic SQL?

Dynamic SQL je programovací metodika pro generování a spouštění příkazů za běhu. Používá se hlavně k psaní univerzálních a flexibilních programů, kde budou příkazy SQL vytvářeny a spouštěny za běhu na základě požadavku.

V tomto výukovém programu se naučíte

  • Způsoby psaní dynamického SQL
  • NDS (Native Dynamic SQL) - okamžité provedení
  • DBMS_SQL pro Dynamic SQL

Způsoby psaní dynamického SQL

PL / SQL poskytuje dva způsoby zápisu dynamického SQL

  1. NDS - Nativní dynamický SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) - okamžité provedení

Nativní dynamický SQL je snadnější způsob psaní dynamického SQL. Používá příkaz 'EXECUTE IMMEDIATE' k vytvoření a spuštění SQL za běhu. Chcete-li však použít tento způsob, je třeba znát datový typ a počet proměnných, které mají být použity v době běhu. Poskytuje také lepší výkon a menší složitost ve srovnání s DBMS_SQL.

Syntax 

EXECUTE IMMEDIATE()[INTO][USING ]
  • Výše uvedená syntaxe zobrazuje příkaz EXECUTE IMMEDIATE.
  • Klauzule INTO je volitelná a používá se pouze v případě, že dynamický SQL obsahuje příkaz select, který načítá hodnoty. Typ proměnné by měl odpovídat typu proměnné příkazu select.
  • Klauzule USING je volitelná a používá se pouze v případě, že dynamický SQL obsahuje libovolnou proměnnou vazby.

Příklad 1 : V tomto příkladu načteme data z emp tabulky pro emp_no '1001' pomocí pří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ýstup

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

Vysvětlení kódu:

  • Řádek kódu 2-6 : Deklarace proměnných.
  • Řádek kódu 8 : Zarámování SQL za běhu. SQL obsahuje proměnnou vazby, kde podmínka ': empno'.
  • Řádek kódu 9 : Spuštění zarámovaného textu SQL (který se provádí v řádku kódu 8) pomocí příkazu NDS „EXECUTE IMMEDIATE“
  • Proměnné v klauzuli 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) se používají k zadržení načtených hodnot z dotazu SQL (emp_name, emp_no, plat, manažer)
  • Klauzule 'USING' dává hodnoty proměnné vazby v dotazu SQL (: emp_no).
  • Řádek kódu 10-13 : Zobrazení načtených hodnot.

DBMS_SQL pro Dynamic SQL

PL / SQL poskytují balíček DBMS_SQL, který vám umožní pracovat s dynamickým SQL. Proces vytváření a provádění dynamického SQL obsahuje následující proces.

  • OTEVŘENÝ KURZOR : Dynamický SQL bude spuštěn stejným způsobem jako kurzor. Abychom mohli provést příkaz SQL, musíme otevřít kurzor.
  • PARSE SQL : Dalším krokem je analýza dynamického SQL. Tento proces pouze zkontroluje syntaxi a udrží dotaz připravený k provedení.
  • BIND VARIABLE Values : Dalším krokem je přiřazení hodnot pro vázané proměnné, pokud existují.
  • DEFINE COLUMN : Dalším krokem je definice sloupce pomocí jejich relativních pozic v příkazu select.
  • PROVÉST : Dalším krokem je provedení analyzovaného dotazu.
  • FETCH VALUES : Dalším krokem je načtení provedených hodnot.
  • ZAVŘÍT KURZOR : Po načtení výsledků by měl být kurzor zavřen.

Příklad 1 : V tomto příkladu načteme data z emp tabulky pro emp_no '1001' pomocí pří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ýstup

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

Vysvětlení kódu:

  • Řádek kódu 1-9 : Deklarace proměnné.
  • Řádek kódu 10 : Zarámování příkazu SQL.
  • Řádek kódu 11 : Otevření kurzoru pomocí DBMS_SQL.OPEN_CURSOR. Vrátí otevřené ID kurzoru.
  • Řádek kódu 12 : Po otevření kurzoru je analyzován SQL.
  • Řádek kódu 13 : Vázaná proměnná '1001' je přiřazena ID kurzoru místo ': empno'.
  • Řádek kódu 14-17 : Definování názvu sloupce na základě jejich relativní polohy v příkazu SQL. V našem případě je relativní pozice (1) emp_name, (2) emp_no (3) plat (4) manažer. Takže na základě této pozice definujeme cílovou proměnnou.
  • Řádek kódu 18 : Provedení dotazu pomocí DBMS_SQL.EXECUTE. Vrátí počet zpracovaných záznamů.
  • Řádek kódu 19-33 : Načítání záznamů pomocí smyčky a jejich zobrazování.
  • Řádek kódu 20: DBMS_SQL.FETCH_ROWS načte jeden záznam ze zpracovaných řádků. Lze jej volat opakovaně, aby načetl všechny řádky. Pokud nemůže načíst řádky, vrátí 0, čímž ukončí smyčku.

souhrn

V této části jsme diskutovali o dynamickém SQL a způsobech provádění DYNAMICKÉHO SQL. Také jsme viděli různé kroky při provádění dynamického SQL oběma způsoby. Také jsme viděli příklady, ve kterých je stejný scénář zpracován jak v NDS, tak v DBMS_SQL způsobech provádění provádění za běhu.