ORACLE游标详解

啥是游标变量？

---

在定义游标时，不绑定具体的查询，而是动态地打开指定类型的查询；
使用游标变量，首先应像集合那样定义一种新的游标变量类型，语法：
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 ;

---

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

作者：-Hermes-
来源：CSDN
原文：https://blog.csdn.net/qq_33301113/article/details/72864068
版权声明：本文为博主原创文章，转载请附上博文链接！