oracle 绑定变量小记

rogerxxx001-谌军

于 2023-08-15 09:58:26 发布

阅读量811

点赞数

CC 4.0 BY-SA版权

文章标签： java 数据库开发语言

本文链接：https://blog.youkuaiyun.com/weixin_44265429/article/details/132291532

本文讨论了PL/SQL中绑定变量的使用，如何通过绑定变量提高程序执行效率，特别是在大批量操作时。讲解了静态绑定和动态绑定的区别，以及如何利用它们减少上下文切换以优化性能，如使用fetchcursorwithbulkcollect和减少交互。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

绑定变量，主要好处，是提高程序执行速度，特别是大批量执行时。

declare

vc_name varchar2(10);

begin

execute immediate 'select ename from emp where empno= :1' into vc_name using 7876; --多个值用逗号分开

dbms_output.put_line(vc_name);

end;

所以在pl/sql中绑定变量的标准语法为：

execute immediate 【使用绑定变量的语句】 using 对应绑定变量的具体输入值;

declare

v1_name varchar2(10);
v2_name varchar2(10);

begin

       --select user_name,USER_ADDRESS from USER_INFO_MAIN where user_code=:1
       execute immediate 'select user_name,USER_ADDRESS from USER_INFO_MAIN where user_code=:1 and RELA_CELL=:2' into v1_name,v2_name using 81010001,14786528841; --多个值用逗号分开
           --execute immediate 'select ename from emp where empno= :1' into vc_name using 7876; --多个值用逗号分开

dbms_output.put_line(v1_name);
dbms_output.put_line(v2_name);

end;

插入语句的使用

declare
   vc_sql_1 varchar2(4000);
   vc_sql_2 varchar2(4000);
   n_temp_1 number;
   n_temp_2 number;
begin
vc_sql_1 := 'insert into sap(num_1,num_2) values(:1,:2)';
execute immediate vc_sql_1 using 7370,7788;
n_temp_1 := sql%rowcount;
vc_sql_2 := 'insert into sap(num_1,num_2) values(:1,:1)';
execute immediate vc_sql_2 using 7371,7799;
n_temp_2 := sql%rowcount;
dbms_output.put_line(to_char(n_temp_1+n_temp_2));
commit;
end；

--using 根据位置传入相关变量参数值

--动态sql可以使用绑定变量,returning 可以和带绑定变量的目标sql连用，目的把受该sql影响的行记录的对应字段值给取出来.
--eg
declare
    vc_column varchar2(10);
    vc_sql varchar2(4000);
    n_temp number;
    vc_name varchar2(10);
begin
   vc_column := 'empno';
   vc_sql :='delete from emp where ' || vc_column || ' = :1 returning ename into :2';
   execute immediate vc_sql using 7369 returning into vc_name;
   dbms_output.put_line(vc_ename);
   commit;
end;

主要优势，一次处理一批数据。

可以见到地将PL/SQL引擎看做专门用来处理PL/SQL代码块中除了sql之外的所有部分(eg:变量、复制、循环等)子系统，SQL引擎用来处理sql语句的子系统。这里的PL/SQL引擎和SQL引起上下文切换就是指他们之间的交互。

--减少交互，提高性能
fetch cursorname bulk collect into [自定义的属组] <limit CN_BATCH_SIZE>

--eg "forall" 表示一次执行一批sql
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
     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;

****************************
2.1.让Oracle自己绑定变量(也叫静态绑定变量)

set serverout on;

set timing on;
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
select count(*) into l_count from table1 where col_1=l_param1 andcol_2=l_param2;
dbms_output.put_line(l_count);
end;
/

在上面的情况(语句中使用变量)，Oracle会自己绑定变量，
即，如果参数保存在一个数组中，select语句放在一个循环中，select 语句只会编译一次。

2.2 .动态绑定变量

set serverout on;

set timing on;
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
l_sql:='select count(*) into :x from table1 where col_1=:y and col_2=:z ';
Execute Immediate l_sql into l_count using l_param1,l_param2;
dbms_output.put_line(l_count);
end;
/

这里强烈推荐使用静态绑定变量,有兴趣的话可以自己比较;

自己记录下，部分转自网络