Oracle动态SQL

动态SQL有两种实现方法：

• 原生动态SQL(DNS):execute immediate
• DBMS_SQL包实现动态SQL

1. 原生动态SQL

• 执行DDL语句

begin
execute immediate 'create table BOOK
(
  bookname    VARCHAR2(64),
  publisherid VARCHAR2(64)
)';
end;

• INSERT/UPDATE/DELETE
  以INSERT为例

begin
execute immediate 'insert into book(bookname,publisherid) values(:val1,:val2)' using 'A',1;
commit;
end;

using用于将参数A,1分别与SQL字符串中的占位符 :val1和:val2绑定,故上述语句执行结果为插入一条A,1的记录到表book中

• SELECT

declare
type settest is table of book%rowtype index by pls_integer;
testinstance settest