关于存储过程中游标的值

[sakuramanu12]

我现在写一个存储过程，实现数据的批量处理，代码如下：
--构建数组
create or replace type ACTIONTYPE_VARCHAR2 as table of varchar2(1000);
create or replace type ENTITYID_VARCHAR2 as table of varchar2(1000);
create or replace type VOUTYPEID_VARCHAR2 as table of varchar2(1000);
--create or replace type WFID_VARCHAR2 as table of varchar2(1000);
--create or replace type CURRENTNODE_VARCHAR2 as table of varchar2(1000);
create or replace type CURRENTSTATUS_NUMBER as table of number(10);
create or replace type NEXTNODE_VARCHAR2 as table of varchar2(1000);
create or replace type NEXTSTATUS_NUMBER as table of number(10);
create or replace type IS_UNDO_NUMBER as table of number(10);
create or replace type OPERUSER_VARCHAR2 as table of varchar2(1000);
--create or replace type OPERDATE_DATE as table of DATE;
create or replace type INITMONEY_NUMBER as table of number(10);
create or replace type RESULTMONEY_NUMBER as table of number(10);
create or replace type TOLLYFLAG_NUMBER as table of number(10);
create or replace type AUTOAUDITFLAG_NUMBER as table of number(10);
create or replace type TIMESTAMP_VARCHAR2 as table of varchar2(1000);
--create or replace type REMARK_VARCHAR2 as table of varchar2(1000);
create or replace procedure drivenWorkFlow(
PARAMS_ACTIONTYPE_VARCHAR2 in ACTIONTYPE_VARCHAR2,
PARAMS_ENTITYID_VARCHAR2 in ENTITYID_VARCHAR2,
PARAMS_VOUTYPEID_VARCHAR2 in VOUTYPEID_VARCHAR2,
--PARAMS_WFID_VARCHAR2 in WFID_VARCHAR2,
--PARAMS_CURRENTNODE_VARCHAR2 in CURRENTNODE_VARCHAR2,
PARAMS_CURRENTSTATUS_NUMBER in CURRENTSTATUS_NUMBER,
PARAMS_NEXTNODE_VARCHAR2 in NEXTNODE_VARCHAR2,
PARAMS_NEXTSTATUS_NUMBER in NEXTSTATUS_NUMBER,
PARAMS_IS_UNDO_NUMBER in IS_UNDO_NUMBER,
PARAMS_OPERUSER_VARCHAR2 in OPERUSER_VARCHAR2,
--PARAMS_OPERDATE_DATE in OPERDATE_DATE,
PARAMS_INITMONEY_NUMBER in INITMONEY_NUMBER,
PARAMS_RESULTMONEY_NUMBER in RESULTMONEY_NUMBER,
PARAMS_TOLLYFLAG_NUMBER in TOLLYFLAG_NUMBER,
PARAMS_AUTOAUDITFLAG_NUMBER in AUTOAUDITFLAG_NUMBER,
WFID_VARCHAR2 in varchar2,
CURRENTNODE_VARCHAR2 in varchar2,
PARAMS_TIMESTAMP_VARCHAR2 in TIMESTAMP_VARCHAR2,
--PARAMS_REMARK_VARCHAR2 in REMARK_VARCHAR2
o_n_ret OUT varchar2
)
is
var_nextnode varchar2(100);
var_test varchar2(100);
var_curstatus number;
var_nextstatus number;
var_nextnodeid varchar2(100);
var_nextnodeidcur varchar2(100);
var_nodetype varchar2(40);
var_count number;
var_cur_loop_count number;
cursor cur1 is
select NEXTNODEID from t_wfnodeconditions where WFID=WFID_VARCHAR2 and NODEID=CURRENTNODE_VARCHAR2;
cursor cur2 is
select to_char(operdate,'yyyy-mm-dd hh24:mi:ss') from t_wfcurrenttasks where WFID=WFID_VARCHAR2;

begin
--判断nextnode数量

