c#存储过程1.txt

oracle数据库存储过程的一些小实例

CREATE OR REPLACE PROCEDURE up_update_gdgj_flag(
i_id   number,
i_zt_flag varchar2
)
Is
 v_gdbh  varchar2(12); --工单编号
 v_node   int;   --子单当前节点号
 v_parid  int;   --该子单上级节点号
 v_flag  char(1);  --子单状态
 --v_maxnode int;   --该级最大节点号
 --v_nextid int;   --下个要处理的子单编号
 v_nextnode int;   --下个要处理的子单节点号
 v_count  int;            --判断是否有串行节点
 V_count1 int;   --判断是否有并行节点
  
  v_endcount int;        --判断是否所有的通知单都已经完成
 --v_rtn   int;


 /*定义游标*/
 CURSOR cur_subdetail(cv_gdbh varchar2,cv_node int,cv_parid number)
 IS SELECT node FROM sub_gdgj_detail where gd_bh=cv_gdbh and node>cv_node and parentid=cv_parid and flow_level='1';

 begin

  /*参数赋值*/
  v_count:=0;
  v_count1:=0;
    v_endcount:=0;

  /*事务开始*/
  savepoint work_begin;

  /*获取记录信息*/
  select gd_bh,node,parentid,zt_flag into v_gdbh,v_node,v_parid,v_flag from sub_gdgj_detail where id=i_id;
  
  /*更改当前记录状态*/
  begin
   update sub_gdgj_detail set zt_flag=i_zt_flag where id=i_id and flow_level='1';
   exception
    when others then
     begin
      rollback to work_begin;
      return;
     end;
  end;


  /*如果工单是正常处理则*/
  if i_zt_flag='2' then

   /*判断是否有并行节点*/
   select count(*) into v_count1 from sub_gdgj_detail where gd_bh=v_gdbh and parentid=v_parid and node=v_node and zt_flag not in('2','3') and flow_level='1';

   if(v_count1=0) then
    begin
     /*打开游标*/
     open cur_subdetail(v_gdbh,v_node,v_parid);
     loop
      FETCH cur_subdetail INTO v_nextnode;
      exit when (cur_subdetail%NOTFOUND or v_count=1);

      v_count:=v_count+1;
      begin
       update sub_gdgj_detail set zt_flag='0' where gd_bh=v_gdbh and node=v_nextnode and parentid=v_parid and flow_level='1' and zt_flag not in('2','3');
       exception
        when others then
         begin
          rollback to work_begin;
          return;
         end;
      end;

     end loop;
     close cur_subdetail;
    end;
   end if;
      /*如果不是还有上级则继续--把上级子单状态从5->1*/
      if v_count1=0 and v_count=0 and v_parid<>0 then

           update sub_gdgj_detail set zt_flag='1' where id=v_parid;
      end if;
      /*如果是只有一级则修改主工单状态
      if  v_count1=0 and v_count=0 and v_parid=0 then
        update gdgj_new_detail set flow_node_id='1005' where gdgj_bh=v_gdbh;
           -- update gdgj_new_kexiang_detail set flow_node_id='1005' where gdgj_bh=v_gdbh;
      end if;*/

  end if;


  /*如果工单是异常处理则*/
  if( i_zt_flag='3' or i_zt_flag = '9' )then
   begin
    /*把并行的节点置成4,0.1.7分别为待签收,待处理,待评估*/
    update  sub_gdgj_detail
    set  zt_flag='3'
    where gd_bh=v_gdbh
        and  zt_flag not in('2','3');
--    and     node=v_node
--    and     parentid=v_parid
--    and     zt_flag in ('0','1','7','4') and flow_level='1';
      
        /*如果还有上级则继续--把上级子单状态从5->0，这样在
        if v_parid<>0 then

           update sub_gdgj_detail set zt_flag='0' where id=v_parid;
        end if;   */
        /*如果是只有一级则修改主工单状态
        if v_parid=0 then
            if i_zt_flag='3' then
        update gdgj_new_detail set flow_node_id='1005' where gdgj_bh=v_gdbh;
            --update gdgj_new_kexiang_detail set flow_node_id='1005'  where gdgj_bh=v_gdbh;
        end if;
        end if;*/

    exception
     when others then
      begin
       rollback to work_begin;
       return;
      end;
   end;
  end if;
    
    /*判断是否所有通知单都已完成,如果完成更新主单状态为评估*/
  select count(*) into v_endcount from sub_gdgj_detail where gd_bh=v_gdbh and zt_flag not in('2','3');
    if  v_endcount=0 then
        update gdgj_new_detail set flow_node_id='1005' where gdgj_gjbh=v_gdbh;
    end if;
    

  commit;
   
  return;
 end up_update_gdgj_flag;