Linux script call SQL_function logmsg() {echo "date + "}-优快云博客

本文链接：https://blog.youkuaiyun.com/jason_deng/article/details/7674461

本文介绍了一个用于执行SQL文件的Shell脚本示例。该脚本通过传递连接字符串和SQL文件路径作为参数来调用Oracle SQL Plus客户端，执行SQL语句并记录执行日志。此外，还提供了一个简单的SQL测试脚本示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

callSQL.sh

#!/usr/bin/ksh
################################################################
# Modification History:
#
# Ver.            Date            By              Activity
# ----            -------         ------          --------
# 1.0             2012/06/18      TEST          Creation
#################################################################
SN=$(basename $0)

function logMsg {
echo "$(date +'%Y/%m/%d %H:%M:%S') - $2"
echo "$(date +'%Y/%m/%d %H:%M:%S') - $2" >> $1
}

if [ $# -lt 2 ]; then
echo "syntax: $SN <connection> <sql file>"
exit 9
fi

which sqlplus > /dev/null 2>&1
if [ $? -ne 0 ]; then
echo "sqlplus not found"
exit 1
fi

sqlFile=$2
logFile=$sqlFile.log

>$logFile
logMsg $logFile "$SN begins......"

sqlplus $1>>$logFile 2>&1 << EOF
        set echo on;
        set timing on;
        set serveroutput on;
        @$sqlFile;
EOF

#grep -v $SN $logFile

ret=$(grep -c "^ERROR" $logFile)

if [ $ret -ne 0 ]; then
logMsg $logFile "$SN Failed, Please check [$logFile]"
else
logMsg $logFile "$SN Finished, Please check [$logFile]"
fi
--------------------------------------------------------------------------------------------------------

test.sql

--------------------------------------------------------------------------------------------------------

-- Created on 6/18/2012 by

declare

-- Local variables here

i integer;

begin

-- Test statements here

update BATCH_TEST set name = 'JASON' where name = 'jason2';