十年网站开发经验 + 多家企业客户 + 靠谱的建站团队
量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决
查看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
你这个写法不对,应该是直接写 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
提示无效数字,你应该看前面查出来的值是不是数字。
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)