Oracle方言下游标专题

  使用配置
内容纲要

file

二. 显式游标

2.1 不带参数的

2.1.1 FETCH INTO方式

DECLARE
    -- 定义一个字段名和类型与表transactions均相同的记录变量transactions_type。
    transactions_type transactions%ROWTYPE
    -- 定义一个显式游标cur，用来查询表transactions中的全部信息。
    CURSOR cur IS SELECT * FROM transactions;
BEGIN
    -- 打开游标cur。
    OPEN cur
    LOOP
        -- 把游标cur查询到的信息放进变量transactions_type中。
        FETCH cur INTO transactions_type
        -- 如果游标cur没有查询到信息，就退出。
        EXIT WHEN cur%NOTFOUND
        -- 输出变量transactions_type中的字段price的值。
        DBMS_OUTPUT.PUT_LINE(transactions_type.price)
    END LOOP;
    CLOSE cur
EXCEPTION
    -- 声明一个异常情况名OTHERS。
    WHEN OTHERS THEN
-- 如果异常OTHERS发生，则返回的值。
    DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!')
    CLOSE cur
END;
/

2.1.2 FOR循环方式

DECLARE
    -- 定义一个字段名和类型与表transactions均相同的记录变量transactions_type。
    transactions_type transactions%ROWTYPE
    -- 定义一个显式游标cur，用来查询表transactions中的全部信息。
    CURSOR cur IS SELECT * FROM transactions;
BEGIN
    -- for..loop 循环语句,对于每一个在游标里的变量值。
    FOR transactions_type IN cur
    LOOP
    -- 输出变量transactions_type的字段名，账号，交易时间和价格的值。
    DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' '  ||transactions_type.trans_time || '
    ' ||transactions_type.price)
    END LOOP;
END;
/

如果想让代码更精简，可以去掉对游标的声明引入子查询即可，下面代码更加精简，得到的结果相同，
和隐式游标很像，但又不是隐式游标（隐式游标主要用于DML操作）

BEGIN
    FOR emp_record IN (SELECT empno,ename,job FROM emp) 
    LOOP
        DBMS_OUTPUT.PUT_LINE('员工号：'||emp_record.empno||'，员工姓名           '||emp_record.ename||'，员工职位'||emp_record.job);
    END LOOP;
END;
/

2.1.3 WHILE循环方式

DECLARE
      transactions_type emp%ROWTYPE
      CURSOR cur IS SELECT * FROM emp;
BEGIN
    OPEN cur
        FETCH cur INTO transactions_type;
        WHILE cur%FOUND 
        LOOP
            DBMS_OUTPUT.PUT_LINE(transactions_type.ename)
            FETCH cur INTO transactions_type;
        END LOOP;
    CLOSE cur
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!')
    CLOSE cur
END;
/

2.2 带参数的

2.2.1 FETCH INTO方式

DECLARE
    -- 定义一个带有参数的游标cur去查询表transactions中账号和参数的值相等的全部信息，其中参数   名为tacc_num，类型为string。
    CURSOR cur(tacc_num string) IS SELECT * FROM transactions WHERE acc_num=tacc_num;
    -- 定义一个字段名和类型与表transactions均相同的记录变量transactions_type。
    transactions_type transactions%rowtype
BEGIN
    -- 打开游标cur，并赋参数值为6513065。
    OPEN cur('6513065')
    LOOP
        -- 把游标cur查询到的信息放进变量transactions_type中。
        FETCH cur INTO transactions_type
        EXIT WHEN cur%NOTFOUND
        -- 输出变量transactions_type中的交易时间。
        DBMS_OUTPUT.PUT_LINE(transactions_type.trans_time)
    END LOOP;
    CLOSE cur
-- 如果有情况名为OTHERS的异常发生，则返回Something unexpected happened!!的信息。
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!')
    CLOSE cur
END;
/

2.2.2 FOR循环方式

DECLARE
    -- 定义一个字段名和类型与表transactions均相同的记录变量transactions_type。
    transactions_type transactions%ROWTYPE
    -- 定义一个带有参数的游标cur去查询表transactions中账号和参数的值相等的全部信息，其中参数名  为tacc_num，类型为string。
    CURSOR cur(tacc_num string) IS SELECT * FROM transactions WHERE cc_num=tacc_num;
