Oracle 存储过程样例

公司的在存储过程样例，主要是从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.20    v_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_clob        dbms_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 := '/*selectto_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 awhere 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'')*/  selecttrunc(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 awhere 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_clob        dbms_lob.append(v_sql_clob,v_sql_insert);  --追加v_sql_insert给v_sql_clob        dbms_lob.append(v_sql_clob,v_sql_select);--追加v_sql_select给v_sql_clob        v_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 then    V_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;