织梦怎么做中英文网站/网站整站优化公司
公司的在存储过程样例，主要是从O表抽取数据到DW表中，在抽取数据的过程中 记录了一些 日志消息 ，写入到一个日志表中。通过日志表可以查看存储过程运行情况。
样例参考学习：
CREATE OR REPLACE PROCEDURE IPMSDW.SP_DW_MY_TEST (v_day_start in date, v_day_num in int) is --v_day_start为开始时间，v_day_num为循环次数v_day_id_begin date;v_day_id_end date;i int;v_step_id varchar2(20);v_sql_clob clob;v_sql varchar2(32767);v_sql_insert varchar2(32767);v_sql_select varchar2(32767);v_log_id number;v_sp_name varchar2(50);V_SQLERRM varchar2(200);
begin--liuxiangke 2017.11.20v_log_id := TO_NUMBER(TO_CHAR(sysdate, 'yyyymmddhh24miss'));--系统当前时间为日志ID 例如'20170920003001'v_sp_name :='SP_DW_MY_TEST';v_step_id := '1';insert into IPMSDW.LOG_SP_DW_TEST(ID, SP_NAME, STATSTIME, sql, LOG) values(v_log_id,v_sp_name,sysdate,'begin',v_step_id ) ;--日志开始commit;i := 0;while i<v_day_num loop --循环开始，v_day_num 循环最大次数v_day_id_begin := trunc(v_day_start) + i; --begin开始天时间v_day_id_end := v_day_id_begin + 1; --end结束时间--把需要汇聚的 时间里 数据清除。v_sql :='delete from IPMSDm.dm_re_st_hywg_n31_14_d where start_time=to_date('''||TO_CHAR(v_day_id_begin, 'yyyy-mm-dd hh24:mi:ss')||''',''yyyy-mm-dd hh24:mi:ss'')';dbms_lob.createtemporary(v_sql_clob,TRUE);--清空变量 v_sql_clobdbms_lob.append(v_sql_clob,v_sql);--追加字符串，给v_sql_clob赋值v_step_id := 'delete_1';insert into IPMSDm.LOG_SP_Dm(ID, SP_NAME, STATSTIME, sql, LOG) values( v_log_id,v_sp_name,sysdate,v_sql_clob,v_step_id ) ;execute immediate v_sql_clob;commit;v_sql_insert := 'insert into ipmsdm.dm_re_st_hywg_n31_14_d
(start_time ,end_time ,hywgn_send_total_num ,hywgn_ec_to_gw_num ,hywgn_smc_to_gw_num ,hywgn_gw_to_gw_num ,gw_send_rpt_num_ok_ratio ,hywgn_send_rate ,hywgn_sm_peak ,hywgn_lisence
)';v_sql_select := '
/*select
to_date('''|| TO_CHAR(v_day_id_begin, 'yyyy-mm-dd hh24:mi:ss') ||''',''yyyy-mm-dd hh24:mi:ss'') as starttime,
to_date('''|| TO_CHAR(v_day_id_end, 'yyyy-mm-dd hh24:mi:ss') ||''',''yyyy-mm-dd hh24:mi:ss'') as endtime,
sum(ec_to_gw_num+smc_to_gw_num+gw_to_gw_num) ,
sum(ec_to_gw_num) ,
sum(smc_to_gw_num) ,
sum(gw_to_gw_num) ,
case when sum(ec_to_gw_num+smc_to_gw_num+gw_to_gw_num) <> 0 then round(sum(gw_send_rpt_num_ok)/sum(ec_to_gw_num+smc_to_gw_num+gw_to_gw_num),2) else 0 end ,round(sum(ec_to_gw_num+smc_to_gw_num+gw_to_gw_num)/300,2) ,
sum(sm_peak) ,
sum(lisence)
from ipmsdw.O_RE_ST_HYWG_N31_14_5M a
where starttime >= to_date('''|| TO_CHAR(v_day_id_begin, 'yyyy-mm-dd hh24:mi:ss') ||''',''yyyy-mm-dd hh24:mi:ss'')and starttime < to_date('''|| TO_CHAR(v_day_id_end, 'yyyy-mm-dd hh24:mi:ss') ||''',''yyyy-mm-dd hh24:mi:ss'')*/select
trunc(starttime,''dd'') as starttime,
trunc(starttime+1,''dd'') as endtime,
sum(ec_to_gw_num+smc_to_gw_num+gw_to_gw_num) ,
sum(ec_to_gw_num) ,
sum(smc_to_gw_num) ,
sum(gw_to_gw_num) ,
case when sum(ec_to_gw_num+smc_to_gw_num+gw_to_gw_num) <> 0 then round(sum(gw_send_rpt_num_ok)/sum(ec_to_gw_num+smc_to_gw_num+gw_to_gw_num),2) else 0 end ,round(sum(ec_to_gw_num+smc_to_gw_num+gw_to_gw_num)/300,2) ,
sum(sm_peak) ,
sum(lisence)
from ipmsdw.O_RE_ST_HYWG_N31_14_5M a
where starttime >= to_date('''|| TO_CHAR(v_day_id_begin, 'yyyy-mm-dd hh24:mi:ss') ||''',''yyyy-mm-dd hh24:mi:ss'')and starttime < to_date('''|| TO_CHAR(v_day_id_end, 'yyyy-mm-dd hh24:mi:ss') ||''',''yyyy-mm-dd hh24:mi:ss'')
group by trunc(starttime,''dd''),trunc(starttime+1,''dd'')';dbms_lob.createtemporary(v_sql_clob,TRUE); --清空v_sql_clobdbms_lob.append(v_sql_clob,v_sql_insert); --追加v_sql_insert给v_sql_clobdbms_lob.append(v_sql_clob,v_sql_select);--追加v_sql_select给v_sql_clobv_step_id := 'insert_1';insert into IPMSDm.LOG_SP_Dm(ID, SP_NAME, STATSTIME, sql, LOG) values( v_log_id,v_sp_name,sysdate,v_sql_clob,v_step_id ) ;commit;execute immediate v_sql_clob;commit;i := i + 1;end loop;
exception --异常情况when others thenV_SQLERRM := sqlerrm; --sqlerrm异常信息 ，只能赋值给变量获取异常信息。rollback;insert into IPMSDW.LOG_SP_DW_TEST(ID, SP_NAME, STATSTIME, sql, LOG) values( v_log_id,v_sp_name,sysdate,v_sql_clob,V_SQLERRM ) ;insert into IPMSDW.LOG_SP_DW_TEST(ID, SP_NAME, STATSTIME, sql, LOG) values( v_log_id,v_sp_name,sysdate,'end','5' ) ;commit;
end;