--归档同步存储过程,用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 存储过程时还死住不动,请高手赐教,急
解决方案 »
- 这个问题一直困惑我
- LIKE '%_10' 查询结果 为什么出现F075010 ?
- 求救!麻烦高手帮忙看一下那里出错了
- 求SQL语句
- sql文
- 调查:后台用unix系统,前台用win2000这种方式有什么缺点,有哪些系统是这么做的。
- 讨论,哪个方法比较好:是用一个自动产生的SEQUENCE ID作为 表的PRIMARY KEY 还是由几个业务字段联合起来做为PRIMARY KEY?
- 请问这个SQL语句是什么意思?
- 在"部门表"建一触发器如下:
- 跪求高手帮忙解答,在线等,谢谢大侠
- 编写出同一实例下但不同用户下相同结构的表之间进行数据复制的脚本,那位可以帮下我哦
- 如何将查询出多行数据返回在一行多列(跪求,我时在没有分了)
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;改成: update t_bill_list t
set t.process_status = strNextFlowNode,
t.operator_code = strInspOperatorCode
where t.decl_no = strDeclNo;
commit;
end loop;
--dbms_output.put_line(to_char(getProcActive%rowcount));
Close getProcActive;
试试
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
) loop...end loop;
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;
---
循环里没有
fetch getProcActive
into strDeclNo, strNextFlowNode, strInspOperatorCode;
是个死循环!
另外,如一楼所说,最好在loop里面提交,也就是改成open getProcActive;
while getProcActive% found loop
fetch getProcActive
into strDeclNo, strNextFlowNode, strInspOperatorCode;
update t_bill_list t
set t.process_status = strNextFlowNode,
t.operator_code = strInspOperatorCode
where t.decl_no = strDeclNo;
commit;
end loop;
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;