DB2存储过程模板(存储过程举例、编译、运行)

一、DB2存储过程中日志表建表语句：

create table db2inst1.dim_proc_run_log  
(
   proc_name varchar(200) , 
   proc_para varchar(20) , 
   step_no integer , 
   step_desc varchar(100) , 
   step_over_time timestamp , 
   step_records integer 
)distribute by hash(proc_name,proc_para,step_no) in userspace1 not logged initially ; 
--注释
comment on table db2inst1.dim_proc_run_log is '存储过程运行日志表';
comment on column db2inst1.dim_proc_run_log.proc_name is '存储过程名称';
comment on column db2inst1.dim_proc_run_log.proc_para is '数据账期';
comment on column db2inst1.dim_proc_run_log.step_desc is '步骤说明';
comment on column db2inst1.dim_proc_run_log.step_no is '步骤';
comment on column db2inst1.dim_proc_run_log.step_over_time is '运行结束时间';
comment on column db2inst1.dim_proc_run_log.step_records is '更新记录数';

二、存储过程举例(SQL文件)：proc_test_proc.sql，脚本文件建议使用utf8编码

create or replace procedure db2inst1.proc_test_proc (in i_date varchar(20),out o_return_code integer, out o_return_msg varchar(300) )
p1: begin
--*****************************************************************************************
-- sql 存储过程
-- 名称     :  proc_test_proc
-- 频率     :  日
-- 参数     :  i_date:        统计日期
--             o_return_code:  返回值
--             o_return_msg:   返回信息
-- 功能描述 :  测试
-- 返回值   :  0 正确; <0 错误
-- 输入表   :  db2inst1.dual
-- 输出表   :  test_proc
-- 创建日期 :  19991231
-- 创建人   :  test
-- 修改历史 :  修改人     修改时间     主要改动说明
--             test       19991231    新增
--             test1      29991231    修改
--*****************************************************************************************
    --声明
    declare sqlcode integer default 0;
    declare retcode integer default 0;

    declare vs_bcyc_id           varchar(20);    --统计日期(yyyy-mm-dd)
    declare vi_bcyc_id           varchar(8);     --统计日期(yyyymmdd)



    declare vs_dynstr1           varchar(200);  --存放动态sql执行语句
    declare vs_proc_name         varchar(50);   --存放日志表存储过程名
    declare vi_proc_para         varchar(20);   --存放日志表存储过程参数
    declare vi_step_no           integer;       --存放日志表步骤序号
    declare vs_step_desc         varchar(100);  --存放日志表步骤描述

    declare vi_step_records      integer default 0; --更新记录数

    --声明异常
    declare continue handler for sqlexception set retcode = sqlcode;
    declare continue handler for sqlwarning set retcode = sqlcode;
    declare continue handler for not found set retcode = sqlcode;

    --赋值
    set o_return_code = 0;
    set o_return_msg = '存储过程未运行!';
    set vs_proc_name = 'db2inst1.proc_test_proc';
    set vi_proc_para = i_date;

    set vs_bcyc_id = i_date; --传入日期参数
    set vi_bcyc_id = to_char(date(vs_bcyc_id),'yyyymmdd');

    -----------------------------------------------------------------------------------------------
    --向日志表插入数据
    -----------------------------------------------------------------------------------------------
    set vi_step_no = 0;
    set vs_step_desc = '存储过程运行开始!';

    --log
    delete from db2inst1.dim_proc_run_log where (proc_name=vs_proc_name and proc_para=vi_proc_para) or proc_para is null;
    insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records) values (vs_proc_name,vi_proc_para,vi_step_no,vs_step_desc,current timestamp,0);
    commit;

    -----------------------------------------------------------------------------------------------
    --清空当前周期数据
    -----------------------------------------------------------------------------------------------
    set vi_step_no = 1;
    set vs_step_desc = '清空结果表当前周期数据';

    --[sql]
    delete from db2inst1.test_proc where dw_date=vi_bcyc_id;
    commit;
    --[sql]
    --判断sql返回值是否正常
    if retcode < 0 then
        set o_return_code = retcode;
        set o_return_msg = 'sql'||char(vi_step_no)||':'||vs_step_desc||'出错!';
        rollback;
    --err_log
     insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records)
     values (vs_proc_name,vi_proc_para,-1,o_return_msg||'sql_code:'||char(o_return_code),current timestamp,0);
    --err_log
        return o_return_code;
    else
        set retcode = 0;
        commit;


    end if;

    --log
    insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records) values (vs_proc_name,vi_proc_para,vi_step_no,vs_step_desc,current timestamp,0);
    commit;

    -----------------------------------------------------------------------------------------------
    --生成结果表
    -----------------------------------------------------------------------------------------------
    set vi_step_no = 2;
    set vs_step_desc = '生成结果表';
    --[sql]
    insert
    into
        db2inst1.test_proc
    select
        vi_bcyc_id as dw_date ,
        a.userid
    from
        db2inst1.dual a ;
    --[sql]
    --获取更新条数
    get diagnostics vi_step_records = row_count;

    if retcode < 0 then
        set o_return_code = retcode;
        set o_return_msg = 'sql'||char(vi_step_no)||':'||vs_step_desc||'出错!';
        rollback;
    --err_log
     insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records)
     values (vs_proc_name,vi_proc_para,-1,o_return_msg||'sql_code:'||char(o_return_code),current timestamp,0);
    --err_log
        return o_return_code;
    else
        set retcode = 0;
        commit;
    end if;

    --log
    insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records) values (vs_proc_name,vi_proc_para,vi_step_no,vs_step_desc,current timestamp,vi_step_records);
    commit;

    -----------------------------------------------------------------------------------------------
    --存储过程执行成功
    -----------------------------------------------------------------------------------------------
    set vi_step_no = 999;
    set vs_step_desc = '存储过程运行结束!';

    --log
    insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records) values (vs_proc_name,vi_proc_para,vi_step_no,vs_step_desc,current timestamp,0);
    commit ;

    set o_return_code = 0 ;
    set o_return_msg =  '存储过程'||vs_proc_name||'运行成功!';
end p1@

三、编译命令（proc_test_proc.sql为例）：

对文件赋权：

chmod 777 proc_test_proc.sql

编译：

db2 -td@ -vf proc_test_proc.sql

四、存储过程运行：

db2 "call db2inst1.proc_test_proc('$input',?,?)"  --$input为日期参数

运行成功结果举例：

  输出参数的值
  --------------------------
  参数名： O_RETURN_CODE
  参数值： 0

  参数名： O_RETURN_MSG
  参数值： 存储过程db2inst1.proc_test_proc运行成功!

  返回状态 = 0