使用Shell脚本操作oracle数据库并新建用户

#!/bin/bash
PDB=
VUSERNAME=HWBDI
SQL11G="select username,profile from dba_users where username=$VUSERNAME;"
SQL12C="select username,profile from dba_users where username='$VUSERNAME';"
SQLCREATEUSER="create user hwbdi identified by hwbdi_123U;
create profile PROFILE_HWBDI limit
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 20
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME DEFAULT
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS DEFAULT
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT
INACTIVE_ACCOUNT_TIME DEFAULT;
alter user HWBDI profile PROFILE_HWBDI;"
SQLGRANT="grant connect to HWBDI;
grant select on SYS.DBA_OBJECTS to HWBDI;
grant select on SYS.DBA_EXTENTS to HWBDI;
grant select on SYS.GV_\$DATABASE to HWBDI;
grant select on SYS.GV_\$INSTANCE to HWBDI;
"
function execSql(){
local sql=$1
local rt=`sqlplus -s / as sysdba<<EOF
set lines 1000 heading off feedback off pagesize 0 verify off echo off;
$sql
EOF`
echo $rt
}

function data_format(){

local _originalData=$1
local _splitSign=$2
if [[ -z "$_splitSign" ]];then
echo "${_originalData}"|awk -v rowsign=$ROW_SPLIT_SIGN '{printf "%s%c",$0,rowsign}'
else
echo "${_originalData}"|awk -v colsign=$COL_SPLIT_SIGN -v rowsign=$ROW_SPLIT_SIGN '{gsub(/[[:blank:]]*'$_splitSign'[[:blank:]]*/,colsign)};{printf "%s%c",$0,rowsign}'
fi

}

function oracle_sql_excute(){
local SQL=$1
local pdb1=$2
if [[ -n "$pdb1" ]];then
execpdb="alter session set container="${pdb1}";"
fi
local oracleGet=`sqlplus -s / as sysdba<<EOF
$execpdb
set feedback off
set lines 1000 heading off feedback off pagesize 0 verify off echo off;
$SQL
EOF`
echo $oracleGet
}

version=`sqlplus -v |awk '{print $3}'|cut -c 1-2`
if [ $version -le 11 ]; then
echo "输出查询sql"${SQL11G}
SQL=$SQL11G
sqlDataGet=`execSql "${SQL}" "off"`
echo "输出11g查询结果："$sqlDataGet
if [[ -z $sqlDataGet ]];then
echo "输出11g：用户不存在需要新建用户"
echo "输出执行sql"${SQLCREATEUSER}
SQLCRE=$SQLCREATEUSER
sqlDataGet1=`execSql "${SQLCRE}" "off"`
sqlDataGet6=`execSql "${SQL}" "off"`
if [[ -n $sqlDataGet6 ]];then
echo"新用户已创建！"
echo "已存在用户,只需要执行grant"
SQLGRA=$SQLGRANT
echo "输出执行sql"${SQLGRANT}
sqlDataGet2=`execSql "${SQLGRANT}" "off"`
echo "用户已赋权"
else
echo"用户创建失败！"
fi
else
echo "已存在用户,只需要执行grant"
SQLGRA=$SQLGRANT
echo "输出执行sql"${SQLGRANT}
sqlDataGet2=`execSql "${SQLGRANT}" "off"`
echo "用户已赋权"
fi
else
SQL2=$SQL12C
echo "输出查询sql:"$SQL2
sqlDataGet3=`oracle_sql_excute "${SQL2}" "$PDB"`
echo "执行结果："$sqlDataGet3
exehw=$(echo $sqlDataGet3|grep "HWBDI")
if [[ -z $exehw ]];then
echo "输出12c：用户不存在需要新建用户"
echo "输出执行sql:"${SQLCREATEUSER}
SQLCRE=$SQLCREATEUSER
sqlDataGet4=`oracle_sql_excute "${SQLCRE}" "$PDB"`
sqlDataGet7=`oracle_sql_excute "${SQL2}" "$PDB"`
echo "执行结果："$sqlDataGet7
exehw1=$(echo $sqlDataGet7|grep "HWBDI")
if [[ -n $exehw1 ]];then
SQLGRA=$SQLGRANT
echo "输出执行sql:"${SQLGRANT}
sqlDataGet5=`oracle_sql_excute "${SQLGRANT}" "$PDB"`
echo "用户已赋权"
else
echo "用户创建失败！"
fi
else
echo "已存在用户,只需要执行grant"
SQLGRA=$SQLGRANT
echo "输出执行sql:"${SQLGRANT}
sqlDataGet5=`oracle_sql_excute "${SQLGRANT}" "$PDB"`
echo "用户已赋权"
fi
fi