plsql中使用绑定变量

来源：互联网发布：搜狗输入法linux 编辑：程序博客网时间：2024/04/25 05:07

摘自《基于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();

0 0