2个存储过程

create or replace procedure P_CODE_MAP_ITEM is
/* 
  v_sql varchar2(5000);
*/
begin
  dbms_output.put_line('更新lbas变更，更新到code_map_item');
 
  --from area@lbas to code_map_item
  merge into code_map_Item a
  using
  (select a.area_Id,a.name,
          9 as code_map_type_id,3 as code_map_dest_Id
  from area@lbas a) b
  on(a.code_map_type_id = 9 and  a.code_map_dest_id = 3
    and b.area_id = a.local_item_code )
  when matched then update set a.local_item_name =b.name
  when not matched then
    insert (local_item_code,local_item_name,
    code_map_type_id,code_map_dest_id,tag)
    values(b.area_id,b.name,
    9,3,0);       
  commit;
  --from serv_type@lbas to code_map_item
  merge into code_map_Item a
  using
  (select a.serv_type_id,a.type_name,
          1 as code_map_type_id,3 as code_map_dest_Id
  from serv_type@lbas a) b
  on(a.code_map_type_id = 1 and a.code_map_dest_id = 3
    and b.serv_type_id = a.local_item_code )
  when matched then update set a.local_item_name =b.type_name
  when not matched then
    insert (local_item_code,local_item_name,
    code_map_type_id,code_map_dest_id,tag)
    values(b.serv_type_id,b.type_name,
    1,3,0); 
  commit; 
  --from user_type@lbas to code_map_item
  merge into code_map_Item a
  using
  (select a.user_type_id,a.type_name,
          8 as code_map_type_id,3 as code_map_dest_Id
  from user_type@lbas a) b
  on(a.code_map_type_id = 8 and a.code_map_dest_id = 3
     and b.user_type_id = a.local_item_code)
  when matched then update set a.local_item_name = b.type_name
  when not matched then
  insert (local_item_code,local_item_name,
          code_map_type_id,code_map_dest_id,tag)
  values(b.user_type_id,b.type_name,8,3,0); 
  commit;
  dbms_output.put_line('更新条目插入code_map_item表完成！');
   
end P_CODE_MAP_ITEM;

/*
  v_sql:=
  'insert into code_map_item
    (local_item_code,local_item_name,
    code_map_type_id,code_map_dest_id,tag)
    select j.area_id,j.name,
    c.code_map_type_id,c.code_map_dest_id,0
    from code_map_type c,area@lbas j
    where c.prov_table_name=''exchange''
    and to_char(j.area_id) not in
    (select local_item_code from code_map_item
    where code_map_type_id=9 and code_map_dest_id=3)';
  execute immediate v_sql;
  commit;
  dbms_output.put_line('从lbas系统中取修改行id,name，同步修改code_map_item'); 
  dbms_output.put_line('修改area@lbas'); 
  v_sql:=
    'update code_map_item a set a.local_item_name =
    (select name from area@lbas
    where area_id=a.local_item_code)
    where code_map_type_id=9 and code_map_dest_id=3';
  execute immediate v_sql;
  commit;
*/   

/*
  dbms_output.put_line('from serv_type@lbas to code_map_item');
  v_sql:=
    'insert into code_map_item
    (local_item_code,local_item_name,
    code_map_type_id,code_map_dest_id,tag)
    select j.serv_type_id,j.type_name,
    c.code_map_type_id,c.code_map_dest_id,0
    from code_map_type c,serv_type@lbas j
    where c.prov_table_name=''serv_type''
    and to_char(j.serv_type_id) not in
    (select code_map_item.local_item_code from code_map_item
    where code_map_type_id=1 and code_map_dest_id=3)';
  execute immediate v_sql;
  commit;
     
  dbms_output.put_line('修改serv_type@lbas');   
  v_sql:=
    'update code_map_item a set a.local_item_name =
    (select type_name from serv_type@lbas
    where serv_type_id=a.local_item_code)
    where code_map_type_id=1 and code_map_dest_id=3';
  execute immediate v_sql;
  commit;
*/ 
/*
  dbms_output.put_line('from user_type@lbas to code_map_item');
  v_sql:=
    'insert into code_map_item
    (local_item_code,local_item_name,
    code_map_type_id,code_map_dest_id,tag)
    select j.user_type_id,j.type_name,
    c.code_map_type_id,c.code_map_dest_id,0
    from code_map_type c,user_type@lbas j
    where c.prov_table_name=''dg_user_property''
    and j.user_type_id not in
    (select code_map_item.local_item_code from code_map_item
    where code_map_type_id=8 and code_map_dest_id=3)'; 
  execute immediate v_sql;
  commit; 
  dbms_output.put_line('修改user_type@lba');   
  v_sql:=
    'update code_map_item a set a.local_item_name =
    (select type_name from user_type@lbas
    where user_type_id=a.local_item_code)
    where a.code_map_type_id=8 and code_map_dest_id=3'; 
  execute immediate v_sql;
  commit; 
*/ 
 

----------------------------

 

