创建一个简单的存储过程
从别的表中取出数据,插入到统计表中
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 ;