-- Create table
create table UPDATE_MSG_CONTENT_LOG
(
detail VARCHAR2(300),
content VARCHAR2(1536),
subject VARCHAR2(300),
pk_message CHAR(20) not null,
backupts DATE,
ts CHAR(19),
receiver CHAR(20)
)
tablespace NNC_DATA01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 8K
minextents 1
maxextents unlimited
);
-- Alter table
alter table UPDATE_MSG_CONTENT_LOGstorage
(
next 1
)
;
-- Add/modify columns
alter table UPDATE_MSG_CONTENT_LOG add receiver CHAR(20);
create or replace procedure Update_Msg_Content
IS
---游标
CURSOR TEMP_CUR IS
select DETAIL,CONTENT,SUBJECT,RECEIVER,PK_MESSAGE,SYSDATE BACKUPTS,TS
from sm_msg_content
where DETAIL not in
((select pk_leaveh || '@6404@' || bill_code from tbm_leaveh) UNION ALL(select pk_overtimeh||'@6405@'||bill_code from tbm_overtimeh))
and (DETAIL like '%@6404@%' OR DETAIL like '%@6405@%')
and msgtype <> 'email'
and msgsourcetype ='notice'
and isdelete = 'N'
and ishandled = 'N'
and isread = 'N';
begin
For TEMP_CUR_RECORD in TEMP_CUR loop---遍历游标
IF TEMP_CUR_RECORD.RECEIVER IS NOT NULL THEN
INSERT INTO Update_Msg_Content_LOG(Detail, Content, Subject, Receiver, Pk_Message, Backupts, Ts) VALUES (TEMP_CUR_RECORD.DETAIL,TEMP_CUR_RECORD.CONTENT,TEMP_CUR_RECORD.SUBJECT,TEMP_CUR_RECORD.RECEIVER,TEMP_CUR_RECORD.PK_MESSAGE,TEMP_CUR_RECORD.BACKUPTS,TEMP_CUR_RECORD.TS);
UPDATE sm_msg_content SET sm_msg_content.isdelete = 'Y' WHERE sm_msg_content.Pk_Message=TEMP_CUR_RECORD.PK_MESSAGE;
ELSE
INSERT INTO Update_Msg_Content_LOG(Detail, Content, Subject, Receiver, Pk_Message, Backupts, Ts) VALUES (TEMP_CUR_RECORD.DETAIL,TEMP_CUR_RECORD.CONTENT,TEMP_CUR_RECORD.SUBJECT,TEMP_CUR_RECORD.RECEIVER,TEMP_CUR_RECORD.PK_MESSAGE,TEMP_CUR_RECORD.BACKUPTS,TEMP_CUR_RECORD.TS);
UPDATE sm_msg_content SET sm_msg_content.isread = 'Y' WHERE sm_msg_content.Pk_Message=TEMP_CUR_RECORD.PK_MESSAGE;
END IF;
end loop;
commit;
end;
Declare
i Integer;
Begin
dbms_job.submit(i,'Update_Msg_Content;',Sysdate,'TRUNC(sysdate+1)+2/24');
end;
/*begin
sys.dbms_job.change(job => 21,
what => 'Update_Msg_Content;',
next_date => to_date('23-11-2017 16:12:17', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(sysdate+1)+17/24');
commit;
end;
/ */