BEGIN
    -- for..loop循环，对于每一个在游标cur('6513065')中的变量transactions_type的值。
    FOR transactions_type IN cur('6513065')
    LOOP
        -- 输出表transactions中账号为6513065的账号，交易时间和价格。
        DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' || ' ' ||
        transactions_type.trans_time ||transactions_type.price)
    END LOOP;
END;
/

2.2.3 WHILE循环方式

DECLARE
    transactions_type emp%ROWTYPE
    CURSOR cur(v_empno string) IS SELECT * FROM emp WHERE empno=v_empno;
BEGIN
    OPEN cur('7499')
    FETCH cur INTO transactions_type;
    WHILE cur%FOUND 
    LOOP
        DBMS_OUTPUT.PUT_LINE(transactions_type.ename)
        FETCH cur INTO transactions_type;
    END LOOP;
    CLOSE cur
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!')
    CLOSE cur
END;
/

2.3 显式游标的5个属性

游标的属性 返回值类型 意义
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%ROWTYPE 记录类型 游标返回结果的记录类型（inceptor不支持该属性前面写表名）
%FOUND 布尔型 最近的FETCH语句返回一行数据则为TRUE，否则为FALSE
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为TRUE，否则为FALSE

用一个语句块把上面的5个属性全都使用一下，了解一下每个属性的用法：

DECLARE
    --声明一个ROWTYPE记录型变量
    v_emp_example emp%ROWTYPE;
    --显式定义游标
    CURSOR emp_cursor IS  SELECT * from DEFAULT.emp where empno<>1000;
BEGIN
    --判断游标是否打开，如果没打开就open
    IF NOT emp_cursor%ISOPEN  THEN
        OPEN emp_cursor;
    END IF;
    LOOP
        --使用Fetch into的方式将数据赋给记录型变量
        FETCH emp_cursor INTO  v_emp_example; 
        --当Fetch语句没有返回一条语句的时候就退出
        --下面的语句等价于EXIT WHEN NOT emp_cursor%FOUND;
        EXIT WHEN emp_cursor%NOTFOUND;
        --%ROWCOUND获得Fetch语句返回的数据行数
        dbms_output.put_line('rowcount is:'|| emp_cursor%ROWCOUNT || '-- empno is:' ||      v_emp_example.empno || '-- emp name is:' || v_emp_example.ename);
    END LOOP;
    CLOSE emp_cursor;
END;
/

注：如果把上面标黄的部分修改成SQL，会发生什么 >_<!! 可以试下

三. 隐式游标

隐式游标是没有明确的声明语句的游标类型。所有的DML操作都被Inceptor内部解析为一个游标名为SQL的隐式游标

3.1 使用方法

结合隐式游标的4个属性来对DML操作进行记录，常用于plsql记录执行日志。

3.2 隐式游标的4个属性

游标的属性 返回值类型 意义
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 永远为FALSE

注：对比显式游标的5个属性我们发现，

  1. 缺少了一个%ROWTYPE，这是因为Inceptor不支持%ROWTYPE前面写游标名；
  2. 每种属性的意义发生了很大区别，
    %ROWCOUNT只会记录DML语句实际执行的行数，而不是FETCH的返回行数；
    %FOUND只会判断增删改查是否操作成功，而不是是否最近的一次FETCH返回了一行数据；
    %ISOPEN只有FALSE状态，隐式状态下无游标的开关动作；

用一个语句块把上面的4个属性全都使用一下，了解一下每个属性的用法：

DECLARE
    --声明一个ROWTYPE记录型变量
    a emp%ROWTYPE;
BEGIN
    --执行一个查询的DML操作
    SELECT * INTO a FROM emp WHERE empno = 7499;
    --如果查询操作执行成功
    IF SQL%FOUND THEN
        --返回执行的结果值
        dbms_output.put_line('员工名字为： '||a.ename);
        --返回DML语句成功执行 数据行数
        dbms_output.put_line('DML操作影响的条数为：'||sql%ROWCOUNT);
        --确认隐式游标下游标的打开状态，默认都是FALSE
        dbms_output.put_line('隐式游标下%ISOPEN为: '||sql%ISOPEN);
    END IF;
    EXCEPTION
        --如果SELECT未获取到数据，抛异常输出
        WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line('并没有获取到目标数据');
