摘自《基于oracle的sql优化》
实例:
declare
vc_name varchar2(10);
begin
execute immediate 'select ename from emp where empno = :1' into v_name using 1234;
dbms_output.put_line(v_name);
end;
/
declare
v_sql varchar2(1000);
v_sql2 varchar2(10000);
n_num number;
n_num2 number;
begin
v_sql := 'insert into emp(empno,ename,job)values(:1,:2,:3';
execute immediate v_sql using 1234,'test','aaa';
n_num := sql%rowcount;
commit;
end;
/
declare
v_column varchar2(10);
v_sql varchar2(2000);
v_ename varchar2(200);
begin
v_column := 'empno';
v_sql := 'delete from emp where '|| v_column || ' = :1 returning ename into :2';
execute immediate v_sql using 123 returing into v_name;
dbms_output.put_line(v_name);
commit;
end;
/
pl/sql中批量绑定的用法
批量fetch的例子
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
v_sql := 'seect ename from emp wehre empno > :1';
open cur_emp for vc_sql usring 7900;
loop
fetch cur_emp bulk collect into enames limit CN_BATCH_SIZE;
for i in 1 .. enames.count loop
dbms_output.put_line(enames(i));
end loop;
exit when enames.count < CN_BATCH_SIZE;
end loop;
close cur_emp;
end;
/
实例二:
declare
CN_BATCH_SIZE constant pls_integer :=1000;
type typ_result is record
(empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sql%type,
comm emp.comm%type,
deptno emp.deptno%type,
rid urowid);
type typ_results is varrray(1000) of typ_result;
results typ_results;
cur_emp sys_refcursor;
vc_sql varchar2(4000) := 'select empno,ename,job,mgr,hiredaste,sal,comm,deptno,rowid from emp where empno>:1';
begin
open cur_emp for vc_sql using 7000;
loop
fetch cur_emp bulk collect into results limit CN_BATCH_SIZE;
for i in 1 .. results.count loop
execute immediate 'update emp set sal=sal+1 where rowid = :1' using results(i).rid;
end loop;
exit when results.count<CN_BATCH_SIZE;
end loop;
close cur_emp;
commit;
end;
/
java中的绑定变量);
String query = "select empno,ename from emp where empno = ?";
pstmt = connection.prepare,
dStatement(query);
pstmt.setInt(1,1234);
rs = pstmt.exectueQuery();
java中批量绑定
String dml = "update emp set sal = ? where empno = ?";
psmt = connection.prepareStatement(dml);
pstmt.clearBatch();
for (int i=0; i<UPDATE_COUNT; ++i)
{
pstmt.setInt(1,123);
pstmt.setInt(2,234);
pstmt.addBatch();
}
pstmt.executeBatch();
connection.commit();