8 codarea declarațiilor SQL dinamice

dynamic SQL este o tehnică de programare care vă permite să construiți declarații SQL dinamic în timpul rulării. Puteți crea aplicații mai flexibile, cu scop general, utilizând SQL dinamic, deoarece textul complet al unei instrucțiuni SQL poate fi necunoscut la compilare. De exemplu, SQL dinamic vă permite să creați o procedură care funcționează pe un tabel al cărui nume nu este cunoscut până la runtime.,Oracle include două moduri de a implementa SQL dinamic într-o aplicație PL/SQL:

• SQL dinamic nativ, unde plasați instrucțiuni SQL dinamice direct în blocuri PL/SQL.
• apelarea procedurilor în pachetul DBMS_SQL.

acest capitol acoperă următoarele subiecte:

• „ce este SQL dinamic?”
• ” De ce să folosiți SQL dinamic?,”
• „Un SQL Dinamic Scenariu Folosind SQL Dinamic Nativ”
• „Alegerea Între Nativ SQL Dinamic și Pachetul DBMS_SQL”
• „Folosind SQL Dinamic în Alte Limbi Decât PL/SQL”
• „Folosind PL/SQL Înregistrări în SQL INSERT și UPDATE Declarații”

puteți găsi detalii despre DBMS_SQL pachetul în Oracle9i Furnizate PL/SQL Pachete și Tipuri Referință.

ce este SQL dinamic?

dynamic SQL vă permite să scrieți programe care fac referință la instrucțiunile SQL al căror text complet nu este cunoscut până la runtime., Înainte de a discuta SQL dinamic în detaliu, o definiție clară a SQL static poate oferi un bun punct de plecare pentru înțelegerea SQL dinamic. Declarațiile SQL statice nu se schimbă de la execuție la execuție. Textul complet al declarațiilor SQL statice sunt cunoscute la compilare, care oferă următoarele beneficii:

