需求：根据传入的参数，将数据查询出来，处理后，存入新表，如数据已存在，则更新，如数据不存在，则插入。

存储过程包头创建过程参考上一篇文章：Oracle带入参和出参存储过程的定义

--门户存储过程包体
CREATE OR REPLACE PACKAGE BODY PORTALATATISTICSPACKAGE AS
  procedure PORTALATATISTICSList(app_id IN VARCHAR2,startTime IN VARCHAR2,endTime IN VARCHAR2) AS
  REGION_COUNT NUMBER;
 BEGIN
--查询门户及业务系统登录情况,将记录保存在I中
for i in(SELECT row_.*, rownum FROM (
                 SELECT l.REGION,
       r.name,
       count((case
               when l.log_type = '1' then
                l.usercode
             end)) as portal_login_num,
       count((case
               when l.app_id = app_id then
                l.usercode
             end)) as bus_system_login_num
  FROM (select distinct usercode,
                        username,
                        region,
                        region_name,
                        log_type,
                        operate_type,
                        to_char(operate_time, 'yyyymmdd') as operate_time,
                        app_id
          from uum211.UUM_USER_LOG u
         where (u.log_type = '1' and u.operate_type = '登录')
            or (log_type = '3')) l,
       uum_region r
 WHERE l.OPERATE_TIME BETWEEN startTime AND endTime
   and r.code = l.region
 GROUP BY l.REGION, r.name
) row_
WHERE rownum <= 100) loop
  --查询当前记录是否存在
  SELECT COUNT(1) INTO REGION_COUNT FROM UUM_PORTAL_STATISTICS WHERE region = I.region;
  IF REGION_COUNT > 0 THEN
        --更新物理表
              UPDATE  UUM_PORTAL_STATISTICS SET portal_login_num = I.portal_login_num,bus_system_login_num = I.bus_system_login_num WHERE region=I.region;  
          ELSE
        --数据插入物理表
        INSERT INTO UUM_PORTAL_STATISTICS(region,region_name,portal_login_num,bus_system_login_num) VALUES(I.region,I.name,I.portal_login_num,I.bus_system_login_num);
            END IF;
        END LOOP;
        COMMIT;
 END PORTALATATISTICSList;
END PORTALATATISTICSPACKAGE;