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

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

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. 

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. 

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; 
/ 

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

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 }; 

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

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

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

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

   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; /     

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

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