oracle 存储过程--每日统计一个表中的数据，并插入另一个表中
根据 LOGINFO 和 MEDICALINFO 表中的数据，根据DocID 和 PROCTIME 字段，查询记录，然后根据ACTION 字段的值，将查询出的数据插入到另外一个表中。


create or replace PROCEDURE PROCEDURE1 AS
CURSOR LOG_CURSOR IS
SELECT TO_CHAR(PROCTIME,'YYYY-MM-DD HH24') ,MEDICALINFO.APPCODE ,LOGINFO.ACTION, COUNT(*)
FROM LOGINFO,MEDICALINFO
WHERE  LOGINFO.DOCID = MEDICALINFO.DOCID AND TO_CHAR(PROCTIME,'YYYY-MM-DD')='2010-12-14'
GROUP BY TO_CHAR(PROCTIME,'YYYY-MM-DD HH24'),MEDICALINFO.APPCODE,LOGINFO.ACTION
ORDER BY APPCODE DESC;
PROCTIMESTR VARCHAR2(20);
PROCTIME DATE;
APPCODE VARCHAR2(20);
ACTION NUMBER;
COUNTSUM NUMBER;
CREATESUM NUMBER;
STAMPSUM NUMBER;
PRINTSUM NUMBER;
BEGIN
OPEN LOG_CURSOR;
LOOP
FETCH LOG_CURSOR INTO PROCTIMESTR,APPCODE,ACTION,COUNTSUM;
SELECT TO_DATE(PROCTIMESTR,'YYYY-MM-DD HH24') INTO PROCTIME FROM DUAL;
EXIT WHEN LOG_CURSOR%NOTFOUND;
IF ACTION=1 THEN CREATESUM := COUNTSUM;
ELSIF ACTION=2 THEN STAMPSUM := COUNTSUM;
ELSIF ACTION=3 THEN PRINTSUM := COUNTSUM;
ELSE NULL;
END IF;
INSERT INTO ACTIONLOG(LOGDATE,STARTHOUR,CREATESUM,STAMPSUM,PRINTSUM,DEPARTMENT) VALUES(PROCTIME,0,CREATESUM,STAMPSUM,PRINTSUM,APPCODE);
END LOOP;
CLOSE LOG_CURSOR;
END PROCEDURE1;
我的异常网推荐解决方案：oracle存储过程,http://www.aiyiweb.com/oracle-develop/177537.html