Oracle PL/SQL Dynamický SQL kurz: Okamžité spouštění a DBMS_SQL

Od: Fiona Brownová Fiona Brownová
Hours aktualizováno

Co je dynamické SQL?

Dynamický SQL je programovací metodologie 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 SQL příkazy vytvářeny a spouštěny za běhu na základě požadavku.

Způsoby psaní dynamického SQL

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

  1. NDS – Native Dynamic SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) – Okamžité provedení

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

Syntax 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • Výše uvedená syntaxe ukazuje 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 se měl shodovat s typem proměnné příkazu select.
  • Klauzule USING je volitelná a používá se pouze v případě, že dynamický SQL obsahuje jakoukoli proměnnou vazby.

Příklad 1: V tomto příkladu se chystáme načíst data z tabulky emp pro emp_no '1001' pomocí příkazu NDS.

NDS - Okamžitě provést

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

Výstup

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

Vysvětlení kódu:

  • Řádek kódu 2-6: Deklarování proměnných.
  • Řádek kódu 8: Rámování SQL za běhu. SQL obsahuje proměnnou vazby v podmínce where ':empno'.
  • Řádek kódu 9: Spuštění zarámovaného textu SQL (což se provádí na řá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 uložení hodnot načtených z SQL dotazu (emp_name, emp_no, plat, manažer)
  • Klauzule 'USING' udává hodnoty proměnné vazby v dotazu SQL (:emp_no).
  • Řádek kódu 10-13: Zobrazení načtených hodnot.

DBMS_SQL pro dynamické SQL

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

  • OTEVŘÍT KURZOR: Dynamický SQL se spustí stejným způsobem jako a kurzor. Abychom mohli provést příkaz SQL, musíme otevřít kurzor.
  • analyzovat SQL: Dalším krokem je analýza dynamického SQL. Tento proces pouze zkontroluje syntaxi a ponechá dotaz připravený k provedení.
  • BIND VARIABLE Hodnoty: Dalším krokem je přiřazení hodnot pro proměnné vazby, pokud existují.
  • DEFINUJTE SLOUPEK: Dalším krokem je definování sloupce pomocí jejich relativních pozic v příkazu select.
  • PROVÁDĚT: Dalším krokem je provedení analyzovaného dotazu.
  • NAČÍST HODNOTY: 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 se chystáme načíst data z emp tabulky pro emp_no '1001' pomocí příkazu DBMS_SQL.

DBMS_SQL pro dynamické SQL

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 pro dynamické SQL

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

Výstup

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

Vysvětlení kódu:

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

Shrnutí

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. Viděli jsme také příklady, ve kterých je stejný scénář zpracováván způsoby NDS i DBMS_SQL, aby bylo možné provádět provádění za běhu.

Máš rád: