过程返回游标实例

开发中有时候需要在程序里直接调用过程进行处理，并获得返回值，这时候可以通过游标返回。

定义返回游标包：

CREATE OR REPLACE PACKAGE HIGHSOFT_TYPES  is

  type dsCursor is REF CURSOR;

end Highsoft_Types;

游标定义时引用包：

O_CURSOR OUT HIGHSOFT_TYPES.dsCursor

过程最后用游标返回结果：

OPEN O_CURSOR FOR

CREATE OR REPLACE PROCEDURE YK_P_BIG_YK_AVG_TIME

(IN_BEG_DATE DATE,
 IN_END_DATE DATE,
 IN_USER_FLAG CHAR,
 O_CURSOR OUT HIGHSOFT_TYPES.dsCursor) IS

  /*各阶段工单平均用时情况
   *in_beg_date 开始日期
   *in_end_date 结束日期
   *in_flag 3高压单 ；4高压双
  */

  V_RESULT      INTEGER;
  V_BUSI_CODE   VARCHAR2(48);
  V_BUSI_NAME   VARCHAR2(48);
  --V_PROMISE1 NUMBER(5);
  --V_PROMISE2 NUMBER(5);
  --V_PROMISE3 NUMBER(5);
  --V_PROMISE4 NUMBER(5);
BEGIN
  V_RESULT := -1;
  --V_PROMISE1 := 1+5+3+3+3; --居民
  --V_PROMISE2 := 2+5+3+3+5; --低压非居民
  --V_PROMISE3 := 5+15+5+5+7; --高压单电源
  --V_PROMISE4 := 7+15+5+5+7; --高压双电源

--取供电局代码
 SELECT A.BUSINESS_PLACE_CODE, A.BUSINESS_PLACE_NAME
   INTO V_BUSI_CODE, V_BUSI_NAME
   FROM BUSINESS_PLACE_INFO A
  WHERE A.F_BUSINESS_PLACE_CODE IS NULL;

  --删除表数据，由于跨日查询，所以不保留历史数据
  DELETE FROM YK_BIG_YK_AVG_TIME;

