当前位置：首页 → 问答吧 → 移植存储过程有错

移植存储过程有错

时间：2011-11-04

来源：互联网

我自己的机子oracle是11g的，存储过程执行正确。服务器是10g的，把存储过程移到上面执行，编译就出错了。
错误指着的那一行是不确定的，意思就是这次编译指着这行，下次编译指着另一行，怎么办呢？
错误信息是：此表达式的类型声明不完整或格式不正确。
存储过程代码如下，盼望高手能帮忙看下SQL code


--构建数组
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 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 MESSAGE_VARCHAR2  as table of varchar2(1000);
create or replace type ERROR_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_INITMONEY_NUMBER in INITMONEY_NUMBER,      
PARAMS_RESULTMONEY_NUMBER in RESULTMONEY_NUMBER,  
PARAMS_TOLLYFLAG_NUMBER in TOLLYFLAG_NUMBER,     
PARAMS_AUTOAUDITFLAG_NUMBER in AUTOAUDITFLAG_NUMBER,  
--PARAMS_WFID_VARCHAR2 in varchar2, 
--PARAMS_CURRENTNODE_VARCHAR2(i) in varchar2,  
PARAMS_TIMESTAMP_VARCHAR2 in TIMESTAMP_VARCHAR2,  

VOUTYPEID_VARCHAR2 in varchar2, 
--PARAMS_REMARK_VARCHAR2 in REMARK_VARCHAR2   
var_message_array OUT MESSAGE_VARCHAR2, 
 var_error_array out ERROR_VARCHAR2
) 
is 
    var_nextnode varchar2(100);
    var_cur_count number;
    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;
    type v_timestamp_table is table of t_budgetvoucher.timestamp%type index by binary_integer; 
    v_timestamp v_timestamp_table;
    --参数游标
    cursor cur1(i number) is
    select NEXTNODEID  from t_wfnodeconditions where WFID=PARAMS_WFID_VARCHAR2(i) and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i);
    
begin

--行级锁
--select operdate bulk collect  into v_operdate from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2 for update wait 10; 
for i in 1..PARAMS_ENTITYID_VARCHAR2.count loop 
    select timestamp bulk collect  into v_timestamp from t_budgetvoucher where GUID=PARAMS_ENTITYID_VARCHAR2(i) for update wait 5; 
end loop;

--开辟异常数组 
var_message_array:=MESSAGE_VARCHAR2();
var_error_array:=ERROR_VARCHAR2();
  for i in 1..PARAMS_ENTITYID_VARCHAR2.count loop 
  --判断nextnode数量
    select count(*) into var_count from t_wfnodeconditions where WFID=PARAMS_WFID_VARCHAR2(i) and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i);
    if var_count =0 then 
    var_error_array.extend;
    var_error_array(var_error_array.count):='当前节点没有子节点;';
    end if;
    if var_count<>0 then
     var_error_array.extend;
    var_error_array(var_error_array.count):='just for array extend';
    end if;
  --根据业务id判断timestamp是否被修改
    select count(*) into var_cur_count from t_budgetvoucher where guid=PARAMS_ENTITYID_VARCHAR2(i) and to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ssxff') =PARAMS_TIMESTAMP_VARCHAR2(i);
     
  --异常和提示
    if var_cur_count <>1 then 
    var_message_array.extend;
    var_message_array(var_message_array.count):='timestamp不相等,此条记录已被修改;';
    end if;
    if var_cur_count =1 then
    var_message_array.extend;
    var_message_array(var_message_array.count):='just for array extend';
    end if;
    
  --不是会签节点
    if var_count =1 then
    select NEXTNODEID into var_nextnodeid from t_wfnodeconditions where WFID=PARAMS_WFID_VARCHAR2(i) and NODEID=PARAMS_CURRENTNODE_VARCHAR2(i);
    select NODETYPE into var_nodetype from t_wfnodes where GUID =var_nextnodeid;
    end if;
        
    if var_count=1 and var_cur_count=1 then  
    --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:=PARAMS_CURRENTNODE_VARCHAR2(i);
        var_curstatus:=4;
        var_nextstatus:=5;
        end if;

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

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

    --根据操作类型写sql

        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' then

            insert into t_wfcompletetasks  select * from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;

            update t_wfcurrenttasks set CURRENTNODE=PARAMS_CURRENTNODE_VARCHAR2(i),CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
            NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
            OPERDATE=sysdate where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;
            
        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=PARAMS_WFID_VARCHAR2 ;
                update t_wfcurrenttasks set CURRENTNODE=PARAMS_CURRENTNODE_VARCHAR2(i),CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
                NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
                OPER
                =sysdate where WFID=PARAMS_WFID_VARCHAR2;
                end if;

                if i>1 then
                update t_wfcurrenttasks set CURRENTNODE=PARAMS_CURRENTNODE_VARCHAR2(i),CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
                NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
                OPERDATE=sysdate where WFID=PARAMS_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'  then
         
            delete from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;
            
        end if;

