RT:
-------
create or replace trigger NEW_TEST
after insert on ad
for each row
declare
pReturn PkgTest.CurReturn;
begin
if :new.content is null then
NEW_ALM_REMIND_SMPRO(pReturn);
end if;
end NEW_TEST;
--------
create or replace procedure NEW_ALM_REMIND_SMPRO(pReturn out PkgTest.CurReturn ) is
i integer:=0;
vv_err_msg VARCHAR2(500);
dbms_lock.sleep(1);while i < 3
LOOP
for pReturn IN (
select ........) loop
if pReturn.xx is null then
insert into sys_smwork (BEGINTIME,RECMOBILE,shortmessage)
values (to_char(sysdate,'yyyymmddhh24miss'),133xxxxxxxx,pReturn.xxxx);
commit;
else
exit;
end if;
end loop;
i:=i+1;
dbms_lock.sleep(1);
END LOOP;
OPEN pReturn FOR
SELECT 1 AS RESULT, '' AS err_msg FROM dual;
RETURN;EXCEPTION
WHEN OTHERS THEN
vv_err_msg := SQLCODE || SQLERRM;
OPEN pReturn FOR
SELECT -1 AS RESULT, vv_err_msg AS err_msg FROM dual;
RETURN;
end NEW_ALM_REMIND_SMPRO;
----------
select ........有2行记录,然后循环3次,手工执行NEW_ALM_REMIND_SMPRO后sys_smwork 表里有6行数据,这个没问题,
但当表AD里插入的字段CONTENT为NULL时,触发过程NEW_ALM_REMIND_SMPRO执行,结果与手工执行NEW_ALM_REMIND_SMPRO的结果不同,少了后面5行数据,只有第一行的数据写入表了,哪里有问题呢?
-------
create or replace trigger NEW_TEST
after insert on ad
for each row
declare
pReturn PkgTest.CurReturn;
begin
if :new.content is null then
NEW_ALM_REMIND_SMPRO(pReturn);
end if;
end NEW_TEST;
--------
create or replace procedure NEW_ALM_REMIND_SMPRO(pReturn out PkgTest.CurReturn ) is
i integer:=0;
vv_err_msg VARCHAR2(500);
dbms_lock.sleep(1);while i < 3
LOOP
for pReturn IN (
select ........) loop
if pReturn.xx is null then
insert into sys_smwork (BEGINTIME,RECMOBILE,shortmessage)
values (to_char(sysdate,'yyyymmddhh24miss'),133xxxxxxxx,pReturn.xxxx);
commit;
else
exit;
end if;
end loop;
i:=i+1;
dbms_lock.sleep(1);
END LOOP;
OPEN pReturn FOR
SELECT 1 AS RESULT, '' AS err_msg FROM dual;
RETURN;EXCEPTION
WHEN OTHERS THEN
vv_err_msg := SQLCODE || SQLERRM;
OPEN pReturn FOR
SELECT -1 AS RESULT, vv_err_msg AS err_msg FROM dual;
RETURN;
end NEW_ALM_REMIND_SMPRO;
----------
select ........有2行记录,然后循环3次,手工执行NEW_ALM_REMIND_SMPRO后sys_smwork 表里有6行数据,这个没问题,
但当表AD里插入的字段CONTENT为NULL时,触发过程NEW_ALM_REMIND_SMPRO执行,结果与手工执行NEW_ALM_REMIND_SMPRO的结果不同,少了后面5行数据,只有第一行的数据写入表了,哪里有问题呢?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货