Oracle 包的使用

一、包规范

    1、report包

Sql代码 

1. CREATE OR REPLACE package report as  
2.     type cursorType is ref cursor;   
3.     type CommInfo is record(   
4.         id_ varchar2(60),   
5.         name_ varchar2(200),   
6.         comm_category_name_ varchar2(60),   
7.         city_company_name_ varchar2(200),   
8.         district_name_ varchar2(100),   
9.         county_company_name_ varchar2(200),   
10.         contact_ varchar2(60),   
11.         mobile_phone_ varchar2(60),   
12.         person_id_ varchar2(60),   
13.         bank_num_ varchar2(60)   
14.     );   
15.     type CardType is record(   
16.         id_ varchar2(60),   
17.         type_ varchar2(60),   
18.         card_brand_ varchar2(60),   
19.         card_brand_name_ varchar2(60)   
20.     );   
21.     type QuantityBean is record(   
22.         commId number,   
23.         pkl number := 0,   
24.         jhl number := 0,   
25.         jfl number := 0,   
26.         zsl number := 0   
27.     );   
28.     function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2;   
29. end;   

[sql] view plaincopy

1. CREATE OR REPLACE package report as  
2.     type cursorType is ref cursor;  
3.     type CommInfo is record(  
4.         id_ varchar2(60),  
5.         name_ varchar2(200),  
6.         comm_category_name_ varchar2(60),  
7.         city_company_name_ varchar2(200),  
8.         district_name_ varchar2(100),  
9.         county_company_name_ varchar2(200),  
10.         contact_ varchar2(60),  
11.         mobile_phone_ varchar2(60),  
12.         person_id_ varchar2(60),  
13.         bank_num_ varchar2(60)  
14.     );  
15.     type CardType is record(  
16.         id_ varchar2(60),  
17.         type_ varchar2(60),  
18.         card_brand_ varchar2(60),  
19.         card_brand_name_ varchar2(60)  
20.     );  
21.     type QuantityBean is record(  
22.         commId number,  
23.         pkl number := 0,  
24.         jhl number := 0,  
25.         jfl number := 0,  
26.         zsl number := 0  
27.     );  
28.     function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2;  
29. end;   

  

   2、reportFunc包

Sql代码 

1. CREATE OR REPLACE package reportFunc as  
2.     function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2;   
3.     function getCardTypeSQL return varchar2;   
4.     function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean;   
5. end;  

[sql] view plaincopy

1. CREATE OR REPLACE package reportFunc as  
2.     function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2;  
3.     function getCardTypeSQL return varchar2;  
4.     function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean;  
5. end;  

 

二、包体

    1、report包体

Sql代码 