END;
/

四. 动态游标（REF游标）

像游标一样，游标变量指向指定查询结果集当前行，但是相对游标，游标变量更加灵活因为其声明并不绑定指定查询。要定义一个游标变量，您需要先声明一个游标类型 REF CURSOR。然后定义这个类型的游标变量。

强类型游标变量和弱类型游标变量的区别：
如果在声明游标类型REF CURSOR的时候指定了返回类型，那么REF CURSOR及其类型的游标变量被称为 强类型。
如果在声明游标类型REF CURSOR的时候不指定返回类型，那么REF CURSOR及其类型的游标变量被称为 弱类型。

4.1 强类型游标变量

4.1.1 FETCH INTO方式

DECLARE
    -- 声明一个名为cur_transaction的游标类型，指定返回值的类型为transactions%rowtype。
    -- 游标变量声明的时候，不会指定查询
    TYPE cur_transaction IS REF CURSOR RETURN transactions%ROWTYPE
    -- 定义一个名为sqlcur的游标变量
    sqlcur cur_transaction
    -- 依次定义两个类型为字符串的变量v_trans_id,v_trans_time。
    v_trans_id STRING
    v_trans_time STRING
BEGIN
    -- 打开游标sqlcur，去查询表transactions中价格为12.13的交易号和交易时间。
    -- 在这里open cursor的时候指定查询
    OPEN sqlcur FOR SELECT trans_id,trans_time FROM transactions WHERE price=12.13;
    LOOP
        -- 将游标查询的结果放进变量v_trans_id,v_trans_time。
        FETCH sqlcur INTO v_trans_id,v_trans_time
        EXIT WHEN sqlcur%NOTFOUND
        -- 输出变量v_trans_id,v_trans_time的值。
        DBMS_OUTPUT.PUT_LINE(v_trans_id||' ' ||v_trans_time)
    END LOOP;
    CLOSE sqlcur
END;
/

4.2 弱类型游标变量

4.2.1 FETCH INTO方式

DECLARE
    -- 声明一个名为cur_transaction的游标类型，没有定义返回值的类型
    TYPE cur_transaction IS REF CURSOR 
    -- 定义一个名为sqlcur的cur_transaction游标变量。
    sqlcur cur_transaction
    -- 依次定义两个类型为字符串的变量v_trans_id,v_trans_time。
    v_trans_id STRING
    v_trans_time STRING
BEGIN
    -- 打开游标sqlcur，去查询表transactions中价格为12.13的交易号和交易时间。
    OPEN sqlcur FOR SELECT trans_id,trans_time FROM transactions WHERE price=12.13;
    LOOP
        -- 将游标查询的结果放进变量v_trans_id,v_trans_time。
        FETCH sqlcur INTO v_trans_id,v_trans_time
        EXIT WHEN sqlcur%NOTFOUND
        -- 输出变量v_trans_id,v_trans_time的值。
        DBMS_OUTPUT.PUT_LINE(v_trans_id||' ' ||v_trans_time)
    END LOOP;
    CLOSE sqlcur
END;
/

五. 游标进阶

5.1 BULK COLLECT

采用bulk collect可以将查询结果一次性的加载到collections（集合变量，与oracle不同的是，incepto只支持nested table）中

注意:
limit字句必须用在批量获取，也就是仅限于fetch cur bulk collect into可以使用limit，普通的fetch cur into 不可以使用limit子句（与oracle一致，PLS-00439）

5.1.1 在FETCH INTO中使用bulk collect

DECLARE
    -- 声明一个名为trans_type的记录类型，分量名分别为trans_id，trans_time，数据类型均为字符串。
    TYPE emp_type is RECORD(ename string,job string)
    -- 声明一个名为cur的游标，去查询表transactions中账号为6513065的交易号和交易时间。
    CURSOR cur IS SELECT ename,job FROM emp_like WHERE empno=7499;
    -- 声明一个名为transactions_type的NESTED TABLE类型，用来存放数据类型为trans_type的变量。
    TYPE empployee_type IS TABLE OF emp_type
    -- 定义一个类型为transactions_type的变量v_transactions_type。
    v_emp_type empployee_type
