《精通Oracle10gPLSQL编程》14、开发动态SQL

动态SQL示例：

DDL： create table temp(cola int, colb varchar2(10));
DCL： grant select on emp to smith;
占位符： delete from emp where sal >: a;
占位符： select ename,sal from emp where empno =: 1;

处理非查询语句：EXECUTE IMMEDIATE

execute immediate dynamic_string
[into [define_variable]|[record]]
[using [in|out|in out] bind_argument]
[{returning|return} into bind_argument];

处理多行查询语句：OPEN-FOR , FETCH , CLOSE

declare
type empcurtyp is ref cursor;
emp_cv empcurtyp;
emp_record emp%rowtype;
sql_stat varchar2(100);
begin
sql_stat := 'select * from emp where deptno = :dno';
open emp_cv for sql_stat using &dno;
loop
fetch emp_cv into emp_record;
exit when emp_cv%notfound;
dbms_output.put_line(emp_record.ename || emp_record.sal);
end loop;
close emp_cv;
end;

在动态SQL中使用BULK子句

通过使用BULK子句，可以加快批量数据的处理速度，提高应用程序性能。