1. CREATE OR REPLACE package body report as    
2.     --生成数据集   
3.  function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2 is  
4.         c1 cursorType;   
5.         c2 cursorType;   
6.         v_CommInfo CommInfo;   
7.         v_CardType CardType;   
8.   pkl_ number := 0; -- 值    
9.   jhl_ number := 0;   
10.   jfl_ number := 0;   
11.   zsl_ number := 0;   
12.   jhRate number := 0;   
13.   jfRate number := 0;   
14.   v_pk_total_ number := 0; -- 值合计    
15.   v_jh_total_ number := 0;   
16.   v_jf_total_ number := 0;   
17.   v_zs_total_ number := 0;   
18.   v_jhl_total_ number := 0;   
19.   v_jfl_total_ number := 0;   
20.   pk_sum number := 0; -- 值合计的合计     
21.   jh_sum number := 0;   
22.   jf_sum number := 0;   
23.   zs_sum number := 0;   
24.   jhl_sum number := 0;   
25.   jfl_sum number := 0;   
26.      
27.   bean QuantityBean;   
28.   result varchar2(200);   
29.   lsh number := 0;   
30.   createDate date := sysdate;   
31.     begin  
32.      delete from R_PIKA_STAT_ where create_date_<createDate-1;   
33.      commit;   
34.       
35.   select SEQ_LSH.nextval into lsh from dual;   
36.      
37.         --代办点    
38.         open c1 for reportFunc.getCommSQL(p_fgs, p_fwt, p_comm);   
39.         loop   
40.    v_pk_total_ := 0;   
41.    v_jh_total_ := 0;   
42.    v_jf_total_ := 0;   
43.    v_zs_total_ := 0;   
44.    v_jhl_total_ := 0;   
45.    v_jfl_total_ := 0;   
46.        
47.             fetch c1 into v_CommInfo;   
48.             exit when c1%notfound;   
49.       
50.             --卡类型    
51.             open c2 for reportFunc.getCardTypeSQL;   
52.             loop   
53.                 fetch c2 into v_CardType;   
54.                 exit when c2%notfound;   
55.        
56.     bean := reportFunc.getQuantityBean(v_CommInfo.id_, v_CardType.id_, p_date1, p_date2, p_status);   
57.        
58.     --批卡量   
59.     pkl_ := bean.pkl;   
60.     v_pk_total_ := v_pk_total_ + pkl_;   
61.         
62.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
63.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
64.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
65.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
66.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
67.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
68.     v_CardType.card_brand_,v_CardType.card_brand_name_,'1.批卡量(套数)', pkl_, createDate);   
69.        
70.     --激活量   
71.     jhl_ := bean.jhl;   
72.     v_jh_total_ := v_jh_total_ + jhl_;   
73.         
74.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
75.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
76.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
77.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
78.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
79.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
80.     v_CardType.card_brand_,v_CardType.card_brand_name_,'2.激活量(套数)', jhl_, createDate);   
81.        
82.     --成功积分登记量   
83.     jfl_ := bean.jfl;   
84.     v_jf_total_ := v_jf_total_ + jfl_;   
85.         
86.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
87.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
88.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
89.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
90.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
91.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
92.     v_CardType.card_brand_,v_CardType.card_brand_name_,'3.成功积分登记量(套数)', jfl_, createDate);   
93.        
94.     --激活率   
95.     jhRate := 0;   
96.     if pkl_ > 0 then  
97.      jhRate := (1.0 * jhl_) / pkl_;   
98.     else  
99.      if jhl_ > 0 then  
100.       jhRate := 1.0;   
101.      end if;   
102.     end if;   
103.         
104.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
105.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
106.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
107.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
108.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
109.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
110.     v_CardType.card_brand_,v_CardType.card_brand_name_,'4.激活率(%)', jhRate, createDate);   
111.         
112.     --积分率 = 已积分数量 / (已激活数量-赠送号激活量)    
113.     zsl_ := bean.zsl;   
114.     v_zs_total_ := v_zs_total_ + zsl_;   
115.         
116.     jfRate := 0;   
117.     if (jhl_ - zsl_) > 0 then  
118.      jfRate := (1.0 * jfl_) / (jhl_ - zsl_);   
119.     else  
120.      if jfl_ > 0 then  
121.       jfRate := 1.0;   
122.         end if;   
123.     end if;   
124.         
125.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
126.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
127.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
128.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
129.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
130.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
131.     v_CardType.card_brand_,v_CardType.card_brand_name_,'5.积分率(%)', jfRate, createDate);   
132.         
133.             end loop;   
134.             close c2;   
135.        
136.    --去掉值为0的代办点   
137.    if v_pk_total_<=0 and v_jh_total_<=0 and v_jf_total_<=0 then  
138.        delete from R_PIKA_STAT_ where comm_id_=v_CommInfo.id_;   
139.    else  
140.        --批卡合计   
141.     update R_PIKA_STAT_ set pk_total_=v_pk_total_ where comm_id_=v_CommInfo.id_;   
142.     pk_sum := pk_sum + v_pk_total_;   
143.         
144.     --激活合计   
145.     update R_PIKA_STAT_ set jh_total_=v_jh_total_ where comm_id_=v_CommInfo.id_;   
146.     jh_sum := jh_sum + v_jh_total_;   
147.        
148.     --积分量合计   
149.     update R_PIKA_STAT_ set jf_total_=v_jf_total_ where comm_id_=v_CommInfo.id_;   
150.     jf_sum := jf_sum + v_jf_total_;   
151.        
152.     --激活率合计   
153.     v_jhl_total_ := 0;   
154.     if v_pk_total_ > 0 then  
155.      v_jhl_total_ := (1.0 * v_jh_total_) / v_pk_total_;   
156.     else  
157.      if v_jh_total_ > 0 then  
158.       v_jhl_total_ := 1.0;   
159.         end if;   
160.     end if;   
161.     update R_PIKA_STAT_ set jhl_total_=v_jhl_total_ where comm_id_=v_CommInfo.id_;   
162.        
163.     --积分率合计   
164.     v_jfl_total_ := 0;   
165.     if (v_jh_total_ - v_zs_total_) > 0 then  
166.      v_jfl_total_ := (1.0 * v_jf_total_) / (v_jh_total_ - v_zs_total_);   
167.     else  
168.      if v_jf_total_ > 0 then  
169.       v_jfl_total_ := 1.0;   
170.      end if;   
171.     end if;   
172.     update R_PIKA_STAT_ set jfl_toal_=v_jfl_total_ where comm_id_=v_CommInfo.id_;   
173.     zs_sum := zs_sum + v_zs_total_;   
174.        
175.    end if;   
176.       
177.         end loop;   
178.         close c1;   
179.            
180.   --合计的合计    
181.   if pk_sum > 0 then  
182.       jhl_sum := (1.0 * jh_sum) / pk_sum;   
183.   else  
184.       if jh_sum > 0 then  
185.         jhl_sum := 1.0;   
186.    end if;   
187.   end if;   
188.      
189.   if (jh_sum - zs_sum) > 0 then  
190.       jfl_sum := (1.0 * jf_sum) / (jh_sum - zs_sum);   
191.   else  
192.       if jf_sum > 0 then  
193.        jfl_sum := 1.0;   
194.    end if;   
195.   end if;   
196.      
197.   result := lsh || ',' || pk_sum || ',' || jh_sum || ',' || jf_sum || ',' || jhl_sum || ',' || jfl_sum;   
198.      
199.   commit;   
200.      
201.   return result;   
202.    end;   
203.        
204.     
205. end;  

[sql] view plaincopy

