[20170929]& 代替冒号绑定变量.txt
--//我昨天看链接,http://orasql.org/2017/09/27/ampersand-instead-of-colon-for-bind-variables/
--//重复测试:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
set def off serverout on
exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
--//正常&var 应该写成 :var ,而这里使用&var,按照以前,这里会替换定义的变量值,而不是变成绑定变量.
--//注:执行时要设置set def off serverout on,不然没有输出,并且还是要输入变量.
SCOTT@book> set def off serverout on
SCOTT@book> exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
1
PL/SQL procedure successfully completed.
SCOTT@book> select substr(sql_text,1,40) stext,sql_id,executions,rows_processed from v$sqlarea a where sql_text like '%dual%&var';
STEXT SQL_ID EXECUTIONS ROWS_PROCESSED
------------------------------------ ------------- ---------- --------------
select 1 from dual where dummy=&var ckkw4u3atxz02 1 1
--//注意看sql文本格式.
SCOTT@book> set def on serverout off
SCOTT@book> @ &r/dpc ckkw4u3atxz02 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID ckkw4u3atxz02, child number 0
-------------------------------------
select 1 from dual where dummy=&var
Plan hash value: 272002086
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DUAL@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (CHAR(30), CSID=852): 'X'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DUMMY"=:VAR)
