oracle之procedures

create or replace procedure omc_sys_permit
(
       pFmenuid     varchar2,   -- 菜单编号
       pUserid      varchar2,   -- 用户编号
       pType        varchar2,   -- 操作类型
       pRetCode     out varchar2,  --返回代码
       pRetInfo     out varchar2   --返回提示

)
/*-- ===========================================
        Author: JiQing.xie
        Timer: 2010-02-05 14:09:00
        Developers: SepSOFT
       Version: v1.0
       Functio: 用户权限操作
  -- ==========================================*/
is
 
      templ varchar2(32);
      tempr varchar2(32);
      temp varchar2(32):='';
      i number:=0;
      j number:=0;
begin
    -- 删除权限--
     delete from omc_user_menu where fuserid=pUserid;
          
      for my in ( select to_char(strvalue) as Value from table(fun_split(pFmenuid,',')))
        loop
             if my.value is not null then      
              i:=instr(my.value,'^');
              templ:=substr(my.value,0,i-1);
              if j=0 then
               tempr:=substr(my.value,i+1);
              end if;
                if j>0 then   
                if temp=templ then
                   tempr:=tempr||','||substr(my.value,i+1);       
                end if;  
                if temp!=templ then 
                 insert into omc_user_menu (fuserid,fmenuid,ftime,factionid)values(pUserid,temp,sysdate,tempr);
                  tempr:=substr(my.value,i+1);          
                end if;   
                end if;
                 j:=1;         
                temp:=templ;            
              end if;  
        end loop;
        if templ is not null then
          insert into omc_user_menu (fuserid,fmenuid,ftime,factionid)values(pUserid,templ,sysdate,tempr);
        end if;
        pRetCode:=0;
        pRetInfo:='授权成功';
      
      if pType ='update' then
           pRetInfo:='修改成功';
     end if;
     commit;
      ---- 添加父类---
      for mytable in (select distinct fpid from omc_menu t where fid in(select fmenuid from omc_user_menu t where fuserid=pUserid))
      loop
          insert into omc_user_menu (fuserid,fmenuid,ftime,factionid)values(pUserid,mytable.fpid,sysdate,0);
      end loop;
exception
      when others then
      pRetCode := 1000;
      pRetInfo :='操作失败';
      xp_sys_errlog('omc_sys_permit',sqlcode,sqlerrm,'pUserid='||pUserid);
end omc_sys_permit;