Oracle带入参和出参存储过程的定义

发布于 2019-01-10  4682 次阅读


包头:声明的所有方法,包括存储函数、存储方法

 CREATE OR REPLACE PACKAGE PORTALATATISTICSPACKAGE AS     
--自定义游标
type PORTALATATISTICS is ref cursor;
--存储过程定义
   procedure PORTALATATISTICSList(startTime IN VARCHAR2,endTime IN VARCHAR2,portalList out PORTALATATISTICS);
END PORTALATATISTICSPACKAGE;
包体(需要实现包头中声明的所有方法,包括存储函数、存储方法):
 CREATE OR REPLACE PACKAGE BODY PORTALATATISTICSPACKAGE AS 
  procedure PORTALATATISTICSList(startTime IN VARCHAR2,endTime IN VARCHAR2,portalList out PORTALATATISTICS) AS
 BEGIN    
 open portalList for SELECT row_.*, rownum FROM (
  SELECT nvl(t1.ID, t0.region) AS region, t0.portal_num AS portal_num, t0.pay_num AS pay_num
    , t1.NAME AS region_DSC
  FROM (
    SELECT t0.region AS region, SUM(t0.portal_num) AS portal_num, SUM(t0.pay_num) AS pay_num
    FROM (
      SELECT SUBSTR(t0.REGION, 1, 4) AS region
        , COUNT(t0.USERCODE) AS portal_num, 0 AS pay_num
      FROM UUM211.USER_LOGINANDLOGOUT_LOG t0
      WHERE t0.OPERATE_TIME BETWEEN startTime AND endTime
      GROUP BY SUBSTR(t0.REGION, 1, 4)
      UNION ALL
      SELECT SUBSTR(t0.REGION, 1, 4) AS region, 0 AS portal_num
        , COUNT(t0.USERCODE) AS pay_num
      FROM UUM211.PAY_USER_LOGINANDLOGOUT_LOG t0
      WHERE t0.OPERATE_TIME BETWEEN startTime AND endTime
      GROUP BY SUBSTR(t0.REGION, 1, 4)
    ) t0
    GROUP BY t0.region
  ) t0, (
    SELECT SUBSTR(t0.ID, 1, 4) AS ID_, t0.NAME AS NAME
      , t0.ID AS ID
    FROM UUM211.UUM_REGION t0
    WHERE (SUBSTR(t0.ID, 1, 4) || '00') = t0.ID
  ) t1
  WHERE t0.region = t1.ID_(+)
  ORDER BY nvl(t1.ID, t0.region) NULLS FIRST
) row_
WHERE rownum <= 100;
 END PORTALATATISTICSList;
END PORTALATATISTICSPACKAGE; 

注:oracle中REF Cursor用法


个人博客,用于记录工作日常的问题。