学步园

记录下工作中写的其中一块触发器和存储过程，以便于将来查询
触发器

create or replace trigger tr_tai_cell_jituan_i
  after insert on tai_cell_jituan_relation  
  for each row
begin
  insert into olapc.tai_cell_jituan_relation@olap (cell_id,jituan_id,cell_desc_jituan)
   values (:new.cell_id,:new.jituan_id,:new.cell_desc_jituan);  
end tr_tai_cell_jituan_i;


/**//****************************************/
create or replace trigger tr_tai_cell_jituan_d
  after delete on tai_cell_jituan_relation  
  for each row
begin    
   delete from  olapc.tai_cell_jituan_relation@olap where cell_id = :old.cell_id and jituan_id = :old.jituan_id;   
end tr_tai_cell_jituan_d;

/**//****************************************/

create or replace trigger tr_tai_cell_jituan_u
  after update on tai_cell_jituan_relation  
  for each row
begin    
   update  olapc.tai_cell_jituan_relation@olap set cell_id=:new.cell_id,jituan_id = :new.jituan_id,cell_desc_jituan=:new.cell_desc_jituan where cell_id = :old.cell_id and jituan_id = :old.jituan_id;   
end tr_tai_cell_jituan_u;

 

存储过程

  1create or replace procedure sp_tpa_jituan_query(timeid number,regionname varchar2,sum_level number,jituanid number,issuccess out number) is
  2v_jituanid number;
  3v_jituanname varchar2(64);
  4v_region_name varchar2(64);
  5v_tch_traffic float;
  6v_time_desc varchar2(64);
  7v_hb_timeid number;
  8v_tb_timeid number;
  9v_hb_traffic float;
 10v_tb_traffic float;
 11v_is_have number;
 12v_hb_rate float;
 13v_tb_rate float;
 14v_count number;
 15    cursor mycur is
 16        select a.jituan_id,a.jituan_name,c.region_name 
 17from tai_jituan a,tai_borough b,(select distinct region_id,region_name from region_city_local where region_name = regionname) c
 18where a.borough_id = b.borough_id and b.region_id = c.region_id;
 19begin
 20  issuccess := -1;
 21  v_count := -1;
 22  v_jituanid := 0;
 23  v_jituanname :='';
 24  v_region_name :='';
 25  v_tch_traffic :=0;
 26  v_time_desc :='';
 27  v_hb_timeid :=0;
 28  v_tb_timeid :=0;
 29  v_hb_traffic :=0;
 30  v_tb_traffic :=0;
 31  v_is_have :=0;
 32  v_hb_rate:=0;
 33  v_tb_rate :=0;
 34  /**//*sum_level:0时;1天;2周;3月;4季;5年
 35  查2007年04月02日某一时刻的（比如03时），
 36  集团id＝1的所属地区、集团名、话务量，前一天的这个时刻的话务量，环比增幅，
 37  上个月同一日的话务量，同比增幅，及判断该集团是否是我得关注*/
 38  if (sum_level = 0) then
 39     --小时汇总查询
 40     if (jituanid <= 0) then
 41        if mycur%isopen = false then
 42         open mycur;
 43        end if;
 44        fetch mycur into v_jituanid, v_jituanname,v_region_name;
 45        while mycur%found 
 46        loop
 47             select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = timeid;
 48            if(v_count>0)then
 49            select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = timeid;
 50            select hour_desc,last_hour_id,yestoday_hour_id into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_hour where hour_id = timeid;
 51           begin
 52            select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = v_hb_timeid;
 53           exception
 54           when others then
 55           v_hb_traffic:=0;
 56           end;
 57           begin
 58            select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = v_tb_timeid;
 59           exception
 60           when others then
 61           v_tb_traffic:=0;
 62           end;
 63            select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 0;
 64            if (v_hb_traffic <> 0) then
 65               v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
 66            else
 67               v_hb_rate := -999999;
 68            end if;
 69            if (v_tb_traffic <> 0) then
 70               v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
 71            else
 72               v_tb_rate := -999999;
 73            end if;
 74            if (v_is_have = 0) then
 75              insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate) 
 76              values (timeid,v_jituanid,0,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate); 
 77            else
 78              update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate 
 79              where time_id = timeid and jituan_id = v_jituanid and sum_level = 0; 
 80            end if;
 81            commit;
 82            end if;
 83            fetch mycur into v_jituanid, v_jituanname,v_region_name;
 84        end loop;
 85        close mycur;
 86     else
 87          select a.jituan_id,a.jituan_name,c.region_name into v_jituanid,v_jituanname,v_region_name 
 88          from tai_jituan a,tai_borough b,(select distinct region_id,region_name from region_city_local where region_name = regionname) c
 89          where a.borough_id = b.borough_id and b.region_id = c.region_id and a.jituan_id=jituanid;
 90           select count(1) into v_count from tpa_jituan where jituan_id = jituanid and sum_level = 0 and time_id = timeid;
 91        if(v_count>0)then       
 92              select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 0 and time_id = timeid;
 93              select hour_desc,LAST_HOUR_ID,YESTODAY_HOUR_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_hour where hour_id = timeid;
 94             begin
 95              select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 0 and time_id = v_hb_timeid;
 96              exception
 97              when others then
 98              v_hb_traffic :=0;
 99              end;
100              begin          
101              select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 0 and time_id = v_tb_timeid;
102             exception 
103             when others then
104             v_tb_traffic :=0;
105             end;
106              select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = jituanid and sum_level = 0;
107              if (v_hb_traffic <> 0) then
108                    v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
109                else
110                      v_hb_rate := -999999;
111                end if;
112              if (v_tb_traffic <> 0) then
113                     v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
114                else
115                     v_tb_rate := -999999;
116                end if;
117              if (v_is_have = 0) then
118                insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate) 
119                values (timeid,jituanid,0,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate); 
120              else
121                update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate 
122                where time_id = timeid and jituan_id = v_jituanid and sum_level = 0; 
123              end if;
124              commit;
125        end if;
126     end if;
127  elsif (sum_level = 1) then
128  --日汇总查询