我正在编写Oracle Database 12c企业版12.1.0.2.0版 - 64位 生产环境 中的过程 .
我在LOOP中有一个异常,因为如果抛出异常,我不希望该过程退出LOOP .
create or replace procedure PARSE_REGISTER_MESSAGE
IS
HOTELS_TO_PROCESS number := 5000;
cursor unparsed_messages is
SELECT REGISTER_psd_id, message
FROM
( SELECT REGISTER_psd_id, message
FROM cc_owner.REGISTER_psd
WHERE parsed != 1
OR parsed IS NULL
ORDER BY CREATION_DATE DESC)
WHERE rownum < HOTELS_TO_PROCESS;
BEGIN
FOR psd_rec in unparsed_messages
LOOP
p_msg.parse_msg (psd_rec.REGISTER_psd_id, null, psd_rec.message);
EXCEPTION
WHEN OTHERS
THEN
DECLARE
l_code INTEGER := SQLCODE;
BEGIN
of_owner.p_db_trc.add_error
( 'PARSE_REGISTER_MESSAGE','',
l_code,
sys.DBMS_UTILITY.format_error_stack,
sys.DBMS_UTILITY.format_error_backtrace,
sys.DBMS_UTILITY.format_call_stack );
END;
END LOOP;
END;
但由于此错误,我无法编译包:
错误(25,10):PLS-00103:当遇到以下情况之一时遇到符号“EXCEPTION”:(如果循环mod为空,pragma引发返回选择更新,则使用<< continue close current delete,开始情况声明结束退出goto fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge
我也尝试过:
create or replace procedure PARSE_REGISTER_MESSAGE
IS
HOTELS_TO_PROCESS number := 5000;
cursor unparsed_messages is
SELECT REGISTER_psd_id, message
FROM
( SELECT REGISTER_psd_id, message
FROM cc_owner.REGISTER_psd
WHERE parsed != 1
OR parsed IS NULL
ORDER BY CREATION_DATE DESC)
WHERE rownum < HOTELS_TO_PROCESS;
psd_rec unparsed_messages%ROWTYPE;
BEGIN
FOR psd_rec in unparsed_messages
LOOP
BEGIN
p_msg.parse_msg (psd_rec.REGISTER_psd_id, null, psd_rec.message);
EXCEPTION
WHEN OTHERS
THEN
DECLARE
l_code INTEGER := SQLCODE;
BEGIN
of_owner.p_db_trc.add_error
( 'PARSE_REGISTER_MESSAGE','',
l_code,
sys.DBMS_UTILITY.format_error_stack,
sys.DBMS_UTILITY.format_error_backtrace,
sys.DBMS_UTILITY.format_call_stack );
END;
END LOOP;
END;
但后来我收到了这个错误:
Error(48,4): PLS-00103: Encountered the symbol ";" when expecting one of the following: loop
2 回答
PLSQL
block
/procedure
的语法是:现在当我查看你的代码时,你已经在
FOR LOOP
中编写了Exception
块,只有在你使用上面的语法时它才会起作用 . 在您的情况下Exception
块的范围不由Oracle
标识,因此它会抛出错误 .您必须按如下所示修改代码,以在for循环中包含
Exception
块;你第二次尝试缺少
END
语句,这就是你收到错误的原因 . 见下文:尝试使用Begin after循环关键字,因为缺少一个BEGIN
for psd_rec in unparsed_messages LOOP BEGIN p_msg.parse_msg(psd_rec.REGISTER_psd_id,null,psd_rec.message);