ORACLE绑定变量初解

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