存储过程代码样例

1、更详细的要求（如命名、缩进、换行等）需参考编程规范，本文档只是从整体上进行演示create or replace procedure sp_kr_ns_hn_mtv_dtal要求编译是完全成功的，不得留有警告或者错误( iv_date in varchar2, oi_return out integer) /* head * name masakr#sp_kr_ns_hn_mtv_dtal * caption 河南手机电视专区日累计报表 * type 日报表 * parameter iv_date in varchar2必须与实际定义完全一致 统计日期，格式：yyyymmdd * parame

2、ter oi_return out integer 执行状态码，0 正常，其它 出错 * description 需求：267536，作用：河南手机电视专区日累计报表和日累计智能查询，运行时间：120秒必须按样例格式涵盖这3方面 * target masakr#tb_kr_ns_hn_mtv_dtal 河南手机电视专区日累计报表 * target masakr#tb_kr_ns_hn_mtv_list_dtal 河南手机电视专区日累计智能查询 * source masadw#tb_dw_su_stm_list_cur 手机电视清单表 * source masamk#tb_mk_sc_user_

3、dtal 用户日累计事实表* middle masakr#tb_kr_ns_hn_mtv_list_dt_tmp* middle masakr#tb_kr_ns_hn_mtv_list_dt_tmp1 * version 2.0 * author 张三 * create-date 2009-08-24 * todo * version * mender * modify_date * modify_desc * copyright *厂家 河南移动 */ is /* description 变量定义 */每个大程序块前用这个固定注释格式/* description 具体说明 */描述代码作用

4、vi_task_id integer; - 任务日志id vv_task_name varchar2(30); - 任务名 vv_table_name varchar2(30); - 表名 vv_task_sign varchar2(30); - 任务标识 vv_task_pos varchar2(50); - 任务位置 vi_err_code integer; - 出错代码 vv_err_msg varchar2(200); - 出错信息 vi_result integer; - 临时结果 exc_return exception; - 程序中间返回自定义异常 exc_error excep

5、tion; - 程序出错返回自定义异常 v_owner varchar2(30); - 属主 v_proc_name varchar2(30); - 通知报表刷新的过程名 version_date varchar2(8); -报表刷新的版本日期 v_tz_flag integer; -通知成功标志（0成功） vd_date date; -统计日期 vd_first_date date; 注意删除不需要的变量定义，否则会有提示性告警 -统计日月初第一天begin /* description 允许并行处理 */ execute immediate alter session enable par

6、allel dml; /* * description 变量初始化 */ vv_task_name := sp_kr_ns_hn_mtv_dtal; vv_table_name := tb_kr_ns_hn_mtv_dtal; vd_date := to_date(iv_date,yyyymmdd); vd_first_date := trunc(vd_date,mm); v_owner := masakr; v_proc_name := sp_kr_ns_hn_mtv_dtal; version_date := iv_date; vv_task_sign := iv_date; /* * d

7、escription 程序开始日志 */ vv_task_pos := 程序开始日志; masasys.sp_kr_sys_log(vi_task_id, vv_task_name, vv_table_name, vv_task_sign, 1, null, null, null); /* * description 参数以及数据源检查 */ vv_task_pos := 检查输入参数; if (iv_date is null) then vi_err_code := -1; vv_err_msg := 没有输入统计日期参数; raise exc_error; end if; vv_task_

8、pos := 判断源数据源是否存在; select count(*) into vi_result from masadw.tb_dw_su_stm_list_cur a where a.deal_date = vd_date and rownum = 1; if vi_result = 0 then vi_err_code := -40000; vv_err_msg := 数据源masadw.tb_dw_su_stm_list_cur没有统计日数据; raise exc_error; end if; select count(*) into vi_result from masamk.tb_

9、mk_sc_user_dtal a where a.statis_date = trunc(sysdate-1) and rownum = 1; if vi_result = 0 then vi_err_code := -40001; vv_err_msg := 数据源masamk.tb_mk_sc_user_dtal没有统计日数据; raise exc_error; end if; /* * description 报表处理开始 */ vv_task_pos := 删除旧数据; delete from tb_kr_ns_hn_mtv_dtal where statis_date = vd_d

10、ate; delete /*+parallel(a,4)*/ from tb_kr_ns_hn_mtv_list_dtal a where statis_date = vd_date or statis_date = vd_date - 3;数据必须有周期控制，分区表要考虑DROP历史分区；报表类要删除过早的历史数据。commit; vv_task_pos := 清除临时表数据;如无特殊要求，应将清除所有中间表的操作集中在一起，便于维护execute immediate truncate table tb_kr_ns_hn_mtv_list_dt_tmp;execute immediate t

