说明：红色为改进后的
1.改进性能
create or replace delete_emp (emp_id number) as
begin
execute immediate 'delete from emp where empno =' || emp_id;
end;
create or replace delete_emp (emp_id number) as
begin
execute immediate 'delete from emp where empno = :arg1' using emp_id;
end;
2.使用重复的占位符
create or replace insert_test(a varchar2,b varchar2) as
begin
execute immediate 'insert into test(col1,col2,col3) values (:arg1,:arg2,arg1)' using a,b,a;
end ;
create or replace insert_test(a varchar2,b varchar2) as
begin
execute immediate 'insert into test(col1,col2,col3) values (:arg1,:arg2,arg1)' using a,b; ---与前面的区别
end ;
3.使用游标属性
上面两个例子均使用隐性游标。
create or replace query_emp as
type table_id is table of emp.emp_id%type;
type table_name is table of emp.emp_name%type;
v_id table_id;
v_name table_name;
begin
execute immediate 'select emp_id,emp_name into v_id,v_name from emp';
if sql%nofound then
dbms_output.put_line('no data found');
else
dbms_output.put_line('有' || sql%rowcount ||'条记录！');
end if;
end;
4.如何传递NULL
create or replace update_emp as
begin
execute immediate 'update emp set dept_id = :arg1' using null;
end;
create or replace update_emp as
a_null varchar2(2);
begin
execute immediate 'update emp set dept_id = :arg1' using a_null;
end ;
阅读(1511) | 评论(0) | 转发(0) |
