[20170929]& 代替冒号綁定變量.txt

[20170929]& 代替冒号綁定變量.txt

--//重複測試:

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)