11、runcate table tb_kr_ns_hn_mtv_list_dt_tmp1; vv_task_pos := 手机视频使用客户日累计数据; insert into tb_kr_ns_hn_mtv_list_dt_tmp必须明确使用列表，不得出现insert into tb_kr_ns_hn_mtv_list_dt_tmpselect * from XXX的情况 ( serv_number , -号码 is_mf_cust , -是否免费业务用户 mf_use_count , -免费业务使用次数 is_ac_cust , -是否按次业务用户 ac_use_count , -按次业务使用次

12、数 is_by_cust , -是否包月用户 by_use_count , -包月业务使用次数 total_sr -按次收入 ) select /*+parallel(a,8)*/ a.serv_number, -号码 case when a.operation_code = 20810000 then 1 else 0 end, -是否免费业务用户 case when a.operation_code = 20810000 and a.play_duration 0 then 1 else 0 end, -免费业务使用次数 case when a.operation_code = 20820

13、000 then 1 else 0 end, -是否按次业务用户 case when a.operation_code = 20820000 and a.cdr_info_fee 0 then 1 else 0 end, -按次业务使用次数 case when a.operation_code = 10830000 then 1 else 0 end, -是否包月用户 case when a.operation_code = 10830000 and a.play_duration 0 then 1 else 0 end, -包月业务使用次数 case when a.operation_cod

14、e = 20820000 then a.cdr_info_fee else 0 end -按次收入 from masadw.tb_dw_su_stm_list_cur a用别名是个好习惯，即使只有一个表。当多表关联时，别名是必须的！ where a.operation_code in (10830000,20810000,20820000) and a.deal_date between vd_first_date and vd_date; commit; vv_task_pos := 汇总手机视频使用客户数日累计明细数据; insert into tb_kr_ns_hn_mtv_list_d

15、tal ( statis_date , -统计日期 serv_number , -号码 area_code , -地区编码 area_name , -归属地市公司 is_mf_cust , -是否免费业务用户 mf_use_count , -免费业务使用次数 is_ac_cust , -是否按次业务用户 ac_use_count , -按次业务使用次数 is_by_cust , -是否包月用户 by_use_count , -包月业务使用次数 total_sr -总收入 ) select /*+parallel(c,8) parallel(a,4)*/ vd_date ,-统计日期 a.ser

