Oracle12C--游标变量(三十八)

版权声明:大侠,在转载时请注明出处,小弟不胜感激 https://blog.csdn.net/qq_33301113/article/details/72864068
  • 啥是游标变量?
    • 在定义游标时,不绑定具体的查询,而是动态地打开指定类型的查询;
    • 使用游标变量,首先应像集合那样定义一种新的游标变量类型,语法:

CURSOR 游标变量类型名称 IS REF CURSOR [RETURN 数据类型];

RETURN子句是可选的,不编写此子句,表示此游标可以保存任何的查询结果(属于弱类型游标变量);如果编写了RETURN子句,表示此游标只能是特定类型,匹配指定的查询返回结果(属于强类型游标变量)

  • 一众栗子
    • 示例1:定义一个游标类型,此游标类型为dept类型

 

DECLARE

TYPE dept_ref IS REF CURSOR RETURN dept%ROWTYPE ; -- 定义游标类型

cur_dept dept_ref ; -- 定义游标变量

v_deptRow dept%ROWTYPE ; -- 定义行类型

BEGIN

OPEN cur_dept FOR SELECT * FROM dept ; -- 打开游标

LOOP

FETCH cur_dept INTO v_deptRow ; -- 取得游标数据

EXIT WHEN cur_dept%NOTFOUND ; -- 如果没有数据则退出

DBMS_OUTPUT.put_line('部门名称:' || v_deptRow.dname || ',部门位置:' || v_deptRow.loc) ;

END LOOP ;

CLOSE cur_dept ; -- 关闭游标

END ;

/

运行结果:

部门名称:ACCOUNTING,部门位置:NEW YORK

部门名称:RESEARCH,部门位置:DALLAS

部门名称:SALES,部门位置:CHICAGO

部门名称:OPERATIONS,部门位置:BOSTON
分析:
本程序先定义了一个可以包含
dept结构的强类型游标变量,所以在程序主体打开游标时所指定的查询语句必须是dept的返回结构,之后继续采用LOOP循环,通过DETCH....INTO语句取得游标中的每一行数据进行数据。

如果此时要修改弱类型游标,只需要将RETURN取消即可,语法:
TYPE dept_ref IS REF CURSOR; --定义弱游标类型,如果查询的是dept表,就表示dept型的游标,如果是emp表,就表示emp型的游标

注意:设置为弱类型游标,操作的ROWTYPE必须与游标类型相符,如果改为其他类型的结果,程序会出现ROWTYPE_MISMATCH异常:
示例:设置错误的数据结构

DECLARE

TYPE dept_ref IS REF CURSOR ; -- 定义游标类型

cur_dept dept_ref ; -- 定义游标变量

v_deptRow dept%ROWTYPE ; -- 定义行类型

BEGIN

OPEN cur_dept FOR SELECT * FROM emp ; -- 打开游标,类型错误

LOOP

FETCH cur_dept INTO v_deptRow ; -- 取得游标数据

EXIT WHEN cur_dept%NOTFOUND ; -- 如果没有数据则退出

DBMS_OUTPUT.put_line('部门名称:' || v_deptRow.dname || ',部门位置:' || v_deptRow.loc) ;

END LOOP ;

CLOSE cur_dept ; -- 关闭游标

EXCEPTION

WHEN ROWTYPE_MISMATCH THEN

DBMS_OUTPUT.put_line('游标数据类型不匹配异常。SQL CODE = ' || SQLCODE || 'SQLERRM = ' || SQLERRM) ;

END ;

/

运行结果:
游标数据类型不匹配异常。SQL CODE = -6504,SQLERRM = ORA-06504: PL/SQL: 结果集变量或查询的返回类型不匹配
分析:
本程序在声明部分定义的是一个
dept_red的弱类型游标变量类型,但是在程序主体部分打开游标时所设置的数据类型并不是dept,而是emp表的数据类型,所以产生了异常。本程序也采用了异常处理保证程序正常执行;

  • 示例2:在开发中使用弱类型游标,可以利用此游标重复操作多种结构:

DECLARE

TYPE cursor_ref IS REF CURSOR ;        -- 定义游标类型

cur_var                cursor_ref ;                -- 定义游标变量

v_deptRow                dept%ROWTYPE ;        -- 定义行类型

v_empRow                emp%ROWTYPE ;        -- 定义行类型

BEGIN

OPEN cur_var FOR SELECT * FROM dept ;        -- 打开游标

LOOP

FETCH cur_var INTO v_deptRow ;        -- 取得游标数据

EXIT WHEN cur_var%NOTFOUND ;        -- 如果没有数据则退出

DBMS_OUTPUT.put_line('1、部门名称:' || v_deptRow.dname || ',部门位置:' || v_deptRow.loc) ;

END LOOP ;

CLOSE cur_var ;

END ;

/

运行结果:

1、部门名称:ACCOUNTING,部门位置:NEW YORK

1、部门名称:RESEARCH,部门位置:DALLAS

1、部门名称:SALES,部门位置:CHICAGO

1、部门名称:OPERATIONS,部门位置:BOSTON
分析:
本程序定义了一个弱类型的游标变量类型,这样在程序主体打开游标时,不管针对何种返回结果,都可以使用同一种游标变量类型进行接收,而在使用
LOOP循环输出时,根据给定的查询列的名称进行内容的取得;

写法2:在Oracle9i之后,提供了"sys_refcursor"子句来替代"TYPE cursor_ref IS REF CURSOR"这种写法。更新上面的示例:
 

DECLARE

cur_var                sys_refcursor ;                                -- 定义游标变量

v_deptRow                dept%ROWTYPE ;                -- 定义行类型

v_empRow                emp%ROWTYPE ;                -- 定义行类型

BEGIN

OPEN cur_var FOR SELECT * FROM dept ;        -- 打开游标

LOOP

FETCH cur_var INTO v_deptRow ;        -- 取得游标数据

EXIT WHEN cur_var%NOTFOUND ;        -- 如果没有数据则退出

DBMS_OUTPUT.put_line('1、部门名称:' || v_deptRow.dname || ',部门位置:' || v_deptRow.loc) ;

END LOOP ;

CLOSE cur_var ;

END ;

/

得到的运行结果是一样的!

展开阅读全文

没有更多推荐了,返回首页