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