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äivitamiseks käitusajal. Seda kasutatakse peamiselt üldotstarbeliste ja paindlike programmide kirjutamiseks, kus SQL-laused luuakse ja käivitatakse käitusajal vastavalt vajadusele, näiteks kui tabeli nimed, veergude loendid või WHERE-tingimused pole enne programmi käivitamist teada.

Dünaamilise SQL-i kirjutamise viisid

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

1. NDS – Native Dynamic SQL (laused EXECUTE IMMEDIATE ja OPEN-FOR)
2. DBMS_SQL (kaasasolev pakett)

Üldreegel on lihtne: kui sisend- ja väljundmuutujate arv ja andmetüübid on kompileerimise ajal teada, kasutage Native Dynamic SQL-i, kuna see on kiirem ja nõuab vähem koodi. Kui see teave on teada ainult käitusajal, kasutage DBMS_SQL-i paketti.

NDS (native Dynamic SQL) – käivitage kohe

Natiivne dünaamiline SQL on dünaamilise SQL-i kirjutamise lihtsam viis. See kasutab SQL-i loomiseks ja käivitamiseks käitusajal käsku EXECUTE IMMEDIATE. Selle lähenemisviisi kasutamiseks peavad käitusajal kasutatavate muutujate andmetüüp ja arv olema eelnevalt teada. See annab ka parema jõudluse ja väiksema keerukuse võrreldes DBMS_SQL-iga.

Süntaks

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

• dünaamiline_sql_string: Stringavaldis (VARCHAR2 või CHAR, mitte NVARCHAR2/NCHAR), mis sisaldab ühte SQL-lauset või PL/SQL-plokki.
• INTO-klausel: Valikuline. Kasutatakse ainult siis, kui dünaamiline SQL on üherealine SELECT; see jäädvustab tagastatud väärtused muutujatesse või kirjesse. Iga valitud veerg vajab tüübiga ühilduvat muutujat.
• KASUTUSklausel: Valikuline. Annab siduvad muutujad. Vaikimisi režiim on IN; OUT ja IN OUT kasutatakse väärtuste vastuvõtmiseks.
• NAASUMISE klausel: Kasutatakse DML-lausetega, mis sisaldavad RETURNING-klauslit, et jäädvustada mõjutatud rea väärtused sidumisargumentidesse.

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

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

Väljund

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

Code Selgitus:

• Read 2–6: Muutujate deklareerimine.
• Rida 8: SQL-i raamimine käitusajal. SQL sisaldab WHERE-tingimuses sidumismuutujat ':empno'.
• Read 9–11: Raamitud SQL-i käivitamine käsuga EXECUTE IMMEDIATE. INTO-klausli muutujad hoiavad allalaaditud väärtusi ja USING-klausel annab väärtuse sidumismuutujale :empno.
• Read 12–15: Kuvatakse hangitud väärtusi.

Dünaamilise SQL-i kasutamine DDL-i jaoks

Staatiline PL/SQL ei saa otse käivitada DDL-käske, näiteks CREATE, ALTER või DROP. EXECUTE IMMEDIATE lahendab selle probleemi, luues lause stringina, mis on mugav ka siis, kui objekti nimi antakse käitusajal:

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

Objektinimesid (tabel, veerg, skeem) ei saa sidumismuutujatena edastada, seega tuleb need stringiks liita. SQL-süstimise vältimiseks valideerige selline sisend alati, näiteks DBMS_ASSERT.SIMPLE_SQL_NAME abil.

DBMS_SQL dünaamilise SQL-i jaoks

PL/SQL pakub DBMS_SQL paketti dünaamilise SQL-iga töötamiseks, kui lause struktuur pole enne käitusaega teada. Dünaamilise SQL-i loomise ja käivitamise protsess hõlmab järgmisi samme:

• AVA KURSORI: Dünaamiline SQL käivitub nagu kursorSQL-lause täitmiseks peame kõigepealt kursori avama.
• PARSEERI SQL: Parsi dünaamilist SQL-i. See kontrollib süntaksit ja hoiab päringu käivitamiseks valmis.
• BIND MUUTUJA Väärtused: Määrake sidumismuutujate väärtused, kui neid on.
• MÄÄRA VEERG: Defineeri iga veerg selle suhtelise positsiooni abil valikulauses.
• TÄITMINE: Käivita parsitud päring.
• VÄÄRTUSTE TOOMINE: Tooge täidetud väärtused.
• SULGE KURSORI: Kui tulemused on laekunud, sulgege kursor.

Näide 1: Selles näites toome andmed emp_no '1001' jaoks emp-tabelist, kasutades DBMS_SQL-lauset. EXCEPTION-plokk sulgeb kursori isegi vea ilmnemisel.

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

Väljund

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

Code Selgitus:

• Read 1–8: Muutuja deklaratsioon.
• Rida 10: SQL-lause raamimine.
• Rida 11: Kursori avamine DBMS_SQL.OPEN_CURSOR abil, mis tagastab avatud kursori ID.
• Rida 12: Pärast kursori avamist parsitakse SQL.
• Rida 13: ':empno' asemele määratakse sidumisväärtus '1001'.
• Read 14–17: Veergude defineerimine nende suhtelise positsiooni järgi: (1) töötaja_nimi, (2) töötaja_number, (3) palk, (4) juht.
• Rida 18: Päringu täitmine funktsiooniga DBMS_SQL.EXECUTE, mis tagastab töödeldud kirjete arvu.
• Read 19–32: Kirjete toomine tsüklis. FETCH_ROWS tagastab 0, kui ridu pole enam alles, mis väljub tsüklist.
• ERANDITE plokk: Tagab kursori sulgemise, et avatud kursorid vea tekkimisel ei lekiks.

NDS vs DBMS_SQL: millal millist kasutada

Mõlemad lähenemisviisid käitavad SQL-i käitusajal, kuid sobivad erinevatele olukordadele:

• Kasuta natiivset dünaamilist SQL-i (TÄIDA KOHE / AVA FOR) kui sisendite ja väljundite arv ja andmetüübid on kompileerimise ajal teada. See on kiirem, hõlpsamini loetav ja nõuab vähem koodi.
• Kasuta DBMS_SQL-i kui struktuur on käitusajani tundmatu, näiteks päringu puhul, mille valitud veergude või sidumismuutujate arv varieerub, mida tuntakse meetodi 4 dünaamilise SQL-ina või lause puhul, mis on liiga suur, et mahtuda ühte 32K VARCHAR2 muutujasse.

KKK