欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

绑定变量的使用

程序员文章站 2022-05-03 11:26:55
...

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