Речь пойдет о динамическом SQL, без которого разработчику прожить, наверное, невозможно. Соизмеряя эти естественные желания пользователей со своими возможностями, фирма Oracle ввела в версии своего сервера 7.1 пакет DBMS_SQL. Если по каким-то причинам этот пакет оказался в вашей системе отсутствующим, его можно завести, воспользовавшись сценариями Dmbssql.sql (открытое описание интерфейса пакета) и Prvtsql.plb (зашифрованный текст тела пакета) в каталоге Rdbms\Admin. Несмотря на новшества более поздних версий его пока рано выбрасывать (об этом ниже), а кроме того он используется для целого ряда внутренних потребностей системы в большинстве конфигураций.

Так вот, в версии 8.1 появился еще один способ работы с динамическим SQL, называемый в документации native SQL. В рамках этой заметки “первый” динамический SQL будет для краткости называться “пакетным”, а “второй” – “встроенным”. Появление встроенного динамического SQL вызвало у многих разработчиков реакцию, по внешним проявлениям сильно смахивающую на вздох облегчения. Причина станет ясна из следующей сравнительной иллюстрации.

Сравнительный пример пакетного и встроенного динамического SQL

Для иллюстрации пакетного (старого) способа работы динамического SQL можно воспользоваться готовым примером, имеющимся в тексте Dbmssql.sql. Чтобы можно было пользоваться широко известной таблицей сотрудников пользователя SCOTT, немного откорректируем и чуть упростим этот пример, так что в результате получится следующее:

SQL> create or replace procedure copy(source in varchar2,
destination in varchar2) is
-- This procedure copies rows from a given source table to 
-- a given destination table assuming that both source and destination 
-- tables have the following columns: 
-- - ENAME of type VARCHAR2(30), 
-- - HIREDATE of type DATE. 
ename varchar2(30);
hiredate date; 
source_cursor integer; 
destination_cursor integer; 
rows_processed integer; 
begin 
-- prepare a cursor to select from the source table 
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,'select ename, hiredate from ' ||
source, 
dbms_sql.native); 
dbms_sql.define_column(source_cursor, 1, ename, 30); 
dbms_sql.define_column(source_cursor, 2, hiredate); 
rows_processed := dbms_sql.execute(source_cursor); 
-- 
-- prepare a cursor to insert into the destination table 
destination_cursor := dbms_sql.open_cursor;
dbms_sql.parse(destination_cursor, 
'insert into ' || destination || 
' values (:ename, :hiredate)', 
dbms_sql.native); 
-- 
-- fetch a row from the source table and
-- insert it into the destination table 
loop 
if dbms_sql.fetch_rows(source_cursor)>0 then 
-- get column values of the row 
dbms_sql.column_value(source_cursor, 1, ename);
dbms_sql.column_value(source_cursor, 2, hiredate); 
-- bind the row into the cursor which insert 
-- into the destination table 
dbms_sql.bind_variable(destination_cursor, 'ename', ename);
dbms_sql.bind_variable(destination_cursor, 'hiredate', hiredate); 
rows_processed := dbms_sql.execute(destination_cursor); 
else 
-- no more row to copy 
exit; 
end if; 
end loop; 
-- 
-- commit and close all cursors 
commit; 
dbms_sql.close_cursor(source_cursor); 
dbms_sql.close_cursor(destination_cursor); 
exception 
when others then 
if dbms_sql.is_open(source_cursor) then
dbms_sql.close_cursor(source_cursor); 
end if; 
if dbms_sql.is_open(destination_cursor) then 
dbms_sql.close_cursor(destination_cursor); 
end if; 
raise; 
end; 
/ 
Procedure created. 

Теперь можно создать проверочную таблицу и выполнить процедуру:

SQL> CREATE TABLE emp1 AS SELECT ename, hiredate FROM emp WHERE 1=2; 
Table created. 
SQL> EXEC copy('emp','emp1'); 
PL/SQL procedure successfully completed. 

Выполнив SELECT * FROM emp1, можно убедиться, что все сотрудники скопировались.

А вот какой пример могла бы поместить фирма Oracle рядом для иллюстрации использования встроенного SQL:

SQL> create or replace procedure copynative(source in varchar2, 
destination in varchar2) is 
-- This procedure copies rows from a given source table to 
-- a given destination table assuming that both source and destination 
-- tables have the following columns: 
-- - ENAME, 
-- - HIREDATE. 
begin 
execute immediate 'insert into ' || destination || 
' select ename, hiredate from ' || source; 
-- commit 
commit; 
end; 
/ 
Procedure created. 
 

Теперь можно обнулить нашу “табличку для битья” и запустить новую процедуру:

SQL> TRUNCATE TABLE emp1; 
Table truncated. 
SQL> EXEC copynative('emp','emp1');
PL/SQL procedure successfully completed. 
 

…И результат тот же.

