PLSQL入门与精通（第81章：利用游标进行递归调用的时候游标数超标问题）

这次我们重点说明一下递归调用的注意点。
递归调用，因为循环处理次数很多，所以他是非常容易消耗资源的，这是我们要注意的。
特别是在进行显示的游标处理时，如果关闭游标是在递归调用前进行的话，随着调用的传递，
打开的游标数量会不停地增加，若超过游标数量的上限，则会出错：“ORA-01000：超过了开启的最大游标数”。
因此，要注意显式游标和递归调用组合使用的时候的这个问题。

接下来我们针对这个问题举例说明：

我们接着使用第79章提到的例子：“用递归调用进行阶层查询”，
我们用递归调用+显式游标的方式进行处理。

程序ID用PROC79，例子内容如下：
指定起点的员工，在职员表中找到他的下级，再继续找下级的下级，
再继续找下级的下级的下级…这样，不断地往下一个阶层查询下级。
如果使用显示游标，但一直没有关闭显式游标的处理直接进行递归调用处理，
这样的话等级越深，直到某一时刻就会发生“ORA-01000：超过了开启的最大游标数。”的错误。

首先，查看数据库设定的最大游标数的限制值，这个值用数据库的初始化参数“OPPEN CURSORS”来查询

show user
用户是“SYSTEM”。–管理员用户

SHOW PARAMETER OPEN CURSORS --参数OPEN CURSORS的值
NAME TYPE VALUE

---

open_cursors integer 300

目前数据库环境中，OPIN CURSORS=300。
也就是说，一个会话中可以打开游标的上限是300个。

所以如果用PROC79程序不停地查找下级的话，如果找到第300层的时候，
应为打开了300个游标，继续往下查找，就会发生“ORA-01000：超过了开启的最大游标数。”的错误。

为了再现这个错误，我们例子需要有300层以上的上下级关系的数据，
同时用PROC79函数，反复进行相同的处理的递归调用，每个递归处理里边都有一个游标被打开，而且没别关闭。

首先，创建TEST81表。是简单的员工表。在此加载与上级、下级相关的员工。只排最低需要的队伍。
创建以下的表：

CREATE TABLE TEST81
(
ID NUMBER CONSTRAINT TEST81_PK PRIMARY KEY , --职员编号，设置为主键，值是惟一的
NAME VARCHAR2(100), --职员姓名
MGR_ID NUMBER CONSTRAINT TEST81_SELF_FK REFERENCES TEST81(ID) --职员所属的上级职员编号，参展的本表ID作为外键。与ID是上下级关系
)

/

表创建成功。

职员编号，设置为主键，值是惟一的，我们假设用一个Sequence作为ID的值进行使用。我们创建一个SEQUENCE：
CREATE SEQUENCE SEQ_TEST81;

创建了顺序。

假设一个职员只有一个下级，模拟301员工,创建他们的上下级关系：

DECLARE
V_ID NUMBER; --所属上级员工ID
BEGIN
–创建最高级别的员工（相当于公司的董事长级别）
INSERT INTO TEST81(ID, NAME , MGR_ID) VALUES (SEQ_TEST81.NEXTVAL, ‘AAA’ , NULL ) --SEQ_TEST81.NEXTVAL默认是从1开始 没有上级，MG_ID列的值是NULL。
RETURNING ID INTO V_ID; --保存所属上级员工ID --这是将INSERT所使用的ID列的值赋值为变量V_ID的意思，作为下一行数据的MG_ID列的值来使用

--在下面连续形成300个阶层关系的员工（每个上级中只有一个下级，共301个员工）
FOR I IN 1..300 LOOP   --300次循环处理，
    INSERT INTO TEST81(ID, NAME , MGR_ID)  --第一次循环时，这里的INSERT语句的MG_ID列的值是上面取得的V_ID变量的值。
    VALUES (SEQ_TEST81.NEXTVAL, 'AAA' || TRIM(TO_CHAR(V_ID, '0000' )),V_ID)
    RETURNING ID INTO V_ID;   --循环的第N次的话，这个V_ID变量的值，是第N+1次的INSERT文的MG_ID列使用的值。
END LOOP;

END ;

/

PL/SQL函数成功完成。

这样，我们给张表插入了301条数据，他们有单一的层级关系，总共301层数据。

我们来查询这张表的数据：

SELECT * FROM TEST81 ORDER BY ID;
ID NAME MGR_ID

---

1 AAA
2 AAA0001 1
3 AAA0002 2
4 AAA0003 3
5 AAA0004 4
6 AAA0005 5
～省略中间数据～
299 AAA0298 298
300 AAA0299 299
301 AAA0300 300

选择了301行。

接下来，创建回调函数PROC81，用于表示上下级关系的数据。
这个函数的逻辑与第79章的PROC79基本相同。

CREATE OR REPLACE
PROCEDURE PROC81( P_EMPNO IN TEST81.ID%TYPE, P_LEVEL IN NUMBER DEFAULT 1)

