绑定变量的使用
不使用绑定变量： 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