显示游标的使用详解

显 示游标就是在声明单元明确定义的SELECT子句，并同时指定一个名字
通过这个名字规范化地引用游标属性，同时在OPEN,FETCH,CLOSE语句中通过名字来引用显示游标
INSERT,UPDATE,DELETE或者MERGE都没有显示游标这一说

㈠ 声明显示游标

语法：
CURSOR cursor_name [ ( [parameter [,parameter...] ) ] [RETURN return_specification]
IS
SELECT_statement
这里Think建议，大家把游标放在包中定义
这是一处声明，到处使用的方式
以后要改善维护这个查询也就变得更加容易，而且能最小化查询语句的解析次数，同样可从性能上获益
不过千万注意，在包级别定义，游标的生命是持续于整个会话范围
这也意味着一个包级别的游标会一直保持打开状态，除非我们显示关闭该游标或者杀掉session
下面对可选部分的参数列表和RETURN作详尽说明

⑴ 为什么要使用RETURN这个语句呢？
首先，使用RETURN语句，实际上相当于公开声明了每次FETCH操作会返回的数据结构
也就是，游标返回什么样的记录，返回的顺序是什么，都包含了哪些列等这些信息都已大白天下
这就带来一个巧妙的地方，即我们可以把游标头和游标体分隔开来，比如：
PACKAGE emp_info
IS
CURSOR emp_cur (name_in IN emp.name%TYPE) RETURN emp%ROWTYPE;
END;

PACKAGE BODY emp_info
IS
CURSOR emp_cur (name_in IN emp.name%TYPE) RETURN emp%ROWTYPE
IS
SELECT * FROM EMP WHERE name LIKE name_in;
END;
显然，包体的游标实际上是个黑盒子
这带来两点好处：
--隐藏信息，包体如何实现就可以变得非常神秘
--最小化重编译，我们可以按需求的变化修改包体内游标的实现而不会影响到包头的游标规范
这也意味着所有依赖于这个包的程序都不会被置成无效状态，自然也无须重编译
游标的RETURN语句，可以由下面任意一种数据类型组成：
▲ table_name%ROWTYPE：基于某个数据库表定义的记录类型
▲ cursor_name%ROWTYPE：基于某个已经定义好的游标定义的记录类型
▲ record_type%ROWTYPE：基于程序员自定义的记录类型
这里还有一个需要注意的是，SELECT 列表和RETURN的返回记录，他俩：
--列的数量要相匹配
--记录的数据类型也须要相互匹配

⑵ 啥时需要把我们的游标参数化呢？
如果我们要在多个地方使用一个游标，每次只是WHERE子句的值不同，我就可以创建一个带参的游标，比如：
DECLARE
CURSOR cursor_name (par_in IN VARCHAR2)
IS
SELECT emp_id,emp_name FROM emps
WHERE emp_name=UPPER(par_in);
游标中最常见的会使用参数的地方就是WHERE子句，不过也可以在SELECT语句中的任何地方使用参数，比如：
DECLARE
CURSOR cursor_name (par_in IN VARCHAR2)
IS
SELECT emp_name,par_in,job FROM emps
WHERE emp_name=UPPER(par_in);
游标参数的作用范围受限于游标，我们无法在游标关联的SELECT语句之外引用游标参数，如，执行单元处引用则无法编译通过
游标参数只能是一个IN型参数，游标是不能通过参数列表把值传出去的
游标参数化大抵有两个好处：
① 避免WHERE过滤条件是硬编码，让游标更好的重用
② 避免游标作用范围的问题，我们可在外层块定义游标，然后在内层块用局部变量调用这个游标

㈡ 打开显示游标

语法：
OPEN cursor_name [ ( parameter [,parameter...] ) ];
Think认为，这里最重要的是关注Oracle的读一致性
当我们打开一个游标时，PL/SQL会执行这个游标的查询语句，并标识出活跃数据集--符合WHERE过滤的记录
但是OPEN不会真正提取出任何一行数据，这个动作是由FETCH来完成
然而，无论我们什么时候开始第一次FETCH数据，Oracle都会保证所有的FETCH反映的都是游标打开那一刻的数据状态
也就是，从打开游标的那一刻直到游标关闭的那一刻，通过游标获取的数据会自动忽略掉游标打开以后
其他活动会话所进行的插入、更新、删除等操作
如果我们使用FOR UPDATE子句，所有的活跃数据集在游标打开那时刻都会被锁定
这就是Oracle的读一致性
Oracle利用SCN来实现这个理论，开始查询时，会确定一个SELECT SCN，这样就保证了事务槽里所有的SCN都小于SELECT SCN

㈢ 从显示游标中提取数据

在PL/SQL程序中，从某个意义讲，游标可简单理解为一个表
行：由WHERE子句确定，如无，则全
列：SELECT列表
语法：
FETCH cursor_name INTO record_or_variable_list;
这里，Think建议，我们应该总是把数据取到用游标的%ROWTYPE定义的记录中，比如：
DECLARE
CURSOR cur IS SELECT * FROM ...;
think_cur cur%ROWTYPE;
BEGIN
OPEN cur;
FETCH cur INTO think_cur
在检查FETCH操作是否成功，应该用游标的%FOUND或%NOTFOUND属性值，而不能用INTO中的变量值来确定
原因很简单，就是当所有的活跃数据集都被FETCH完，其实，我们还是可以往下FETCH而没有异常抛出

㈣ 显示游标中的列别名

有两个地方需要涉及到列别名
● 把数据提取到用该游标的%ROWTYPE属性声明的记录中
● 我们想在程序中使用该列
比如：
DECLARE
CURSOR cur IS SELECT SUM(salary) total_sum FROM ...;
think_cur cur%ROWTYPE;
BEGIN
OPEN cur;
FETCH cur INTO think_cur；
或者
IF think_cur.total_sum THEN ...


㈤ 关闭显示游标

当用完一个游标后，我们一定要确保关闭这个游标
语法：
CLOSE cursor_name;
对于包级别的游标，一旦我们使用完毕，就要立即跟上一个CLOSE语句，这一点尤为重要！
比如：
BEGIN
OPEN my_package.cursor_name;
...
...
CLOSE my_package.cursor_name;
EXCEPTION
WHEN OTHERS
THEN
IF my_package.cursor_name%ISOPEN
THEN
CLOSE my_package.cursor_name;
END IF;
END;
我们只能关闭当前处于打开状态的游标，可借助%ISOPEN判断：
IF cursor_name%ISOPEN
THEN
CLOSE cursor_name；
END IF;
如果我们打开的游标超过会话级别设定的数量（OPEN_CUSSORS）,则会抛出：
ORA-01000：maximum open cursors exceeded
如果遇到这个异常，Think认为，应首先定位到，包级别游标的使用情况