存储过程 实例

create or replace procedure update_dic_org_by_mdm is

  CURSOR v_roo_date IS
 
    select distinct trim(tb.minister_code) minister_code
      from user_mdm tb
     where tb.modify_flag = '1'
       and length(tb.minister_code) > 0;

  v_mdm_root_code user_mdm.minister_code %type;
  --v_mdm_date      user_mdm%rowtype;

  v_dept dic_org%rowtype;

  --v_code varchar2(128);
  --v_path varchar2(1024);
  -- v_name varchar2(128);
  --l_sql  varchar2(3000);

begin

  for list_root_date in v_roo_date loop
    v_mdm_root_code := list_root_date.minister_code;
 
    --DBMS_OUTPUT.PUT_LINE('v_mdm_root_code=' || v_mdm_root_code);
 
    declare
      cursor cv_dept is
      --  select * from dic_org t where t.id = '101011';
        select T.Code,
               t.parent_name,
               t.name,
               case t.update_flag
                 when '1' then
                  '新增'
                 when '2' then
                  '修改'
                 when '3' then
                  '删除'
                 else
                  '-1'
               end as update_type
          from user_mdm t
         where t.modify_flag = '1'
         start with t.code = v_mdm_root_code
        connect by nocycle prior t.code = t.parent_code;
   
    begin
      --变量v_dept不必我们显示声明  
      --for循环会为我们隐式的打开和关闭游标，不必我们现实的打开和关闭游标  
      for v_dept in cv_dept loop
       
     
        dbms_output.put_line('code:' || v_dept.code || '  路径--' ||
                             v_dept.parent_name || '  _____名称' ||
                             v_dept.name);
                            
                            
      if v_dept.update_type ='新增' then
       
        dbms_output.put_line('新增');
      
      end if;
                            
      if v_dept.update_type ='修改' then
       
        dbms_output.put_line('修改');
      
      end if;     

      if v_dept.update_type ='删除' then
       
        dbms_output.put_line('删除');
      
      end if;    
     
     
      end loop;
    end;
 
  end loop;

end update_dic_org_by_mdm;