--插入营业区域数据
INSERT INTO YK_BIG_YK_AVG_TIME
  (BEGIN_DATE,
   END_DATE,
   BUSI_NAME,
   BUSINESS_PLACE_CODE,
   REPLY_ADVICE,
   REPLY_TIME,
   REPLY_AVG,
   ACCEPT_DESIGN,
   ACCEPT_DESIGN_TIME,
   ACCEPT_DESIGN_AVG,
   EXAMINE_ADVICE,
   EXAMINE_ADVICE_TIME,
   EXAMINE_ADVICE_AVG,
   APPLY_EXAM,
   APPLY_EXAM_TIME,
   APPLY_EXAM_AVG,
   ALL_AVG_TIME,
   ALL_AVG_PROMISE)

  SELECT IN_BEG_DATE,
         IN_END_DATE,
         V_BUSI_NAME,
         T2.NEXT_DEPT_CODE,
         REPLY_ADVICE,
         REPLY_TIME,
         ROUND(DECODE(REPLY_ADVICE, 0, 0, REPLY_TIME / REPLY_ADVICE), 2) REPLY_AVG,
         ACCEPT_DESIGN,
         ACCEPT_DESIGN_TIME,
         ROUND(DECODE(ACCEPT_DESIGN,
                      0,
                      0,
                      ACCEPT_DESIGN_TIME / ACCEPT_DESIGN),
               2) ACCEPT_DESIGN_AVG,
         EXAMINE_ADVICE,
         EXAMINE_ADVICE_TIME,
         ROUND(DECODE(EXAMINE_ADVICE,
                      0,
                      0,
                      EXAMINE_ADVICE_TIME / EXAMINE_ADVICE),
               2) EXAMINE_ADVICE_AVG,

         APPLY_EXAM,
         APPLY_EXAM_TIME,
         ROUND(DECODE(APPLY_EXAM, 0, 0, APPLY_EXAM_TIME / APPLY_EXAM), 2) APPLY_EXAM_AVG,

         --各个阶段平均用时累计
         ROUND(DECODE(FIVE_PHASE_ALL_NUM,
                      0,
                      0,
                      FIVE_PHASE_ALL_TIME / FIVE_PHASE_ALL_NUM),
               2) ALL_AVG_TIME,
         --各个阶段平均用时与承诺对比
         ROUND((DECODE(ROUND(DECODE(FIVE_PHASE_ALL_NUM,
                                    0,
                                    0,
                                    FIVE_PHASE_ALL_TIME / FIVE_PHASE_ALL_NUM),
                             2),
                       0,
                       0,
                       ROUND(DECODE(FIVE_PHASE_ALL_NUM,
                                     0,
                                     0,
                                     FIVE_PHASE_ALL_TIME / FIVE_PHASE_ALL_NUM),
                              2) -
                       --承诺时间
                        15)),
               2) ALL_AVG_PROMISE
    FROM (SELECT V.DEPT_CODE,
                 --不允许为空
                 SUM(NVL(A.REPLY_ADVICE, 0)) REPLY_ADVICE,
                 SUM(DECODE(A.REPLY_ADVICE, 1, NVL(A.REPLY_TIME, 0), 0)) REPLY_TIME,

                 SUM(NVL(A.ACCEPT_DESIGN, 0)) ACCEPT_DESIGN,
                 SUM(DECODE(A.ACCEPT_DESIGN,
                            1,
                            NVL(A.ACCEPT_DESIGN_TIME, 0),
                            0)) ACCEPT_DESIGN_TIME,

                 SUM(NVL(A.EXAMINE_ADVICE, 0)) EXAMINE_ADVICE,
                 SUM(DECODE(A.EXAMINE_ADVICE,
                            1,
                            NVL(A.EXAMINE_ADVICE_TIME, 0),
                            0)) EXAMINE_ADVICE_TIME,

                 SUM(NVL(A.APPLY_EXAM, 0)) APPLY_EXAM,
                 SUM(DECODE(A.APPLY_EXAM, 1, NVL(A.APPLY_EXAM_TIME, 0), 0)) APPLY_EXAM_TIME,

                 --5个考核阶段工单累计的总用时
                 SUM(NVL(A.REPLY_TIME, 0) + NVL(A.ACCEPT_DESIGN_TIME, 0) +
                     NVL(A.EXAMINE_ADVICE_TIME, 0) +
                     NVL(A.APPLY_EXAM_TIME, 0)) FIVE_PHASE_ALL_TIME,
                 --工单数量
                 COUNT(1) FIVE_PHASE_ALL_NUM
            FROM YK_BIG_YK_APP_SERVE A, VIEW_NEXT_BUSI V
           WHERE A.BUSINESS_PLACE_CODE = V.NEXT_DEPT_CODE
             AND A.FILES_DATE >= IN_BEG_DATE
             AND A.FILES_DATE <= IN_END_DATE
             AND A.USER_FLAG = IN_USER_FLAG
           GROUP BY V.DEPT_CODE) T1,
         --关联营业区域
         (SELECT T.NEXT_DEPT_CODE
            FROM VIEW_NEXT_BUSI T
           WHERE T.DEPT_CODE = V_BUSI_CODE) T2
   WHERE T1.DEPT_CODE(+) = T2.NEXT_DEPT_CODE
   ORDER BY T1.DEPT_CODE;

 COMMIT;
 --返回游标
 OPEN O_CURSOR FOR
SELECT TO_CHAR(Y.BEGIN_DATE, 'YYYY-MM-DD') BEGIN_DATE1,
       TO_CHAR(Y.END_DATE, 'YYYY-MM-DD') END_DATE1,
       (SELECT B.BUSINESS_PLACE_NAME
          FROM BUSINESS_PLACE_INFO B
         WHERE B.BUSINESS_PLACE_CODE = Y.BUSINESS_PLACE_CODE) BUSINESS_PLACE_NAME,
       BUSINESS_PLACE_CODE,
       REPLY_ADVICE,
       REPLY_TIME,
       REPLY_AVG,
       ACCEPT_DESIGN,
       ACCEPT_DESIGN_TIME,
       ACCEPT_DESIGN_AVG,
       EXAMINE_ADVICE,
       EXAMINE_ADVICE_TIME,
       EXAMINE_ADVICE_AVG,
       APPLY_EXAM,
       APPLY_EXAM_TIME,
       APPLY_EXAM_AVG,
       ALL_AVG_TIME,
       ALL_AVG_PROMISE,
       BEGIN_DATE,
       END_DATE,
       BUSI_NAME
  FROM YK_BIG_YK_AVG_TIME Y
     ORDER BY Y.BUSINESS_PLACE_CODE;
 EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
    P_PUB_ERROR_LOG(V_RESULT, SQLCODE, SQLERRM, 'YK_P_BIG_YK_AVG_TIME');
END;