Oracle游标绑定变量应用

绑定变量：using → 只需要解析一次；

BEGIN

EXECUTE IMMEDIATE

'update emp set sal=sal*2 where empno=:p_empno' USING &p_eno;

COMMIT;

END;

注意：不能使用绑定变量替换实际的数据库对象名（表，视图，列等），只能替换字面量，

如果对象名是在运行时生成的，我们仍然需要对其用字符串拼接，同时，sql只会匹配已经

在共享池中相同的对象名

在动态sql中使用绑定变量和不使用绑定变量的性能测试（在测试表中添加10000条记录）：

--创建测试表demo；

DROP TABLE demo;

CREATE TABLE demo(d_id NUMBER(6) PRIMARY KEY);

SELECT * FROM demo;

--1.不使用绑定变量

DECLARE

begin_time NUMBER(10);

end_time NUMBER(10);

BEGIN

begin_time := dbms_utility.get_time;

EXECUTE IMMEDIATE

'truncate table demo';

FOR i IN 1..10000

LOOP

EXECUTE IMMEDIATE

'INSERT INTO demo(d_id) VALUES('||i||')';

END LOOP;

end_time :=dbms_utility.get_time;

dbms_output.put_line((end_time-begin_time)/100||'秒');

END;

--耗时12.88秒

来源：(http://blog.sina.com.cn/s/blog_3fed8ad60100bzod.html) - 游标变量、动态sql及变量绑定的使用_杨都_新浪博客 

--使用绑定变量

DECLARE

begin_time NUMBER(10);

end_time NUMBER(10);

BEGIN

begin_time := dbms_utility.get_time;

EXECUTE IMMEDIATE

'truncate table demo';

FOR i IN 1..10000

LOOP

EXECUTE IMMEDIATE

'INSERT INTO demo(d_id) VALUES(:x)' USING i;

END LOOP;

end_time :=dbms_utility.get_time;

dbms_output.put_line((end_time-begin_time)/100||'秒');

END;

--耗时6.56秒

从上面的测试可以看出，使用绑定变量明显比字符串拼接的效率高得多。

用本地动态sql技术执行动态sql的方法

--1.无绑定变量的非sql查询语句

BEGIN

EXECUTE IMMEDIATE

'create index emp_ind_1 on emp(sal,hiredate)';

END;

--注意：如果指向DDL语句时使用绑定变量是非法的

DECLARE

v_sql_str varchar2(100);

BEGIN

v_sql_str := 'update emp set sal=999 where empno=7788';

EXECUTE IMMEDIATE v_sql_str;

END;

--2.带固定数目绑定变量的非sql查询语句

DECLARE

v_sql_str VARCHAR2(100);

BEGIN

v_sql_str:='update emp set sal = :sal where empno = :eno';

EXECUTE IMMEDIATE v_sql_str USING 9999,7788;

END;

--带returning子句；

DECLARE

v_sql_str VARCHAR2(100);

v_sal emp.sal%TYPE;

v_ename emp.ename%TYPE;

BEGIN

v_sql_str :=

'update emp set sal=999 where empno=7788 returning ename,sal into :1,:2';

EXECUTE IMMEDIATE v_sql_str RETURNING INTO v_ename, v_sal;

dbms_output.put_line(v_ename||'→'||v_sal);

END;

--带固定数目列和绑定变量的sql查询语句；

--1.返回单行数据

DECLARE

v_sql_str VARCHAR2(100);

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE;

BEGIN

v_sql_str :=

'select ename,sal from emp where empno = :eno';

EXECUTE IMMEDIATE v_sql_str INTO v_ename,v_sal USING 7788;

dbms_output.put_line(v_ename||'→'||v_sal);

END;

--输出“SCOTT→3000”；

--2.返回多行数据

DECLARE

TYPE emp_cur IS REF CURSOR;

my_emp_cur emp_cur;

my_emp_rec emp%ROWTYPE;

BEGIN

OPEN my_emp_cur FOR 'select * from emp where deptno=:x' USING 30;

LOOP

FETCH my_emp_cur INTO my_emp_rec;

EXIT WHEN my_emp_cur%NOTFOUND;

dbms_output.put_line(my_emp_rec.ename||'→'||my_emp_rec.sal);

END LOOP;

END;

--返回多行数据且带有using和BULK COLLECT INTO子句

DECLARE

TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;

emp_table emp_table_type;

BEGIN

EXECUTE IMMEDIATE

'select * from emp where deptno=:x' BULK COLLECT INTO emp_table USING 30;

FOR i IN 1..emp_table.COUNT

LOOP

dbms_output.put_line(emp_table(i).ename||'→'||emp_table(i).sal);

END LOOP;

END;

--执行动态查询语句.--案例：输入要排序的字段名，实现动态排序。.

DECLARE

MY_FIELD VARCHAR2(100);

STR_SQL VARCHAR2(1000);

TYPE EMP_CUR_TYPE IS REF CURSOR;

CUR                  EMP_CUR_TYPE;

E_ROW                EMP%ROWTYPE;

BEGIN.               MY_FIELD := '&enter_the_order_field';

STR_SQL              := 'select * from emp order by ' || MY_FIELD;

OPEN                 CUR FOR STR_SQL;

LOOP                 FETCH CUR INTO E_ROW;

DBMS_OUTPUT.PUT_LINE(E_ROW.DEPTNO || '->' || E_ROW.EMPNO || '->' ||

                       E_ROW.ENAME || '->' || E_ROW.SAL);

EXIT                 WHEN CUR%NOTFOUND;

END LOOP;

CLOSE CUR;

END;

注意：

虽然变量绑定减少了硬编译次数，节省了cpu等资源，大多是情况下使用它可以显著的提高系统性能和系统的并发访问量，

但是仍然有一些情况下不适合使用变量绑定：

1、对于隔相当长一段时间才执行一次的sql语句，利用绑定变量的好处会被不能有效利用优化器而抵消；

2、数据仓库的情况下；

3、在对建有索引的字段，且字段(集)的集非常大时，利用绑定变量可能会导致查询计划错误，因而导致查询效率非常低。