oracle-简单的存储过程demo_存储过程简单demo

创建一个简单的存储过程

从别的表中取出数据，插入到统计表中

create or replace procedure P_CARD_IN_STAT
is
-- 月卡产出统计 --
begin
     INSERT INTO TBL_CARD_IN_STAT(STAT_TIME,GAME_LEVEL,VIP_LEVEL,TARGET,STAT_NUMBER,STAT_USER)
     select TO_CHAR(sysdate-1,'YYYY-MM-DD') AS STAT_TIME, game_level,vip_level,target,sum(STAT_NUMBER) STAT_NUMBER ,
     count(distinct(user_id)) STAT_USER from tbl_card_in t where  TO_CHAR(tim/(3600*24)+TO_DATE('1970-01-01 03','YYYY-MM-DD hh24'),'YYYY-MM-DD') = TO_CHAR(sysdate-1,'YYYY-MM-DD')
     group by game_level,vip_level,target;
 
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end ;