归档同步存储过程,用ORACLE JOB调用
create or replace procedure pro_synXcProcActive is
KEY_ID INTEGER;
ERR_CODE NUMBER;
ERR_MSG VARCHAR2(512); strDeclNo varchar2(20);
strNextFlowNode VARCHAR2(10);
strInspOperatorCode VARCHAR2(6);
cursor getProcActive is
select v.decl_no, v.next_flow_node, v.insp_operator_code
from t_bill_list a, v_proc_active v
where 1 = 1
and a.decl_no = v.decl_no
and a.process_status <> v.next_flow_node;
begin
open getProcActive;
fetch getProcActive
into strDeclNo, strNextFlowNode, strInspOperatorCode;
while getProcActive% found loop
update t_bill_list t
set t.process_status = strNextFlowNode,
t.operator_code = strInspOperatorCode
where t.decl_no = strDeclNo;
end loop;
--dbms_output.put_line(to_char(getProcActive%rowcount));
Close getProcActive;
commit;
EXCEPTION
when others then SELECT T_ORACLE_ERROR_SEQ.NEXTVAL INTO KEY_ID FROM DUAL;
ERR_MSG := SQLERRM;
ERR_CODE := SQLCODE;
INSERT INTO T_ORACLE_ERROR(ERROR_ID, ERR_MSG, DECL_NO, ERR_CODE, MEMO, ERR_DATE)
VALUES(KEY_ID, ERR_MSG, ' ', ERR_CODE, '单据状态同步存储过程错误 ', sysdate);
rollback;
end pro_synXcProcActive; 执行这个存储过程,但是执行不到update,有时exec存储过程时还死住不动
谁帮忙看下是什么原因?
create or replace procedure pro_synXcProcActive is
KEY_ID INTEGER;
ERR_CODE NUMBER;
ERR_MSG VARCHAR2(512); strDeclNo varchar2(20);
strNextFlowNode VARCHAR2(10);
strInspOperatorCode VARCHAR2(6);
cursor getProcActive is
select v.decl_no, v.next_flow_node, v.insp_operator_code
from t_bill_list a, v_proc_active v
where 1 = 1
and a.decl_no = v.decl_no
and a.process_status <> v.next_flow_node;
begin
open getProcActive;
fetch getProcActive
into strDeclNo, strNextFlowNode, strInspOperatorCode;
while getProcActive% found loop
update t_bill_list t
set t.process_status = strNextFlowNode,
t.operator_code = strInspOperatorCode
where t.decl_no = strDeclNo;
end loop;
--dbms_output.put_line(to_char(getProcActive%rowcount));
Close getProcActive;
commit;
EXCEPTION
when others then SELECT T_ORACLE_ERROR_SEQ.NEXTVAL INTO KEY_ID FROM DUAL;
ERR_MSG := SQLERRM;
ERR_CODE := SQLCODE;
INSERT INTO T_ORACLE_ERROR(ERROR_ID, ERR_MSG, DECL_NO, ERR_CODE, MEMO, ERR_DATE)
VALUES(KEY_ID, ERR_MSG, ' ', ERR_CODE, '单据状态同步存储过程错误 ', sysdate);
rollback;
end pro_synXcProcActive; 执行这个存储过程,但是执行不到update,有时exec存储过程时还死住不动
谁帮忙看下是什么原因?
"但是执行不到update"是单步调试得出的结论吗?
open getProcActive;
fetch getProcActive
into strDeclNo, strNextFlowNode, strInspOperatorCode;
while getProcActive% found loop
update t_bill_list t
set t.process_status = strNextFlowNode,
t.operator_code = strInspOperatorCode
where t.decl_no = strDeclNo;
end loop;
--dbms_output.put_line(to_char(getProcActive%rowcount));
Close getProcActive;
commit; 改为
open getProcActive;
loop
fetch getProcActive
into strDeclNo, strNextFlowNode, strInspOperatorCode;
EXIT WHEN getProcActive%NOTFOUND;
update t_bill_list t
set t.process_status = strNextFlowNode,
t.operator_code = strInspOperatorCode
where t.decl_no = strDeclNo;
end loop;
--dbms_output.put_line(to_char(getProcActive%rowcount));
commit;
Close getProcActive;
这种写法没有见过.
还是按楼上的采用常规写法:
EXIT WHEN getProcActive%NOTFOUND;
用的是游标?
执行不动的时候,估计是数据截取或者往不为空列 设置null值吧
楼主把错误信息贴出来,大家看下!
报oracle的alert.log里面找到执行这个存储过程停止的日志贴出来,看下具体原因是?