oracel中sql语句和pl/sql语句使用绑定变量

作者：袖梨 2022-06-29

关于绑定变量的用法：

之前的文章介绍了绑定变量对于系统的重要性，这里对绑定变量的使用做进一步的分析和说明。

1）在sql语句中如何带入bind value

SQL> variable x number;
SQL> exec :x:=100;

PL/SQL procedure successfully completed.

SQL> select * from t where id=:x;

no rows selected

SQL> exec :x:=101;

SQL> select * from t where id=:x;

no rows selected

SQL> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS from v$sql where sql_text like 'select * from t where id=%';

SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS
------------- ---------------------------------------- ---------- -----------
3yxwagyspybax select * from t where id=:x 1 1

SQL> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS from v$sql where sql_text like 'select * from t where id=%';

SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS
------------- ---------------------------------------- ---------- -----------
3yxwagyspybax select * from t where id=:x 2 2
对于sql语句直接是用类似variable的方式定义，然后exec赋值，就可以在sql语句中使用绑定变量。

需要注意&x的方式并不是使用绑定变量的正确方式，请看下列例子：

SQL> select * from t where id=&op;
Enter value for op: 10
old 1: select * from t where id=&op
new 1: select * from t where id=10

no rows selected

SQL> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS from v$sql where sql_text like 'select * from t where id=%';

SQL_ID        SQL_TEXT                                 EXECUTIONS PARSE_CALLS
------------- ---------------------------------------- ---------- -----------
6wxj4tkdajgbx select * from t where id=10                       1           1
3yxwagyspybax select * from t where id=:x                       2           2
关于pl/sql中bind value的方式：

pl/sql中有两种变量的形式，一个是pl/sql定义的变量，另一种是绑定变量，这两种变量都可以让优化器减少sql的解析次数，下面简单看下：

1 pl/sql中定义的变量：

SQL> declare
2 a number;
3 v_name varchar2(128);
4 begin
5 for i in 1..100 loop
6 delete from t where object_id=i+1 returning object_name into v_name;
7 dbms_output.put_line(v_name);
8 commit;
9 end loop;
end;
11 /

SQL> select sql_id,sql_text from v$sql where lower(sql_text) like '%delete from t%';

SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
g3bug3dp68bqt DELETE FROM T WHERE OBJECT_ID=:B1 +1 RETURNING OBJECT_NAME INTO :O0
391g7kanthzt9 declare a number; v_name varchar2(128); begin for i in 1..100 loop delete from t where object_id=i+1
returning object_name into v_name; dbms_output.put_line(v_name); commit; end loop; end;
oracle这里将代码全部解析为DELETE FROM T WHERE OBJECT_ID=:B1 +1 RETURNING OBJECT_NAME INTO :O0的cursor，减少了优化器反复解析sql而造成的资源消耗。

2 pl/sql中的绑定变量：
pl/sql程序中是以execute immediate [带绑定变量的目标sql] using [对应绑定变量的具体输入值]的这种语法，其中using后面接的就是前面sql的绑定变量的值，有多少个绑定变量后面using就接多少个具体值，这些值是按照顺序来代替前面的sql中的绑定变量的值。

SQL> set serveroutput on;
SQL> declare
2 v_name varchar2(32);
3 begin
4 execute immediate 'select object_name from xiaoyu01 where object_id=:1 and owner=:1' into v_name using 10,'SYS';
5 dbms_output.put_line(v_name);
6 end;
7 /

PL/SQL procedure successfully completed.
这里的into是在pl/sql中一种赋值给变量的方式，这个变量v_name并不是绑定变量

注意1：绑定变量在pl/sql中只能用using来赋值，而pl/sql中的变量则是用into来赋值

注意2：跟sql语句中绑定不同的地方是，pl/sql语句中即使绑定变量的名称完全相同也不一定表示这两个绑定变量的具体值是相同，pl/sql中的绑定变量完全按照顺序读取using后面的具体值。

注意3：execute immediate sql_text是声明动态sql语句的，这里的sql_text必须满足sql引擎的语法，而非动态sql则必须满足pl/sql存储引擎的语法。

注意4：动态sql语句虽然被pl/sql程序体所封装，sql引擎依然会按照动态sql的原文本（不会进行大小写转换）来生成单独的cursor对象。

SQL> select sql_id,sql_text from v$sql where sql_text like 'select object_name from xiaoyu01%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
g9zuzpp1r1jh0
select object_name from xiaoyu01 where object_id=:1 and owner=:1
pl/sql中的非动态sql语句如何生成cursor cache：
sql和pl/sql其实是由两个各自的存储引擎来工作的，pl/sql中的sql语句会交给sql引擎来处理，对于动态sql语句通过上面的测试看出来oracle是根据动态sql语句的原sql_text文本来生成cursor，而如果不是动态sql语句，oracle是如何存储这个pl/sql对应的cursor了，其实这里有两种情况：

1 如果是select into类型的语句，oracle存储在cursor cache中的就只有这个pl/sql程序体

SQL> declare
2 v_name varchar2(64);
3 begin
4 select object_name into v_name from t where rownum<2;
end;
6 /

PL/SQL procedure successfully completed.

SQL> select sql_id,sql_text from v$sql where lower(sql_text) like '%select object_name into v_name from t where rownum<2%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
1chbdafu44tcy declare     v_name varchar2(64);     begin     select object_name into v_name from t where rownum<2;
               end;

bnprfwc84nv7u select sql_id,sql_text from v$sql where lower(sql_text) like '%select object_name into v_name from t
where rownum<2%'
2 如果是DML类型的语句，oracle即会存储原来的pl/sql，也会将pl/sql中原来的DML语句全部修改为大写的，从而生成一个新的cursor存储在cursor cache中

SQL> alter system flush shared_pool;

