绑定变量的使用

不使用绑定变量： scott@ORCLselectename from emp where empno=7788; ENAME ---------- SCOTT scott@ORCLselectename from emp where empno=7369; ENAME ---------- SMITH sys@ORCLselectsql_text,loads,sql_id from v$sqlarea where sql_text like select

不使用绑定变量：

scott@ORCL>selectename from emp where empno=7788;

ENAME

----------

SCOTT

scott@ORCL>selectename from emp where empno=7369;

ENAME

----------

SMITH

sys@ORCL>selectsql_text,loads,sql_id from v$sqlarea where sql_text like 'select ename from empwhere empno=%';

SQL_TEXT LOADS SQL_ID

-------------------------------------------------------------------------------------------------------------------

selectename from emp where empno=7369 1 4s63dmxqzc2hg

selectename from emp where empno=7788 1 4b84jg8yc5nwa

使用绑定变量：

scott@ORCL>variablezbcxy number;

scott@ORCL>exec:zbcxy:=7788;

PL/SQL过程已成功完成。

scott@ORCL>selectename from emp where empno=:zbcxy;

ENAME

----------

SCOTT

scott@ORCL>exec:zbcxy:=7369;

PL/SQL过程已成功完成。

scott@ORCL>selectename from emp where empno=:zbcxy;

ENAME

----------

SMITH

sys@ORCL>select sql_text,loads,sql_id from v$sqlarea where sql_text like'select ename from emp where empno=%';

SQL_TEXT LOADS SQL_ID

----------------------------------------------------------------------------------------------------------------

selectename from emp where empno=:zbcxy 1 8y38u6k926y6h

在pl/sql中自动使用绑定变量（有些情况例外）：

情况一：

scott@ORCL>createor replace procedure p_1(empno in number)

2 is

3 sql_text varchar2(100);

4 begin

5 sql_text:='select ename from emp whereempno='||empno;

6 execute immediate sql_text;

7 end;

8 /

过程已创建。

scott@ORCL>execp_1(7788);

PL/SQL过程已成功完成。

scott@ORCL>execp_1(7369);

PL/SQL过程已成功完成。

sys@ORCL>selectsql_text,loads,sql_id from v$sqlarea where sql_text like 'select ename from empwhere empno=%';

SQL_TEXT LOADS SQL_ID

---------------------------------------------------------------------------------------------------------------------

selectename from emp where empno=7369 1 4s63dmxqzc2hg

selectename from emp where empno=7788 1 4b84jg8yc5nwa

拼串的方式不走绑定变量

情况二：

scott@ORCL>createor replace procedure p_2(empno in number)

2 is

3 sql_text varchar2(100);

4 begin

5 sql_text:='select sal from emp where empno=:1';

6 execute immediate sql_text using empno;

7 end;

8 /

过程已创建。

scott@ORCL>execp_2(7788);

PL/SQL过程已成功完成。

scott@ORCL>execp_2(7369);

PL/SQL过程已成功完成。

sys@ORCL>selectsql_text,loads,sql_id from v$sqlarea where sql_text like 'select sal from empwhere empno=%';

SQL_TEXT LOADS SQL_ID

---------------------------------------------------------------------------------------------------------------

selectsal from emp where empno=:1 1 a29ya1gs6s7xq 

情况三：

scott@ORCL>createor replace procedure p_3(enum in number)

2 is

3 v_deptno varchar2(30);

4 begin

5 select deptno||'is 2033' into v_deptno from emp where empno=enum;

6 dbms_output.put_line(v_deptno);

7 end;

8

9 /

过程已创建。

scott@ORCL>execp_3(7788);

20is2033 

PL/SQL过程已成功完成。 

scott@ORCL>execp_3(7369);

20is2033 

PL/SQL过程已成功完成。 

scott@ORCL>execp_3(7499);

30is2033 

PL/SQL过程已成功完成。 

sys@ORCL>selectsql_text,loads,sql_id from v$sqlarea where sql_text like '%2033%'; 

SQL_TEXT LOADS SQL_ID

---------------------------------------------------------------------------------------------------------------------------------

selectsql_text,loads,sql_id from v$sqlarea where sql_text like '%2033%' 1 2p7hsnpb9hgmv

SELECTDEPTNO||'is 2033' FROM EMP WHERE EMPNO=:B1 1 2tzyfarcukgq7