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

Mikä on dynaaminen SQL?

Dynaaminen SQL on ohjelmointimenetelmä lausekkeiden luomiseen ja suorittamiseen ajon aikana. Sitä käytetään pääasiassa yleiskäyttöisten ja joustavien ohjelmien kirjoittamiseen, joissa SQL-käskyt luodaan ja suoritetaan ajon aikana tarpeen mukaan.

Tapoja kirjoittaa dynaaminen SQL

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

1. NDS – Native Dynamic SQL
2. DBMS_SQL

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 ajon aikana. Mutta käyttääksesi tätä tapaa, ajon aikana käytettävän muuttujan 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(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]

• Yllä oleva syntaksi näyttää EXECUTE IMMEDIATE -komennon.
• Lause INTO on valinnainen ja sitä käytetään vain, jos dynaaminen SQL sisältää arvot hakevan select-käskyn. Muuttujan tyypin tulee vastata select-lauseen muuttujatyyppiä.
• Lause USING on valinnainen ja sitä käytetään vain, jos dynaaminen SQL sisältää minkä tahansa sidosmuuttujan.

Esimerkki 1: Tässä esimerkissä aiomme hakea tiedot emp-taulukosta emp_no '1001' käyttämällä NDS-käskyä.

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

ulostulo

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

Koodin selitys:

• Koodirivi 2-6: Muuttujien ilmoittaminen.
• Koodirivi 8: SQL:n kehystys ajon aikana. SQL sisältää sidosmuuttujan jossa ehto ':empno'.
• Koodirivi 9: Kehystetyn SQL-tekstin suorittaminen (joka tehdään koodirivillä 8) NDS-komennolla 'EXECUTE IMMEDIATE'
• INTO-lauseen muuttujia (lv_emp_name, ln_emp_no, ln_salary, ln_manager) käytetään säilyttämään SQL-kyselystä haetut arvot (emp_name, emp_no, palkka, johtaja)
• 'USING'-lause antaa arvot sidosmuuttujalle SQL-kyselyssä (:emp_no).
• Koodirivi 10-13: Näyttää haetut arvot.

DBMS_SQL dynaamiselle SQL:lle

PL/SQL tarjoaa DBMS_SQL-paketin, jonka avulla voit työskennellä dynaamisen SQL:n kanssa. Dynaamisen SQL:n luonti- ja suoritusprosessi sisältää seuraavan prosessin.

• AVAA KUORSORI: Dynaaminen SQL suoritetaan samalla tavalla kuin a kohdistin. Joten SQL-käskyn suorittamiseksi meidän on avattava kohdistin.
• PARSE SQL: Seuraava vaihe on dynaamisen SQL:n jäsentäminen. Tämä prosessi vain tarkistaa syntaksin ja pitää kyselyn valmiina suoritettavaksi.
• BIND VARIABLE Arvot: Seuraava vaihe on määrittää arvot sidosmuuttujille, jos sellaisia ​​on.
• MÄÄRITÄ SARAKE: Seuraava vaihe on määrittää sarake käyttämällä niiden suhteellisia paikkoja select-käskyssä.
• SUORITA: Seuraava askel on suorittaa jäsennetty kysely.
• HAE ARVOT: Seuraava vaihe on suoritettujen arvojen hakeminen.
• SULJE KUORSIN: Kun tulokset on haettu, kohdistin tulee sulkea.

Esimerkki 1: Tässä esimerkissä aiomme hakea tiedot emp-taulukosta emp_no '1001' käyttämällä DBMS_SQL-käskyä.

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

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

ulostulo

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

Koodin selitys:

• Koodirivi 1-9: Muuttuva ilmoitus.
• Koodirivi 10: SQL-lauseen kehystys.
• Koodirivi 11: Kursorin avaaminen DBMS_SQL:llä.OPEN_CURSOR. Se palauttaa avatun kohdistimen tunnuksen.
• Koodirivi 12: Kun kohdistin on avattu, SQL jäsennetään.
• Koodirivi 13: Sidosmuuttuja '1001' määrittää kohdistimen tunnuksen ':empno' sijaan.
• Koodirivi 14-17: Sarakkeen nimen määrittäminen niiden suhteellisen sijainnin perusteella SQL-käskyssä. Meidän tapauksessamme suhteellinen asema on (1) emp_name, (2) emp_no (3) palkka (4) johtaja. Joten tämän sijainnin perusteella määrittelemme kohdemuuttujan.
• Koodirivi 18: Suoritetaan kysely DBMS_SQL.EXECUTE-komennolla. Se palauttaa käsiteltyjen tietueiden määrän.
• Koodirivi 19-33: Tietueiden hakeminen silmukan avulla ja saman näyttäminen.
• Koodirivi 20: DBMS_SQL.FETCH_ROWS hakee yhden tietueen käsitellyistä riveistä. Sitä voidaan kutsua toistuvasti kaikkien rivien hakemiseksi. Jos se ei voi noutaa rivejä, se palauttaa 0:n, jolloin se poistuu silmukasta.

Yhteenveto

Tässä osiossa olemme käsitelleet dynaamista SQL:ää ja tapoja suorittaa DYNAAMINEN SQL. Olemme myös nähneet eri vaiheet dynaamisen SQL:n suorittamisessa molemmilla tavoilla. Olemme myös nähneet esimerkkejä, joissa samaa skenaariota käsitellään sekä NDS- että DBMS_SQL-tavoilla suorituksen suorittamiseksi ajon aikana.