Oracle Bind Graduation测试

SQL> truncate table t;
 
Table truncated
 
SQL> var n number;
SQL> var v vharchar2(2000);
VHARCHAR2(2000) unknown datatype
 ----声明一个2000的变量
SQL> var v varchar2(2000);
SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);
 
1 row inserted
n
---------
 
v
---------
 
SQL> commit;
 
Commit complete
 
SQL> select sql_text, sql_id, address, version_count, EXECUTIONS from v$sqlarea where sql_text like 'insert /*+ bind_date_length */%';
 
SQL_TEXT                                 SQL_ID        ADDRESS          VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ---------------- ------------- ----------
 
SQL> select sql_text, sql_id, address, version_count, EXECUTIONS from v$sqlarea where sql_text like '%insert /*+ bind_date_length */%';
 
SQL_TEXT                                 SQL_ID        ADDRESS          VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ---------------- ------------- ----------
 select sql_text, sql_id, address, versi 5pu17aqzuj510 000000006CDAA090             1          1
on_count, EXECUTIONS from v$sqlarea wher                                              
e sql_text like '%insert /*+ bind_date_l                                              
ength */%'                                                                            
 
 insert /*+ bind_date_length */ into t ( 6gd5ddr109hha 00000000724E4AB8             1          1
n,v) values (:n, :v)                                                                  
 
 select sql_text, sql_id, address, versi 3hznvywvuzu6v 000000006BB650C8             1          1
on_count, EXECUTIONS from v$sqlarea wher                                              
e sql_text like 'insert /*+ bind_date_le                                              
ngth */%'                                                                             
 
SQL> select sql_id, child_number, address, child_address, sqltype from v$sql where sql_id='6gd5ddr109hha';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE
------------- ------------ ---------------- ---------------- ----------
6gd5ddr109hha            0 00000000724E4AB8 00000000724E4758          6
 
SQL>  select * from v$sql_bind_metadata where address='00000000724E4758';
 
ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
00000000724E4758          2          1       2000          0 V
00000000724E4758          1          2         22          0 N
---可以看到变量分级到了2000
SQL> var v varchar2(2);
SQL> exec :v:='a';
 而现在的变量长度只有2，但是看下面的测试就知道了，还是使用前面的变量分级
PL/SQL procedure successfully completed
v
---------
a
 
SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);
 
1 row inserted
n
---------
 
v
---------
a
 
SQL> commit;
 
Commit complete
 
SQL> select sql_text, sql_id, address, version_count, EXECUTIONS from v$sqlarea where sql_text like '%insert /*+ bind_date_length */%';
 
SQL_TEXT                                 SQL_ID        ADDRESS          VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ---------------- ------------- ----------
 select sql_text, sql_id, address, versi 5pu17aqzuj510 000000006CDAA090             1          2
on_count, EXECUTIONS from v$sqlarea wher                                              
e sql_text like '%insert /*+ bind_date_l                                              
ength */%'                                                                            
 
 insert /*+ bind_date_length */ into t ( 6gd5ddr109hha 00000000724E4AB8             1          2
n,v) values (:n, :v)                                                                  
 
 select sql_text, sql_id, address, versi 3hznvywvuzu6v 000000006BB650C8             1          1
on_count, EXECUTIONS from v$sqlarea wher                                              
e sql_text like 'insert /*+ bind_date_le                                              
ngth */%'                                                                             
 
SQL> select sql_id, child_number, address, child_address, sqltype from v$sql where sql_id='6gd5ddr109hha';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE
------------- ------------ ---------------- ---------------- ----------
6gd5ddr109hha            0 00000000724E4AB8 00000000724E4758          6
 
SQL>  select * from v$sql_bind_metadata where address='00000000724E4758';
 
ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
00000000724E4758          2          1       2000          0 V
00000000724E4758          1          2         22          0 N


通过上面的测试得知：
绑定变量分级不能从高到低，而且分级的依据是声明变量的时候的变量长度而不是变量值真正的长度。
接下来看绑定变量分级从低到高的演化过程
以下的测试是在sqlplus中完成的
SQL> show user;
USER is "SYS"
SQL> truncate table t;


Table truncated.


SQL> var n number;
SQL> var v varchar2(2);
SQL> exec :n:=1;


PL/SQL procedure successfully completed.


SQL> exec :v:='a2';


PL/SQL procedure successfully completed.


SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);


1 row created.


SQL> commit;


Commit complete.
SQL> select sql_text, sql_id, address, version_count, EXECUTIONS from v$sqlarea where sql_text like '%insert /*+ bind_date_length */ into t%';
 
SQL_TEXT                                                                         SQL_ID        ADDRESS          VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- ------------- ---------------- ------------- ----------
 select sql_text, sql_id, address, version_count, EXECUTIONS from v$sqlarea wher g2c9yxg3hppb7 000000006CF6C510             1          1
insert /*+ bind_date_length */ into t (n,v) values (:n, :v)                      5kv0j7gh65zmd 000000007254D560             1          1
 
SQL> select sql_id, child_number, address, child_address, sqltype,EXECUTIONS from v$sql where sql_id='5kv0j7gh65zmd';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE EXECUTIONS
------------- ------------ ---------------- ---------------- ---------- ----------
5kv0j7gh65zmd            0 000000007254D560 000000006CC94E80          6          1
 
SQL> select * from v$sql_bind_metadata where address='000000006CC94E80';
 
ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
000000006CC94E80          2          1         32          0 V
000000006CC94E80          1          2         22          0 N


