Такой родной (native) SQL
(или “Такой нативный, такой наивный”;
“So Native, So Naive…”)

20 января 2001 г.

Владимир Пржиялковский,
преподаватель УКЦ УКЦ Interface Ltd.

Эта статья впервые была опубликована на сайте http://www.interface.ru/. 16.10.2000

Признаюсь, что эта заметка запоздала. Идея написать ее возникла у меня в голове год назад, если не больше, но как-то все откладывалось. За это время тема native SQL несколько раз уже возникала в русскоязычных источниках: в статье Павла Лузанова, помещенной в Русском internet-журнале по Oracle, издаваемом московским представительством Oracle, и в одном (или нескольких) из трех internet-форумах по Oracle на русском языке. Тем не менее, руководствуясь эмпирикой, гласящей, что много информации об Oracle на нашем родном языке не бывает, рискну добавить в этот разговор о родном SQL и лепту от себя.

Речь пойдет о динамическом 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 данные извлекаются построчно в отдельную запись.

А вот, что позволяет делать исключительно пакетный динамический SQL:

*   Поддерживать “Метод 4” пакетного SQL, при котором во время компиляции не фиксируется число извлекаемых столбцов или число переменных привязки. Метод 4 – наиболее сложный режим использования пакетного динамического SQL.

*   В Oracle8 – описывать столбцы динамического курсора так, чтобы те получали значения из столбцов индексированной (index-by) таблицы записей.

*   Работать с SQL-предложениями длиной более 32К (а желающие наверняка найдутся !)

*   Возвращать данные с помощью RETURNING в массив переменных, в то время как встроенный динамический SQL допускает использование RETURNING только в единственном запросе.

*   Повторно использовать динамические курсоры, что улучшает производительность.

*   Выполняться на клиентской части приложения, например, в Oracle Developer.

Судите сами, что вас больше устроит. Но, отказываться от DBMS_SQL полностью, кажется, еще не время.

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

В заключение еще один сравнительный пример старого и нового способа выполнения динамического 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. Такую удобную процедуру имеет смысл дать в распоряжение всем разработчикам.