select count(*) into var_count from t_wfnodeconditions where WFID=WFID_VARCHAR2 and NODEID=CURRENTNODE_VARCHAR2;
for i in 1..PARAMS_CURRENTSTATUS_NUMBER.count loop
open cur2;
loop
var_test :=null;
fetch cur2 into var_test;
EXIT WHEN cur2%NOTFOUND;
--不是会签节点
if var_count =1 then
select NEXTNODEID into var_nextnodeid from t_wfnodeconditions where WFID=WFID_VARCHAR2 and NODEID=CURRENTNODE_VARCHAR2;
select NODETYPE into var_nodetype from t_wfnodes where GUID =var_nextnodeid;
end if;
--nextnode不是结束节点
if var_nodetype<>'003' then
--操作类型区分nextnode
if PARAMS_ACTIONTYPE_VARCHAR2(i)='input' and var_count=1 then
var_nextnode:=var_nextnodeid;
var_curstatus:=0;
var_nextstatus:=1;
end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='next' and var_count=1 then
var_nextnode:=var_nextnodeid;
var_curstatus:=2;
var_nextstatus:=3;
end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='edit' then
var_nextnode:=CURRENTNODE_VARCHAR2;
var_curstatus:=4;
var_nextstatus:=5;
end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='提取' then
var_nextnode:=CURRENTNODE_VARCHAR2;
var_curstatus:=6;
var_nextstatus:=7;
end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='HANG' then
var_nextnode:=CURRENTNODE_VARCHAR2;
var_curstatus:=8;
var_nextstatus:=9;
end if;

--根据操作类型写sql,用delete和insert代替update

if PARAMS_ACTIONTYPE_VARCHAR2(i)='input' or PARAMS_ACTIONTYPE_VARCHAR2(i)='next' or PARAMS_ACTIONTYPE_VARCHAR2(i)='edit' or
PARAMS_ACTIONTYPE_VARCHAR2(i)='提取' or PARAMS_ACTIONTYPE_VARCHAR2(i)='HANG' and var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
--if i=1 then
--往历史表插入数据，更新currenttask表
insert into t_wfcompletetasks select * from t_wfcurrenttasks where WFID=WFID_VARCHAR2 ;
--end if;

