(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Такой родной (native) SQL

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

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



 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Oracle »
Написать редактору 
 Рекомендовать » Дата публикации: 16.10.2000 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Standard Edition 2 Named User Plus License
Oracle Database Personal Edition Named User Plus Software Update License & Support
Oracle Database Standard Edition 2 Processor License
Oracle Database Personal Edition Named User Plus License
ESET Secure Authentication newsale for 5 user, лицензия на 1 год
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
OS Linux для начинающих. Новости + статьи + обзоры + ссылки
СУБД Oracle "с нуля"
Утиль - лучший бесплатный софт для Windows
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Сайт инструмент (1)
Я бывший программист пользовался 1 сайтом проверенным он мне действительно помог я блогодоря...
 
Где взять лицензионный ключ для AllFusion Process Modeler (BPwin) 7? (5)
Выручайте!!! где найти ключ, ужасно срочно нужна программа. заранее спасибо!
 
работа на дому! (5)
Доброго времени суток дорогие друзья. Многоуровневый маркетинг окончательно признан...
 
Регистрация на Oracle.com (4)
Сразу прошу прощения за тупой вопрос, но вчера зарегался на oracle.com (чтоб 9i слить себе...
 
Ищу кодера (2)
Добрый день! Ищу кодера который сможет сделать копии сайтов. Сколько будет стоить скопировать...
 
 
 



    
rambler's top100 Rambler's Top100