Признаюсь, что эта заметка запоздала. Идея написать ее возникла у меня в голове год назад, если не больше, но как-то все откладывалось. За это время тема 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. Такую удобную процедуру имеет смысл дать в распоряжение всем разработчикам.