一. 有输入参数和输出参数的
#!/bin/bash
source /etc/profile
source ~/.bash_profile
##################################################################
#功能描述：调用带有输入参数和输出参数的存储过程
#执行周期：日
# CRONTAB: * 1 * * * * ${prog_path}CALL_PRODEDURE.sh >> ${prog_path}CALL_PRODEDURE.log 2&1
#作 者：洪杰
#创建时间：2016/07/13
#修改记录：
##################################################################
#programing path
prog_path=/utxt/softsmb/scripts/
#database tnsname
db_srv="smb/czty_smb@zsms"
#exporting
export_path=/utxt/softsmb/scripts/
START_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "start time:${START_DATE}"
sqlplus -s ${db_srv} <<EOF
declare
v_acct_date varchar2(8);--输入参数
TOM_SQLCODE varchar2(100);--输出参数
ERR1 VARCHAR2(100);--输出参数
begin
ERR1 := '';
TOM_SQLCODE := '';
select to_char(sysdate-1, 'yyyymmdd') into V_ACCT_DATE from dual;
PRO_SERV_DELAY_REPORT_DAY(V_ACCT_DATE, ERR1, TOM_SQLCODE);
end;
/
exit
EOF
END_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "end time:${END_DATE}"二. 只有输出参数的
#!/bin/bash
source /etc/profile
source ~/.bash_profile
#################################
#
# * 1 * * * * /utxt/softsmb/scripts/call_PRO_SERV_DELAY_REPORT_DAY.sh >> /utxt/softsmb/scripts/call_PRO_SERV_DELAY_REPORT_DAY.log 2&1
#
#################################
#programing path
prog_path=/utxt/softddcx/scripts/
#database tnsname
db_srv="ddjk/czty_ddjk@ddcx"
#exporting
export_path=/utxt/softddcx/scripts
START_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "start time:${START_DATE}"
sqlplus -s ${db_srv} >> ${export_path}/logs/call_no_para.log <<EOF
declare
V_RECE_CODE varchar2(100);--输出参数
V_RECE_MSG VARCHAR2(100);--输出参数
begin
V_RECE_CODE := '';
V_RECE_MSG := '';
PRO_ADD_TAB_PARTITIONS(V_RECE_CODE, V_RECE_MSG);
ddcx.PRO_ADD_TAB_PARTITIONS(V_RECE_CODE, V_RECE_MSG);
bhnl.PRO_ADD_TAB_PARTITIONS(V_RECE_CODE, V_RECE_MSG);
end;
/
exit
EOF
END_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "end time:${END_DATE}"