SQL> var v varchar2(16);
SQL> exec :v:='a16';


PL/SQL procedure successfully completed.


SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);


1 row created.


SQL> commit;


Commit complete.


 
SQL> select sql_text, sql_id, address, version_count, EXECUTIONS from v$sqlarea where sql_text like '%insert /*+ bind_date_length */ into t%';
 
SQL_TEXT                                                                         SQL_ID        ADDRESS          VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- ------------- ---------------- ------------- ----------
 select sql_text, sql_id, address, version_count, EXECUTIONS from v$sqlarea wher g2c9yxg3hppb7 000000006CF6C510             1          2
insert /*+ bind_date_length */ into t (n,v) values (:n, :v)                      5kv0j7gh65zmd 000000007254D560             1          2
 
SQL> select sql_id, child_number, address, child_address, sqltype,EXECUTIONS from v$sql where sql_id='5kv0j7gh65zmd';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE EXECUTIONS
------------- ------------ ---------------- ---------------- ---------- ----------
5kv0j7gh65zmd            0 000000007254D560 000000006CC94E80          6          2
 
SQL> select * from v$sql_bind_metadata where address='000000006CC94E80';
 
ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
000000006CC94E80          2          1         32          0 V
000000006CC94E80          1          2         22          0 N
 
SQL> 




SQL> var v varchar2(17);
SQL> exec :v:='a17';


PL/SQL procedure successfully completed.


SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);


1 row created.


SQL> commit;


Commit complete.
SQL> select sql_id, child_number, address, child_address, sqltype,EXECUTIONS from v$sql where sql_id='5kv0j7gh65zmd';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE EXECUTIONS
------------- ------------ ---------------- ---------------- ---------- ----------
5kv0j7gh65zmd            0 000000007254D560 000000006CC94E80          6          2
5kv0j7gh65zmd            1 000000007254D560 000000006B753000          6          1
 
SQL> select * from v$sql_bind_metadata where address='000000006B753000';
 
ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
000000006B753000          2          1        128          0 V
000000006B753000          1          2         22          0 N


可以看出定义的变量超过了16位那么oracle就会自动创建一个子游标，他的变量分级就由32变成了128


SQL> var v varchar2(63);
SQL> exec :v:='a63';


PL/SQL procedure successfully completed.


SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);


1 row created.


SQL> commit;


Commit complete.
SQL> select * from v$sql_bind_metadata where address='000000006B753000';
 
ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
000000006B753000          2          1        128          0 V
000000006B753000          1          2         22          0 N
 
SQL>  select sql_id, child_number, address, child_address, sqltype,EXECUTIONS from v$sql where sql_id='5kv0j7gh65zmd';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE EXECUTIONS
------------- ------------ ---------------- ---------------- ---------- ----------
5kv0j7gh65zmd            0 000000007254D560 000000006CC94E80          6          2
5kv0j7gh65zmd            1 000000007254D560 000000006B753000          6          2


SQL> var v varchar2(64);
SQL> exec :v:='a64';


PL/SQL procedure successfully completed.


SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);


1 row created.


SQL> commit;


Commit complete.


SQL> select sql_id, child_number, address, child_address, sqltype,EXECUTIONS from v$sql where sql_id='5kv0j7gh65zmd';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE EXECUTIONS
------------- ------------ ---------------- ---------------- ---------- ----------
5kv0j7gh65zmd            0 000000007254D560 000000006CC94E80          6          2
5kv0j7gh65zmd            1 000000007254D560 000000006B753000          6          3




SQL> var v varchar2(65);
SQL> exec :v:='a65';


PL/SQL procedure successfully completed.


SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);


1 row created.


SQL> commit;


Commit complete.




SQL> select sql_id, child_number, address, child_address, sqltype,EXECUTIONS from v$sql where sql_id='5kv0j7gh65zmd';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE EXECUTIONS
------------- ------------ ---------------- ---------------- ---------- ----------
5kv0j7gh65zmd            0 000000007254D560 000000006CC94E80          6          2
5kv0j7gh65zmd            1 000000007254D560 000000006B753000          6          3
5kv0j7gh65zmd            2 000000007254D560 000000006CF20088          6          1-------------新增了一个子游标
 
SQL> select * from v$sql_bind_metadata where address='000000006CF20088';
 
ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
000000006CF20088          2          1       2000          0 V---------------------------2000的长度
000000006CF20088          1          2         22          0 N




SQL> var v varchar2(2001);
SQL> exec :v:='a2001';


PL/SQL procedure successfully completed.


SQL> insert /*+ bind_date_length */ into t (n,v) values (:n, :v);


1 row created.


SQL> commit;


Commit complete.


SQL> select sql_id, child_number, address, child_address, sqltype,EXECUTIONS from v$sql where sql_id='5kv0j7gh65zmd';
 
SQL_ID        CHILD_NUMBER ADDRESS          CHILD_ADDRESS       SQLTYPE EXECUTIONS
------------- ------------ ---------------- ---------------- ---------- ----------
5kv0j7gh65zmd            0 000000007254D560 000000006CC94E80          6          2
5kv0j7gh65zmd            1 000000007254D560 000000006B753000          6          3
5kv0j7gh65zmd            2 000000007254D560 000000006CF20088          6          1
5kv0j7gh65zmd            3 000000007254D560 00000000725E9E40          6          1
 
SQL> select * from v$sql_bind_metadata where address='00000000725E9E40';
 
ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
00000000725E9E40          2          1       4001          0 V
00000000725E9E40          1          2         22          0 N