--nextnode为结束节点
    if var_nodetype='003' then
    delete from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;
    end if;

   end if;
end if;

作者: sakuramanu 发布时间: 2011-11-04

SQL code


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

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

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

                if PARAMS_ACTIONTYPE_VARCHAR2(i)='HANG' then
                var_nextnode:=PARAMS_CURRENTNODE_VARCHAR2(i);
                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' then
               /*
                if i=1 then
                --往历史表插入数据，更新currenttask表
                insert into t_wfcompletetasks  select * from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2 and OPERUSER=PARAMS_OPERUSER_VARCHAR2(i);

                update t_wfcurrenttasks set CURRENTNODE=PARAMS_CURRENTNODE_VARCHAR2(i),CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
                NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
                OPERDATE=sysdate where WFID=PARAMS_WFID_VARCHAR2;
                *
                delete from t_wfcurrenttasks where WFID=PARAMS_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 (PARAMS_WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),PARAMS_CURRENTNODE_VARCHAR2(i) ,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),VOUTYPEID_VARCHAR2);
                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 (PARAMS_WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),PARAMS_CURRENTNODE_VARCHAR2(i) ,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),VOUTYPEID_VARCHAR2);
                  end if;
                */
            insert into t_wfcompletetasks  select * from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;

            update t_wfcurrenttasks set CURRENTNODE=PARAMS_CURRENTNODE_VARCHAR2(i),CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnode,
            NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
            OPERDATE=sysdate where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;

         end if;
         --循环游标
         open cur1(i);
         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 var_cur_loop_count=i and cur1%ROWCOUNT=1  then
                      if  var_cur_count=1 and cur1%ROWCOUNT=1  then
                      insert into t_wfcompletetasks  select * from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;
                      delete from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;
                      insert into t_wfcurrenttasks (WFID,ENTITYID ,CURRENTNODE,  CURRENTSTATUS,NEXTNODE  ,NEXTSTATUS ,
                              ACTIONTYPE  ,IS_UNDO  ,OPERUSER,OPERDATE,INITMONEY,RESULTMONEY,  REMARK  ,TOLLYFLAG, AUTOAUDITFLAG,VOUTYPEID)
                      values (PARAMS_WFID_VARCHAR2(i),PARAMS_ENTITYID_VARCHAR2(i),PARAMS_CURRENTNODE_VARCHAR2(i) ,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),'node_one',PARAMS_TOLLYFLAG_NUMBER(i),PARAMS_AUTOAUDITFLAG_NUMBER(i),VOUTYPEID_VARCHAR2);
                     /*
                      update t_wfcurrenttasks set CURRENTNODE=PARAMS_CURRENTNODE_VARCHAR2(i),CURRENTSTATUS=var_curstatus,NEXTNODE=var_nextnodeidcur,
                      NEXTSTATUS=var_nextstatus,ACTIONTYPE=PARAMS_ACTIONTYPE_VARCHAR2(i),OPERUSER=PARAMS_OPERUSER_VARCHAR2(i),
                      OPERDATE=sysdate,remark='sss' where WFID=PARAMS_WFID_VARCHAR2 and to_char(operdate,'yyyy-mm-dd hh24:mi:ss') =PARAMS_TIMESTAMP_VARCHAR2(i);
                      
                      insert into t_wfcompletetasks  select * from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2 and OPERUSER=PARAMS_OPERUSER_VARCHAR2(i);

                      delete from t_wfcurrenttasks where WFID=PARAMS_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 (PARAMS_WFID_VARCHAR2,PARAMS_ENTITYID_VARCHAR2(i),PARAMS_CURRENTNODE_VARCHAR2(i) ,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),VOUTYPEID_VARCHAR2);

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

                      if var_cur_count=1 and cur1%ROWCOUNT>1 then
                       
                      insert into t_wfcurrenttasks (WFID,ENTITYID ,CURRENTNODE,  CURRENTSTATUS,NEXTNODE  ,NEXTSTATUS ,
                              ACTIONTYPE  ,IS_UNDO  ,OPERUSER,OPERDATE,INITMONEY,RESULTMONEY,  REMARK  ,TOLLYFLAG, AUTOAUDITFLAG,VOUTYPEID)
                      values (PARAMS_WFID_VARCHAR2(i),PARAMS_ENTITYID_VARCHAR2(i),PARAMS_CURRENTNODE_VARCHAR2(i) ,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),'node>one',PARAMS_TOLLYFLAG_NUMBER(i),PARAMS_AUTOAUDITFLAG_NUMBER(i),VOUTYPEID_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'  then
                 
                   delete from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;

                 end if;

            end if;
          end loop;
             close cur1;

          if var_nodetype='003'  then
            delete from t_wfcurrenttasks where WFID=PARAMS_WFID_VARCHAR2(i) and entityid=PARAMS_ENTITYID_VARCHAR2(i) and voutypeid=VOUTYPEID_VARCHAR2;
             end if;
        end if;
 end loop;

commit;

    exception when others then
    rollback; 
    raise;
end drivenWorkFlow;