DECLARE
sql_stmt VARCHAR2(200);
emp_id NUMBER(4) := 7566;
emp_rec scott.emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE bonus2 (id NUMBER, amt NUMBER)'; sql_stmt := 'SELECT * FROM scott.emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
END;
关于这个动态sql的执行机制,就是他
sql_stmt VARCHAR2(200);
emp_id NUMBER(4) := 7566;
emp_rec scott.emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE bonus2 (id NUMBER, amt NUMBER)'; sql_stmt := 'SELECT * FROM scott.emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
END;
关于这个动态sql的执行机制,就是他
不就和一般sql一样理解就可以了
例如
EXECUTE IMMEDIATE 'select count(*) into num from ' || tabname ||';
tabname是可变的
Oracle编译PL/SQL程序块分为两个种: 其一为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型; 另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时, 那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。 通常,静态SQL采用前一种编译方式,而动态SQL采用后一种编译方式。
2、动态sql 简而言之,动态sql就是程序运行时才确定要执行的sql语句,并且应该可以根据需要变化输入参数、输出参数。 Oracle中提供了两种方式来执行动态SQL: (1)、8i以及8i以前:dbms_sql (2)、9i:本地动态sql(execute immediate)。
3、 为何使用动态sql 主要从以下方面考虑使用哪种方式: (1) 是否知道涉及的列数和类型 dbms_sql包括了一个可以“描述”结果集的存储过程(dbms_sql.describe_columns),而本地动态sql没有。 (2) 是否知道可能涉及的绑定变量数和类型 dbms_sql允许过程化的绑定语句的输入,而本地动态sql需要在编译时确定。 (3) 是否使用“数组化”操作(array processing) dbms_sql允许,而本地动态sql基本不可以,但可以用其他方式实现(对查询可用fetch bulk collect into,对insert等,可用一个begin … end块中加循环实现)。 (4) 是否在同一个会话中多次执行同一语句 dbms_sql可以分析一次执行多次,而本地动态sql会在每次执行时进行软分析。 (5) 是否需要用ref cursor返回结果集 仅本地动态sql可用ref cursor返回结果集。 4、如何使用动态sql (一)、dbms_sql实现: (1) 调用open_cursor获得一个游标句柄; (2) 调用parse分析语句。一个游标句柄可以用于多条不同的已分析语句,但一个时间点仅一条有效; (3) 调用bind_variable或bind_array来提供语句的任何输入; (4) 若是一个查询(select语句),调用define_column或define_array来告知oracle如何返回结果; (5) 调用execute执行语句; (6) 若是一个查询,调用fetch_rows来读取数据。可以使用column_value从select列表根据位置获得这些值; (7) 否则,若是一个pl/sql块或带有return子句的dml语句,可以调用variable_value从块中根据变量名获得out值; (8) 调用close_cursor. 注意这里对任何异常都应该处理,以关闭游标,防止泄露资源。 (二)、本地动态sql语法: execute immediate ‘动态SQL语句’ [into {变量1, 变量2, … 变量n | 记录体}] [using [in | out | in out] 绑定变量1, … 绑定变量n] [{returning | return} into 输出1 [, …, 输出n]…];
对这一语法作如下说明: 1) 动态SQL是指DDL和不确定的DML(即带参数的DML)。
2) 绑定参数列表为输入参数列表,即其类型为in类型,在运行时刻与动态SQL语句中的参数(实际上占位符,可以理解为函数里面的形式参数)进行绑定。
3) 输出参数列表为动态SQL语句执行后返回的参数列表。 4) 由于动态SQL是在运行时刻进行确定的,所以相对于静态而言,其更多的会损失一些系统性能来换取其灵活性。
5) 注意本地动态sql仅支持弱类型ref cursor,即对于ref cursor,不支持bulk collect。
使用技巧: 1. execute immediate将不会提交一个DML事务执行,应该显式提交 如果通过execute immediate处理DML命令,那么在完成以前需要显式提交或者作为execute immediate自己的一部分。 如果通过execute immediate处理DDL命令,它提交所有以前改变的数据。 2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors. 3. 当执行SQL语句时,不要用分号,当执行PL/SQL块时,在其尾部用分号. 下面的例子展示了所有用到execute immediate的可能方面.1. 在PL/SQL运行DDL语句 begin
execute immediate 'create table temp(id number,name varchar2(5))';
end; 2. 给动态语句传值(USING 子句) declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end; 说明:
动态SQL语句使用了占位符“:1”,其实它相当于函数的形式参数,使用“:”作为前缀,
然后使用using语句将“50”在运行时刻将“:1”给替换掉,这里“50”相当于函数里的实参。3. 从动态语句检索值(INTO子句) declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end; 4. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型。黓认为IN类型,其它类型必须显式指定 declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end; 5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量 declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end; 6. 传递并检索值.INTO子句用在USING子句前 declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end; 7. 多行查询选项.对此选项用insert语句填充临时表,用临时表进行进一步的处理,也可以用REF cursor纠正此缺憾.
declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
'select empno, ename from emp ' ||
'where sal > :1'
using l_sal;
commit;
end; 对于处理动态语句,execute immediate 比以前可能用到的更容易并且更高效.
当意图执行动态语句时,适当地处理异常更加重要.应该关注于捕获所有可能的异常.
--其实跟静态的原理差不多,只是执行的方式不一样
DECLARE
sql_stmt VARCHAR2(200);
emp_id NUMBER(4) := 7566;
emp_rec scott.emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE bonus2 (id NUMBER, amt NUMBER)';
--把'CREATE TABLE bonus2 (id NUMBER, amt NUMBER)'
--解析成CREATE TABLE bonus2 (id NUMBER, amt NUMBER),然后运行它 sql_stmt := 'SELECT * FROM scott.emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
--前面的一样就是using 的时候调用emp_id NUMBER(4) := 7566的值;
END;