Урок за динамичен SQL на Oracle PL / SQL: Изпълнете незабавно & DBMS_SQL

Съдържание:

Anonim

Какво е Dynamic SQL?

Dynamic SQL е методология за програмиране за генериране и изпълнение на оператори по време на изпълнение. Използва се главно за писане на универсални и гъвкави програми, където SQL изразите ще бъдат създадени и изпълнени по време на изпълнение въз основа на изискването.

В този урок ще научите -

  • Начини за писане на динамичен SQL
  • NDS (Native Dynamic SQL) - изпълнява незабавно
  • DBMS_SQL за динамичен SQL

Начини за писане на динамичен SQL

PL / SQL предоставя два начина за писане на динамичен SQL

  1. NDS - Вграден динамичен SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) - изпълнява незабавно

Native Dynamic SQL е по-лесният начин за писане на динамичен SQL. Той използва командата „ИЗПЪЛНИТЕ НЕЗАБАВНО“, за да създаде и изпълни SQL по време на изпълнение. Но за да се използва по този начин, типът данни и броят на променливите, които ще се използват по време на изпълнение, трябва да бъдат известни преди. Той също така дава по-добра производителност и по-малко сложност в сравнение с DBMS_SQL.

Синтаксис 

EXECUTE IMMEDIATE()[INTO][USING ]
  • Горният синтаксис показва командата EXECUTE IMMEDIATE.
  • Клаузата INTO не е задължителна и се използва само ако динамичният SQL съдържа оператор select, който извлича стойности. Типът на променливата трябва да съвпада с типа на променливата на оператора select.
  • Клаузата USING не е задължителна и се използва само ако динамичният SQL съдържа променлива за свързване.

Пример 1 : В този пример ще извлечем данните от таблицата emp за emp_no '1001', като използваме оператора NDS.

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

Изход

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

Обяснение на кода:

  • Кодов ред 2-6 : Деклариране на променливи.
  • Кодов ред 8 : Рамкиране на SQL по време на изпълнение. SQL съдържа променливата за обвързване, където условието ': empno'.
  • Кодов ред 9 : Изпълнение на рамкирания SQL текст (което се прави в кодов ред 8) с помощта на командата NDS „ИЗПЪЛНИТЕ НЕЗАБАВНО“
  • Променливите в клаузата „INTO“ (lv_emp_name, ln_emp_no, ln_salary, ln_manager) се използват за съхранение на извлечените стойности от SQL заявката (emp_name, emp_no, заплата, мениджър)
  • Клаузата „USING“ дава стойностите на променливата за свързване в SQL заявката (: emp_no).
  • Кодов ред 10-13 : Показване на извлечените стойности.

DBMS_SQL за динамичен SQL

PL / SQL предоставят пакета DBMS_SQL, който ви позволява да работите с динамичен SQL. Процесът на създаване и изпълнение на динамичния SQL съдържа следния процес.

  • ОТВОРЕН КУРСОР : Динамичният SQL ще се изпълни по същия начин като курсора. Така че, за да изпълним SQL израза, трябва да отворим курсора.
  • PARSE SQL : Следващата стъпка е да се анализира динамичния SQL. Този процес просто ще провери синтаксиса и ще поддържа заявката готова за изпълнение.
  • BIND VARIABLE Стойности : Следващата стъпка е да присвоите стойностите за променливите за обвързване, ако има такива.
  • ОПРЕДЕЛЕТЕ КОЛОНА : Следващата стъпка е да дефинирате колоната, като използвате относителните им позиции в оператора за избор.
  • ИЗПЪЛНЕНИЕ : Следващата стъпка е да се изпълни анализираната заявка.
  • FETCH VALUES : Следващата стъпка е да се извлекат изпълнените стойности.
  • CLOSE CURSOR : След като резултатите бъдат извлечени, курсорът трябва да бъде затворен.

Пример 1 : В този пример ще извлечем данните от таблицата emp за emp_no '1001', като използваме оператора DBMS_SQL.

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

Изход

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

Обяснение на кода:

  • Кодов ред 1-9 : Декларация на променлива.
  • Кодов ред 10 : Рамкиране на SQL израза.
  • Кодов ред 11 : Отваряне на курсора с помощта на DBMS_SQL.OPEN_CURSOR. Той ще върне отворения идентификатор на курсора.
  • Кодов ред 12 : След отваряне на курсора SQL се анализира.
  • Кодов ред 13 : Променливата за обвързване „1001“ присвоява на идентификатора на курсора вместо „: empno“.
  • Кодов ред 14-17 : Дефиниране на името на колоната въз основа на относителното им положение в SQL израза. В нашия случай относителната позиция е (1) emp_name, (2) emp_no (3) заплата (4) мениджър. Така че въз основа на тази позиция ние определяме целевата променлива.
  • Кодов ред 18 : Изпълнение на заявката с помощта на DBMS_SQL.EXECUTE. Той връща броя на обработените записи.
  • Кодов ред 19-33 : Извличане на записите с помощта на цикъл и показване на същото.
  • Кодов ред 20: DBMS_SQL.FETCH_ROWS ще извлече един запис от обработените редове. Може да се извиква многократно, за да извлече всички редове. Ако не може да извлече редове, ще върне 0, като по този начин ще излезе от цикъла.

Обобщение

В този раздел обсъдихме динамичния SQL и начините за изпълнение на DYNAMIC SQL. Видяхме и различните стъпки при изпълнението на динамичния SQL и по двата начина. Виждали сме и примерите, в които един и същ сценарий се обработва както в NDS, така и в DBMS_SQL начини за изпълнение на изпълнение по време на изпълнение.