Oracle Урок за PL/SQL Dynamic SQL: Незабавно изпълнение & DBMS_SQL
Какво е динамичен SQL?
Динамичен SQL е методология за програмиране за генериране и изпълнение на отчети по време на изпълнение. Използва се главно за писане на универсални и гъвкави програми, където SQL изразите ще бъдат създадени и изпълнени по време на изпълнение въз основа на изискването.
Начини за писане на динамичен SQL
PL/SQL предоставя два начина за писане на динамичен SQL
- NDS – собствен динамичен SQL
- DBMS_SQL
NDS (Native Dynamic SQL) – Незабавно изпълнение
Native Dynamic SQL е по-лесният начин за писане на динамичен SQL. Той използва командата „EXECUTE IMMEDIATE“, за да създаде и изпълни SQL по време на изпълнение. Но за да се използва по този начин, типът данни и номерът на променливата, която ще се използва по време на изпълнение, трябва да бъдат известни преди това. Освен това осигурява по-добра производителност и по-малко сложност в сравнение с DBMS_SQL.
Синтаксис
EXECUTE IMMEDIATE(<SQL>) [INTO<variable>] [USING <bind_variable_value>]
- Горният синтаксис показва командата EXECUTE IMMEDIATE.
- Клаузата INTO не е задължителна и се използва само ако динамичният SQL съдържа оператор за избор, който извлича стойности. Типът на променливата трябва да съвпада с типа на променливата на оператора select.
- Клауза USING не е задължителна и се използва само ако динамичният SQL съдържа някаква свързваща променлива.
Пример 1: В този пример ще извлечем данните от emp таблица за emp_no '1001' с помощта на NDS израз.
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; /
Продукция
Employee Name : XXX Employee Number: 1001 Salary: 15000 Manager ED: 1000
Обяснение на кода:
- Кодов ред 2-6: Деклариране на променливи.
- Кодов ред 8: Рамкиране на SQL по време на изпълнение. SQL съдържа променливата за свързване в условието where ':empno'.
- Кодов ред 9: Изпълнение на рамкирания SQL текст (което се извършва в кодов ред 8) с помощта на NDS командата 'EXECUTE IMMEDIATE'
- Променливите в клаузата „INTO“ (lv_emp_name, ln_emp_no, ln_salary, ln_manager) се използват за задържане на извлечените стойности от SQL заявката (emp_name, emp_no, salary, manager)
- Клаузата 'USING' дава стойностите на свързващата променлива в SQL заявката (:emp_no).
- Кодов ред 10-13: Показване на извлечените стойности.
DBMS_SQL за динамичен SQL
PL/SQL предоставя пакета DBMS_SQL, който ви позволява да работите с динамичен SQL. Процесът на създаване и изпълнение на динамичния SQL съдържа следния процес.
- ОТВОРЕТЕ КЪРСОР: Динамичният SQL ще се изпълни по същия начин като a курсор. Така че, за да изпълним SQL оператора, трябва да отворим курсора.
- PARSE SQL: Следващата стъпка е да анализирате динамичния SQL. Този процес просто ще провери синтаксиса и ще поддържа заявката готова за изпълнение.
- BIND VARIABLE Стойности: Следващата стъпка е да присвоите стойностите за обвързващи променливи, ако има такива.
- ДЕФИНИРАЙТЕ КОЛОНА: Следващата стъпка е да дефинирате колоната, като използвате относителните им позиции в оператора select.
- ИЗПЪЛНИ: Следващата стъпка е да изпълните анализираната заявка.
- ИЗВЛЕЧВАНЕ НА СТОЙНОСТИ: Следващата стъпка е да извлечете изпълнените стойности.
- ЗАТВОРИ КУРСОР: След като резултатите бъдат извлечени, курсорът трябва да бъде затворен.
Пример 1: В този пример ще извлечем данните от emp таблица за emp_no '1001' с помощта на DBMS_SQL израз.
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: /
Продукция
Employee Name:XXX Employee Number:1001 Salary:15000 Manager 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) salary (4) manager. Така че въз основа на тази позиция ние дефинираме целевата променлива.
- Кодов ред 18: Изпълнение на заявката чрез DBMS_SQL.EXECUTE. Връща броя на обработените записи.
- Кодов ред 19-33: Извличане на записите с помощта на цикъл и показване на същите.
- Кодов ред 20: DBMS_SQL.FETCH_ROWS ще извлече един запис от обработените редове. Може да се извиква многократно, за да извлече всички редове. Ако не може да извлече редове, ще върне 0, като по този начин ще излезе от цикъла.
Oбобщение
В този раздел обсъдихме динамичния SQL и начините за изпълнение на DYNAMIC SQL. Също така видяхме различните стъпки при изпълнение на динамичния SQL и по двата начина. Видяхме също примери, в които един и същ сценарий се обработва както в NDS, така и в DBMS_SQL начини за изпълнение по време на изпълнение.