Oracle PL/SQL Dynamic SQL Tutorial: Suorita välitön & DBMS_SQL

Mikä on dynaaminen SQL?

Dynaaminen SQL on ohjelmointimenetelmä lausekkeiden luomiseen ja suorittamiseen ajonaikana. Sitä käytetään pääasiassa yleiskäyttöisten ja joustavien ohjelmien kirjoittamiseen, joissa SQL-lausekkeet luodaan ja suoritetaan ajonaikana vaatimusten perusteella, esimerkiksi silloin, kun taulukoiden nimiä, sarakeluetteloita tai WHERE-ehtoja ei tiedetä ennen ohjelman suorittamista.

Tapoja kirjoittaa dynaamista SQL:ää

PL/SQL tarjoaa kaksi tapaa kirjoittaa dynaamista SQL:ää:

1. NDS – Native Dynamic SQL (EXECUTE IMMEDIATE- ja OPEN-FOR-lausekkeet)
2. DBMS_SQL (toimitettu paketti)

Yleinen sääntö on yksinkertainen: jos syöte- ja tulosmuuttujien lukumäärä ja tietotyypit tiedetään käännösaikana, käytä Native Dynamic SQL:ää, koska se on nopeampi ja vaatii vähemmän koodia. Kun tämä tieto tunnetaan vasta suorituksen aikana, käytä DBMS_SQL-pakettia.

NDS (Native Dynamic SQL) – Suorita välittömästi

Natiivi dynaaminen SQL on helpompi tapa kirjoittaa dynaamista SQL:ää. Se käyttää EXECUTE IMMEDIATE -komentoa SQL:n luomiseen ja suorittamiseen suorituksen aikana. Tämän lähestymistavan käyttämiseksi suorituksen aikana käytettävien muuttujien tietotyyppi ja lukumäärä on tiedettävä etukäteen. Se tarjoaa myös paremman suorituskyvyn ja vähemmän monimutkaisuutta verrattuna DBMS_SQL:ään.

Syntaksi

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

• dynaaminen_sql_merkkijono: Merkkijonolauseke (VARCHAR2 tai CHAR, ei NVARCHAR2/NCHAR), joka sisältää yhden SQL-lauseen tai PL/SQL-lohkon.
• INTO-lauseke: Valinnainen. Käytetään vain silloin, kun dynaaminen SQL on yksirivinen SELECT; se tallentaa palautetut arvot muuttujiin tai tietueeseen. Jokaisella valitulla sarakkeella on oltava tyyppiyhteensopiva muuttuja.
• USING-lauseke: Valinnainen. Tarjoaa sidontamuuttujia. Oletustila on IN; OUT- ja IN OUT-tiloja käytetään arvojen vastaanottamiseen takaisin.
• PALAUTUSLAUSEKE: Käytetään DML-lauseiden kanssa, joissa on RETURNING-lause, kaappaamaan muuttuvan rivin arvot sidonta-argumentteihin.

Esimerkki 1: Tässä esimerkissä haemme tiedot emp-taulukosta emp_no '1001':lle käyttämällä NDS-lauseketta ja bind-muuttujaa.

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

ulostulo

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

Code Selitys:

• Rivit 2–6: Muuttujien deklarointi.
• Rivi 8: SQL-komennon kehystäminen suorituksen aikana. SQL sisältää sidontamuuttujan ':empno' WHERE-ehdossa.
• Rivit 9–11: Kehystetyn SQL-komennon suorittaminen EXECUTE IMMEDIATE -komennolla. INTO-lausekkeen muuttujat sisältävät noudetut arvot ja USING-lauseke antaa arvon sidonta-muuttujalle :empno.
• Rivit 12–15: Näyttää noudetut arvot.

Dynaamisen SQL:n käyttö DDL:ssä

Staattinen PL/SQL ei voi suorittaa suoraan DDL-komentoja, kuten CREATE, ALTER tai DROP. EXECUTE IMMEDIATE ratkaisee tämän muodostamalla lausekkeen merkkijonona, mikä on kätevää myös silloin, kun objektin nimi annetaan suorituksen aikana:

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

