ORACLE绑定变量初解

来源：互联网发布：c语言void是什么意思编辑：程序博客网时间：2024/05/20 10:15

1、

SQL> var i number
SQL> exec :i := 7369;

PL/SQL procedure successfully completed.

SQL> select * from emp where empno=:i;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL> exec :i :=7788;

PL/SQL procedure successfully completed.

SQL> select * from emp where empno=:i;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20

SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from emp where empno=%';

SQL_TEXT PARSE_CALLS
select * from emp where empno=:i 2

作用是可以避免硬解析。

QL> define b=7876
SQL> select * from emp where empno=&b;
old   1: select * from emp where empno=&b
new   1: select * from emp where empno=7876

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

SQL> select * from emp where empno=&&b;
old   1: select * from emp where empno=&&b
new   1: select * from emp where empno=7876

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

SQL>
SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from emp where empno=%';

SQL_TEXT PARSE_CALLS
select * from emp where empno=7876 2

动态sql的绑定

SQL> set serveroutput on
SQL> declare
   2   v_string varchar2(100);
   3   v_id tt.id%type ;
   4   v_name tt.name%type ;
   5   begin
   6   v_string:='select * from tt where id=:v_id';
   7   execute immediate v_string into v_id , v_name using &a;
   8   dbms_output.put_line(v_id||' '||v_name) ;
   9   end;
10   /
输入 a 的值:   1
原值 7: execute immediate v_string into v_id , v_name using &a;
新值 7: execute immediate v_string into v_id , v_name using 1;
1 test

PL/SQL 过程已成功完成。

SQL> declare
   2   v_string varchar2(100);
   3   v_id tt.id%type;
   4   v_name tt.name%type ;
   5   begin
   6   v_string:='insert into tt values(:id,:name)';
   7   execute immediate v_string using &id,&name ;
   8   end;
   9   /
输入 id 的值:   1000
输入 name 的值:   'test'
原值 7: execute immediate v_string using &id,&name ;
新值 7: execute immediate v_string using 1000,'test' ;

PL/SQL 过程已成功完成。

SQL> select * from tt where id=1000;

       ID NAME
---------- ----------
   1000 test