BEGIN
    -- 打开游标。
    OPEN cur
    LOOP
        -- 将游标查询到的结果放进变量v_transactions_type。
        FETCH cur BULK COLLECT INTO v_emp_type
        -- 如果游标没有查询到结果就退出。
        EXIT WHEN cur%NOTFOUND
    END LOOP;
    -- 关闭游标。
    CLOSE cur
    -- 输出变量v_transactions_type的行数。
    dbms_output.put_line('b: ' ||v_emp_type.count())
END;
/

5.1.2 在SELECT INTO中使用bulk collect

DECLARE
    -- 声明一个名为trans_type的记录类型，分量名分别为trans_id，trans_time，price，数据类型分别为string,string,double。
    TYPE emp_type is RECORD(ename string,job string,mgr int)
    --声明一个名为transactions_type的NESTED TABLE类型用来存放数据类型为trans_type的变量。
    TYPE employee_type IS TABLE OF emp_type
    -- 定义一个类型为transactions_type的变量v_transactions_type。
    v_employee_type employee_type
BEGIN
    -- 查询表transactions中的交易号，交易时间和价格信息，并放进变量v_transactions_type中。
    SELECT ename,job,mgr BULK COLLECT INTO v_employee_type FROM emp_like WHERE empno=7499;
    -- IF条件语句为如果sql语句执行了。
    IF SQL%FOUND THEN
        -- 成功执行sql语句所输出的内容。
        dbms_output.put_line('数据条数为： ' ||v_employee_type.count())
        dbms_output.put_line('successfully select the data into v_transactions_type')
    ELSE
        -- 没有成功执行sql语句所输出的内容。
        dbms_output.put_line('nothing')
    END IF
END;
/

5.1.3 bulk collect和forall联合使用

在PL/SQL 和SQL引擎(engines)中，太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能.
在发送语句到SQL引擎前，FORALL语句告知PL/SQL 引擎批挷定输入集合

DECLARE
    TYPE emp_type is table of emp%ROWTYPE;
    v_emp_type emp_type;
    CURSOR curr is select * from emp ;
BEGIN
    open curr;
    loop
        fetch curr bulk collect into v_emp_type limit 5;
        --注意！和常规的fetch exit有区别，最后一条数据<=5，则curr%NOTFOUND会返回TRUE
        --所以建议使用v_emp_type.count()=0的方式
        --exit when curr%NOTFOUND;
        exit when v_emp_type.count()=0;
        dbms_output.put_line('增加:'||v_emp_type.count);
        forall i in 1..v_emp_type.count
        --注意forall后面只能接dml语句，无法接类似dbms_output.put_line()等等
        INSERT INTO emp_like SELECT             v_emp_type(i).empno,v_emp_type(i).ename,v_emp_type(i).job,v_emp_type(i).mgr,
v_emp_type(i).hiredate,v_emp_type(i).sal,v_emp_type(i).comm,v_emp_type(i).deptno            FROM system.dual;
        --如果一定要使用%NOTFOUND来做退出判断的话，需要写在这里
        --exit when curr%NOTFOUND;
    end loop;
    close curr;
END;
/

file

六. FAQ

6.1 FETCH INTO 、FOR 循环、WHILE循环的区别：

  1. FOR循环，不需要显式声明游标，是隐式打开、关闭游标
  2. FETCH 游标，需要显式声明游标，是显式打开和、关闭游标
  3. REF游标，只能用FETCH

效率来说，FOR>FETCH>WHILE
写法来说，FOR>FETCH>WHILE

6.2 BULK COLLECT INTO 对collection类型的支持：

file

file

Oracle语法树中，bulk collect into可以放到任意的collection类型变量中，
而inceptor手册中要求放到一个nested table类型的变量里，不支持放到Associative arrays，Varry等集合类型中；我们对inceptor的每种collection类型单独做测试，看下返回结果：

6.2.1 Array

实测Array类型是支持bulk collect into的