/************************************************************************/
–用递归调用进行阶梯查询的函数（游标打开后进行递归调用）
/************************************************************************/
IS
REC TEST81%ROWTYPE;
BEGIN

/**************************************************************/
--取得指定员工的行
/**************************************************************/
SELECT * INTO REC FROM TEST81 WHERE ID = P_EMPNO;

/**************************************************************/
--显示该员工的信息
/**************************************************************/
DBMS_OUTPUT.PUT_LINE( TO_CHAR(P_LEVEL, '0000' ) || ' '
|| TO_CHAR(REC.ID, '0000' ) || ' ' || REC. NAME || ' ' || REC.MGR_ID);

/***********************************************************************/
--查找该员工的下级，每一层进行同样的处理
/**********************************************************************/
FOR REC IN ( SELECT * FROM TEST81 WHERE MGR_ID = P_EMPNO ORDER BY NAME ) LOOP  --游标FOR循环中进行递归调用。游标此处会被反复打开。但没有关闭
    PROC81(REC.ID，P LEVEL+1）；--递归调用
END LOOP;

END PROC81;
/

程序已创建。

上边的例子中，在游标FOR循环中进行递归调用。游标此处会被反复打开。但没有关闭
因此，在指定最上一层数据的职员编号1来执行这个程序的话，
反复查找下一级数据，会有300个游标同时被打开，再继续执行会达到打开游标的上限，从而会发生错误。

我们来实际执行一下。

SQL>SET SERVEROUT ON --画面输出DBMS OUTPUT有效

EXEC PROC81(1)
0001 0001 AAA
0002 0002 AAA0001 1
0003 0003 AAA0002 2
0004 0004 AAA0003 3
～中途省略～
0293 0293 AAA0292 292
0294 0294 AAA0293 293
0295 0295 AAA0294 294
0296 0296 AAA0295 295
0297 0297 AAA0296 296

BEGIN PROC81(1); END ;
*
行1发生错误。:

ORA-01000：超过最大开启游标数。

在目前的执行的环境中，到第297层就发生了错误。
这是因为在执行查询297员工下级的游标FOR循环句时，
由于超出了开启游标的最大数300，从而导致了错误发生。
注意：次数循环看是没有到300，但因为在这个会话中这个循环例子之前已经可能了可能打开的游标。

如果要不出现这个错误，让程序正常执行结束，我们就需要循环中关闭游标，再进行回调用，就OK了。
要实现这个功能，我们需要将游标获取的数据行暂时存储在数组变量中，紧接着吧打开的游标关闭掉。
后边处理使用那个配列，这样递归调用就不存在反复打开游标而没有关闭的问题了。

CREATE OR REPLACE
PROCEDURE PROC81( P_EMPNO IN TEST81.ID%TYPE, P_LEVEL IN NUMBER DEFAULT 1)
//
–递归调用进行阶梯查询的函数（关闭游标后再转回调用）
//
IS
REC TEST81%ROWTYPE;
TYPE REC_TAB_TYPE IS TABLE OF TEST81%ROWTYPE;
REC TAB REC TAB TYPE；–存储下级信息的数组变量（配列）
BEGIN
//
–取得指定员工的行
/*/

SELECT * INTO REC FROM TEST81 WHERE ID = P_EMPNO;

/**************************************************************/
--显示该员工的信息
/**************************************************************/
DBMS_OUTPUT.PUT_LINE( TO_CHAR(P_LEVEL, '0000' ) || ' '
|| TO_CHAR(REC.ID, '0000' ) || ' ' || REC. NAME || ' ' || REC.MGR_ID);

/***********************************************************************/
--查找该员工的下级，每一层进行同样的处理
/**********************************************************************/
--获取下级的信息并放到到数组变量重（获取后，未使用显示的游标）
SELECT * BULK COLLECT INTO REC_TAB FROM TEST81 WHERE MGR_ID = P_EMPNO ORDER BY NAME ;  --查询结果放到数组变量（REC_TAB）中,使用“SELECT BULK COLLECCT INTO语句”之后游标及时关闭。
IFREC TAB.COUNT > 0THEN  --当该员工存在下级时，进行以下处理
    FOR I IN REC_TAB.FIRST ..REC_TAB.LAST LOOP  --使用配列进行循环处理，没再使用游标
        PROC81（REC TAB（I）.ID，P LEVEL+1）；--对各下级员工进行递归调用
    END LOOP;
END IF;

END PROC81;
/

程序已创建。

实行一下。

SQL> EXEC PROC81(1)
0001 0001 AAA
0002 0002 AAA0001 1
0003 0003 AAA0002 2
～中途省略～
0298 0298 AAA0297 297
0299 0299 AAA0298 298
0300 0300 AAA0299 299
0301 0301 AAA0300 300

PL/SQL函数成功完成。

执行没有出错。说明开启游标的数量都不会增加，不会发生错误。

这就是本次的例子。谢谢各位。