使用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,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,NUM)
          SELECT V_TYPE,V_FUN_ID,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,7) ;        
    ELSE 
        INSERT INTO TMP_TJ_CREDIT(BIZ_TYPE,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,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,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,YM_DATE YM,NUM,SNUM,PE
    FROM(
      SELECT BIZ_TYPE,PE
      FROM TMP_TJ_CREDIT
      ORDER BY BIZ_TYPE,YM_DATE
    ) A ;
  
END P_RPT_TJ_CREDIT;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!