16、v_number , -号码 b.area_code ,-地区编码 max(trim(b.area_code_name) ,-归属地市公司 max(nvl(a.is_mf_cust,0) , -是否免费业务用户 sum(nvl(a.mf_use_count,0) , -免费业务使用次数 max(nvl(a.is_ac_cust,0) , -是否按次业务用户 sum(nvl(a.ac_use_count,0) , -按次业务使用次数 max(nvl(a.is_by_cust,0) , -是否包月用户 sum(nvl(a.by_use_count,0) , -包月业务使用次数 case when

17、max(nvl(a.is_by_cust,0) = 1 then sum(nvl(a.total_sr, 0)/100 + 10 else sum(nvl(a.total_sr, 0)/100 end - 总收入 from masamk.tb_mk_sc_user_dtal c, tb_kr_ns_hn_mtv_list_dt_tmp a, masadw.tb_dic_area_code b where trim(c.area_code) = trim(b.area_code) and a.serv_number = c.serv_number and c.user_status_id H a

18、nd c.statis_date = trunc(sysdate - 2) group by a.serv_number , -号码 b.area_code; commit; vv_task_pos := 汇总报表数据; insert into tb_kr_ns_hn_mtv_dtal ( statis_date , area_prov , -省权 area_desc , -地区名称 area_code , -区号 - mf_cust_count , -免费业务用户数 mf_use_count , -免费业务使用次数 ac_cust_count , -按次业务用户数 ac_use_count

19、, -按次业务使用次数 ac_sr , -按次业务收入 by_cust_count , -包月业务用户数 by_use_count , -包月业务使用次数 by_sr , -包月业务收入 total_cust_count , -总用户数 total_sr -总收入 ) select vd_date, -1, case when grouping(trim(a.area_code)=1 then 全省 else max(b.area_code_name) end , decode(grouping(trim(a.area_code), 1, Z, trim(a.area_code) area_c

20、ode, - count(distinct case when a.is_mf_cust = 1 then a.serv_number end), -免费业务用户数 sum(a.mf_use_count), -免费业务使用次数 count(distinct case when a.is_ac_cust = 1 then a.serv_number end), -按次业务用户数 sum(a.ac_use_count), -按次业务使用次数 sum(case when a.is_ac_cust = 1 and a.is_by_cust 1 then a.total_sr when a.is_ac_

21、cust = 1 and a.is_by_cust = 1 then a.total_sr - 10 else 0 end), -按次业务收入 count(distinct case when a.is_by_cust = 1 then a.serv_number end), -包月业务用户数 sum(a.by_use_count), -包月业务使用次数 count(distinct case when a.is_by_cust = 1 then a.serv_number end)*10, -包月业务收入 count(distinct a.serv_number), -总用户数 sum(a.

22、total_sr) from tb_kr_ns_hn_mtv_list_dtal a , masadw.tb_dic_area_code b where trim(a.area_code) = trim(b.area_code(+) and a.statis_date = vd_date group by rollup(trim(a.area_code); vv_task_pos := 分公司级补0，为报表刷新做准备;报表补零有很多算法，由作者自己决定，样例是一种常见的方法。 insert into tb_kr_ns_hn_mtv_dtal( statis_date , area_prov ,

23、 -省权 area_desc , -地区名称 area_code , -区号 mf_cust_count , -免费业务用户数 mf_use_count , -免费业务使用次数 ac_cust_count , -按次业务用户数 ac_use_count , -按次业务使用次数 ac_sr , -按次业务收入 by_cust_count , -包月业务用户数 by_use_count , -包月业务使用次数 by_sr , -包月业务收入 total_cust_count , -总用户数 total_sr -总收入 ) select vd_date, -1, aa.area_code_name,

24、 aa.area_code, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 from (select b.area_code, b.area_code_name from tb_kr_ns_hn_mtv_dtal a, masadw.tb_dic_area_code b where a.area_code(+) = b.area_code and a.area_code is null and a.statis_date(+) = vd_date ) aa; /* description 得到数据量 */vi_result := sql%rowcount;这个必须在COMMIT前执

25、行 commit; /* * description 报表处理结束 */ vv_task_pos := 清除临时表数据;execute immediate truncate table tb_kr_ns_hn_mtv_list_dt_tmp;execute immediate truncate table tb_kr_ns_hn_mtv_list_dt_tmp1; vv_task_pos := 通知刷新报表; v_tz_flag := masasys.fun_bi_rpt_reflash_log(v_owner,v_proc_name,version_date); 取消通知刷新函数执行失败向作

26、者发短信的处理方式，改为报错后写日志，但是不要对数据区进行回滚 if v_tz_flag0 then vi_err_code := -1; vv_err_msg := 通知刷新函数返回错误; raise exc_error;end if; /* * description 程序运行完成，记录程序结束日志，正常返回 */ vv_task_pos := 程序结束日志; masasys.sp_kr_sys_log(vi_task_id, null, null, null, 0, null, null, vi_result); oi_return := 0;exception when exc_ret

27、urn then /* description 程序中间返回，记录程序结束日志，正常返回 */ masasys.sp_kr_sys_log(vi_task_id, null, null, null, 0, null, vv_task_pos, vi_result); oi_return := 0; when exc_error then /* description 程序出错返回自定义异常，记录程序出错日志，出错返回 */ rollback; masasys.sp_kr_sys_log(vi_task_id, null, null, null, vi_err_code, vv_err_msg,

28、 vv_task_pos, vi_result); oi_return := vi_err_code; when others then /* description 程序出错，得到出错信息，回滚事务，记录程序出错日志，出错返回 */ vi_err_code := sqlcode; vv_err_msg := substr(sqlerrm, 1, 200); rollback; masasys.sp_kr_sys_log(vi_task_id, null, null, null, vi_err_code, vv_err_msg, vv_task_pos, vi_result); oi_retu

29、rn := vi_err_code;end ;/* description 以下是程序附加信息*/将以前在程序头部的几类附加信息统一调整到尾部，便于CTL+END一步定位。注意必须按：建表语句、前台位置、日志SQL的顺序书写/*建表语句 -51 kr create table tb_kr_ns_hn_mtv_dtal ( statis_date date, area_prov char(2), -省权 area_desc varchar2(64), -地区名称 area_code char(5), -区号 mf_cust_count integer, -免费业务用户数 mf_use_count integer, -免费业务使用次数 ac_cust_count integer, -按次业务用户数 ac_use_count integer, -按次业务使用次数 ac_sr number, -按次业务收入 by_cust_count integer, -包月业务用户数 by_use_count integer, -包月业务使用次数 by_sr number, -包月业务收入 total_cust_count integer, -总用户数 total_sr number -总收入 );comment on table masakr. tb