update t_wfcurrenttasks set CURRENTNODE=CURRENTNODE_VARCHAR2,CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
OPERDATE=sysdate where WFID=WFID_VARCHAR2 ;
/*
delete from t_wfcurrenttasks where WFID=WFID_VARCHAR2 and OPERUSER=PARAMS_OPERUSER_VARCHAR2(i);
insert into t_wfcurrenttasks (WFID,ENTITYID ,CURRENTNODE, CURRENTSTATUS,NEXTNODE ,NEXTSTATUS ,
ACTIONTYPE ,IS_UNDO ,OPERUSER,OPERDATE,INITMONEY,RESULTMONEY, REMARK ,TOLLYFLAG, AUTOAUDITFLAG,VOUTYPEID)
values (WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),CURRENTNODE_VARCHAR2 ,var_curstatus,var_nextnode,var_nextstatus,
PARAMS_ACTIONTYPE_VARCHAR2(i),PARAMS_IS_UNDO_NUMBER(i),PARAMS_OPERUSER_VARCHAR2(i),sysdate,PARAMS_INITMONEY_NUMBER(i),
PARAMS_RESULTMONEY_NUMBER(i),'test',PARAMS_TOLLYFLAG_NUMBER(i),PARAMS_AUTOAUDITFLAG_NUMBER(i),PARAMS_VOUTYPEID_VARCHAR2(i));
end if;

if i>1 then
insert into t_wfcurrenttasks (WFID,ENTITYID ,CURRENTNODE, CURRENTSTATUS,NEXTNODE ,NEXTSTATUS ,
ACTIONTYPE ,IS_UNDO ,OPERUSER,OPERDATE,INITMONEY,RESULTMONEY, REMARK ,TOLLYFLAG, AUTOAUDITFLAG,VOUTYPEID)
values (WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),CURRENTNODE_VARCHAR2 ,var_curstatus,var_nextnode,var_nextstatus,
PARAMS_ACTIONTYPE_VARCHAR2(i),PARAMS_IS_UNDO_NUMBER(i),PARAMS_OPERUSER_VARCHAR2(i),sysdate,PARAMS_INITMONEY_NUMBER(i),
PARAMS_RESULTMONEY_NUMBER(i),'test',PARAMS_TOLLYFLAG_NUMBER(i),PARAMS_AUTOAUDITFLAG_NUMBER(i),PARAMS_VOUTYPEID_VARCHAR2(i));
end if;
*/
end if;
/*
if PARAMS_ACTIONTYPE_VARCHAR2(i)='edit' or
PARAMS_ACTIONTYPE_VARCHAR2(i)='提取' or PARAMS_ACTIONTYPE_VARCHAR2(i)='HANG' then
if i=1 then
insert into t_wfcompletetasks select * from t_wfcurrenttasks where WFID=WFID_VARCHAR2 ;
update t_wfcurrenttasks set CURRENTNODE=CURRENTNODE_VARCHAR2,CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
OPER
=sysdate where WFID=WFID_VARCHAR2;
end if;

if i>1 then
update t_wfcurrenttasks set CURRENTNODE=CURRENTNODE_VARCHAR2,CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
OPERDATE=sysdate where WFID=WFID_VARCHAR2;
end if;
end if;
*/
if PARAMS_ACTIONTYPE_VARCHAR2(i)='recall' or PARAMS_ACTIONTYPE_VARCHAR2(i)='discard' or PARAMS_ACTIONTYPE_VARCHAR2(i)='delete' or
PARAMS_ACTIONTYPE_VARCHAR2(i)='back' and var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
--删除currenttask表，从历史表中取数据放入currenttask表
--if i=1 then
delete from t_wfcurrenttasks where WFID=WFID_VARCHAR2 ;
--insert into t_wfcurrenttasks select taskid,wfid,entityid,currentnode,currentstatus,nextnode,nextstatus,actiontype,is_undo,operuser,t_wfcompletetasks.operdate,initmoney,resultmoney,remark,tollyflag,autoauditflag,voutypeid from t_wfcompletetasks,(select OPERDATE,ROW_NUMBER() OVER(ORDER BY OPERDATE DESC)as rk from t_wfcompletetasks ) t
--where t_wfcompletetasks.operdate=t.OPERDATE and rk=1 and rownum<2;
--end if;
/*
if i>1 then
insert into t_wfcurrenttasks select taskid,wfid,entityid,currentnode,currentstatus,nextnode,nextstatus,actiontype,is_undo,operuser,t_wfcompletetasks.operdate,initmoney,resultmoney,remark,tollyflag,autoauditflag,voutypeid from t_wfcompletetasks,(select OPERDATE,ROW_NUMBER() OVER(ORDER BY OPERDATE DESC)as rk from t_wfcompletetasks ) t
where t_wfcompletetasks.operdate=t.OPERDATE and rk=1 and rownum<2;
end if;
*/
end if;

--nextnode为结束节点
if var_nodetype='003' and var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
delete from t_wfcurrenttasks where WFID=WFID_VARCHAR2;
end if;

end if;

[sakuramanu12]

