oracle中游标与动态绑定变量

一、      游标：

用数据库语言来描述：游标是映射在结果集中一行数据上的位置实体，有了游标用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等等。

在Oracle9i之前，使用FETCH语句每次只能提取一行数据；从Oracle9i开始，通过使用FETCH…BULK COLLECT INTO语句，每次可以提取多行数据。语法如下：

(1) FETCH cursor_name INTO variable1,variable2,…；

此方法必须要使用循环语句处理结果集的所有数据。

(2) FETCH cursor_name BULK COLLECT INTO collect1,collect2,…[LIMIT rows]

[LIMIT rows]可用来限制每次游标每次提取的行数。

游标的分类： 显式游标和隐式游标

显示游标的使用：

1. 声明游标

CURSOR mycur(vartype number) is

select emp_no,emp_zc from cus_emp_basic

where com_no = vartype;

1. 打开游标

open mycur(000627) 注：000627:参数

1. 读取数据

fetch mycur into varno,varprice;

1. 关闭游标

close mycur;

游标的属性

oracle游标有4个属性： %ISOPEN 、%FOUND 、%NOTFOUND、%ROWCOUNT

• %ISOPEN 判断游标是否被打开，如果打开%ISOPEN 等于true,否则等于false
• %FOUND %NOTFOUND 判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false
• %ROWCOUNT 返回当前位置为止游标读取的记录行数。

二、      动态绑定变量

动态绑定变量解决Oracle应用程序可伸缩性的一个关键环节;而Oracle的共享池就决定了开发人员必须使用绑定变量；如果想要Oracle 运行减慢,甚至完全终止，那就可以不用绑定变量。

这里举例说明上述问题;

为了查询一个员工代号是123,你可以这样查询:

select * from emp where empno=’123’;

你也可以这样查询:

select * from emp where empno=:empno;

象我们往常一样,你查询员工’123’一次以后,有可能再也不用;接着你有可能查询员工’456’,然后查询’789’等等;如果查询使用象第一个查询语句,你每次查询都是一个新的查询(我们叫它硬编码的查询方法);因此,Oracle每次必须分析,解析,安全检查, 优化等等;

第二个查询语句提供了绑定变量:empno,它的值在查询执行时提供,查询经过一次编译后,查询方案存储在共享池中,可以用来检索和重用;在性能和伸缩性方面,这两者的差异是巨大的,甚至是惊人的;通俗点讲,就不是一个级别;

第一个查询使用的频率越高,所消耗的系统硬件资源越大,从而降低了用户的使用数量;它也会把优化好的其它查询语句从共享池中踢出;就象一个老鼠坏了一锅汤似的,系统的整体性能降低; 而执行绑定变量,提交相同对象的完全相同的查询的用户(这句话,大家听起来比较难理解,随后我会给出详细的解释),一次性使用就可重复使用,其效率不言耳语; 打个形象的比喻来说,第一个查询就象一次性使用的筷子,而第二个查询象是铁筷子,只要洗干净,张三李四都能用,合理有效地使用了资源

动态绑定与静态对比：

DECLARE

v_sql VARCHAR2(500);

BEGIN

--不使用绑定变量

/*FOR i IN 1..50000 LOOP

v_sql :='insert into t_temp values('||i||')';

EXECUTE IMMEDIATE v_sql;

END LOOP;*/

 

--使用绑定变量增加数据

FOR i IN 1..50000 LOOP

v_sql :='insert into t_temp values(:a)';

EXECUTE IMMEDIATE v_sql USING i;

END LOOP;

END;

--TRUNCATE TABLE t_temp;

动态查询sql举例：

--查询的动态sql

DECLARE

  V_SQL    VARCHAR2(100);

  V_CURSOR SYS_REFCURSOR;

  V_EMP    EMP%ROWTYPE;

BEGIN

  V_SQL := 'select * from emp &条件';

  OPEN V_CURSOR FOR V_SQL;

  LOOP

    FETCH V_CURSOR INTO V_EMP;

    EXIT WHEN V_CURSOR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);

  END LOOP;

  CLOSE V_CURSOR;

END;