Не правда ли, отличия разительны? Обратите внимание, что несмотря на пропуск предложения EXCEPTION, второй текст функционально ничуть не уже первого. В данном случае он даже имеет дополнительную общность, так как не требует указания типа копируемых полей. Причем, если кому-то понравится возможность получения результирующего числа обработанных строк (использованная лишь формально в первом примере), то второй пример можно модифицировать так:

SQL> create or replace function fcopynative(source in varchar2, 
destination in varchar2)
return integer is 
-- comments … 
begin 
execute immediate 'insert into ' || destination || 
' select ename, hiredate from ' || source; 
return sql%rowcount; 
-- commit 
commit; 
end; 
/ 

Новые возможности

Для работы со встроенным динамическим SQL используются следующие конструкции:

EXECUTE IMMEDIATE SQL_string 
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument 
[, [IN | OUT | IN OUT] bind_argument]...]; 

Плюс три конструкции специально для работы с запросами, порождающими множественные результаты:

OPEN {cursor_variable | :host_cursor_variable} FOR SQL_string  
[USING bind_argument[, bind_argument]...]; 
FETCH {cursor_variable | :host_cursor_variable}
   INTO {define_variable[, define_variable]... | record};
CLOSE {cursor_variable | :host_cursor_variable}; 

Выглядит, хотя и сложее, чем в примере выше, но все еще проще, чем правила и конструкции DBMS_SQL. За дальнейшими подробностями можно обратиться к документации.

Свойства старого и нового способа

Какие особенности есть у старого и нового способа работы с динамическим SQL? Вот, что позволяет делать встроенный динамический SQL:

• Работать со всеми без исключения типами данных Oracle, включая и типы объектов, заданные пользователем, и типы коллекции (переменные массивы, вложенные таблицы, индексированные таблицы). DBMS_SQL позволяет работать лишь с типами данных, совместимыми с Oracle7.
• Извлекать множественные данные (серию строк) непосредственно в конструкцию PL/SQL. В DBMS_SQL данные извлекаются построчно в отдельную запись.

• Поддерживать “Метод 4” пакетного SQL, при котором во время компиляции не фиксируется число извлекаемых столбцов или число переменных привязки. Метод 4 – наиболее сложный режим использования пакетного динамического SQL.
• В Oracle8 – описывать столбцы динамического курсора так, чтобы те получали значения из столбцов индексированной (index-by) таблицы записей.
• Работать с SQL-предложениями длиной более 32К (а желающие наверняка найдутся !)
• Возвращать данные с помощью RETURNING в массив переменных, в то время как встроенный динамический SQL допускает использование RETURNING только в единственном запросе.
• Повторно использовать динамические курсоры, что улучшает производительность.
• Выполняться на клиентской части приложения, например, в Oracle Developer.

Еще один маленький, но показательный пример

В заключение еще один сравнительный пример старого и нового способа выполнения динамического SQL. Он хорош тем, что (а) компактен и (б) утилитарен. Автор примера – Стивен Фойерстин . Допустим, мы хотим написать процедуру, динамически запускающую на выполнение указанное в виде текста SQL-предложение. Вот какое решение может быть для пакетного SQL:

   CREATE OR REPLACE PROCEDURE runddl (ddl_in IN VARCHAR2)
   /* Pre Oracle8i implementation */
   IS
   cur INTEGER:= DBMS_SQL.OPEN_CURSOR;
   fdbk INTEGER;
   BEGIN
   DBMS_SQL.PARSE (cur, ddl_in, DBMS_SQL.NATIVE);
   
   fdbk := DBMS_SQL.EXECUTE (cur);
   
   DBMS_SQL.CLOSE_CURSOR (cur);
   EXCEPTION
   WHEN OTHERS
   THEN 
   DBMS_OUTPUT.PUT_LINE (
   'RunDDL Failure on ' || ddl_in);
   DBMS_OUTPUT.PUT_LINE (SQLERRM);
   DBMS_SQL.CLOSE_CURSOR (cur);
   END;
   /
   
   

А вот, какое решение может быть получено с помощью встроенного SQL:

   
   CREATE OR REPLACE PROCEDURE runddl81 (ddl_in IN VARCHAR2)
   AUTHID CURRENT_USER 
   IS
   BEGIN
   EXECUTE IMMEDIATE ddl_in;
   END;
   /
 

Заметим здесь еще одну конструкцию: AUTHID CURRENT_USER. Она позволяет запускать runddl81 любому пользователю при том, что будут соблюдаться именно его полномочия по работе с БД (об этом подробнее см. в “Новое в 8i: полномочия предъявителя в PL/SQL”. То есть SCOTT может выдать

EXEC runddl81(‘create table newone (rightnow DATE)’);

И новая табличка заведется у него; когда же точно такое предложение выдаст DEMO, то появится таблица DEMO.NEWONE. Такую удобную процедуру имеет смысл дать в распоряжение всем разработчикам.

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Отправить ссылку на страницу по e-mail