游标变量、动态SQL及变量绑定

lmwtzw6u5l0

　　--游标变量和REF CURSOR
　　--游标变量：是一个指向或引用下层游标的变量，相当于对象的引用
　　--目标：将emp表中的职工根据部门编号打印出姓名和工资
　　DECLARE
　　TYPE my_refcur_type IS REF CURSOR;
　　my_refcur my_refcur_type;
　　my_record emp%ROWTYPE;
　　BEGIN
　　OPEN my_refcur FOR SELECT * FROM emp WHERE deptno=20;
　　LOOP
　　FETCH my_refcur INTO my_record;
　　EXIT WHEN my_refcur%NOTFOUND;
　　dbms_output.put_line(my_record.ename||'→'||my_record.sal);
　　END LOOP;
　　CLOSE my_refcur;
　　END;
　　--绑定变量的使用：
　　--1.在SQL*PLUS中使用绑定变量
　　SQL> variable deptno number;
　　SQL> exec :deptno :=10;
　　SQL> select * from emp where deptno=:deptno;
　　--2.在PL/SQL中使用绑定变量
　　CREATE OR REPLACE PROCEDURE DSAL(P_EMPNO IN NUMBER) AS
　　BEGIN
　　UPDATE EMP SET SAL = SAL * 2 WHERE EMPNO = :P_EMPNO;
　　COMMIT;
　　END;
　　--3.在JDBC中使用绑定变量：preparestatement（预编译对象）
　　--4.在动态sql中绑定变量(将emp表中指定编号员工的工资增加一倍)
　　--4.1 字符串拼接→每次都会硬解析
　　BEGIN
　　EXECUTE IMMEDIATE
　　'update emp set sal = sal*2 where empno='||&P_empno;
　　COMMIT;
　　END;
　　--3.2 绑定变量：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秒
　　--使用绑定变量
　　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、在对建有索引的字段，且字段(集)的集非常大时，利用绑定变量可能会导致查询计划错误，因而导致查询效率非常低。
　　转自新浪博客：http://blog.sina.com.cn/s/blog_3fed8ad60100bzod.html