oracle 存储过程 示例

create or replace procedure sp_sosoad_common(p_day in number) is v_Program_Name Varchar2(30) := 'SOSOAD'; v_srctable Varchar2(30) := 'bu.t_dw_sosoad_qv' ; v_tgttable Varchar2(30) := 't_dm_sosoad_common' ; v_tableowner Varchar2(30) := 'U_OBU_SOSOAD' ; v_Partition_Name Varchar2(30) := 'P_' || p_day; v_Commit Number; v_Sql Varchar2(2000); v_Bool Number; v_Sperr exception; v_Errmsg varchar2(300); begin bic.Pkg_Global.Sp_Gb_Program_Start(v_Program_Name); dbms_application_info.set_client_info('t_dm_sosoad_common'); --开启session并发 v_Sql := 'alter session disable parallel dml'; Execute Immediate v_Sql; -- 判断源表数据是否已计算 select count(*) into v_Bool from bu.t_dw_sosoad_qv where statis_date = p_day and rownum <= 1; if v_Bool <> 1 then bic.Pkg_Global.Sp_Gb_Program_Error_Raise(v_Program_Name, v_tgttable, p_day || '依赖表bu.t_dw_sosoad_qv数据尚无'); raise v_Sperr; end if; -- 增加、清除分区 Select Count(1) Into v_Bool From All_Tab_Partitions Where Table_Owner = v_tableowner And Table_Name = v_tgttable And Partition_Name = v_Partition_Name; if v_Bool = 1 then v_sql := 'alter table ' || v_tgttable || ' truncate partition p_' || p_day; else v_sql := 'alter table ' || v_tgttable || ' add partition p_' || p_day || ' values (' || p_day || ') '; end if; execute immediate v_sql; -- 判断目的表中数据是否已存在； 存在则先删掉再入库； 否则直接入库 select count(*) into v_Bool from t_dm_sosoad_common where statis_date = p_day and rownum <= 1; if v_Bool = 1 then v_sql := 'delete from t_dm_sosoad_common where statis_date = ' || p_day; execute immediate v_sql; end if; -- 程序主体 insert /*+append */ into t_dm_sosoad_common ( statis_date , visitor_province , server_idc , server_id , ad_class , site_id , position , seq , mt , group_cid , cid , exhibit , click , charge , qv_ad , qv_all ) select src.statis_date , src.visitor_provin , src.server_idc , src.server_id , src.ad_class , src.site_id , src.position , src.sequence , src.target_type , src.group_cid , src.cid , sum(src.exhibit) , sum(src.click) , sum(src.charge) , sum(src.qv_ad ) , sum(src.qv_all) from bu.t_dw_sosoad_qv src where src.statis_date = p_day AND src.target_type in(0,1,2,4) AND src.bt in (0,1,2,4) AND src.st in (0,1,2,3) AND src.site_id in (201, 202) group by src.statis_date, src.visitor_provin, src.server_idc, src.server_id, src.ad_class, src.site_id, src.position, src.sequence, src.target_type, src.group_cid, src.cid ; v_Commit := Sql%Rowcount; Commit; --表分析，以提高查询 dbms_stats.gather_table_stats(v_tableowner, v_tgttable, 'P_' || p_day, degree => 32, estimate_percent => 0.0001, block_sample => true); bic.Pkg_Global.Sp_Gb_Program_Complete(v_Program_Name, 0, v_commit, v_tgttable ||'表分析完成'); bic.Pkg_Global.Sp_Gb_Program_Complete(v_Program_Name, 0, v_Commit, v_tgttable || '运行结束'); dbms_session.free_unused_user_memory; --异常处理 Exception When v_Sperr then bic.Pkg_Global.Sp_Gb_Program_Error_Raise(v_Program_Name, v_tgttable, p_day || '相关依赖表未入库'); When Others Then rollback; v_Errmsg := SUBSTR(SQLERRM, 1, 255); Bic.Pkg_Global.Sp_Gb_Program_Error_Raise(v_Program_Name, v_tgttable, v_Errmsg); dbms_session.free_unused_user_memory; end sp_sosoad_common;