execute immediate [带绑定变量的目标sql] using [对应绑定变量的具体输入值];
举例1:
declare
vc_name varchar2(10);
begin
execute immediate 'select ename from emp where empno=:1' into vc_name using 7369;
dbms_output.put_line(vc_name);
end;
/
举例2:
declare
vc_column varchar2(10);
vc_sql varchar2(4000);
n_temp number;
vc_ename varchar2(10);
begin
vc_column := 'enpno';
vc_sql := 'delete from emp where '||vc_column||' = :1 returning ename into :2';
execute immedaite vc_sql using 7369 returning into vc_ename;
dbms_output.put_line(vc_ename);
commit;
end;
/
举例3:
declare
cur_emp sys_refcursor;
vc_sql varchar2(4000);
type namelist is table of varchar2(10);
enames namelist;
CN_BATCH_SIZE constant pls_integer := 1000;
begin
vc_sql := 'select ename from emp where empno > :1';
open cur_emp for vc_sql using 7900;
loop
fecth cur_emp bulk connect into enames limit CN_BATCH_SIZE;
for i in 1..enames.count
loop
dbms_output.put_line(enames(i));
end loop;
exit where ename.count < CN_BATCH_SIZE;
end loop;
close cur_emp;
end;
/
举例1:
declare
vc_name varchar2(10);
begin
execute immediate 'select ename from emp where empno=:1' into vc_name using 7369;
dbms_output.put_line(vc_name);
end;
/
举例2:
declare
vc_column varchar2(10);
vc_sql varchar2(4000);
n_temp number;
vc_ename varchar2(10);
begin
vc_column := 'enpno';
vc_sql := 'delete from emp where '||vc_column||' = :1 returning ename into :2';
execute immedaite vc_sql using 7369 returning into vc_ename;
dbms_output.put_line(vc_ename);
commit;
end;
/
举例3:
declare
cur_emp sys_refcursor;
vc_sql varchar2(4000);
type namelist is table of varchar2(10);
enames namelist;
CN_BATCH_SIZE constant pls_integer := 1000;
begin
vc_sql := 'select ename from emp where empno > :1';
open cur_emp for vc_sql using 7900;
loop
fecth cur_emp bulk connect into enames limit CN_BATCH_SIZE;
for i in 1..enames.count
loop
dbms_output.put_line(enames(i));
end loop;
exit where ename.count < CN_BATCH_SIZE;
end loop;
close cur_emp;
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28878983/viewspace-2137128/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28878983/viewspace-2137128/