create or replace procedure Check_Serv_big_item(p_month_id in number) is
  v_sql                     varchar2(5000);
  last_month_id             number(6);
  record_num                number(10);
  last_record_num           number(10);
  amount                    number(12);
  last_amount               number(12);
  amount_difference         number(12);
  difference_ratio          number(12,2);
begin
 
    --得到上月的月份数
    Select to_char(add_months(to_date(To_char(p_month_id),'yyyymm'),-1),'yyyymm')
    Into last_month_id From dual;
    /*
    --得到分局code
    select to_char(p_area_id) into code from dual;
    */
   
    --往check_interface表里插入Serv_big_item表
   
    --得到该月计费收入接口表的总记录数和总费用
    v_sql:= 'Select count(*) as record_num, sum(total_charge) as amount
             from serv_big_item partition(p'||p_month_id||') a ';
    EXECUTE IMMEDIATE v_sql Into record_num,amount;
                     
    --得到上月计费收入接口表的总记录数和总费用
    v_sql:= 'Select count(*) as last_record_num,
                    sum(total_charge) as last_amount
             from serv_big_item partition(p'||last_month_id||') a ';
    EXECUTE IMMEDIATE v_sql Into last_record_num,last_amount;

    --得到本月与上月的总费用差额
    amount_difference  := amount  - last_amount ;
         
    --得到本月与上月的总费用差额率
    If last_amount = 0 Then difference_ratio := 0;
    Else difference_ratio := amount_difference/last_amount;
    End If;
   
    --删除表Check_interface中当月的用户数据   
    v_sql:= 'delete from check_interface
             where table_name = ''Serv_big_item'' and month_id = :v1';
    execute immediate v_sql using p_month_id;
    commit;     
   
    --往check_interface表里插入Serv_big_item表中总量的统计值          
    v_sql:= 'Insert into check_interface
                    (month_id,table_name,column_name,code,code_name,
                    record_num,last_record_num,
                    amount,amount_ratio,last_amount,
                    amount_difference,difference_ratio)
             Values (:v1,''Serv_big_item'',''area_id'',
                    ''0000'',''总量'',:v2,:v3,:v4,
                    ''1.00'',:v5,:v6,:v7) ';
    EXECUTE IMMEDIATE v_sql
    USING p_month_id,record_num,last_record_num,
          amount,last_amount,amount_difference,difference_ratio;
    commit;

    --area_id维度插入(39个分局)
    --往check_interface表里插入Serv_big_item表中各area_id的本月各编码统计值
    v_sql:='insert into check_interface
                   (month_id,table_name,column_name,code,code_name,
                   record_num,amount,amount_ratio)
            select :v1,''Serv_big_item'',''area_id'',nvl(e.area_id,''-1'') as code,
                   nvl(e.area_name,''null'') as code_name,count(*) as record_num,
                   sum(total_charge) as amount,sum(total_charge)/:v2 as amount_ratio      
            from serv_big_item partition(p'||p_month_id||') a,region b,region_group c,dept d,area e
            where a.region_id = b.region_id(+)
                  and b.group_id = c.group_id(+) and c.dept_id = d.dept_id(+)
                  and d.area_id = e.area_id(+)
            group by e.area_id,e.area_name
            order by e.area_id ';
    execute immediate v_sql using p_month_id,amount;
    commit;
   
    --往check_interface表里更新插入Serv表中各State_f的上月各编码统计值 
    v_sql:='Merge Into check_interface aa
            Using
                  (Select ''Serv_big_item'' as table_name,''area_id'' as column_name,
                          nvl(e.area_id,''-1'') as code,nvl(e.area_name,''null'') as code_name,
                          count(*) as last_record_num,
                          count(total_charge) as last_amount
                  from serv_big_item partition(p'||last_month_id||') a,region b,region_group c,dept d,area e
                  where e.area_id(+)=d.area_id and d.dept_id(+)=c.dept_id
                        and c.group_id(+)=b.group_id and b.region_id(+)=a.region_id
                        group by e.area_id,e.area_name
                        order by e.area_id) ee
            On (aa.month_id   = :v1 And
               aa.table_name = ee.table_name And aa.column_name = ee.column_name And
               aa.code       = ee.code       And aa.code_name   = ee.code_name)
            When Matched Then
                 Update Set
                        aa.last_record_num = ee.last_record_num,aa.last_amount = ee.last_amount,
                        aa.amount_difference = (aa.amount - ee.last_amount),
                        aa.difference_ratio = decode(ee.last_amount,0,0,(aa.amount - ee.last_amount)/ee.last_amount)
            When Not Matched Then
                 Insert
                        (aa.month_id,aa.table_name,aa.column_name,aa.code,aa.code_name,
                        aa.record_num,aa.last_record_num,aa.amount,aa.amount_ratio,
                        aa.last_amount,aa.amount_difference,aa.difference_ratio)
                 Values (:v2,ee.table_name,ee.column_name,ee.code,ee.code_name,
                         0,ee.last_record_num,0,0,ee.last_amount,(0-ee.last_amount),0)';
   
    dbms_output.put_line(substr(v_sql,1,255));
    EXECUTE IMMEDIATE v_sql Using p_month_id,p_month_id;
    Commit;

end Check_Serv_big_item;