LightDB游标的声明和使用

首先来看看oracle官网对于游标的说明：

To create a cursor variable, either declare a variable of the predefined type SYS_REFCURSOR or define a REF CURSOR type and then declare a variable of that type.

If you specify return_type, then the REF CURSOR type and cursor variables of that type are strong; if not, they are weak. SYS_REFCURSOR and cursor variables of that type are weak.

With a strong cursor variable, you can associate only queries that return the specified type. With a weak cursor variable, you can associate any query.

Weak cursor variables are more error-prone than strong ones, but they are also more flexible. Weak REF CURSOR types are interchangeable with each other and with the predefined type SYS_REFCURSOR. You can assign the value of a weak cursor variable to any other weak cursor variable.

翻译过来简单讲就是oracle有两类游标，一类是不指定返回类型的弱类型游标，另一类是指定返回类型的强类型游标。弱类型可以通过ref cursor再加上无返回类型来声明，也可以通过sys_refcursor来声明。弱类型灵活通用但容易出错，强类型则相反。

LightDB一直在努力兼容主流数据库的各种用法，现已兼容oracle游标的使用：

DECLARE
  TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;  -- strong type, with return type
  TYPE genericcurtyp IS REF CURSOR;                 -- weak type, without return type

  cursor1  empcurtyp;       -- strong cursor variable
  cursor2  genericcurtyp;   -- weak cursor variable
  my_cursor SYS_REFCURSOR;  -- weak cursor variable
  
  v_emp emp%ROWTYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('---------------strong type----------------');
  open cursor1 for select * from emp;
  LOOP  -- Fetch into variables
    FETCH cursor1 INTO v_emp;
    EXIT WHEN cursor1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename||' '||v_emp.job);
  END LOOP;
  CLOSE cursor1;
  
  DBMS_OUTPUT.PUT_LINE('--------------weak type-----------------');
  open cursor2 for select * from emp;
  LOOP  -- Fetch into variables
    FETCH cursor2 INTO v_emp;
    EXIT WHEN cursor2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename||' '||v_emp.job);
  END LOOP;
  CLOSE cursor2;
  
  DBMS_OUTPUT.PUT_LINE('--------------weak type(sys_refcursor)-----------------');
  open my_cursor for select * from emp;
  LOOP  -- Fetch into variables
    FETCH my_cursor INTO v_emp;
    EXIT WHEN my_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename||' '||v_emp.job);
  END LOOP;
  CLOSE my_cursor;
END;
/

以上代码在LightDB中的执行结果如下：

lightdb@test_oracle_plorasql=# DECLARE
lightdb@test_oracle_plorasql$#   TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;  -- strong type
lightdb@test_oracle_plorasql$#   TYPE genericcurtyp IS REF CURSOR;                       -- weak type
lightdb@test_oracle_plorasql$# 
lightdb@test_oracle_plorasql$#   cursor1  empcurtyp;       -- strong cursor variable
lightdb@test_oracle_plorasql$#   cursor2  genericcurtyp;   -- weak cursor variable
lightdb@test_oracle_plorasql$#   my_cursor SYS_REFCURSOR;  -- weak cursor variable
lightdb@test_oracle_plorasql$#   
lightdb@test_oracle_plorasql$#   v_emp emp%ROWTYPE;
lightdb@test_oracle_plorasql$# BEGIN
lightdb@test_oracle_plorasql$#   DBMS_OUTPUT.PUT_LINE('---------------strong type----------------');
lightdb@test_oracle_plorasql$#   open cursor1 for select * from emp;
lightdb@test_oracle_plorasql$#   LOOP  -- Fetch into variables
lightdb@test_oracle_plorasql$#     FETCH cursor1 INTO v_emp;
lightdb@test_oracle_plorasql$#     EXIT WHEN cursor1%NOTFOUND;
lightdb@test_oracle_plorasql$#     DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename||' '||v_emp.job);
lightdb@test_oracle_plorasql$#   END LOOP;
lightdb@test_oracle_plorasql$#   CLOSE cursor1;
lightdb@test_oracle_plorasql$#   
lightdb@test_oracle_plorasql$#   DBMS_OUTPUT.PUT_LINE('--------------weak type-----------------');
lightdb@test_oracle_plorasql$#   open cursor2 for select * from emp;
lightdb@test_oracle_plorasql$#   LOOP  -- Fetch into variables
lightdb@test_oracle_plorasql$#     FETCH cursor2 INTO v_emp;
lightdb@test_oracle_plorasql$#     EXIT WHEN cursor2%NOTFOUND;
lightdb@test_oracle_plorasql$#     DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename||' '||v_emp.job);
lightdb@test_oracle_plorasql$#   END LOOP;
lightdb@test_oracle_plorasql$#   CLOSE cursor2;
lightdb@test_oracle_plorasql$#   
lightdb@test_oracle_plorasql$#   DBMS_OUTPUT.PUT_LINE('--------------weak type(sys_refcursor)-----------------');
lightdb@test_oracle_plorasql$#   open my_cursor for select * from emp;
lightdb@test_oracle_plorasql$#   LOOP  -- Fetch into variables
lightdb@test_oracle_plorasql$#     FETCH my_cursor INTO v_emp;
lightdb@test_oracle_plorasql$#     EXIT WHEN my_cursor%NOTFOUND;
lightdb@test_oracle_plorasql$#     DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename||' '||v_emp.job);
lightdb@test_oracle_plorasql$#   END LOOP;
lightdb@test_oracle_plorasql$#   CLOSE my_cursor;
lightdb@test_oracle_plorasql$# END;
lightdb@test_oracle_plorasql$# /
---------------strong type----------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
--------------weak type-----------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
--------------weak type(sys_refcursor)-----------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
DO
lightdb@test_oracle_plorasql=#