[oracle@hb shell_test]$ cat echo_time 
#!/bin/sh

一.最簡單的調(diào)用sqlplus
sqlplus -S 'sys/unimas as sysdba' << !
select to_char(sysdate,'yyyy-mm-dd') today from dual;
exit;
!

[oracle@hb shell_test]$ ./echo_time 

TODAY
----------
2011-03-21

-S 是silent mode，不輸出類似“SQL>”，連接數(shù)據(jù)庫，關(guān)閉數(shù)據(jù)庫之類的信息。


eof可以是任何字符串 比如'laldf'那么當(dāng)你輸入單獨一行l(wèi)aldf時'shell認(rèn)為輸入結(jié)束，但是必須表示塊開始必須使用<<；
開始和結(jié)束要匹配這個符號“<<”后面的內(nèi)容
舉例子:

[oracle@hb shell_test]$ sqlplus -s 'sys/unimas as sysdba' << abc
> select to_char(sysdate,'yyyy-mm-dd') today from dual;
> exit;
> abc

TODAY
----------
2011-03-21

二.sqlplus的結(jié)果傳遞給shell的方法一

[oracle@hb shell_test]$ cat test2.sh 
#!/bin/bash
VALUE=`sqlplus -S 'test/unimas' << !
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
select to_char(sysdate,'yyyy-mm-dd') today from dual;
exit
!`
echo $VALUE
if [ -n '$VALUE' ]; then
echo 'The rows is $VALUE'
exit 0
else
echo 'There is no row'
fi

三.sqlplus的結(jié)果傳遞給shell的方法二

[oracle@hb shell_test]$ cat test1.sh 
#!/bin/bash
sqlplus -S 'test/unimas' << !
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
col coun new_value v_coun
select count(*) coun from lesson;
exit v_coun
!
VALUE='$?'
echo 'show row:$VALUE'

col coun new_value v_coun v_coun為number類型。因為exit 只能返回數(shù)值類型。

四.把shell參數(shù)傳遞給sqlplus

#!/bin/bash
t_id='$1'
sqlplus -S 'test/unimas' << !
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
select teachername from teacher where id=$t_id;
exit
!

五.sqlplus的結(jié)果存儲在文件中

#!/bin/sh
sqlplus -S 'test/unimas'<set heading off
set feedback off
set pagesize 0
set verify off
set echo off
spool spool_file
SELECT * from teacher;
spool off
exit;
EOF

http://blog./space.php?uid=9124312&do=blog&id=181372

####################################################################################################################################

查看調(diào)度系統(tǒng)狀態(tài)腳本：

#!/bin/sh

if [[ -z '$1' ]] || [[ '$1' -ne 0 && '$1' -ne 2 ]]       #使用[[ ]] 進(jìn)行邏輯短路操作
then
    echo 'Please input your parameter: query status[0,2]!'
    exit
fi

#for buname in cnlog enlog ItLog JrLog AuLog InnerLog
for buname in cnlog enlog
do
    sqlplus -S 'etl/etl@dw_testdb' << abc        #使用 << EOF方式輸入信息
    set line 155
    set pages 9999
    SELECT /*+ PARALLEL(a,4) */ * FROM $buname.hla_job_rec a where status = $1;
    exit
abc
done