Objektien nimiä (taulukko, sarake, skeema) ei voida välittää sidontamuuttujina, joten ne on yhdistettävä merkkijonoksi. Tarkista aina tällainen syöte, esimerkiksi DBMS_ASSERT.SIMPLE_SQL_NAME:llä, SQL-injektion välttämiseksi.

DBMS_SQL dynaamiselle SQL:lle

PL/SQL tarjoaa DBMS_SQL-paketin dynaamisen SQL:n käsittelyyn, kun lausekkeen rakennetta ei tiedetä ennen suoritusta. Dynaamisen SQL:n luonti- ja suoritusprosessi sisältää seuraavat vaiheet:

• AVAA KURSORI: Dynaaminen SQL suoritetaan kuten kohdistinSQL-lausekkeen suorittamiseksi meidän on ensin avattava kohdistin.
• JÄSENTÄÄ SQL: Jäsentää dynaamisen SQL:n. Tämä tarkistaa syntaksin ja pitää kyselyn suoritusvalmiina.
• BIND-MUUTTUJAN arvot: Määritä sidontamuuttujien arvot, jos niitä on.
• MÄÄRITÄ SARAKKE: Määrittele jokainen sarake käyttämällä sen suhteellista sijaintia select-lausekkeessa.
• SUORITTAA: Suorita jäsennetty kysely.
• NOUDETAVAT ARVOT: Nouda suoritetut arvot.
• SULJE KURSORI: Kun tulokset on haettu, sulje kursori.

Esimerkki 1: Tässä esimerkissä haemme tiedot emp-taulukosta emp_no '1001':lle käyttämällä DBMS_SQL-lauseketta. EXCEPTION-lohko sulkee kohdistimen, vaikka tapahtuisi virhe.

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

ulostulo

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

Code Selitys:

• Rivit 1–8: Muuttujan deklarointi.
• Rivi 10: SQL-lauseen kehystäminen.
• Rivi 11: Kohdistimen avaaminen DBMS_SQL.OPEN_CURSOR-funktiolla, joka palauttaa avatun kohdistimen tunnuksen.
• Rivi 12: Kun kohdistin on avattu, SQL jäsennetään.
• Rivi 13: Sidonta-arvo '1001' asetetaan ':empno'-arvon tilalle.
• Rivit 14–17: Sarakkeiden määrittely niiden suhteellisen sijainnin perusteella: (1) työntekijän_nimi, (2) työntekijän_numero, (3) palkka, (4) esimies.
• Rivi 18: Suoritetaan kysely DBMS_SQL.EXECUTE-metodilla, joka palauttaa käsiteltyjen tietueiden määrän.
• Rivit 19–32: Tietueiden hakeminen silmukassa. FETCH_ROWS palauttaa arvon 0, kun rivejä ei ole jäljellä, mikä sulkee silmukan.
• POIKKEUS-lohko: Varmistaa, että kohdistin on suljettu, jotta avoimet kohdistimet eivät vuoda, jos virhe ilmenee.

NDS vs. DBMS_SQL: Milloin käyttää kumpaakin

Molemmat lähestymistavat ajavat SQL:ää ajonaikana, mutta ne sopivat eri tilanteisiin:

• Käytä natiivia dynaamista SQL:ää (SUORITA HETI / AVAA FOR) kun syötteiden ja tulosteiden lukumäärä ja tietotyypit tiedetään käännösaikana. Se on nopeampaa, helpompaa lukea ja vaatii vähemmän koodia.
• Käytä DBMS_SQL:ää kun rakenne on tuntematon ajonaikaiseen asti, esimerkiksi kyselyssä, jonka valittujen sarakkeiden tai sidontamuuttujien määrä vaihtelee, joka tunnetaan nimellä method-4 dynaaminen SQL, tai lauseke, joka on liian suuri mahtuakseen yhteen 32 kilotavun VARCHAR2-muuttujaan.

UKK