Oracle PL/SQL-i dünaamiline SQL-i õpetus: käivitage kohe ja DBMS_SQL

Mis on dünaamiline SQL?

Dünaamiline SQL on programmeerimismetoodika lausete genereerimiseks ja käitamiseks käitusajal. Seda kasutatakse peamiselt üldotstarbeliste ja paindlike programmide kirjutamiseks, kus SQL-laused luuakse ja käivitatakse vastavalt nõudele käitusajal.

Dünaamilise SQL-i kirjutamise viisid

PL/SQL pakub dünaamilise SQL-i kirjutamiseks kahte võimalust

  1. NDS – Native Dynamic SQL
  2. DBMS_SQL

NDS (native Dynamic SQL) – käivitage kohe

Native Dynamic SQL on lihtsam viis dünaamilise SQL-i kirjutamiseks. See kasutab SQL-i loomiseks ja käivitamiseks käitamise ajal käsku „EXECUTE IMMEDIATE”. Kuid sel viisil kasutamiseks peab käitusajal kasutatava muutuja andmetüüp ja arv olema eelnevalt teada. See annab ka parema jõudluse ja vähem keerukust võrreldes DBMS_SQL-iga.

Süntaks

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • Ülaltoodud süntaks näitab käsku EXECUTE IMMEDIATE.
  • Klausel INTO on valikuline ja seda kasutatakse ainult siis, kui dünaamiline SQL sisaldab valikulauset, mis toob väärtusi. Muutuja tüüp peaks ühtima valikulause muutuja tüübiga.
  • Klausel USING on valikuline ja seda kasutatakse ainult siis, kui dünaamiline SQL sisaldab mis tahes sidumismuutujat.

Näiteks 1: Selles näites toome emp tabelist andmed emp_no '1001' jaoks, kasutades NDS-lauset.

NDS – Käivitage kohe

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äljund

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

Koodi selgitus:

  • Koodirida 2-6: Muutujate deklareerimine.
  • Koodirida 8: SQL-i raamimine käitusajal. SQL sisaldab sidumismuutujat, kus tingimus ':empno'.
  • Koodirida 9: raamitud SQL-teksti täitmine (mida tehakse koodireal 8), kasutades NDS-i käsku 'EXECUTE IMMEDIATE'
  • 'INTO' klauslis olevaid muutujaid (lv_emp_name, ln_emp_no, ln_salary, ln_manager) kasutatakse SQL-päringust hangitud väärtuste hoidmiseks (emp_name, emp_no, salary, manager)
  • Klausel 'USING' annab väärtused SQL-päringu sidumismuutujale (:emp_no).
  • Koodirida 10-13: toodud väärtuste kuvamine.

DBMS_SQL dünaamilise SQL-i jaoks

PL/SQL pakub paketti DBMS_SQL, mis võimaldab teil töötada dünaamilise SQL-iga. Dünaamilise SQL-i loomise ja täitmise protsess sisaldab järgmist protsessi.

  • AVA KURSOR: dünaamiline SQL käivitatakse samamoodi nagu a kursor. Nii et SQL-lause täitmiseks peame avama kursori.
  • PARSE SQL-i: Järgmine samm on dünaamilise SQL-i sõelumine. See protsess kontrollib lihtsalt süntaksit ja hoiab päringu täitmiseks valmis.
  • BIND VARIABLE Väärtused: Järgmine samm on sidumismuutujate väärtuste määramine, kui need on olemas.
  • MÄÄRATLEMINE VEERG: Järgmine samm on veeru määratlemine, kasutades nende suhtelisi positsioone valikulauses.
  • TÄITMINE: Järgmine samm on sõelutud päringu täitmine.
  • TOO VÄÄRTUSED: Järgmine samm on käivitatud väärtuste toomine.
  • SULGE KURSOR: pärast tulemuste toomist tuleb kursor sulgeda.

Näiteks 1: Selles näites toome emp tabelist andmed emp_no '1001' jaoks, kasutades DBMS_SQL-lauset.

DBMS_SQL dünaamilise SQL-i jaoks

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 dünaamilise SQL-i jaoks

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äljund

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

Koodi selgitus:

  • Koodirida 1-9: Muutuja deklaratsioon.
  • Koodirida 10: SQL-lause raamimine.
  • Koodirida 11: kursori avamine DBMS_SQL.OPEN_CURSOR abil. See tagastab avatud kursori ID.
  • Koodirida 12: Pärast kursori avamist sõelutakse SQL.
  • Koodirida 13: sidumismuutuja '1001' määrab kursori ID asemel ':empno'.
  • Koodirida 14-17: veeru nime määramine nende suhtelise positsiooni alusel SQL-lauses. Meie puhul on suhteline ametikoht (1) emp_name, (2) emp_no (3) palk (4) juhataja. Selle positsiooni põhjal määratleme sihtmuutuja.
  • Koodirida 18: Päringu täitmine DBMS_SQL.EXECUTE abil. See tagastab töödeldud kirjete arvu.
  • Koodirida 19-33: kirjete toomine tsükli abil ja sama kuvamine.
  • Koodirida 20: DBMS_SQL.FETCH_ROWS toob töödeldud ridadest ühe kirje. Seda saab korduvalt kutsuda, et tuua kõik read. Kui see ei saa ridu tuua, tagastab see 0, väljudes seega tsüklist.

kokkuvõte

Selles jaotises oleme arutanud dünaamilist SQL-i ja DÜNAAMILINE SQL-i käivitamise viise. Samuti oleme näinud erinevaid samme dünaamilise SQL-i täitmisel mõlemal viisil. Oleme näinud ka näiteid, kus sama stsenaariumi käsitletakse nii NDS-i kui ka DBMS_SQL-i viisidel, et täita käitusajal.