--会签节点
if var_count>1 then
var_cur_loop_count:=i;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='edit' then
var_nextnode:=CURRENTNODE_VARCHAR2;
var_curstatus:=4;
var_nextstatus:=5;
end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='提取' then
var_nextnode:=CURRENTNODE_VARCHAR2;
var_curstatus:=6;
var_nextstatus:=7;
end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='HANG' then
var_nextnode:=CURRENTNODE_VARCHAR2;
var_curstatus:=8;
var_nextstatus:=9;
end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='edit' or PARAMS_ACTIONTYPE_VARCHAR2(i)='提取' or PARAMS_ACTIONTYPE_VARCHAR2(i)='HANG'
and var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
/*
if i=1 then
--往历史表插入数据，更新currenttask表
insert into t_wfcompletetasks select * from t_wfcurrenttasks where WFID=WFID_VARCHAR2 and OPERUSER=PARAMS_OPERUSER_VARCHAR2(i);

update t_wfcurrenttasks set CURRENTNODE=CURRENTNODE_VARCHAR2,CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
OPERDATE=sysdate where WFID=WFID_VARCHAR2;
*
delete from t_wfcurrenttasks where WFID=WFID_VARCHAR2 and OPERUSER=PARAMS_OPERUSER_VARCHAR2(i);
insert into t_wfcurrenttasks (WFID,ENTITYID ,CURRENTNODE, CURRENTSTATUS,NEXTNODE ,NEXTSTATUS ,
ACTIONTYPE ,IS_UNDO ,OPERUSER,OPERDATE,INITMONEY,RESULTMONEY, REMARK ,TOLLYFLAG, AUTOAUDITFLAG,VOUTYPEID)
values (WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),CURRENTNODE_VARCHAR2 ,var_curstatus,var_nextnode,var_nextstatus,
PARAMS_ACTIONTYPE_VARCHAR2(i),PARAMS_IS_UNDO_NUMBER(i),PARAMS_OPERUSER_VARCHAR2(i),sysdate,PARAMS_INITMONEY_NUMBER(i),
PARAMS_RESULTMONEY_NUMBER(i),'test',PARAMS_TOLLYFLAG_NUMBER(i),PARAMS_AUTOAUDITFLAG_NUMBER(i),PARAMS_VOUTYPEID_VARCHAR2(i));
end if;

if i>1 then
insert into t_wfcurrenttasks (WFID,ENTITYID ,CURRENTNODE, CURRENTSTATUS,NEXTNODE ,NEXTSTATUS ,
ACTIONTYPE ,IS_UNDO ,OPERUSER,OPERDATE,INITMONEY,RESULTMONEY, REMARK ,TOLLYFLAG, AUTOAUDITFLAG,VOUTYPEID)
values (WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),CURRENTNODE_VARCHAR2 ,var_curstatus,var_nextnode,var_nextstatus,
PARAMS_ACTIONTYPE_VARCHAR2(i),PARAMS_IS_UNDO_NUMBER(i),PARAMS_OPERUSER_VARCHAR2(i),sysdate,PARAMS_INITMONEY_NUMBER(i),
PARAMS_RESULTMONEY_NUMBER(i),'test',PARAMS_TOLLYFLAG_NUMBER(i),PARAMS_AUTOAUDITFLAG_NUMBER(i),PARAMS_VOUTYPEID_VARCHAR2(i));
end if;
*/
insert into t_wfcompletetasks select * from t_wfcurrenttasks where WFID=WFID_VARCHAR2 ;

update t_wfcurrenttasks set CURRENTNODE=CURRENTNODE_VARCHAR2,CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
OPERDATE=sysdate where WFID=WFID_VARCHAR2 ;

end if;
--循环游标取值
open cur1;
loop
var_nextnodeidcur :=null;
fetch cur1 into var_nextnodeidcur ;
EXIT WHEN cur1%NOTFOUND;

select NODETYPE into var_nodetype from t_wfnodes where GUID =var_nextnodeidcur;
if PARAMS_ACTIONTYPE_VARCHAR2(i)='input' then
var_nextnode:=var_nextnodeidcur;
var_curstatus:=0;
var_nextstatus:=1;
end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='next' then
var_nextnode:=var_nextnodeidcur;
var_curstatus:=2;
var_nextstatus:=3;
end if;

if var_nodetype<>'003' then
--往历史表插入数据，删除currenttask表记录，插入count条记录
if PARAMS_ACTIONTYPE_VARCHAR2(i)='input' or PARAMS_ACTIONTYPE_VARCHAR2(i)='next' then
if i=1 and var_cur_loop_count=i and cur1%ROWCOUNT=i and var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
insert into t_wfcompletetasks select * from t_wfcurrenttasks where WFID=WFID_VARCHAR2 and var_test=PARAMS_TIMESTAMP_VARCHAR2(i);

update t_wfcurrenttasks set CURRENTNODE=CURRENTNODE_VARCHAR2,CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnodeidcur,
NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
OPERDATE=sysdate where WFID=WFID_VARCHAR2 ;
/*
insert into t_wfcompletetasks select * from t_wfcurrenttasks where WFID=WFID_VARCHAR2 and OPERUSER=PARAMS_OPERUSER_VARCHAR2(i);

delete from t_wfcurrenttasks where WFID=WFID_VARCHAR2 and OPERUSER=PARAMS_OPERUSER_VARCHAR2(i);

insert into t_wfcurrenttasks (WFID,ENTITYID ,CURRENTNODE, CURRENTSTATUS,NEXTNODE ,NEXTSTATUS ,
ACTIONTYPE ,IS_UNDO ,OPERUSER,OPERDATE,INITMONEY,RESULTMONEY, REMARK ,TOLLYFLAG, AUTOAUDITFLAG,VOUTYPEID)
values (WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),CURRENTNODE_VARCHAR2 ,var_curstatus,var_nextnodeidcur,var_nextstatus,
PARAMS_ACTIONTYPE_VARCHAR2(i),PARAMS_IS_UNDO_NUMBER(i),PARAMS_OPERUSER_VARCHAR2(i),sysdate,PARAMS_INITMONEY_NUMBER(i),
PARAMS_RESULTMONEY_NUMBER(i),'test',PARAMS_TOLLYFLAG_NUMBER(i),PARAMS_AUTOAUDITFLAG_NUMBER(i),PARAMS_VOUTYPEID_VARCHAR2(i));

update t_wfcurrenttasks set CURRENTNODE=CURRENTNODE_VARCHAR2,CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnodeidcur,
NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
OPERDATE=sysdate where WFID=WFID_VARCHAR2;
*/
var_cur_loop_count:=i+1;
end if;

