Linux/Unix shell sql 之間傳遞變數

靈活結合Linux/Unix Shell 與SQL 之間的變數傳輸，極大程度的提高了DBA的工作效率，本文針對Linux/Unix shell sql 之間傳遞變數給出幾個簡單的樣本以供參考。

Linux/Unix 下調用SQL,RAMN 請參考：Linux/Unix shell 指令碼中調用SQL,RMAN指令碼

一、樣本

1、shell變數接受sql傳回值之方式一
Oracle@SZDB:~> more ./retval.sh
#!/bin/bash
RETVAL=`sqlplus -silent scott/tiger <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT * FROM emp WHERE ename='SCOTT';
EXIT;
EOF`
if [ -z "$RETVAL" ]; then
  echo "No rows returned from database"
  exit 0
else
  echo $RETVAL
fi

oracle@SZDB:~> chmod u+x retval.sh
oracle@SZDB:~> ./retval.sh
7788 SCOTT ANALYST 7566 19-APR-87 34171.88 20

2、shell變數接受sql傳回值之方式二
oracle@SZDB:~> more ./retval_2.sh
#!/bin/bash
sqlplus -S "scott/tiger" <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
col cnt new_value v_cnt
SELECT count(*) cnt FROM emp WHERE deptno=10;
exit v_cnt
EOF
VALUE="$?"
echo "show rows for deptno 10:$VALUE"

3、將shell變數作為參數傳遞給sql指令碼
oracle@SZDB:~> more ./retval_3.sh
#!/bin/bash
v_empno="$1"
sqlplus -S "scott/tiger" <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT ename FROM emp WHERE empno=$v_empno;
exit
EOF
exit

oracle@SZDB:~> ./retval_3.sh 7788
SCOTT