linux下批量執行oracle腳本的shell腳本

#! /bin/bash -l
create_dbtable()
{
    set -x
    dbuser=$1
    dbpassword=$2
    sqlfile=$3
    conn=${dbuser}/${dbpassword};
    echo "create data of $dbuser..."
    if [ ${dbuser} = 'sys' ]
      then
        conn="${conn} as sysdba";
    fi
    sqlplus "${conn}" <<EOF
      whenever sqlerror exit 1;
         @$sqlfile
EOF
    if [  $? != 0 ]
    then
        echo "Error:execute ${sqlfile} error"
        exit 1
    fi
    echo "execute ${sqlfile} completelly!"
}

main()
{
        export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
        create_dbtable $*
        if [ $? -ne 0 ];then
            echo "Error:execute ${sqlfile} error"
            exit 1
        fi
        echo "execute ${sqlfile} successfully."
}
main $*
      

 将上述内容儲存在create_database.sh中

BEGIN EXECUTE IMMEDIATE 'DROP TABLE T_NOTIFICATION_INFO'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
/*==============================================================*/
/* TABLE: "T_NOTIFICATION_INFO"                                 */
/*==============================================================*/
CREATE TABLE T_NOTIFICATION_INFO  (
  "SUBSCRIPTIONID"     VARCHAR(36)                     NOT NULL,
  "MANAGERREFERENCE"   VARCHAR(2000),
  "TIMETICK"           INT,
  "NOTIFICATIONCATEGORIES" VARCHAR(4000),
  "FILTER"             VARCHAR(4000),
  "SUBSTATE"           INT,
  "ADDTIME"            DATE,
  CONSTRAINT PK_T_NOTIFICATION_INFO PRIMARY KEY ("SUBSCRIPTIONID")
);
           

 将上述内容儲存在createTable.sql中

1)root使用者登入後

将create_database.sh,createTable.sql兩個檔案上傳

2)修改create_database.sh檔案可執行權限

chmod 775 create_database.sh

3)退出，用oracle使用者登入

所在目錄下執行

./create_database.sh sys i2000db 'createTable.sql'

注：./create_database.sh 後面帶3個參數,根據實際情況調整

1 使用者名 sys

2 密碼   i2000db

3 sql檔案 createTable.sql