1. CREATE OR REPLACE package body report as   
2.     --生成数据集  
3.  function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2 is  
4.         c1 cursorType;  
5.         c2 cursorType;  
6.         v_CommInfo CommInfo;  
7.         v_CardType CardType;  
8.   pkl_ number := 0; -- 值   
9.   jhl_ number := 0;  
10.   jfl_ number := 0;  
11.   zsl_ number := 0;  
12.   jhRate number := 0;  
13.   jfRate number := 0;  
14.   v_pk_total_ number := 0; -- 值合计   
15.   v_jh_total_ number := 0;  
16.   v_jf_total_ number := 0;  
17.   v_zs_total_ number := 0;  
18.   v_jhl_total_ number := 0;  
19.   v_jfl_total_ number := 0;  
20.   pk_sum number := 0; -- 值合计的合计    
21.   jh_sum number := 0;  
22.   jf_sum number := 0;  
23.   zs_sum number := 0;  
24.   jhl_sum number := 0;  
25.   jfl_sum number := 0;  
26.     
27.   bean QuantityBean;  
28.   result varchar2(200);  
29.   lsh number := 0;  
30.   createDate date := sysdate;  
31.     begin  
32.      delete from R_PIKA_STAT_ where create_date_<createDate-1;  
33.      commit;  
34.      
35.   select SEQ_LSH.nextval into lsh from dual;  
36.     
37.         --代办点   
38.         open c1 for reportFunc.getCommSQL(p_fgs, p_fwt, p_comm);  
39.         loop  
40.    v_pk_total_ := 0;  
41.    v_jh_total_ := 0;  
42.    v_jf_total_ := 0;  
43.    v_zs_total_ := 0;  
44.    v_jhl_total_ := 0;  
45.    v_jfl_total_ := 0;  
46.       
47.             fetch c1 into v_CommInfo;  
48.             exit when c1%notfound;  
49.      
50.             --卡类型   
51.             open c2 for reportFunc.getCardTypeSQL;  
52.             loop  
53.                 fetch c2 into v_CardType;  
54.                 exit when c2%notfound;  
55.       
56.     bean := reportFunc.getQuantityBean(v_CommInfo.id_, v_CardType.id_, p_date1, p_date2, p_status);  
57.       
58.     --批卡量  
59.     pkl_ := bean.pkl;  
60.     v_pk_total_ := v_pk_total_ + pkl_;  
61.        
62.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
63.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
64.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
65.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
66.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
67.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
68.     v_CardType.card_brand_,v_CardType.card_brand_name_,'1.批卡量(套数)', pkl_, createDate);  
69.       
70.     --激活量  
71.     jhl_ := bean.jhl;  
72.     v_jh_total_ := v_jh_total_ + jhl_;  
73.        
74.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
75.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
76.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
77.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
78.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
79.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
80.     v_CardType.card_brand_,v_CardType.card_brand_name_,'2.激活量(套数)', jhl_, createDate);  
81.       
82.     --成功积分登记量  
83.     jfl_ := bean.jfl;  
84.     v_jf_total_ := v_jf_total_ + jfl_;  
85.        
86.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
87.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
88.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
89.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
90.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
91.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
92.     v_CardType.card_brand_,v_CardType.card_brand_name_,'3.成功积分登记量(套数)', jfl_, createDate);  
93.       
94.     --激活率  
95.     jhRate := 0;  
96.     if pkl_ > 0 then  
97.      jhRate := (1.0 * jhl_) / pkl_;  
98.     else  
99.      if jhl_ > 0 then  
100.       jhRate := 1.0;  
101.      end if;  
102.     end if;  
103.        
104.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
105.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
106.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
107.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
108.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
109.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
110.     v_CardType.card_brand_,v_CardType.card_brand_name_,'4.激活率(%)', jhRate, createDate);  
111.        
112.     --积分率 = 已积分数量 / (已激活数量-赠送号激活量)   
113.     zsl_ := bean.zsl;  
114.     v_zs_total_ := v_zs_total_ + zsl_;  
115.        
116.     jfRate := 0;  
117.     if (jhl_ - zsl_) > 0 then  
118.      jfRate := (1.0 * jfl_) / (jhl_ - zsl_);  
119.     else  
120.      if jfl_ > 0 then  
121.       jfRate := 1.0;  
122.         end if;  
123.     end if;  
124.        
125.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
126.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
127.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
128.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
129.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
130.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
131.     v_CardType.card_brand_,v_CardType.card_brand_name_,'5.积分率(%)', jfRate, createDate);  
132.        
133.             end loop;  
134.             close c2;  
135.       
136.    --去掉值为0的代办点  
137.    if v_pk_total_<=0 and v_jh_total_<=0 and v_jf_total_<=0 then  
138.        delete from R_PIKA_STAT_ where comm_id_=v_CommInfo.id_;  
139.    else  
140.        --批卡合计  
141.     update R_PIKA_STAT_ set pk_total_=v_pk_total_ where comm_id_=v_CommInfo.id_;  
142.     pk_sum := pk_sum + v_pk_total_;  
143.        
144.     --激活合计  
145.     update R_PIKA_STAT_ set jh_total_=v_jh_total_ where comm_id_=v_CommInfo.id_;  
146.     jh_sum := jh_sum + v_jh_total_;  
147.       
148.     --积分量合计  
149.     update R_PIKA_STAT_ set jf_total_=v_jf_total_ where comm_id_=v_CommInfo.id_;  
150.     jf_sum := jf_sum + v_jf_total_;  
151.       
152.     --激活率合计  
153.     v_jhl_total_ := 0;  
154.     if v_pk_total_ > 0 then  
155.      v_jhl_total_ := (1.0 * v_jh_total_) / v_pk_total_;  
156.     else  
157.      if v_jh_total_ > 0 then  
158.       v_jhl_total_ := 1.0;  
159.         end if;  
160.     end if;  
161.     update R_PIKA_STAT_ set jhl_total_=v_jhl_total_ where comm_id_=v_CommInfo.id_;  
162.       
163.     --积分率合计  
164.     v_jfl_total_ := 0;  
165.     if (v_jh_total_ - v_zs_total_) > 0 then  
166.      v_jfl_total_ := (1.0 * v_jf_total_) / (v_jh_total_ - v_zs_total_);  
167.     else  
168.      if v_jf_total_ > 0 then  
169.       v_jfl_total_ := 1.0;  
170.      end if;  
171.     end if;  
172.     update R_PIKA_STAT_ set jfl_toal_=v_jfl_total_ where comm_id_=v_CommInfo.id_;  
173.     zs_sum := zs_sum + v_zs_total_;  
174.       
175.    end if;  
176.      
177.         end loop;  
178.         close c1;  
179.           
180.   --合计的合计   
181.   if pk_sum > 0 then  
182.       jhl_sum := (1.0 * jh_sum) / pk_sum;  
183.   else  
184.       if jh_sum > 0 then  
185.         jhl_sum := 1.0;  
186.    end if;  
187.   end if;  
188.     
189.   if (jh_sum - zs_sum) > 0 then  
190.       jfl_sum := (1.0 * jf_sum) / (jh_sum - zs_sum);  
191.   else  
192.       if jf_sum > 0 then  
193.        jfl_sum := 1.0;  
194.    end if;  
195.   end if;  
196.     
197.   result := lsh || ',' || pk_sum || ',' || jh_sum || ',' || jf_sum || ',' || jhl_sum || ',' || jfl_sum;  
198.     
199.   commit;  
200.     
201.   return result;  
202.    end;  
203.       
204.    
205. end;  

    2、reportFunc包体

Sql代码 

