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

Sisällysluettelo:

Anonim

Mikä on dynaaminen SQL?

Dynaaminen SQL on ohjelmointimenetelmä lauseiden 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 suorituksen aikana vaatimuksen perusteella.

Tässä opetusohjelmassa opit-

  • Tapoja kirjoittaa dynaamista SQL: ää
  • NDS (Native Dynamic SQL) - Suorita välitön
  • DBMS_SQL dynaamiselle SQL: lle

Tapoja kirjoittaa dynaamista SQL: ää

PL / SQL tarjoaa kaksi tapaa kirjoittaa dynaaminen SQL

  1. NDS - Natiivi dynaaminen SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) - Suorita välitön

Natiivi dynaaminen SQL on helpompi tapa kirjoittaa dynaamista SQL: ää. Se käyttää 'EXECUTE IMMEDIATE' -komentoa SQL: n luomiseen ja suorittamiseen ajon aikana. Mutta tällä tavoin ajon aikana käytettävän muuttujan tietotyyppi ja muuttujien lukumäärä on tunnettava aiemmin. Se antaa myös paremman suorituskyvyn ja vähemmän monimutkaisuuden verrattaessa DBMS_SQL: ään.

Syntaksi

EXECUTE IMMEDIATE()[INTO][USING ]
  • Yllä olevassa syntaksissa näkyy EXECUTE IMMEDIATE -komento.
  • Lauseke INTO on valinnainen ja sitä käytetään vain, jos dynaaminen SQL sisältää select-käskyn, joka noutaa arvot. Muuttujatyypin on vastattava select-käskyn muuttujatyyppiä.
  • Lauseke KÄYTTÖ on valinnainen ja sitä käytetään vain, jos dynaaminen SQL sisältää mitä tahansa sidontamuuttujaa.

Esimerkki 1 : Tässä esimerkissä haemme tiedot emp_no '1001: n emp-taulukosta NDS-käskyn avulla.

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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;/

Tuotos

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

Koodin selitys:

  • Koodirivi 2-6 : Muuttujien ilmoittaminen.
  • Koodirivi 8 : SQL: n kehystäminen ajon aikana. SQL sisältää sidosmuuttujan missä ehto ': empno'.
  • Koodirivi 9 : Kehystetyn SQL-tekstin suorittaminen (joka tapahtuu koodirivillä 8) NDS-komennolla 'EXECUTE IMMEDIATE'
  • INTO-lausekkeen muuttujia (lv_emp_name, ln_emp_no, ln_salary, ln_manager) käytetään SQL-kyselystä haettujen arvojen pitämiseen (emp_name, emp_no, palkka, manager)
  • 'USING' -lauseke antaa arvot sidontamuuttujalle SQL-kyselyssä (: emp_no).
  • Koodirivi 10-13 : Haettujen arvojen näyttäminen.

DBMS_SQL dynaamiselle SQL: lle

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

  • OPEN CURSOR : Dynaaminen SQL suoritetaan samalla tavalla kuin kohdistin. Joten SQL-käskyn suorittamiseksi meidän on avattava kohdistin.
  • PARSE SQL : Seuraava vaihe on jäsentää dynaaminen SQL. Tämä prosessi tarkistaa vain syntaksin ja pitää kyselyn valmiina suoritettavaksi.
  • BIND VARIABLE Arvot : Seuraava vaihe on määrittää sidontamuuttujien arvot, jos sellaisia ​​on.
  • MÄÄRITÄ SARAKE : Seuraava vaihe on määrittää sarake käyttämällä niiden suhteellista sijaintia select-lauseessa.
  • SUORITA : Seuraava vaihe on suorittaa jäsennetty kysely.
  • FETCH ARVES : Seuraava askel on noutaa suoritetut arvot.
  • SULJE KURSORI : Kun tulokset on haettu, kohdistin on suljettava.

Esimerkki 1 : Tässä esimerkissä haemme tiedot emp_no '1001: n emp-taulukosta DBMS_SQL-käskyn avulla.

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:/

Tuotos

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

Koodin selitys:

  • Koodirivi 1-9 : Muuttujailmoitus.
  • Koodirivi 10 : SQL-käskyn kehystäminen.
  • Koodirivi 11 : Kohdistimen avaaminen DBMS_SQL.OPEN_CURSOR-sovelluksella. Se palauttaa avatun kohdistintunnuksen.
  • Koodirivi 12 : Kun kohdistin on avattu, SQL jäsennetään.
  • Koodirivi 13 : Sitova muuttuja '1001' osoitetaan kohdistimen tunnukselle ': empno'.
  • Koodirivi 14-17 : Sarakkeen nimen määritteleminen niiden suhteellisen sijainnin perusteella SQL-käskyssä. Meidän tapauksessamme suhteellinen asema on (1) emp_name, (2) emp_no (3) palkka (4) manager. Joten tämän sijainnin perusteella määritämme kohdemuuttujan.
  • Koodirivi 18 : Kyselyn suorittaminen DBMS_SQL.EXECUTE-toiminnolla. Se palauttaa käsiteltyjen tietueiden määrän.
  • Koodirivi 19-33 : Tietueiden hakeminen silmukan avulla ja niiden näyttäminen.
  • Koodirivi 20: DBMS_SQL.FETCH_ROWS hakee yhden tietueen käsitellyiltä riveiltä. Sitä voidaan kutsua toistuvasti kaikkien rivien noutamiseksi. Jos se ei voi hakea rivejä, se palauttaa 0 ja poistuu siten silmukasta.

Yhteenveto

Tässä osassa olemme keskustelleet dynaamisesta SQL: stä ja tavoista suorittaa DYNAMIC SQL. Olemme myös nähneet dynaamisen SQL: n suorittamisen eri vaiheet molemmilla tavoilla. Olemme myös nähneet esimerkkejä, joissa samaa skenaariota käsitellään sekä NDS- että DBMS_SQL-tavoilla suorituksen suorittamiseksi ajon aikana.