oracle查出数据导入另一个表,oracle存储过程将一个表的数据导入另一个表

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;