DBMS_SQL包 执行动态SQL

amy_888

　　

DBMS_SQL
　　
Oracle lets you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This enables you to create more general-purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.
　　
 
 
 

CREATE OR REPLACE PROCEDURE copy (
source      IN VARCHAR2,
destination IN VARCHAR2) IS
id_var             NUMBER;
name_var           VARCHAR2(30);
birthdate_var      DATE;
source_cursor      INTEGER;
destination_cursor INTEGER;
ignore             INTEGER;
BEGIN
-- Prepare a cursor to select from the source table:
source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor,
'SELECT id, name, birthdate FROM ' || source,
DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);
ignore := 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 (:id_bind, :name_bind, :birthdate_bind)',
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, id_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var);
-- Bind the row into the cursor that inserts into the destination table. You
-- could alter this example to require the use of dynamic SQL by inserting an
-- if condition before the bind.
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var);
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var);
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind',
birthdate_var);
ignore := DBMS_SQL.EXECUTE(destination_cursor);
ELSE
-- No more rows 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;
/
 
例如，动态SQL可以让你创建一个程序上的表的名称，直到运行时才知道。
 
当然动态SQL有还有一种可以使用动态游标的方式来实现，但是在Oracle Froms里面不支持，改用上面的方式即可。
 
 
It's from http://www.csee.umbc.edu/portal/help/oracle8/server.815/a68001/dbms_sql.htm
 



DECLARE
vc_msg BINARY_INTEGER;
vc_istry VARCHAR2(1);
vc_tempSql VARCHAR2(200);
my_cur integer;
num integer;
vc_mrn  VARCHAR2(5);
BEGIN
my_cur := dbms_sql.open_cursor;  --创建游标
vc_tempSql := 'select t.mrn from temp_pl_m_asf t where t.asfno = 1000000001';
dbms_sql.parse(my_cur, vc_tempSql, 1);
dbms_sql.define_column(my_cur,1,vc_mrn,5);
num := dbms_sql.execute(my_cur);
loop
if dbms_sql.fetch_rows(my_cur) > 0 then
begin
dbms_sql.column_value(my_cur, 1, vc_mrn);
end;
else
exit;
end if;
end loop;
dbms_sql.close_cursor(my_cur);
END;
 创建动态游标