Oracle PL / SQL Dynamic SQL-zelfstudie: onmiddellijk uitvoeren & DBMS_SQL

Inhoudsopgave:

Anonim

Wat is dynamische SQL?

Dynamische SQL is een programmeermethodologie voor het genereren en uitvoeren van instructies tijdens runtime. Het wordt voornamelijk gebruikt om de algemene en flexibele programma's te schrijven waar de SQL-instructies tijdens runtime zullen worden gemaakt en uitgevoerd op basis van de vereiste.

In deze tutorial leer je-

  • Manieren om dynamische SQL te schrijven
  • NDS (Native Dynamic SQL) - Direct uitvoeren
  • DBMS_SQL voor dynamische SQL

Manieren om dynamische SQL te schrijven

PL / SQL biedt twee manieren om dynamische SQL te schrijven

  1. NDS - Native Dynamic SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) - Direct uitvoeren

Native Dynamic SQL is de gemakkelijkere manier om dynamische SQL te schrijven. Het gebruikt de opdracht 'EXECUTE IMMEDIATE' om de SQL tijdens runtime te maken en uit te voeren. Maar om op deze manier te gebruiken, moeten het datatype en het aantal variabelen dat tijdens een run-time wordt gebruikt, eerder bekend zijn. Het geeft ook betere prestaties en minder complexiteit in vergelijking met DBMS_SQL.

Syntaxis 

EXECUTE IMMEDIATE()[INTO][USING ]
  • De bovenstaande syntaxis toont de opdracht EXECUTE IMMEDIATE.
  • Clausule INTO is optioneel en wordt alleen gebruikt als de dynamische SQL een select-instructie bevat die waarden ophaalt. Het type variabele moet overeenkomen met het type variabele van de instructie select.
  • Clausule USING is optioneel en wordt alleen gebruikt als de dynamische SQL een bindvariabele bevat.

Voorbeeld 1 : In dit voorbeeld gaan we de gegevens ophalen uit de emp-tabel voor emp_no '1001' met behulp van de NDS-instructie.

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

Uitvoer

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

Code Verklaring:

  • Coderegel 2-6 : Variabelen declareren.
  • Coderegel 8 : Framing van de SQL tijdens runtime. SQL bevat de bindvariabele in where conditie ': empno'.
  • Coderegel 9 : het uitvoeren van de omkaderde SQL-tekst (dit wordt gedaan in coderegel 8) met behulp van het NDS-commando 'EXECUTE IMMEDIATE'
  • De variabelen in de 'INTO'-clausule (lv_emp_name, ln_emp_no, ln_salary, ln_manager) worden gebruikt om de opgehaalde waarden van de SQL-query vast te houden (emp_name, emp_no, salaris, manager)
  • De clausule 'USING' geeft de waarden aan de variabele bind in de SQL-query (: emp_no).
  • Coderegel 10-13 : Weergave van de opgehaalde waarden.

DBMS_SQL voor dynamische SQL

PL / SQL bieden het DBMS_SQL-pakket waarmee u met dynamische SQL kunt werken. Het proces voor het maken en uitvoeren van de dynamische SQL omvat het volgende proces.

  • OPEN CURSOR : De dynamische SQL wordt op dezelfde manier uitgevoerd als een cursor. Dus om de SQL-instructie uit te voeren, moeten we de cursor openen.
  • PARSE SQL : de volgende stap is het parseren van de dynamische SQL. Dit proces controleert alleen de syntaxis en houdt de query gereed om uit te voeren.
  • BIND VARIABLE Waarden : De volgende stap is het toewijzen van de waarden voor eventuele bindvariabelen.
  • DEFINEER KOLOM : De volgende stap is om de kolom te definiëren met behulp van hun relatieve posities in de select-instructie.
  • UITVOEREN : De volgende stap is het uitvoeren van de geparseerde query.
  • WAARDEN OPHALEN : De volgende stap is het ophalen van de uitgevoerde waarden.
  • SLUIT CURSOR : Zodra de resultaten zijn opgehaald, moet de cursor worden gesloten.

Voorbeeld 1 : In dit voorbeeld gaan we de gegevens ophalen uit de emp-tabel voor emp_no '1001' met behulp van de DBMS_SQL-instructie.

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

Uitvoer

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

Code Verklaring:

  • Coderegel 1-9 : Variabele declaratie.
  • Coderegel 10 : Framing van de SQL-instructie.
  • Coderegel 11 : de cursor openen met DBMS_SQL.OPEN_CURSOR. Het retourneert de cursor-id die is geopend.
  • Coderegel 12 : Nadat de cursor is geopend, wordt de SQL geparseerd.
  • Code lijn 13 : Bind variabele '1001' is toe te wijzen aan de cursor id in plaats daarvan ': empno'.
  • Coderegel 14-17 : de kolomnaam definiëren op basis van hun relatieve positie in de SQL-instructie. In ons geval is de relatieve positie (1) emp_name, (2) emp_no (3) salaris (4) manager. Dus op basis van deze positie definiëren we de doelvariabele.
  • Coderegel 18 : De query uitvoeren met DBMS_SQL.EXECUTE. Het geeft het aantal verwerkte records terug.
  • Coderegel 19-33 : de records ophalen met behulp van een lus en deze weergeven.
  • Coderegel 20: DBMS_SQL.FETCH_ROWS haalt één record op uit de verwerkte rijen. Het kan herhaaldelijk worden aangeroepen om alle rijen op te halen. Als het geen rijen kan ophalen, wordt 0 geretourneerd, waardoor de lus wordt verlaten.

Overzicht

In deze sectie hebben we dynamische SQL besproken en de manieren om DYNAMIC SQL uit te voeren. We hebben ook de verschillende stappen gezien bij het uitvoeren van de dynamische SQL op beide manieren. We hebben ook de voorbeelden gezien waarin hetzelfde scenario wordt afgehandeld in zowel NDS- als DBMS_SQL-manieren om uitvoering tijdens runtime uit te voeren.