DECLARE
TYPE ORG_VARRAY_TYPE IS VARRAY(20) OF emp_like2%ROWTYPE;
    V_ORG_VARRAY ORG_VARRAY_TYPE
    CURSOR cur IS SELECT * FROM emp_like2 ;
BEGIN
    OPEN cur
    LOOP
        FETCH cur BULK COLLECT INTO V_ORG_VARRAY LIMIT 20
        forall i in 1..V_ORG_VARRAY.count
            INSERT INTO emp_like3 SELECT V_ORG_VARRAY(i).empno,V_ORG_VARRAY(i).ename,V_ORG_VARRAY(i).job,V_ORG_VARRAY(i).mgr,V_ORG_VARRAY(i).hiredate,V_ORG_VARRAY(i).sal,V_ORG_VARRAY(i).comm,V_ORG_VARRAY(i).deptno FROM system.dual;
        EXIT WHEN cur%NOTFOUND
    END LOOP;
    CLOSE cur
END;
/

注意：
因为声明变长数组时已经限定最大是20，所以后面bulk collect 的limit数值最多只能是20，不可以超过，如果超过就会报Subscript outside of limit的错误

6.2.2 Nested table

见BULK COLLECT 5.1.1

6.2.3 Associative arrays

DECLARE
TYPE ORG_VARRAY_TYPE IS TABLE OF test_associate_arrays%ROWTYPE INDEX BY STRING;
    V_ORG_VARRAY ORG_VARRAY_TYPE
    CURSOR cur IS SELECT * FROM test_associate_arrays ;
BEGIN
    OPEN cur
    LOOP
        FETCH cur BULK COLLECT INTO V_ORG_VARRAY LIMIT 2
        forall i in 1..V_ORG_VARRAY.count()
            INSERT INTO taa_like SELECT V_ORG_VARRAY(i).id,V_ORG_VARRAY(i).name FROM system.dual;
        EXIT WHEN cur%NOTFOUND
    END LOOP;
    CLOSE cur
END;
/

会报错，map类型无法转换成list类型

file

6.3 常见问题

6.3.1 游标定义使用数据字典表，执行结果不完整

我们构造下这个异常场景：

设置如下两个参数，超时时间设置为1000ms*5=5s

SET ngmr.local.job.record.timeout.ms=1000
SET plsql.cursor.local.job.record.timeout.weight=5

执行测试语句块，没有报错，但是结果缺失很多

DECLARE 
    tables_type system.tables_v%ROWTYPE;
    CURSOR CUR IS SELECT * FROM system.tables_v;
BEGIN
    FOR tables_type IN CUR
    LOOP
        --这里我们通过reflect实现sleep 10s
        select reflect("java.lang.Thread", "sleep", bigint(10000)) from system.dual;
        DBMS_OUTPUT.PUT_lINE(tables_type.TABLE_NAME);
    END LOOP;
END;
/

  • 原因是游标定义时使用了数据字典的表，Inceptor在使用数据字典时会自动切换为local mode执行，local模式下，Inceptor限制了单条sql的fetch执行时间，PL/SQL中的游标也受到了影响，游标打开之后获取结果，包括循环中其它sql的执行时间的总和，如果超时会被强制杀掉，执行结果就不完整。

  • 5.1之前

  • 可以延长超时时间做为workaround，设置参数ngmr.local.job.record.timeout.ms，默认为60000，也就是1分钟。

  • 5.1及之后

  • 增加了一个参数plsql.cursor.local.job.record.timeout.weight，默认值100，所以在PL/SQL中，游标的执行时间（包括获取结果集，以及loop中语句的累计总执行时间）就是plsql.cursor.local.job.record.timeout.weight乘以ngmr.local.job.record.timeout.ms，默认是100*60秒=1.5小时。如果还有超时可以调大plsql.cursor.local.job.record.timeout.weight。

参考WIKI
http://172.16.1.168:8090/pages/viewpage.action?pageId=19531569

这篇文章对您有帮助吗？

平均评分 5 / 5. 次数: 3

尚无评价，您可以第一个评哦！

非常抱歉，这篇文章对您没有帮助.

烦请您告诉我们您的建议与意见，以便我们改进，谢谢您。