如何查看Oracle SQL绑定变量

查看Oracle SQL绑定变量方法如下：

创新互联专注于企业成都全网营销推广、网站重做改版、鼓楼网站定制设计、自适应品牌网站建设、成都h5网站建设、电子商务商城网站建设、集团公司官网建设、外贸营销网站建设、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为鼓楼等各大城市提供网站开发制作服务。

alter session set nls_date_format = 'yyyy-mm-dd,hh24:mi:ss';

set linesize 400

col sql_Id format a20

col name format a20

col datatype_string format a14

col value_string format a20

--这个sql从内存中读取绑定变量值信息，若是不在内存中，则使用下一个sql

select sql_id,name, datatype_string, last_captured,value_string from v$sql_bind_capture where sql_id='dxfcacn4t4ppw' order by LAST_CAPTURED,POSITION;

--这个sql从awr中读取绑定变量值信息

select instance_number, sql_id,name, datatype_string, last_captured,value_string from dba_hist_sqlbind where sql_id='fahv8x6ngrb50'order by LAST_CAPTURED,POSITION;

绑定变量的获取

对于带有绑定变量的SQL语句 ORACLE在第一次执行的时候 将会进行绑定变量的PEEK 是否PEEK是否隐含参数_OPTIM_PEEK_USER_BINDS控制 默认为TRUE ORACLE在第一次

PEEK之后 将绑定变量的值放到V$SQL BIND_DATA列里 如下

SQL SELECT COUNT( ) FROM TEST;

COUNT( )

SQL DESC TEST;

Name                                                  Null?    Type

OWNER                                                          VARCHAR ( )

NAME                                                           VARCHAR ( )

TYPE                                                           VARCHAR ( CHAR)

LINE                                                           NUMBER

TEXT                                                           VARCHAR ( )

SQL SELECT LINE COUNT( ) FROM TEST GROUP BY LINE;

LINE   COUNT( )

SQL VARIABLE LINE NUMBER

SQL EXEC :LINE:= ;

PL/SQL procedure successfully pleted

SQL SELECT COUNT( ) FROM TEST WHERE LINE=:LINE;

COUNT( )

SQL COL SQL_ID FORMAT A

SQL COL BIND_DATA FORMAT A

SQL SELECT SQL_ID BIND_DATA FROM V$SQL WHERE SQL_TEXT= SELECT COUNT( ) FROM TEST WHERE LINE=:LINE ;

SQL_ID               BIND_DATA

abhf n xqgrr         BEDA A C EB C C

SQL SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNE EXTRACT_BINDS( BEDA A C EB C C ));

VALUE_STRING

SQL SELECT * FROM TABLE(DBMS_XPLAN DISPLAY_CURSOR( abhf n xqgrr ));

PLAN_TABLE_OUTPUT

SQL_ID  abhf n xqgrr child number

SELECT COUNT( ) FROM TEST WHERE LINE=:LINE

Plan hash value:

| Id  | Operation             | Name        | Rows  | Bytes | Cost  |

|   | SELECT STATEMENT      |             |       |       |   |

|   |  SORT AGGREGATE       |             |     |     |       |

|*  |   INDEX FAST FULL SCAN| IDX_TEST_ |   K|  K|   |

Predicate Information (identified by operation id):

filter( LINE =:LINE)

Note

cpu costing is off (consider enabling it)

rows selected

SQL EXEC :LINE:= ;

PL/SQL procedure successfully pleted

SQL SELECT COUNT( ) FROM TEST WHERE LINE=:LINE;

COUNT( )

SQL ALTER SYSTEM FLUSH SHARED_POOL;

System altered

SQL SELECT COUNT( ) FROM TEST WHERE LINE=:LINE;

COUNT( )

SQL  SELECT SQL_ID BIND_DATA FROM V$SQL WHERE SQL_TEXT= SELECT COUNT( ) FROM TEST WHERE LINE=:LINE ;

SQL_ID               BIND_DATA

abhf n xqgrr         BEDA A C ECC C C

SQL SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNE EXTRACT_BINDS( BEDA A C ECC C C ));

VALUE_STRING

SQL PRINT:

LINE

SQL EXEC :LINE:= ;

PL/SQL procedure successfully pleted

SQL SELECT COUNT( ) FROM TEST WHERE LINE=:LINE;

COUNT( )

SQL SELECT SQL_ID BIND_DATA FROM V$SQL WHERE SQL_TEXT= SELECT COUNT( ) FROM TEST WHERE LINE=:LINE ;

SQL_ID               BIND_DATA

abhf n xqgrr         BEDA A C ECC C C

SQL  SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNE EXTRACT_BINDS( BEDA A C ECC C C ));

VALUE_STRING

对于随后的绑定变量的捕获 ORACLE将放在V$SQL_BIND_CATPURE里 默认每隔 秒捕获一次 捕获间隔受隐含参数_cursor_bind_capture_interval的控制

为了演示的方便 我们把这个间隔设小一点

SQL alter system set _cursor_bind_capture_interval = ;

System altered

SQL exec :LINE:= ;

PL/SQL procedure successfully pleted

SQL SELECT COUNT( ) FROM TEST WHERE LINE=:LINE;

COUNT( )

SQL EXEC :LINE:= ;

PL/SQL procedure successfully pleted

SQL SELECT COUNT( ) FROM TEST WHERE LINE=:LINE;

COUNT( )

SQL EXEC :LINE:= ;

PL/SQL procedure successfully pleted

SQL SELECT COUNT( ) FROM TEST WHERE LINE=:LINE;

COUNT( )

SQL SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID= abhf n xqgrr ;

VALUE_STRING

SQL EXEC :LINE:= ;

PL/SQL procedure successfully pleted

SQL SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID= abhf n xqgrr ;

VALUE_STRING

SQL SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID= abhf n xqgrr ;

VALUE_STRING

SQL EXEC :LINE:= ;

PL/SQL procedure successfully pleted

SQL SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID= abhf n xqgrr ;

VALUE_STRING

lishixinzhi/Article/program/Oracle/201311/18232

oracle 如何使用绑定变量

你这个写法不对，应该是直接写 A.containerowner='APL'

而不能写：p_containerowner,这种写法只有在动态sql中才能用

比如：

execute immediate 'begin insert into test07 values(:x,:x:y:x); end;'

using a,b;

a和b的值是预先定义好的，直接填充占位符x y

Oracle中关于绑定变量的使用技巧

提示无效数字，你应该看前面查出来的值是不是数字。

select max(ts_pp_supplier_id) into v_id from TS_SUPPLIER;

其中：max(ts_pp_supplier_id)，要确认两点：

1.ts_pp_supplier_id字段是数字。

2.TS_SUPPLIER表查询出来是不是有记录。如果没有记录，max(ts_pp_supplier_id)会是null。所以你应该再包一层nvl(max(ts_pp_supplier_id),0)