if i>1 and var_cur_loop_count=i and cur1%ROWCOUNT=i and var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
insert into t_wfcompletetasks select * from t_wfcurrenttasks where WFID=WFID_VARCHAR2 ;

insert into t_wfcurrenttasks (WFID,ENTITYID ,CURRENTNODE, CURRENTSTATUS,NEXTNODE ,NEXTSTATUS ,
ACTIONTYPE ,IS_UNDO ,OPERUSER,OPERDATE,INITMONEY,RESULTMONEY, REMARK ,TOLLYFLAG, AUTOAUDITFLAG,VOUTYPEID)
values (WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),CURRENTNODE_VARCHAR2 ,var_curstatus,var_nextnodeidcur,var_nextstatus,
PARAMS_ACTIONTYPE_VARCHAR2(i),PARAMS_IS_UNDO_NUMBER(i),PARAMS_OPERUSER_VARCHAR2(i),sysdate,PARAMS_INITMONEY_NUMBER(i),
PARAMS_RESULTMONEY_NUMBER(i),'test',PARAMS_TOLLYFLAG_NUMBER(i),PARAMS_AUTOAUDITFLAG_NUMBER(i),PARAMS_VOUTYPEID_VARCHAR2(i));
var_cur_loop_count:=i+1;
end if;

end if;

if PARAMS_ACTIONTYPE_VARCHAR2(i)='recall' or PARAMS_ACTIONTYPE_VARCHAR2(i)='discard' or PARAMS_ACTIONTYPE_VARCHAR2(i)='delete' or
PARAMS_ACTIONTYPE_VARCHAR2(i)='back' then
--删除currenttask表，从历史表中取数据放入currenttask表
if var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
delete from t_wfcurrenttasks where WFID=WFID_VARCHAR2 ;
end if;


end if;

end if;
end loop;
close cur1;

if var_nodetype='003' and var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
delete from t_wfcurrenttasks where WFID=WFID_VARCHAR2;
end if;
end if;
end loop;
close cur2;
end loop;
commit;
o_n_ret := var_test;
exception when others then
raise;
end drivenWorkFlow;
/

问题在这：
cursor cur2 is
select to_char(operdate,'yyyy-mm-dd hh24:mi:ss') from t_wfcurrenttasks where WFID=WFID_VARCHAR2;
open cur2;
loop
var_test :=null;
fetch cur2 into var_test;
EXIT WHEN cur2%NOTFOUND;
就是把wfcurrenttasks 中的operdate时间字段赋值到var_test中，和PARAMS_TIMESTAMP_VARCHAR2(i)时间比较，
然后删除时间相等的， if var_test=PARAMS_TIMESTAMP_VARCHAR2(i) then
delete from t_wfcurrenttasks where WFID=WFID_VARCHAR2 ;
end if;
通过var_test判断条件，现在发现var_test的值为0，无论时间相不相等，delete语句都执行了。
不知道问题在哪，为什么var_test的值为0呢。。数据库中operdate字段是有值的

[007-x]

把游标的列加上别名试试呢
cursor cur2 is
select to_char(operdate,'yyyy-mm-dd hh24:mi:ss') operdate from t_wfcurrenttasks where WFID=WFID_VARCHAR2;
open cur2;

[sakuramanu]

慢慢测试。。

[opps_zhou]

你这是在倒分？？？