Oracle 存储过程样例

最新推荐文章于 2022-10-09 16:58:43 发布

liuxiangke0210

最新推荐文章于 2022-10-09 16:58:43 发布

阅读量521

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/liuxiangke0210/article/details/78709425

版权

oracle 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

公司的在存储过程样例，主要是从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 := '
/*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_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;