在Oracle中执行动态SQL.docxVIP

在Oracle中执行动态SQL一、使用动态游标实现Sql代码 ?1、声明动态游标? ??TYPE?i_cursor_type?IS?REF?CURSOR;? ??2、声明游标变量? ??my_cursor?i_cursor_type;? ??3、使用游标? ??n_deptno:=20;? ??dyn_select?:=?’select?empno,ename?from?emp?where?deptno=’||n_deptno;? ??OPEN?my_cursor?FOR?dyn_select;?LOOP?? ????FETCH?my_cursor?INTO?n_empno,v_ename;??EXIT?WHEN?my_cursor%NOTFOUND;??--用n_empno,v_ename做其它处理??--? ??END?LOOP;? ??CLOSE?my_cursor;???1、声明动态游标 TYPE i_cursor_type IS REF CURSOR; 2、声明游标变量 my_cursor i_cursor_type; 3、使用游标 n_deptno:=20; dyn_select := ’select empno,ename from emp where deptno=’||n_deptno; OPEN my_cursor FOR dyn_select; LOOP FETCH my_cursor INTO n_empno,v_ename; EXIT WHEN my_cursor%NOTFOUND; --用n_empno,v_ename做其它处理 -- END LOOP; CLOSE my_cursor; ?Sql代码 ?declare? ????type?i_cursor_type?is?ref?cursor; ????my_cursor?i_cursor_type; ????n_deptno?number(4); ????n_empno??number(4); ????n_ename??varchar2(100); ????dyn_select?varchar2(100); ??? ??begin????n_deptno:=20; ????dyn_select?:=select?empno,ename?from?scott.emp?where?deptno=||n_deptno; ????open?my_cursor?for?dyn_select; ????loop? ?????????fetch?my_cursor?into?n_empno,n_ename; ?????????exit?when?my_cursor%NOTFOUND; ?????? ????end?loop; ????close?my_cursor; ??end;??declare type i_cursor_type is ref cursor; my_cursor i_cursor_type; n_deptno number(4); n_empno number(4); n_ename varchar2(100); dyn_select varchar2(100);begin n_deptno:=20; dyn_select :=select empno,ename from scott.emp where deptno=||n_deptno; open my_cursor for dyn_select; loop fetch my_cursor into n_empno,n_ename; exit when my_cursor%NOTFOUND; end loop; close my_cursor;end;?二、使用?EXECUTE?IMMEDIATE EXECUTE?IMMEDIATE代替了以前Oracle8i中DBMS_SQL?package包.它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，EXECUTE?IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE?IMMEDIATE,因为它获的收益在包之上。使用技巧 1.?EXECUTE?IMMEDIATE将不会提交一个DML事务执行，应该显式提交 ?如果通过EXECUTE?IMMEDIATE处理DML命令，那么在完成以前需要显式提交或者作为EXECUTE?IMMEDIATE自己的一部分.?如果通过EXECUTE?IMMEDIATE处理DDL命令,它提交所有以前改变的数据 2.?不