使用sys_refcursor和临时表返回oracle结果集

使用sys_refcursor和临时表返回oracle结果集

订阅专栏
sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集。 如果仅仅为了返回值，无需自己在包头中定义游标类型，只需直接使用sys_refcursor即可轻松返回结果。

   但如果返回结果需要中间计算，就需要临时表进行存储，最后返回。

  1）ON COMMIT DELETE ROWS
   它是临时表的默认参数，表示临时表中的数据仅在事物过程（Transaction）中有效，当事物提交（COMMIT）后，临时表的暂时段将被自动截断（TRUNCATE），但是临时表的结构 以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

  2）ON COMMIT PRESERVE ROWS
  它表示临时表的内容可以跨事物而存在，不过，当该会话结束时，临时表的暂时段将随着会话的结束而被丢弃，临时表中的数据自然也就随之丢弃。但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

 会话，指的是客户端连接到服务器的一个上下文环境。它是一个逻辑概念。你比如，创建了一个会话，这个会话记录了客户端的ip，客户端的用户名等信息。以及发布的sql命令的游标等。这些信息都是存储在内存中的。oracle实例赋予一个id（session——id）来逻辑地把这些信息组织在一起。
事务，指的是你在当前会话中要做的一件事情，而这件事情包括好多步操作。任何一步操作失败，你要做的整个这个事情也会失败。这就是事务的原子性。
会话和事务其实在概念上没有多大的联系，所以也就没有什么区别之分。给你举个例子来说明会话与事物的一个大概关系。
比如，你要给你家人拜年（这是一个事物）。前提是你必须先联系上你的家人，假如你打电话给你家人，当接通时你们就产生了一个会话，然后你就可以拜年了（就可以进行事务了）。

由此可见，事务的运行必须建立在会话的基础上，但是有了会话不一定有事务（可以空闲，比如你接通了电话，什么都不说）。

下面是一个实例，外面传递参数，查询结果插入临时表，然后再对临时表数据进行处理，最后返回结果集。一些和业务相关的内容，读者可以略过，主要关注sys_refcursor及临时表的用法。

create or replace PROCEDURE P_RPT_TJ_CREDIT (
V_TYPE VARCHAR2
, V_FUN_IDS IN OUT CLOB
, V_RESULT OUT SYS_REFCURSOR --系统游标变量,方便返回记录集
) AS
V_SQL VARCHAR2(2048);
V_STR VARCHAR2(2048);

TYPE CV_FUNS IS REF CURSOR; -- 定义动态游标
CV_FUN CV_FUNS;

V_FUN_NAME VARCHAR2(128);
V_FUN_KIND VARCHAR2(64);
V_ROUTE_ID VARCHAR2(2048);

V_FUN_ID VARCHAR2(36);
V_INX NUMBER(4);

V_BIZ_TYPE VARCHAR2(24);
V_YM_DATE CHAR(7);
V_NUM NUMBER(10) DEFAULT 0;
V_SNUM NUMBER(10) DEFAULT 0;
V_PE NUMBER(12,2) DEFAULT 0;

CURSOR CV_RESULTS IS
SELECT BIZ_TYPE,FUNCTION_ID,YM_DATE,NUM
FROM TMP_TJ_CREDIT
ORDER BY BIZ_TYPE,FUNCTION_ID,YM_DATE;

BEGIN
DELETE FROM TMP_TJ_CREDIT; -- 清除临时表数据

V_FUN_IDS := '''' || REPLACE(V_FUN_IDS,',',''',''') || '''';
V_SQL := 'SELECT ID,FUNCTION_NAME,FUNCTION_KIND,ROUTE_ID FROM SYS_FUNCTION WHERE ID IN (' || V_FUN_IDS || ')';
dbms_output.put_line(V_SQL);
OPEN CV_FUN FOR V_SQL;

LOOP
FETCH CV_FUN INTO V_FUN_ID,V_FUN_NAME,V_FUN_KIND,V_ROUTE_ID;
EXIT WHEN CV_FUN%NOTFOUND;

