好久没写存储过程了,今天要做一个job,所以写了一个存储过程,大家帮看一下,哪里有错
create or replace procedure copymessage
is
v_curHour number(2,0);
v_curMinute number(2,0);
v_curSysdate varchar2(50);
v_interval number(10,0);
errm varchar2(600);
begin
select to_char(sysdate,'YYYY-DD-MM HH24:MI:SS') into v_curSysdate FROM DUAL;
v_curHour:=to_number(substr(v_curSysdate,12,2));
v_curMinute:=to_number(substr(v_curSysdate,15,2));
v_interval:=v_curHour*60+v_curMinute;
commit;
SET TRANSACTION READ WRITE;
begin
INSERT INTO T_WORKITEM_S(WORKITEM_ID,M_ID,PROCESS_DEF_ID,PROCESS_DEF_NAME,PROCESS_INS_ID,PROCESS_INS_NAME,ACTIVITY_INS_ID,
ACTIVITY_INS_NAME, TYPE,CREATED_TIME,PARTICIPANT,START_TIME,TIME_UNIT,TIME_LIMIT,ATTACHMENT_ID,
FORM_ID,APP_ID, ACTIVITY_LIST, COMPLETED_TIME,DESCRIPTION,STATE,PRESTATE,FLAG )
SELECT WORKITEM_ID,M_ID,PROCESS_DEF_ID,PROCESS_DEF_NAME,PROCESS_INS_ID,PROCESS_INS_NAME,ACTIVITY_INS_ID,
ACTIVITY_INS_NAME, TYPE,CREATED_TIME,PARTICIPANT,START_TIME,TIME_UNIT,TIME_LIMIT,ATTACHMENT_ID,
FORM_ID,APP_ID, ACTIVITY_LIST, COMPLETED_TIME,DESCRIPTION,STATE,PRESTATE,FLAG
FROM T_WORKITEM_S_SCHEDULE t
WHERE to_number(substr(t.created_time,12,2))*60+to_number(substr(t.created_time,15,2))-v_interval<60
EXCEPTION WHEN OTHERS THEN
begin
errm := '错误信息:' || SQLERRM;
GOTO quitwithrollback;
end;
END;
commit work;
<<quitwithrollback>>
NULL;
ROLLBACK WORK;
insert into t_copymessage_errlog(exceptiontime,errinfo)
select v_curSysdate,errm from dual;
<<endsave>>
NULL;
end copymessage;
错误提示:
Compilation errors for PROCEDURE MOBILE.COPYMESSAGEError: PL/SQL: ORA-00933: SQL command not properly ended
Line: 24
Text: EXCEPTION WHEN OTHERS THENError: PL/SQL: SQL Statement ignored
Line: 16
Text: INSERT INTO T_WORKITEM_S(WORKITEM_ID,M_ID,PROCESS_DEF_ID,PROCESS_DEF_NAME,PROCESS_INS_ID,PROCESS_INS_NAME,ACTIVITY_INS_ID,Error: PLS-00103: Encountered the symbol "<"
Line: 31
Text: <<quitwithrollback>>Error: PLS-00103: Encountered the symbol "INSERT"
Line: 34
Text: insert into t_copymessage_errlog(exceptiontime,errinfo)
create or replace procedure copymessage
is
v_curHour number(2,0);
v_curMinute number(2,0);
v_curSysdate varchar2(50);
v_interval number(10,0);
errm varchar2(600);
begin
select to_char(sysdate,'YYYY-DD-MM HH24:MI:SS') into v_curSysdate FROM DUAL;
v_curHour:=to_number(substr(v_curSysdate,12,2));
v_curMinute:=to_number(substr(v_curSysdate,15,2));
v_interval:=v_curHour*60+v_curMinute;
commit;
SET TRANSACTION READ WRITE;
begin
INSERT INTO T_WORKITEM_S(WORKITEM_ID,M_ID,PROCESS_DEF_ID,PROCESS_DEF_NAME,PROCESS_INS_ID,PROCESS_INS_NAME,ACTIVITY_INS_ID,
ACTIVITY_INS_NAME, TYPE,CREATED_TIME,PARTICIPANT,START_TIME,TIME_UNIT,TIME_LIMIT,ATTACHMENT_ID,
FORM_ID,APP_ID, ACTIVITY_LIST, COMPLETED_TIME,DESCRIPTION,STATE,PRESTATE,FLAG )
SELECT WORKITEM_ID,M_ID,PROCESS_DEF_ID,PROCESS_DEF_NAME,PROCESS_INS_ID,PROCESS_INS_NAME,ACTIVITY_INS_ID,
ACTIVITY_INS_NAME, TYPE,CREATED_TIME,PARTICIPANT,START_TIME,TIME_UNIT,TIME_LIMIT,ATTACHMENT_ID,
FORM_ID,APP_ID, ACTIVITY_LIST, COMPLETED_TIME,DESCRIPTION,STATE,PRESTATE,FLAG
FROM T_WORKITEM_S_SCHEDULE t
WHERE to_number(substr(t.created_time,12,2))*60+to_number(substr(t.created_time,15,2))-v_interval<60
EXCEPTION WHEN OTHERS THEN
begin
errm := '错误信息:' || SQLERRM;
GOTO quitwithrollback;
end;
END;
commit work;
<<quitwithrollback>>
NULL;
ROLLBACK WORK;
insert into t_copymessage_errlog(exceptiontime,errinfo)
select v_curSysdate,errm from dual;
<<endsave>>
NULL;
end copymessage;
错误提示:
Compilation errors for PROCEDURE MOBILE.COPYMESSAGEError: PL/SQL: ORA-00933: SQL command not properly ended
Line: 24
Text: EXCEPTION WHEN OTHERS THENError: PL/SQL: SQL Statement ignored
Line: 16
Text: INSERT INTO T_WORKITEM_S(WORKITEM_ID,M_ID,PROCESS_DEF_ID,PROCESS_DEF_NAME,PROCESS_INS_ID,PROCESS_INS_NAME,ACTIVITY_INS_ID,Error: PLS-00103: Encountered the symbol "<"
Line: 31
Text: <<quitwithrollback>>Error: PLS-00103: Encountered the symbol "INSERT"
Line: 34
Text: insert into t_copymessage_errlog(exceptiontime,errinfo)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货