1. CREATE OR REPLACE package body reportFunc as    
2.     --返回获取代办点信息的SQL    
3.  function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2 is  
4.      sql_1 varchar2(2000);   
5.  begin  
6.      sql_1 := 'select a.id_,a.name_,e.name_ comm_category_name_,b.name_ city_company_name_,d.name_ district_name_,c.name_ county_company_name_,';   
7.   sql_1 := sql_1 || 'case when (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.contact_ else (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end contact_,';   
8.   sql_1 := sql_1 || 'case when (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.mobile_phone_ else (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end mobile_phone_,';   
9.   sql_1 := sql_1 || 'case when (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.person_id_ else (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end person_id_,';   
10.   sql_1 := sql_1 || 'case when (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.bank_num_ else (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end bank_num_';   
11.   sql_1 := sql_1 || ' from commission_info_ a';   
12.   sql_1 := sql_1 || ' left join organization_ b on a.city_company_id_=b.party_id_';   
13.   sql_1 := sql_1 || ' left join organization_ c on a.county_company_id_=c.party_id_';   
14.   sql_1 := sql_1 || ' left join district_ d on a.district_=d.id_';   
15.   sql_1 := sql_1 || ' left join commission_category_ e on a.comm_cagegory_=e.id_';   
16.   sql_1 := sql_1 || ' where a.id_ is not null';   
17.   if p_fgs is not null then  
18.      sql_1 := sql_1 || ' and a.city_company_id_=''' || p_fgs || '''';   
19.   end if;   
20.   if p_fwt is not null then  
21.      sql_1 := sql_1 || ' and a.county_company_id_=''' || p_fwt || '''';   
22.   end if;   
23.   if p_comm is not null then  
24.      sql_1 := sql_1 || ' and a.id_ in(' || p_comm || ')';   
25.   end if;   
26.   sql_1 := sql_1 || ' order by a.id_ asc';   
27.      
28.   return sql_1;   
29.  end;   
30.     
31.  --返回获取卡类型信息的SQL    
32.  function getCardTypeSQL return varchar2 is  
33.      sql_2 varchar2(1000);   
34.  begin  
35.      sql_2 := 'select a.id_,a.type_,a.card_brand_,b.description_ card_brand_name_';   
36.   sql_2 := sql_2 || ' from sim_card_type_ a inner join enumeration_ b';   
37.   sql_2 := sql_2 || ' on a.card_brand_=b.enum_id_ order by a.id_ asc';   
38.   return sql_2;   
39.  end;   
40.     
41.  -- 数量Bean    
42.  function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean is  
43.      s varchar2(2000);   
44.   bean report.QuantityBean;   
45.      c1 report.cursorType;   
46.  begin  
47.      s := 'select a.id_,';   
48.      
49.   s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and status_ in(' || p_status || ') and card_type_id_=' || p_type;   
50.   if p_date1 is not null then  
51.        s := s || ' and status_date_>=' || p_date1;   
52.   end if;   
53.   if p_date2 is not null then  
54.        s := s || ' and status_date_<' || p_date2;   
55.   end if;   
56.   s := s || ') pkl,';   
57.      
58.   s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and card_type_id_=' || p_type;   
59.   if p_date1 is not null then  
60.        s := s || ' and active_date_>=' || p_date1;   
61.   end if;   
62.   if p_date2 is not null then  
63.        s := s || ' and active_date_<' || p_date2;   
64.   end if;   
65.   s := s || ') jhl,';   
66.      
67.   s := s || '(select count(id_) from sim_card_detail_ where is_integral_=1 and commission_id_=a.id_ and card_type_id_=' || p_type;   
68.   if p_date1 is not null then  
69.        s := s || ' and active_date_>=' || p_date1;   
70.   end if;   
71.   if p_date2 is not null then  
72.        s := s || ' and active_date_<' || p_date2;   
73.   end if;   
74.   s := s || ') jfl,';   
75.      
76.   s := s || '(select count(id_) from sim_card_detail_ where status_=''RESOURCE_STATUS_13'' and commission_id_=a.id_ and card_type_id_=' || p_type;   
77.   if p_date1 is not null then  
78.        s := s || ' and active_date_>=' || p_date1;   
79.   end if;   
80.   if p_date2 is not null then  
81.        s := s || ' and active_date_<' || p_date2;   
82.   end if;   
83.   s := s || ') zsl';   
84.      
85.      
86.   s := s || ' from commission_info_ a where a.id_=' || p_comm;   
87.      
88.   open c1 for s;   
89.   loop   
90.       fetch c1 into bean;   
91.    exit when c1%notfound;   
92.   end loop;   
93.   close c1;   
94.      
95.      return bean;   
96.  end;   
97.     
98. end;  

    一、包规范

        1、report包

    Sql代码 

    1. CREATE OR REPLACE package report as  
    2.     type cursorType is ref cursor;   
    3.     type CommInfo is record(   
    4.         id_ varchar2(60),   
    5.         name_ varchar2(200),   
    6.         comm_category_name_ varchar2(60),   
    7.         city_company_name_ varchar2(200),   
    8.         district_name_ varchar2(100),   
    9.         county_company_name_ varchar2(200),   
    10.         contact_ varchar2(60),   
    11.         mobile_phone_ varchar2(60),   
    12.         person_id_ varchar2(60),   
    13.         bank_num_ varchar2(60)   
    14.     );   
    15.     type CardType is record(   
    16.         id_ varchar2(60),   
    17.         type_ varchar2(60),   
    18.         card_brand_ varchar2(60),   
    19.         card_brand_name_ varchar2(60)   
    20.     );   
    21.     type QuantityBean is record(   
    22.         commId number,   
    23.         pkl number := 0,   
    24.         jhl number := 0,   
    25.         jfl number := 0,   
    26.         zsl number := 0   
    27.     );   
    28.     function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2;   
    29. end;   
    [sql] view plaincopy

    1. CREATE OR REPLACE package report as  
    2.     type cursorType is ref cursor;  
    3.     type CommInfo is record(  
    4.         id_ varchar2(60),  
    5.         name_ varchar2(200),  
    6.         comm_category_name_ varchar2(60),  
    7.         city_company_name_ varchar2(200),  
    8.         district_name_ varchar2(100),  
    9.         county_company_name_ varchar2(200),  
    10.         contact_ varchar2(60),  
    11.         mobile_phone_ varchar2(60),  
    12.         person_id_ varchar2(60),  
    13.         bank_num_ varchar2(60)  
    14.     );  
    15.     type CardType is record(  
    16.         id_ varchar2(60),  
    17.         type_ varchar2(60),  
    18.         card_brand_ varchar2(60),  
    19.         card_brand_name_ varchar2(60)  
    20.     );  
    21.     type QuantityBean is record(  
    22.         commId number,  
    23.         pkl number := 0,  
    24.         jhl number := 0,  
    25.         jfl number := 0,  
    26.         zsl number := 0  
    27.     );  
    28.     function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2;  
    29. end;   

      

       2、reportFunc包

    Sql代码 

    1. CREATE OR REPLACE package reportFunc as  
    2.     function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2;   
    3.     function getCardTypeSQL return varchar2;   
    4.     function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean;   
    5. end;  
    [sql] view plaincopy

    1. CREATE OR REPLACE package reportFunc as  
    2.     function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2;  
    3.     function getCardTypeSQL return varchar2;  
    4.     function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean;  
    5. end;  

     

    二、包体

        1、report包体

    Sql代码 

    1. CREATE OR REPLACE package body report as    
    2.     --生成数据集   
    3.  function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2 is  
    4.         c1 cursorType;   
    5.         c2 cursorType;   
    6.         v_CommInfo CommInfo;   
    7.         v_CardType CardType;   
    8.   pkl_ number := 0; -- 值    
    9.   jhl_ number := 0;   
    10.   jfl_ number := 0;   
    11.   zsl_ number := 0;   
    12.   jhRate number := 0;   
    13.   jfRate number := 0;   
    14.   v_pk_total_ number := 0; -- 值合计    
    15.   v_jh_total_ number := 0;   
    16.   v_jf_total_ number := 0;   
    17.   v_zs_total_ number := 0;   
    18.   v_jhl_total_ number := 0;   
    19.   v_jfl_total_ number := 0;   
    20.   pk_sum number := 0; -- 值合计的合计     
    21.   jh_sum number := 0;   
    22.   jf_sum number := 0;   
    23.   zs_sum number := 0;   
    24.   jhl_sum number := 0;   
    25.   jfl_sum number := 0;   
    26.      
    27.   bean QuantityBean;   
    28.   result varchar2(200);   
    29.   lsh number := 0;   
    30.   createDate date := sysdate;   
    31.     begin  
    32.      delete from R_PIKA_STAT_ where create_date_<createDate-1;   
    33.      commit;   
    34.       
    35.   select SEQ_LSH.nextval into lsh from dual;   
    36.      
    37.         --代办点    
    38.         open c1 for reportFunc.getCommSQL(p_fgs, p_fwt, p_comm);   
    39.         loop   
    40.    v_pk_total_ := 0;   
    41.    v_jh_total_ := 0;   
    42.    v_jf_total_ := 0;   
    43.    v_zs_total_ := 0;   
    44.    v_jhl_total_ := 0;   
    45.    v_jfl_total_ := 0;   
    46.        
    47.             fetch c1 into v_CommInfo;   
    48.             exit when c1%notfound;   
    49.       
    50.             --卡类型    
    51.             open c2 for reportFunc.getCardTypeSQL;   
    52.             loop   
    53.                 fetch c2 into v_CardType;   
    54.                 exit when c2%notfound;   
    55.        
    56.     bean := reportFunc.getQuantityBean(v_CommInfo.id_, v_CardType.id_, p_date1, p_date2, p_status);   
    57.        
    58.     --批卡量   
    59.     pkl_ := bean.pkl;   
    60.     v_pk_total_ := v_pk_total_ + pkl_;   
    61.         
    62.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
    63.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
    64.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
    65.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
    66.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
    67.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
    68.     v_CardType.card_brand_,v_CardType.card_brand_name_,'1.批卡量(套数)', pkl_, createDate);   
    69.        
    70.     --激活量   
    71.     jhl_ := bean.jhl;   
    72.     v_jh_total_ := v_jh_total_ + jhl_;   
    73.         
    74.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
    75.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
    76.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
    77.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
    78.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
    79.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
    80.     v_CardType.card_brand_,v_CardType.card_brand_name_,'2.激活量(套数)', jhl_, createDate);   
    81.        
    82.     --成功积分登记量   
    83.     jfl_ := bean.jfl;   
    84.     v_jf_total_ := v_jf_total_ + jfl_;   
    85.         
    86.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
    87.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
    88.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
    89.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
    90.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
    91.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
    92.     v_CardType.card_brand_,v_CardType.card_brand_name_,'3.成功积分登记量(套数)', jfl_, createDate);   
    93.        
    94.     --激活率   
    95.     jhRate := 0;   
    96.     if pkl_ > 0 then  
    97.      jhRate := (1.0 * jhl_) / pkl_;   
    98.     else  
    99.      if jhl_ > 0 then  
    100.       jhRate := 1.0;   
    101.      end if;   
    102.     end if;   
    103.         
    104.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
    105.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
    106.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
    107.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
    108.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
    109.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
    110.     v_CardType.card_brand_,v_CardType.card_brand_name_,'4.激活率(%)', jhRate, createDate);   
    111.         
    112.     --积分率 = 已积分数量 / (已激活数量-赠送号激活量)    
    113.     zsl_ := bean.zsl;   
    114.     v_zs_total_ := v_zs_total_ + zsl_;   
    115.         
    116.     jfRate := 0;   
    117.     if (jhl_ - zsl_) > 0 then  
    118.      jfRate := (1.0 * jfl_) / (jhl_ - zsl_);   
    119.     else  
    120.      if jfl_ > 0 then  
    121.       jfRate := 1.0;   
    122.         end if;   
    123.     end if;   
    124.         
    125.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,   
    126.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,   
    127.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)    
    128.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,   
    129.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,   
    130.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),   
    131.     v_CardType.card_brand_,v_CardType.card_brand_name_,'5.积分率(%)', jfRate, createDate);   
    132.         
    133.             end loop;   
    134.             close c2;   
    135.        
    136.    --去掉值为0的代办点   
    137.    if v_pk_total_<=0 and v_jh_total_<=0 and v_jf_total_<=0 then  
    138.        delete from R_PIKA_STAT_ where comm_id_=v_CommInfo.id_;   
    139.    else  
    140.        --批卡合计   
    141.     update R_PIKA_STAT_ set pk_total_=v_pk_total_ where comm_id_=v_CommInfo.id_;   
    142.     pk_sum := pk_sum + v_pk_total_;   
    143.         
    144.     --激活合计   
    145.     update R_PIKA_STAT_ set jh_total_=v_jh_total_ where comm_id_=v_CommInfo.id_;   
    146.     jh_sum := jh_sum + v_jh_total_;   
    147.        
    148.     --积分量合计   
    149.     update R_PIKA_STAT_ set jf_total_=v_jf_total_ where comm_id_=v_CommInfo.id_;   
    150.     jf_sum := jf_sum + v_jf_total_;   
    151.        
    152.     --激活率合计   
    153.     v_jhl_total_ := 0;   
    154.     if v_pk_total_ > 0 then  
    155.      v_jhl_total_ := (1.0 * v_jh_total_) / v_pk_total_;   
    156.     else  
    157.      if v_jh_total_ > 0 then  
    158.       v_jhl_total_ := 1.0;   
    159.         end if;   
    160.     end if;   
    161.     update R_PIKA_STAT_ set jhl_total_=v_jhl_total_ where comm_id_=v_CommInfo.id_;   
    162.        
    163.     --积分率合计   
    164.     v_jfl_total_ := 0;   
    165.     if (v_jh_total_ - v_zs_total_) > 0 then  
    166.      v_jfl_total_ := (1.0 * v_jf_total_) / (v_jh_total_ - v_zs_total_);   
    167.     else  
    168.      if v_jf_total_ > 0 then  
    169.       v_jfl_total_ := 1.0;   
    170.      end if;   
    171.     end if;   
    172.     update R_PIKA_STAT_ set jfl_toal_=v_jfl_total_ where comm_id_=v_CommInfo.id_;   
    173.     zs_sum := zs_sum + v_zs_total_;   
    174.        
    175.    end if;   
    176.       
    177.         end loop;   
    178.         close c1;   
    179.            
    180.   --合计的合计    
    181.   if pk_sum > 0 then  
    182.       jhl_sum := (1.0 * jh_sum) / pk_sum;   
    183.   else  
    184.       if jh_sum > 0 then  
    185.         jhl_sum := 1.0;   
    186.    end if;   
    187.   end if;   
    188.      
    189.   if (jh_sum - zs_sum) > 0 then  
    190.       jfl_sum := (1.0 * jf_sum) / (jh_sum - zs_sum);   
    191.   else  
    192.       if jf_sum > 0 then  
    193.        jfl_sum := 1.0;   
    194.    end if;   
    195.   end if;   
    196.      
    197.   result := lsh || ',' || pk_sum || ',' || jh_sum || ',' || jf_sum || ',' || jhl_sum || ',' || jfl_sum;   
    198.      
    199.   commit;   
    200.      
    201.   return result;   
    202.    end;   
    203.        
    204.     
    205. end;  
    [sql] view plaincopy

    1. CREATE OR REPLACE package body report as   
    2.     --生成数据集  
    3.  function genPiKaDataSet(p_fgs varchar2, p_fwt varchar2, p_comm varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return varchar2 is  
    4.         c1 cursorType;  
    5.         c2 cursorType;  
    6.         v_CommInfo CommInfo;  
    7.         v_CardType CardType;  
    8.   pkl_ number := 0; -- 值   
    9.   jhl_ number := 0;  
    10.   jfl_ number := 0;  
    11.   zsl_ number := 0;  
    12.   jhRate number := 0;  
    13.   jfRate number := 0;  
    14.   v_pk_total_ number := 0; -- 值合计   
    15.   v_jh_total_ number := 0;  
    16.   v_jf_total_ number := 0;  
    17.   v_zs_total_ number := 0;  
    18.   v_jhl_total_ number := 0;  
    19.   v_jfl_total_ number := 0;  
    20.   pk_sum number := 0; -- 值合计的合计    
    21.   jh_sum number := 0;  
    22.   jf_sum number := 0;  
    23.   zs_sum number := 0;  
    24.   jhl_sum number := 0;  
    25.   jfl_sum number := 0;  
    26.     
    27.   bean QuantityBean;  
    28.   result varchar2(200);  
    29.   lsh number := 0;  
    30.   createDate date := sysdate;  
    31.     begin  
    32.      delete from R_PIKA_STAT_ where create_date_<createDate-1;  
    33.      commit;  
    34.      
    35.   select SEQ_LSH.nextval into lsh from dual;  
    36.     
    37.         --代办点   
    38.         open c1 for reportFunc.getCommSQL(p_fgs, p_fwt, p_comm);  
    39.         loop  
    40.    v_pk_total_ := 0;  
    41.    v_jh_total_ := 0;  
    42.    v_jf_total_ := 0;  
    43.    v_zs_total_ := 0;  
    44.    v_jhl_total_ := 0;  
    45.    v_jfl_total_ := 0;  
    46.       
    47.             fetch c1 into v_CommInfo;  
    48.             exit when c1%notfound;  
    49.      
    50.             --卡类型   
    51.             open c2 for reportFunc.getCardTypeSQL;  
    52.             loop  
    53.                 fetch c2 into v_CardType;  
    54.                 exit when c2%notfound;  
    55.       
    56.     bean := reportFunc.getQuantityBean(v_CommInfo.id_, v_CardType.id_, p_date1, p_date2, p_status);  
    57.       
    58.     --批卡量  
    59.     pkl_ := bean.pkl;  
    60.     v_pk_total_ := v_pk_total_ + pkl_;  
    61.        
    62.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
    63.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
    64.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
    65.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
    66.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
    67.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
    68.     v_CardType.card_brand_,v_CardType.card_brand_name_,'1.批卡量(套数)', pkl_, createDate);  
    69.       
    70.     --激活量  
    71.     jhl_ := bean.jhl;  
    72.     v_jh_total_ := v_jh_total_ + jhl_;  
    73.        
    74.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
    75.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
    76.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
    77.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
    78.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
    79.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
    80.     v_CardType.card_brand_,v_CardType.card_brand_name_,'2.激活量(套数)', jhl_, createDate);  
    81.       
    82.     --成功积分登记量  
    83.     jfl_ := bean.jfl;  
    84.     v_jf_total_ := v_jf_total_ + jfl_;  
    85.        
    86.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
    87.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
    88.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
    89.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
    90.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
    91.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
    92.     v_CardType.card_brand_,v_CardType.card_brand_name_,'3.成功积分登记量(套数)', jfl_, createDate);  
    93.       
    94.     --激活率  
    95.     jhRate := 0;  
    96.     if pkl_ > 0 then  
    97.      jhRate := (1.0 * jhl_) / pkl_;  
    98.     else  
    99.      if jhl_ > 0 then  
    100.       jhRate := 1.0;  
    101.      end if;  
    102.     end if;  
    103.        
    104.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
    105.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
    106.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
    107.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
    108.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
    109.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
    110.     v_CardType.card_brand_,v_CardType.card_brand_name_,'4.激活率(%)', jhRate, createDate);  
    111.        
    112.     --积分率 = 已积分数量 / (已激活数量-赠送号激活量)   
    113.     zsl_ := bean.zsl;  
    114.     v_zs_total_ := v_zs_total_ + zsl_;  
    115.        
    116.     jfRate := 0;  
    117.     if (jhl_ - zsl_) > 0 then  
    118.      jfRate := (1.0 * jfl_) / (jhl_ - zsl_);  
    119.     else  
    120.      if jfl_ > 0 then  
    121.       jfRate := 1.0;  
    122.         end if;  
    123.     end if;  
    124.        
    125.     insert into R_PIKA_STAT_(id_,lsh_,comm_id_,comm_name_,category_name_,city_company_name_,district_name_,  
    126.     county_company_name_,contact_,mobile_phone_,person_id_,bank_num_,card_type_id_,type_,  
    127.     card_brand_,card_brand_name_,data_type_,amount_,create_date_)   
    128.     values(SEQ_PIKA_STAT.nextval,lsh,v_CommInfo.id_,v_CommInfo.name_,v_CommInfo.comm_category_name_,  
    129.     v_CommInfo.city_company_name_,v_CommInfo.district_name_,v_CommInfo.county_company_name_,v_CommInfo.contact_,  
    130.     v_CommInfo.mobile_phone_,v_CommInfo.person_id_,v_CommInfo.bank_num_,v_CardType.id_,replace(v_CardType.type_,v_CardType.card_brand_name_,''),  
    131.     v_CardType.card_brand_,v_CardType.card_brand_name_,'5.积分率(%)', jfRate, createDate);  
    132.        
    133.             end loop;  
    134.             close c2;  
    135.       
    136.    --去掉值为0的代办点  
    137.    if v_pk_total_<=0 and v_jh_total_<=0 and v_jf_total_<=0 then  
    138.        delete from R_PIKA_STAT_ where comm_id_=v_CommInfo.id_;  
    139.    else  
    140.        --批卡合计  
    141.     update R_PIKA_STAT_ set pk_total_=v_pk_total_ where comm_id_=v_CommInfo.id_;  
    142.     pk_sum := pk_sum + v_pk_total_;  
    143.        
    144.     --激活合计  
    145.     update R_PIKA_STAT_ set jh_total_=v_jh_total_ where comm_id_=v_CommInfo.id_;  
    146.     jh_sum := jh_sum + v_jh_total_;  
    147.       
    148.     --积分量合计  
    149.     update R_PIKA_STAT_ set jf_total_=v_jf_total_ where comm_id_=v_CommInfo.id_;  
    150.     jf_sum := jf_sum + v_jf_total_;  
    151.       
    152.     --激活率合计  
    153.     v_jhl_total_ := 0;  
    154.     if v_pk_total_ > 0 then  
    155.      v_jhl_total_ := (1.0 * v_jh_total_) / v_pk_total_;  
    156.     else  
    157.      if v_jh_total_ > 0 then  
    158.       v_jhl_total_ := 1.0;  
    159.         end if;  
    160.     end if;  
    161.     update R_PIKA_STAT_ set jhl_total_=v_jhl_total_ where comm_id_=v_CommInfo.id_;  
    162.       
    163.     --积分率合计  
    164.     v_jfl_total_ := 0;  
    165.     if (v_jh_total_ - v_zs_total_) > 0 then  
    166.      v_jfl_total_ := (1.0 * v_jf_total_) / (v_jh_total_ - v_zs_total_);  
    167.     else  
    168.      if v_jf_total_ > 0 then  
    169.       v_jfl_total_ := 1.0;  
    170.      end if;  
    171.     end if;  
    172.     update R_PIKA_STAT_ set jfl_toal_=v_jfl_total_ where comm_id_=v_CommInfo.id_;  
    173.     zs_sum := zs_sum + v_zs_total_;  
    174.       
    175.    end if;  
    176.      
    177.         end loop;  
    178.         close c1;  
    179.           
    180.   --合计的合计   
    181.   if pk_sum > 0 then  
    182.       jhl_sum := (1.0 * jh_sum) / pk_sum;  
    183.   else  
    184.       if jh_sum > 0 then  
    185.         jhl_sum := 1.0;  
    186.    end if;  
    187.   end if;  
    188.     
    189.   if (jh_sum - zs_sum) > 0 then  
    190.       jfl_sum := (1.0 * jf_sum) / (jh_sum - zs_sum);  
    191.   else  
    192.       if jf_sum > 0 then  
    193.        jfl_sum := 1.0;  
    194.    end if;  
    195.   end if;  
    196.     
    197.   result := lsh || ',' || pk_sum || ',' || jh_sum || ',' || jf_sum || ',' || jhl_sum || ',' || jfl_sum;  
    198.     
    199.   commit;  
    200.     
    201.   return result;  
    202.    end;  
    203.       
    204.    
    205. end;  

        2、reportFunc包体

    Sql代码 

    1. CREATE OR REPLACE package body reportFunc as    
    2.     --返回获取代办点信息的SQL    
    3.  function getCommSQL(p_fgs varchar2, p_fwt varchar2, p_comm varchar2) return varchar2 is  
    4.      sql_1 varchar2(2000);   
    5.  begin  
    6.      sql_1 := 'select a.id_,a.name_,e.name_ comm_category_name_,b.name_ city_company_name_,d.name_ district_name_,c.name_ county_company_name_,';   
    7.   sql_1 := sql_1 || 'case when (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.contact_ else (select name_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end contact_,';   
    8.   sql_1 := sql_1 || 'case when (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.mobile_phone_ else (select tel_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end mobile_phone_,';   
    9.   sql_1 := sql_1 || 'case when (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.person_id_ else (select person_id_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end person_id_,';   
    10.   sql_1 := sql_1 || 'case when (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') is null then a.bank_num_ else (select bank_num_ from commission_user_ where rownum=1 and commission_id_=a.id_ and user_type_=1 and user_status_=''COMMISSIONUSER_STATUS_01'') end bank_num_';   
    11.   sql_1 := sql_1 || ' from commission_info_ a';   
    12.   sql_1 := sql_1 || ' left join organization_ b on a.city_company_id_=b.party_id_';   
    13.   sql_1 := sql_1 || ' left join organization_ c on a.county_company_id_=c.party_id_';   
    14.   sql_1 := sql_1 || ' left join district_ d on a.district_=d.id_';   
    15.   sql_1 := sql_1 || ' left join commission_category_ e on a.comm_cagegory_=e.id_';   
    16.   sql_1 := sql_1 || ' where a.id_ is not null';   
    17.   if p_fgs is not null then  
    18.      sql_1 := sql_1 || ' and a.city_company_id_=''' || p_fgs || '''';   
    19.   end if;   
    20.   if p_fwt is not null then  
    21.      sql_1 := sql_1 || ' and a.county_company_id_=''' || p_fwt || '''';   
    22.   end if;   
    23.   if p_comm is not null then  
    24.      sql_1 := sql_1 || ' and a.id_ in(' || p_comm || ')';   
    25.   end if;   
    26.   sql_1 := sql_1 || ' order by a.id_ asc';   
    27.      
    28.   return sql_1;   
    29.  end;   
    30.     
    31.  --返回获取卡类型信息的SQL    
    32.  function getCardTypeSQL return varchar2 is  
    33.      sql_2 varchar2(1000);   
    34.  begin  
    35.      sql_2 := 'select a.id_,a.type_,a.card_brand_,b.description_ card_brand_name_';   
    36.   sql_2 := sql_2 || ' from sim_card_type_ a inner join enumeration_ b';   
    37.   sql_2 := sql_2 || ' on a.card_brand_=b.enum_id_ order by a.id_ asc';   
    38.   return sql_2;   
    39.  end;   
    40.     
    41.  -- 数量Bean    
    42.  function getQuantityBean(p_comm varchar2, p_type varchar2, p_date1 varchar2, p_date2 varchar2, p_status varchar2) return report.QuantityBean is  
    43.      s varchar2(2000);   
    44.   bean report.QuantityBean;   
    45.      c1 report.cursorType;   
    46.  begin  
    47.      s := 'select a.id_,';   
    48.      
    49.   s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and status_ in(' || p_status || ') and card_type_id_=' || p_type;   
    50.   if p_date1 is not null then  
    51.        s := s || ' and status_date_>=' || p_date1;   
    52.   end if;   
    53.   if p_date2 is not null then  
    54.        s := s || ' and status_date_<' || p_date2;   
    55.   end if;   
    56.   s := s || ') pkl,';   
    57.      
    58.   s := s || '(select count(id_) from sim_card_detail_ where commission_id_=a.id_ and card_type_id_=' || p_type;   
    59.   if p_date1 is not null then  
    60.        s := s || ' and active_date_>=' || p_date1;   
    61.   end if;   
    62.   if p_date2 is not null then  
    63.        s := s || ' and active_date_<' || p_date2;   
    64.   end if;   
    65.   s := s || ') jhl,';   
    66.      
    67.   s := s || '(select count(id_) from sim_card_detail_ where is_integral_=1 and commission_id_=a.id_ and card_type_id_=' || p_type;   
    68.   if p_date1 is not null then  
    69.        s := s || ' and active_date_>=' || p_date1;   
    70.   end if;   
    71.   if p_date2 is not null then  
    72.        s := s || ' and active_date_<' || p_date2;   
    73.   end if;   
    74.   s := s || ') jfl,';   
    75.      
    76.   s := s || '(select count(id_) from sim_card_detail_ where status_=''RESOURCE_STATUS_13'' and commission_id_=a.id_ and card_type_id_=' || p_type;   
    77.   if p_date1 is not null then  
    78.        s := s || ' and active_date_>=' || p_date1;   
    79.   end if;   
    80.   if p_date2 is not null then  
    81.        s := s || ' and active_date_<' || p_date2;   
    82.   end if;   
    83.   s := s || ') zsl';   
    84.      
    85.      
    86.   s := s || ' from commission_info_ a where a.id_=' || p_comm;   
    87.      
    88.   open c1 for s;   
    89.   loop   
    90.       fetch c1 into bean;   
    91.    exit when c1%notfound;   
    92.   end loop;   
    93.   close c1;   
    94.      
    95.      return bean;   
    96.  end;   
    97.     
    98. end;