-- 循环插入每个给定菜单的数据统计值（每个菜单及其子菜单在每月的数量）  
IF V_TYPE = 'CREDIT' THEN
    INSERT INTO TMP_TJ_CREDIT(BIZ_TYPE,FUNCTION_ID,FUNCTION_NAME,YM_DATE,NUM)
      SELECT V_TYPE,V_FUN_ID,V_FUN_NAME,SUBSTR(CREATE_DATE,1,7) YM ,SUM(TJ_NUM) NUM 
        FROM TJ_CREDIT_DETAIL_INFO 
        WHERE (CHECK_FLAG = '1' OR CHECK_FLAG = '2')
            AND ( FUNCTION_ID = V_FUN_ID OR FUNCTION_ID IN (SELECT ID FROM SYS_FUNCTION WHERE ROUTE_ID LIKE '%' || V_ROUTE_ID || '%') )
        GROUP BY V_TYPE,V_FUN_ID,V_FUN_NAME,SUBSTR(CREATE_DATE,1,7) ;        
ELSE 
    INSERT INTO TMP_TJ_CREDIT(BIZ_TYPE,FUNCTION_ID,FUNCTION_NAME,YM_DATE,NUM)
      SELECT V_TYPE,V_FUN_ID,V_FUN_NAME,SUBSTR(CREATE_DATE,1,7) YM ,SUM(TJ_NUM) NUM 
        FROM LOG_DATA_RESULT 
        WHERE (CHECK_FLAG = '1' OR CHECK_FLAG = '2')
            AND ( FUNCTION_ID = V_FUN_ID OR FUNCTION_ID IN (SELECT ID FROM SYS_FUNCTION WHERE ROUTE_ID LIKE '%' || V_ROUTE_ID || '%') )
        GROUP BY V_TYPE,V_FUN_ID,V_FUN_NAME,SUBSTR(CREATE_DATE,1,7) ;  
END IF;

END LOOP;

--循环计算上月数据及其增长率（同一菜单,且月份是递增（步长1））
V_INX := 0;
V_FUN_ID := NULL;
FOR CV_RESULT IN CV_RESULTS LOOP
IF V_FUN_ID = CV_RESULT.FUNCTION_ID AND V_YM_DATE != CV_RESULT.YM_DATE THEN
IF V_YM_DATE IS NOT NULL AND ( TO_NUMBER(SUBSTR(V_YM_DATE,6,2)) + 1 = TO_NUMBER(SUBSTR(CV_RESULT.YM_DATE,6,2)) ) THEN
V_SNUM := V_NUM;
IF V_SNUM != 0 THEN
V_PE := ROUND((CV_RESULT.NUM -V_SNUM) / V_SNUM *100,2);
END IF;
END IF;
ELSE
V_SNUM := 0;
V_PE := 0;
END IF;

UPDATE TMP_TJ_CREDIT SET SNUM = V_SNUM ,PE = V_PE
WHERE FUNCTION_ID = CV_RESULT.FUNCTION_ID AND YM_DATE = CV_RESULT.YM_DATE;

V_BIZ_TYPE := CV_RESULT.BIZ_TYPE;
V_FUN_ID   := CV_RESULT.FUNCTION_ID;
V_YM_DATE  := CV_RESULT.YM_DATE;
V_NUM      := CV_RESULT.NUM; 

V_SNUM := 0;
V_PE   := 0;    

END LOOP;

--最后返回结果，包括序号
OPEN V_RESULT FOR
SELECT ROWNUM,BIZ_TYPE TYPE,FUNCTION_ID,FUNCTION_NAME,YM_DATE YM,NUM,SNUM,PE
FROM(
SELECT BIZ_TYPE ,FUNCTION_ID,FUNCTION_NAME,YM_DATE,NUM,SNUM,PE
FROM TMP_TJ_CREDIT
ORDER BY BIZ_TYPE,FUNCTION_ID,YM_DATE
) A ;

END P_RPT_TJ_CREDIT;

————————————————
版权声明：本文为CSDN博主「梦想画家」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/neweastsun/article/details/52618581