• compilarea de succes verifică dacă declarațiile SQL referință obiecte de bază de date valide.
• compilarea cu succes verifică dacă privilegiile necesare sunt în vigoare pentru a accesa obiectele bazei de date.,
• performanța SQL static este, în general, mai bună decât SQL dinamic.din cauza acestor avantaje, ar trebui să utilizați SQL dinamic numai dacă nu puteți utiliza SQL static pentru a vă atinge obiectivele sau dacă utilizarea SQL static este greoaie în comparație cu SQL dinamic. Cu toate acestea, SQL static are limitări care pot fi depășite cu SQL dinamic. Este posibil să nu cunoașteți întotdeauna textul complet al instrucțiunilor SQL care trebuie executate într-o procedură PL/SQL., Programul dvs. poate accepta introducerea utilizatorului care definește instrucțiunile SQL de executat sau poate fi necesar ca programul dvs. să finalizeze unele lucrări de procesare pentru a determina cursul corect de acțiune. În astfel de cazuri, ar trebui să utilizați SQL dinamic.

  de exemplu, este posibil ca o aplicație de raportare dintr-un mediu data warehouse să nu cunoască numele exact al tabelului până la momentul rulării., Aceste tabele ar putea fi numit în funcție la început de lună și an de trimestru, de exemplu INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, și așa mai departe. Puteți utiliza SQL dinamic în aplicația de raportare pentru a specifica numele tabelului în timpul rulării.

  De asemenea, este posibil să doriți să rulați o interogare complexă cu o ordine de sortare selectabilă de utilizator., În loc de codificare interogare de două ori, cu diferite ORDER BY clauze, puteți construi interogare dinamic pentru a include o anumită ORDER BY clauză.programele SQL dinamice pot gestiona modificările definițiilor de date, fără a fi necesară recompilarea. Acest lucru face SQL dinamic mult mai flexibil decât SQL static. SQL dinamic vă permite să scrie cod reutilizabile, deoarece SQL poate fi ușor adaptat pentru diferite medii..,dinamic SQL, de asemenea, vă permite să execute date definition language (DDL) declarații și alte declarații SQL care nu sunt acceptate în programe SQL pur statice.

  de ce să folosiți SQL dinamic?

  ar trebui să utilizați SQL dinamic în cazurile în care SQL static nu acceptă operația pe care doriți să o efectuați sau în cazurile în care nu cunoașteți instrucțiunile SQL exacte care trebuie executate printr-o procedură PL/SQL. Aceste instrucțiuni SQL pot depinde de introducerea utilizatorului sau pot depinde de procesarea lucrărilor efectuate de program.,DL and SCL Statements in PL/SQL

  In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:

  • Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
  • Session control language (SCL) statements, such as ALTER SESSION and SET ROLE
    See Also:

    Oracle9i SQL Reference for information about DDL and SCL statements.,de asemenea, puteți utiliza numai clauzaTABLE în instrucțiunea SELECT prin SQL dinamic. De exemplu, următoarele PL/SQL bloc conține un SELECT declarație care utilizează TABLE clauză și nativ SQL dinamic:

    de Executare Interogări Dinamice

    puteți utiliza SQL dinamic pentru a crea aplicații care executa interogări dinamice, al cărui text integral nu este cunoscut până la execuție., Mai multe tipuri de aplicații, trebuie să utilizați interogări dinamice, inclusiv:

    • Aplicații care permit utilizatorilor să introducă sau alege interogare de căutare sau de sortare criterii la runtime
    • Aplicații care permit utilizatorilor să introducă sau alege optimizare aluzii la timp a alerga
    • Aplicații care interoga o bază de date în care datele definiții din tabele sunt în continuă schimbare
    • Aplicații care interoga o bază de date în cazul în care noi tabele sunt create de multe ori

    Pentru exemple, a se vedea „Interogarea Folosind SQL Dinamic: Exemplu”, și a vedea exemple de interogare într-Un „SQL Dinamic Scenariu Folosind SQL Dinamic Nativ”.,

    referențierea obiectelor bazei de date care nu există la compilare

    multe tipuri de aplicații trebuie să interacționeze cu datele generate periodic. De exemplu, este posibil să cunoașteți definițiile tabelelor la momentul compilării, dar nu și numele tabelelor.SQL dinamic poate rezolva această problemă, deoarece vă permite să așteptați până la runtime pentru a specifica numele tabelelor. De exemplu, în aplicația exemplu data warehouse discutată în „ce este SQL dinamic?”, noi tabele sunt generate în fiecare trimestru, iar aceste tabele au întotdeauna aceeași definiție., Puteți lăsa un utilizator să specifice numele tabelului în timpul rulării cu o interogare SQL dinamică similară cu următoarea:

    optimizarea dinamică a execuției

    puteți utiliza SQL dinamic pentru a construi o instrucțiune SQL într-un mod care optimizează execuția prin concatenarea sugestiilor într-o instrucțiune SQL dinamic. Acest lucru vă permite să modificați indicii bazate pe statisticile bazei de date curente, fără a necesita recompilare.,

    De exemplu, următoarea procedură folosește o variabilă numită a_hint pentru a permite utilizatorilor să treacă un indiciu opțiunea de a SELECT declarație:

    În acest exemplu, utilizatorul poate trece la oricare dintre următoarele valori pentru a_hint:

    a_hint = '/*+ ALL_ROWS */' a_hint = '/*+ FIRST_ROWS */' a_hint = '/*+ CHOOSE */' sau orice alte valabilă opțiunea de indiciu.

    A se vedea, de asemenea:

    Oracle9i baza de date Ghid de performanță și de referință pentru mai multe informații despre utilizarea indicii.,

    de Executare Dinamic PL/SQL Blocuri

    puteți folosi EXECUTE IMMEDIATE declarație să execute anonim PL/SQL blocuri. Puteți adăuga flexibilitate prin construirea conținutului blocului în timpul rulării.

    de exemplu, să presupunem că ythroughthrough doriți să scrieți o aplicație care ia un număr de eveniment și expediază către un handler pentru eveniment. Numele handler este în formă EVENT_HANDLER_event_num, unde event_num este numărul de eveniment., O abordare este de a pune în aplicare dispecer ca o declarație comutator, în cazul în care codul se ocupă de fiecare eveniment prin efectuarea unui apel static la handler corespunzătoare. Acest cod nu este foarte extensibil, deoarece codul dispecerului trebuie actualizat ori de câte ori se adaugă un handler pentru un nou eveniment.,folosind SQL dinamic nativ, puteți scrie un dispecer de evenimente mai mic, mai flexibil, similar cu următoarele:

    efectuarea operațiunilor dinamice folosind Invoker-Rights

    folosind caracteristica invoker-rights cu SQL dinamic, puteți construi aplicații care emite instrucțiuni SQL dinamice sub privilegiile și schema invoker. Aceste două caracteristici, invoker-rights și dynamic SQL, vă permit să construiți subcomponente de aplicații reutilizabile care pot funcționa și accesa datele și modulele invoker.,

    A se vedea, de asemenea:

    PL / SQL Ghidul utilizatorului și de referință pentru informații despre utilizarea invokers-drepturi și SQL dinamic nativ.,

    Un SQL Dinamic Scenariu Folosind SQL Dinamic Nativ

    Acest scenariu vă arată cum să efectuați următoarele operații folosind SQL dinamic nativ:

    • Executa DDL și DML operațiunile
    • Executa singur rând și rând mai multe întrebări

    date în acest scenariu este o companie de resurse umane baza de date (nume hr) cu următorul model de date:

    Un maestru tabel numit offices conține lista cu toate locatiile companiei., offices tabel are următoarea definiție:

    Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

    mai Multe emp_locație tabele conțin informații angajat, în cazul în care locația este numele orașului în cazul în care biroul este situat. De exemplu, un tabel numit emp_houston conține informații angajat pentru compania Houston birou, în timp ce un tabel numit emp_boston conține informații angajat pentru compania Boston.,

    Fiecare emp_locație tabel are următoarea definiție:

    următoarele secțiuni descriu diverse nativ SQL dinamic operațiunile care pot fi efectuate asupra datelor din hr baza de date.

    Eșantion DML Operație Folosind SQL Dinamic Nativ

    următoarele nativ SQL dinamic procedură oferă o mărire de salariu pentru toți angajații cu un anumit titlu job:

    Eșantion DDL Operație Folosind SQL Dinamic Nativ

    EXECUTE IMMEDIATE declarație poate efectua DDL operațiuni., De exemplu, următoarea procedură adaugă un birou locație:

    următoarea procedură șterge o locație birou:

    Probă de un Singur Rând de Interogare Folosind SQL Dinamic Nativ

    EXECUTE IMMEDIATE declarație poate efectua dinamic cu un singur rând de interogări. Puteți specifica lega variabile în USING clauză și aduce rezultate rând în țintă specificate în INTO clauza de declarație.,

    următoarele funcții preia numărul de angajați la o anumită locație efectuează o anumită muncă:

    Eșantion mai Multe Rânduri de Interogare Folosind SQL Dinamic Nativ

    OPEN-FOR, FETCH și CLOSE declarații pot efectua dinamice multiple-row interogări., De exemplu, procedura următoare listează toți angajații cu un anumit loc de muncă la o locație specificată:

    Alegerea Între Nativ SQL Dinamic și Pachetul DBMS_SQL

    Oracle oferă două metode pentru utilizarea dinamice SQL în PL/SQL: SQL dinamic nativ și DBMS_SQL pachet. SQL dinamic nativ vă permite să plasați declarații SQL dinamice direct în PL / cod SQL. Aceste declarații dinamice includ declarații DML( inclusiv interogări), blocuri anonime PL/SQL, declarații DDL, declarații de control al tranzacțiilor și declarații de control al sesiunii.,

    To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.

    Note:

    To use native dynamic SQL, the COMPATIBLE initialization parameter must be set to 8.1.0 or higher. See Oracle9i Database Migration for more information about the COMPATIBLE parameter.,

    DBMS_SQL pachetul este o bibliotecă PL / SQL care oferă un API pentru a executa instrucțiuni SQL dinamic. Pachetul DBMS_SQL are proceduri pentru a deschide un cursor, a analiza un cursor, a lega alimentarea și așa mai departe. Programele care utilizează pachetul DBMS_SQL efectuează apeluri către acest pachet pentru a efectua operații SQL dinamice.următoarele secțiuni oferă informații detaliate despre avantajele ambelor metode.,

    Vezi de Asemenea și:

    PL/SQL Ghidul Utilizatorului și de Referință pentru informații detaliate despre utilizarea SQL dinamic nativ și Oracle9i Furnizate PL/SQL Pachete și Tipuri Referință pentru informații detaliate despre utilizarea DBMS_SQL pachet. În ghidul și referința utilizatorului PL/SQL, SQL dinamic nativ este denumit pur și simplu SQL dinamic.,

    Avantajele oferite de SQL Dinamic Nativ

    SQL dinamic Nativ oferă următoarele avantaje față de DBMS_SQL ambalaj:

    Nativ SQL Dinamic este Ușor de Utilizat

    Pentru nativ SQL dinamic este integrat cu SQL, puteți să-l utilizați în același mod în care utilizați SQL static în termen de cod PL/SQL. Codul SQL dinamic nativ este de obicei mai compact și mai ușor de citit decât codul echivalent care utilizează pachetul DBMS_SQL.,cu pachetul DBMS_SQL trebuie să apelați multe proceduri și funcții într-o secvență strictă, făcând chiar și operații simple necesită mult cod. Puteți evita această complexitate utilizând SQL dinamic nativ în schimb.

    tabelul 8-1 ilustrează diferența dintre cantitatea de cod necesară pentru a efectua aceeași operație folosind pachetul DBMS_SQL și SQL dinamic nativ.,

    Tabelul 8-1 Cod Comparație a Pachetului DBMS_SQL și Nativ SQL Dinamic

    Nativ SQL Dinamic este mai Rapid decât DBMS_SQL

    Nativ dinamic SQL în PL/SQL efectuează relativ la performanța de SQL static, deoarece PL/SQL interpret a construit-in-suport pentru ea. Programele care utilizează SQL dinamic nativ sunt mult mai rapide decât programele care utilizează pachetul DBMS_SQL. De obicei, declarațiile SQL dinamice native efectuează apeluri de 1,5 până la 3 ori mai bune decât echivalentul DBMS_SQL. (Câștigurile dvs. de performanță pot varia în funcție de aplicația dvs.,)

    SQL dinamic nativ grupează pregătirea instrucțiunii, legarea și pașii de execuție într-o singură operație, care minimizează copierea datelor și procedura de apel deasupra capului și îmbunătățește performanța.

    DBMS_SQL pachetul se bazează pe un API procedural și suportă apeluri de proceduri ridicate și copierea datelor deasupra capului. De fiecare dată când legați o variabilă, pachetul DBMS_SQL copiază variabila PL/SQL bind în spațiul său pentru a fi utilizată în timpul execuției., De fiecare dată când executați o aduce, datele sunt copiate în spațiul gestionat de DBMS_SQL pachet și apoi preluat de date este copiat, o coloană la un moment dat, în caz PL/SQL variabile, rezultând în regie substanțiale.

    sfat de performanță: utilizarea variabilelor Bind

    când utilizați fie SQL dinamic nativ, fie pachetulDBMS_SQL, puteți îmbunătăți performanța folosind variabile bind, deoarece variabilele bind permit Oracle să partajeze un singur cursor pentru mai multe instrucțiuni SQL.,

    de exemplu, următorul cod SQL dinamic nativ nu utilizează variabile bind:

    pentru fiecare variabilă distinctă my_deptno, este creat un nou cursor, provocând dispută de resurse și performanțe slabe. În schimb, se leagă my_deptno ca o lega variabile:

    Aici, la fel cursorul este refolosite pentru diferite valori ale bind my_deptno, îmbunătățirea performanței și scalabilty.,SQL dinamic nativ suportă tipuri definite de utilizator

    SQL dinamic nativ suportă toate tipurile acceptate de SQL static în PL / SQL, inclusiv tipuri definite de utilizator, cum ar fi obiecte definite de utilizator, colecții, și REFs. Pachetul DBMS_SQL nu acceptă aceste tipuri definite de utilizator.

    Notă:

    DBMS_SQL pachetul oferă suport limitat pentru matrice. A se vedea Oracle9i furnizate PL/SQL pachete și tipuri de referință pentru informații.,

    SQL dinamic nativ acceptă preluarea în înregistrări

    SQL dinamic nativ și SQL static ambele acceptă preluarea în înregistrări, darDBMS_SQL pachetul nu. Cu SQL dinamic nativ, rândurile rezultate dintr-o interogare pot fi preluate direct în înregistrări PL/SQL.,

    În exemplul următor, rânduri dintr-o interogare sunt preluate în emp_rec record:

    Avantaje ale Pachetului DBMS_SQL

    DBMS_SQL pachet oferă următoarele avantaje față de nativ SQL dinamic:

    DBMS_SQL este Susținută în Parte de Client Programe

    DBMS_SQL pachetul este susținută în parte de client programe, dar nativ SQL dinamic nu este., Fiecare apel către pachetul DBMS_SQL din programul client-side se traduce printr-un apel de procedură la distanță PL/SQL (RPC); aceste apeluri apar atunci când trebuie să legați o variabilă, să definiți o variabilă sau să executați o instrucțiune.

    DBMS_SQL Sprijină DESCRIE

    DESCRIBE_COLUMNS procedura în DBMS_SQL pachet poate fi folosit pentru a descrie coloanele pentru un cursor deschis și analizat prin DBMS_SQL. Această caracteristică este similară cu comanda DESCRIBE din SQL * Plus., SQL dinamic nativ nu are un DESCRIBE facilitate.

    DBMS_SQL Suportă mai Multe rânduri Actualizări și Șterge cu un REVENIND Clauza

    DBMS_SQL pachet acceptă declarații cu un RETURNING clauză care a actualiza sau a șterge mai multe rânduri. SQL dinamic nativ acceptă doar o clauză RETURNING dacă un singur rând este returnat.,

    See Also:

    „Performing DML with RETURNING Clause Using Dynamic SQL: Example” for examples of DBMS_SQL package code and native dynamic SQL code that uses a RETURNING clause.

    DBMS_SQL Supports SQL Statements Larger than 32KB

    The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

    DBMS_SQL Lets You Reuse SQL Statements

    The PARSE procedure in the DBMS_SQL package parses a SQL statement once., După parsarea inițială, puteți utiliza instrucțiunea de mai multe ori cu diferite seturi de argumente bind.SQL dinamic nativ pregătește o instrucțiune SQL de fiecare dată când instrucțiunea este utilizată, care implică de obicei parsarea, optimizarea și generarea planului. Deși operațiunile suplimentare de pregătire suportă o penalizare mică de performanță, încetinirea este de obicei depășită de beneficiile de performanță ale SQL dinamic nativ.,

    Exemple de DBMS_SQL Pachet de Cod și SQL Dinamic Nativ Cod

    următoarele exemple ilustrează diferențele în codul necesar pentru a finaliza operațiunile cu DBMS_SQL pachet și SQL dinamic nativ. În mod special, următoarele tipuri de exemple sunt prezentate:

    • O interogare
    • UN DML de funcționare
    • UN DML revenind funcționare

    În general, nativ SQL dinamic codul este mai ușor de citit și compact, care poate îmbunătăți producător de productivitate.,

    Interogarea Folosind SQL Dinamic: Exemplu

    următorul exemplu include o interogare dinamic declarație cu o leagă variabila (:jobname) și două selectați coloane (ename și sal):

    stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname';

    Acest exemplu interogări pentru angajații cu descrierea de locuri de muncă SALESMAN în job coloana de emp masă. Tabelul 8-2 arată codul eșantion care realizează această interogare folosind DBMS_SQL pachet și SQL dinamic nativ.,

    Tabelul 8-2 Interogarea Folosind Pachetul DBMS_SQL și Nativ SQL Dinamic

    Efectuarea DML Folosind SQL Dinamic: Exemplu

    următorul exemplu include o dinamică INSERT declarație pentru un tabel cu trei coloane:

    stmt_str := 'INSERT INTO dept_new VALUES (:deptno, :dname, :loc)';

    Acest exemplu inserează un rând nou pentru care valorile coloanelor sunt în PL/SQL variabile deptnumber, deptname și location. Tabelul 8-3 arată codul eșantion care realizează această operație DML folosind DBMS_SQL pachet și SQL dinamic nativ.,

    Tabel 8-3 DML Funcționării cu Ajutorul Pachetului DBMS_SQL și Nativ SQL Dinamic

    Efectuarea DML cu REVENIREA Clauza Folosind SQL Dinamic: Exemplu

    următorul exemplu folosește o dinamică UPDATE declarație pentru a actualiza locația de un departament, apoi se întoarce numele departamentului:

    stmt_str := 'UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname';

    Tabel 8-4 prezinta mostre de cod care realizează această operație folosind atât DBMS_SQL pachet și SQL dinamic nativ.,

    Tabel 8-4 DML Revenind Funcționării cu Ajutorul Pachetului DBMS_SQL și Nativ SQL Dinamic

    Folosind SQL Dinamic în Alte Limbi Decât PL/SQL

    Deși în acest capitol se discută PL/SQL suport pentru SQL dinamic, puteți apela SQL dinamic din alte limbi:

    • Dacă utilizați C/C++, puteți apela SQL dinamic cu Oracle Call Interface (OCI), sau puteți folosi Pro*C/C++ precompiler pentru a adăuga SQL dinamic extensii la C cod.
    • dacă utilizați COBOL, puteți utiliza Pro * COBOL precompiler pentru a adăuga extensii SQL dinamice la codul COBOL.,
    • dacă utilizați Java, puteți dezvolta aplicații care utilizează SQL dinamic cu JDBC.dacă aveți o aplicație care utilizează OCI, Pro * C / C++ sau pro * COBOL pentru a executa SQL dinamic, ar trebui să luați în considerare trecerea la SQL dinamic nativ în interiorul procedurilor și funcțiilor stocate PL/SQL. Călătoriile dus-întors în rețea necesare pentru a efectua operații SQL dinamice din aplicațiile din partea clientului ar putea afecta performanța. Procedurile stocate pot locui pe server, eliminând aeriene de rețea., Puteți apela procedurile stocate PL/SQL și funcțiile stocate din aplicația OCI, Pro * C / C++ sau pro * COBOL.,br>Vezi de Asemenea și:

      Pentru informații despre apelarea Oracle proceduri stocate și funcții stocate din diverse limbi, se referă la:

      • Oracle Call Interface Programmer ‘s Guide
      • Pro*C/C++ Precompiler Programmer’ s Guide
      • Pro*COBOL Precompiler Programmer ‘ s Guide
      • Oracle9i Java Proceduri Stocate Ghidul Dezvoltatorului

    , Folosind PL/SQL Înregistrări în SQL INSERT și UPDATE Declarațiile

    Deși puteți enumera fiecare domeniu de PL/SQL record de la introducerea sau actualizarea rânduri într-un tabel, codul rezultat nu este deosebit de ușor de citit sau de întreținut., În schimb, puteți utiliza înregistrări PL / SQL direct în aceste declarații. Cea mai convenabilă tehnică este de a declara înregistrarea folosind un atribut %ROWTYPE, astfel încât să aibă exact aceleași câmpuri ca tabelul SQL.deși această tehnică ajută la integrarea mai strânsă a variabilelor PL/SQL și a tipurilor cu declarațiile SQL DML, nu puteți utiliza înregistrările PL / SQL ca variabile bind în declarațiile SQL dinamice.