shell执行oracle存储过程，获得存储过程返回值【转】

一个小的etl调度，需要将存储过程执行情况进行返回并控制其后续依赖是否执行，本人只是将调用执行存储过程的shell脚本中存储过程输出参数返回，并没有写具体的控制程给大家，如果在这个思路上继续进行开发，那就是一个小的etl调度程序。

 

下面将各个脚本进行说明：（ex_produre.sh）

1.执行存储过程脚本

#!/bin/bash
user_name=$1
user_pass=$2
produre_name=$3
statis_sign=$4
sql_str=`
sqlplus -S $user_name/$user_pass as sysdba <<EOF
    set linesize 800;
    set long 2048576;
    set serveroutput on;
    var oi_return number;
    call  $user_name.$produre_name($statis_sign,:oi_return);
    select :oi_return from dual;
exit
EOF`
echo "$sql_str"|sed -e '4,/^$/!d;/^$/d'|
while read run_return
do
echo $run_return
done
2.调执行存储过程的shell脚本（ex_proc.sh）

#!/bin/bash
sh ex_produre.sh etl jiangtao  pdm_cust_act_behav_base 201003 |grep -v OI_RETURN |grep -v -| while read vi_result
do
#this date
echo $vi_result
if [ $vi_result -eq 0 ]
then 
echo "this produre is normal run "
else
echo "this produre is not normal run "
fi
done

3.相关存储过程及建表脚本（这个大家可以做为模板使用，这可是一家大公司的模版，哈哈）

a.存储过程（pdm_cust_act_behav_base ）

create or replace procedure pdm_cust_act_behav_base (is_month in varchar2, oi_return out number)
    /** HEAD
     * @name etl.pdm_cust_act_behav_base
     * @caption ？？？？？？？？？？
     * @type ？？？
     * @parameter is_month in varchar2 ？？？？？？？YYYYMM
     * @parameter oi_return out number ？？？？？？？？？0 ？？？-1 ？？
     * @description ？？？？？？？？？？
     * @target etl#tdm_cust_act_behav_base
     * @source hwmk#tmm_ci_user_basic_m
     * @middle
     * @version 1.0
     * @author
     * @create-date
     * @TODO ？
     * @version
     * @mender
     * @modify_date
     * @modify_desc
     * @copyright
     */

-- ********************************************************************************
-- ？？？？:    etl.pdm_cust_act_behav_base
-- ？？？？:    ？？？？？？？？？？
-- ？？？？:    is_month   - ？？？？
-- ？？？？:    oi_return - ？？？？？？？？？0 ？？？-1 ？？
-- ？？？？:    hwmk.tmm_ci_user_basic_m
-- ？？？？:    etl.tdm_cust_act_behav_base
-- ？？？？:
-- ？？？？:
-- ？？？？:
-- ？？？？:    v1.0
-- ？？？？:
-- ？？？？:
-- ？？？？:
-- ？？？？:
-- ？？？？:
-- ********************************************************************************

is

    vs_task_name            varchar2(30);       -- ？？？？
    vs_table_name           varchar2(30);       -- ？？？
    vs_message              varchar2(200);      -- ？？？？
    vi_task_id              integer;            -- ？？id
    vi_month                integer;            -- ？？？？
begin
    vs_task_name := 'pdm_cust_act_behav_base';
    vs_table_name := 'tdm_cust_act_behav_base';

    -- ？？？？？？
    etl.ps_log(vs_task_name, vs_table_name, is_month, 1, null, vi_task_id);

    -- ？？: ？？？？？？？？？？
    if (is_month is null) then
        vs_message := '？？？？？？？？？？';
        etl.ps_log(null, null, null, 3, vs_message, vi_task_id);
        oi_return := -1;
        return;
    end if;

    ------------------------------------------------------------
    -- ？？？？？？
    vi_month := to_number(is_month);

    ------------------------------------------------------------
    insert into etl.tdm_cust_act_behav_base
    (
       statis_month,
       serv_id
    )
    select
      vi_month,
      15204669284
    from dual
    ;
    commit;
    ------------------------------------------------------------
    -- ？？？？？？
    etl.ps_log(null, null, null, 2, null, vi_task_id);
    -- ？？？？
    oi_return := 0;
    return;