System altered.

SQL> declare
2      v_name varchar2(64);
3      begin
4      delete from t where rownum<2;
end;
6     /

PL/SQL procedure successfully completed.
SQL> select sql_id,sql_text,first_load_time from v$sql where lower(sql_text) like '%delete from t where rownum<2%';

SQL_ID        SQL_TEXT                                                                                             FIRST_LOAD_TIME
------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------
cffprycyhasgg declare     v_name varchar2(64);     begin     delete from t where rownum<2; end;                    2015-05-22/00:35:14
3h7ddvwzdg35d DELETE FROM T WHERE ROWNUM<2                                                                         2015-05-22/00:35:14
1k8m36uynvrpy select sql_id,sql_text,first_load_time from v$sql where lower(sql_text) like '%delete from t where r 2015-05-22/00:36:14
              ownum<2%'
pl/sql程序体中DML语句使用绑定变量：

1 insert into语句中使用绑定变量

SQL> declare
2 begin
3 execute immediate 'insert into t(object_id,object_name) values(:1,:2)' using 100,'xiaoyu';
4 commit;
5 end;
6 /

PL/SQL procedure successfully completed.
2 delete from语句中使用绑定变量

SQL> declare
2 begin
3 execute immediate 'delete from t where object_id=:1' using 100;
4 commit;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;
SQL> declare
2 v_name varchar2(128);
3 v_sql varchar2(128);
4 begin
5 v_sql:='delete from t where rownum=:1 returning object_name into :2';
6 execute immediate v_sql using 1 returning into v_name;
7 dbms_output.put_line(v_name);
8 commit;
9 end;
10 /

C_COBJ#

PL/SQL procedure successfully completed.

SQL> select sql_text,sql_id from v$sql where sql_text like 'delete from t where rownum=:1%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
delete from t where rownum=:1 returning object_name into :2
784c1dh672vvc
补充returning into的知识：returning [column_name] into [variable]就是将dml语句中影响的数据行的列值记录到变量中，在pl/sql procedure和sql语句中都可以按照语法来使用：

PL/SQL procedure中能够返回insert、update、delete操作影响的数据行的列值,记录修改的数据行的列值是用returning into的方式来记录的。

SQL> declare
2 v_name varchar2(128);
3 begin
4 delete from t where rownum<2 returning object_name into v_name;
5 dbms_output.put_line(v_name);
6 commit;
7 end;
8 /

PROXY_ROLE_DATA$

PL/SQL procedure successfully completed.
在sql语句中也可以直接加上returning into记录修改行的某些列值：

SQL> variable v_name varchar2(32);
SQL> delete from t where rownum<2 returning object_name into :v_name;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> print :v_name;

V_NAME
--------------------------------------------------------------------------------
I_IND1
由于pl/sql引擎每次处理sql语句都需要和sql引擎交换（这里sql语句不仅仅是动态sql语句，就是select、insert、update、delete等sql语句），而如果批量的存储引擎交换是很消耗资源的，比如如下两种形式：

1 显式游标或参考游标需要fetch操作时，循环操作需要pl/sql引擎来处理，而fetch一条记录对应要执行的sql语句则需要sql引擎来处理
2 显式游标或参考游标的循环内部需要执行sql语句时

如果能一次fetch一批记录或者一次执行一批sql语句，则可以有效减少pl/sql引擎和sql引擎的交互次数，当然pl/sql代码的性能也会得到一定的提升。

简单说下开发相关的知识，%type和%rowtype、type typename is table of datatype 、sys_refcursor、constant pls_integer

%type是用来说明一个变量的类型与另一个已经定义的变量或者表的某一列类型相同，oracle就提供了%Type定义方式，如果被参照的那个变量被修改后，这个新变量也会马上自动修改，比如定义eName emp.name%type，如果emp表的name字段类型被修改了，变量ename也会自动修改
%rowtype是用来定义一个变量于表中的所有数据类型一致，比如我们要获取表中一行的所有数据，如果每个列都定义一个变量%type太麻烦，可以直接定义v_emp emp%rowtype,而其中的v_emp.name就是emp表中对应那一行数据的name列的值
sys_refcursor是系统自带的定义游标的方式，跟cursor c is select 。。。 from emp等不同的是，sys_refcursor多用于动态游标，就是open状态时才予以赋值给游标，而cursor那种定义方式是在定义时就知道了游标的具体值
type typename is table of datatype是声明一种自定义的数据类型(还可以声明数组），比如type type1 is table of emp.ename%type;type type2 is table of emp$rowtype;type type3 is table of varchar2(32);

介绍上面的开发方面的知识后，来看看如何批量fetch游标给具体的数组变量例子：

declare
cur_t sys_refcursor;
type typelist is table of varchar2(32); 定义数组类型typelist
type_name typelist;
v_sql varchar2(100);
CN_BATCH_SIZE constant pls_integer := 1000; constant pls_integer定义常量
begin
v_sql:='select name from t where id=:id';
open cur_t for v_sql using 100; 给游标cur_t赋值，动态游标是打开时候来赋值，这点与静态游标不同
loop
fetch cur_t bulk collect into type_name limit CN_BATCH_SIZE; 将游标再赋值给数组，这里采用了fetch cursor_name bulk collect into 自定义的数组 limit CN_BATCH_SIZE来批量fetch
for i in 1..type_name.count loop 循环数组输出，数组.count表示数据的总长度，数组(i)表示是哪个数组
dbms_output.put_line(type_name(i));
end loop;
exit when type_name.count end loop;
close cur_t;
end;
/
批量执行sql的崔华的blog上有相应的文章模板，这里只说明批量执行的语法：
forall 1 in 1..[自定义数组的长度]
execute immediate [带绑定变量的目标sql] using [对应绑定变量的具体输入值]；