exception

    when others then
        -- ？？？？？？
        vs_message := substr(sqlerrm, 1, 200);
        -- ？？？？
        rollback;
        -- ？？？？？？
        etl.ps_log(null, null, null, 3, vs_message, vi_task_id);
        -- ？？？？
        oi_return := -1;
        return;

end;
/
b.存储过程（ps_log）

create or replace procedure ps_log
(
    is_task_name   in     varchar2,
    is_table_name  in     varchar2,
    is_task_sign   in     varchar2,
    ii_task_status in     integer,
    is_task_log    in     varchar2,
    oi_task_id     in out integer
)

-- ********************************************************************************
-- ？？？？:    etl.ps_log
-- ？？？？:    DW？？？？？？？？
-- ？？？？:    is_task_name   - ？？？？
--              is_table_name  - ？？？？
--              is_task_sign   - ？？？？, ？？？？？？？？？？？
--              ii_task_status - ？？？？, 1 ？？？？？, 2 ？？？？？？, 3 ？？？？？？
--              is_task_log    - ？？？？, ？？？？[？？？？？]？[？？？？？？],
--                                         ？？？？[？？？？]
--              oi_task_id     - ？？ID, ？？？2？3？？？？？？
--              ii_rowcount    - ？？？
-- ？？？？:    oi_task_id     - ？？ID, ？？？1？？？？？？
-- ？？？？:
-- ？？？？:    etl.ts_log
-- ？？？？:
-- ？？？？:    ？？？
-- ？？？？:    2010-02-01
-- ？？？？:    V1.0
-- ？？？？:
-- ？？？？:
-- ？？？？:
-- ？？？？:
-- ？？？？:    ？？？？
-- ********************************************************************************

is

    vs_err_msg      varchar2(255);      -- ？？？？？？

begin

    if ii_task_status = 1 then

        -- ？？？？？？？？？？？？
        select etl.seq_dw_log.nextval
          into oi_task_id
          from dual;

        insert into etl.ts_log
        (
            task_id,            -- ？？ID
            task_name,          -- ？？？？
            table_name,         -- ？？？？
            task_sign,          -- ？？？？
            start_time,         -- ？？？？？？
            end_time,           -- ？？？？？？？？？？？？？
            task_status,        -- ？？？？
            task_log            -- ？？？？
        )
        values
        (
            oi_task_id,
            is_task_name,
            is_table_name,
            is_task_sign,
            sysdate,
            null,
            '1',
            '？？？？？'
        );

    elsif ii_task_status = 2 then

        -- ？？？？？？？？？？？？ID？？？？？？？？
        update etl.ts_log
           set end_time = sysdate,
               task_status = '2',
               task_log = '？？？？？？'
         where task_id = oi_task_id;
    else

        -- ？？？？？？？？？？ID？？？？？？？？？？？？？
        update etl.ts_log
           set end_time = sysdate,
               task_status = '3',
               task_log = substr(is_task_log, 1, 200)
         where task_id = oi_task_id;
    end if;
    commit;

    -- ？？？？
    return;

exception

    -- ？？？？
    when others then
        -- ？？？？？？
        vs_err_msg := substr(sqlerrm, 1, 200);
        -- ？？？？
        rollback;
        -- ？？？？？？
        dbms_output.put_line('etl.ps_log: ' || vs_err_msg);
        -- ？？？？
        return;

end;
/

c.建表脚本：

-- Create sequence 
create sequence SEQ_DW_LOG
minvalue 1
maxvalue 999999999999999999999999999
start with 2731
increment by 1
cache 20;
create table TS_LOG
(
  TASK_ID     INTEGER,
  TASK_NAME   VARCHAR2(30),
  TABLE_NAME  VARCHAR2(30),
  TASK_SIGN   VARCHAR2(20),
  START_TIME  DATE,
  END_TIME    DATE,
  TASK_STATUS VARCHAR2(1),
  TASK_LOG    VARCHAR2(200),
  ROWCOUNT    NUMBER
);
-- Create table
create table TDM_CUST_ACT_BEHAV_BASE
(
  STATIS_MONTH NUMBER(10),
  SERV_ID      NUMBER(12)
);