shell与sqlplus交互

sqlplus 常用参数
-s: 不显示登录时的头信息,也不显示提示符,一般用在shell中调用sqlplus时
set heading off: 不显示列名
set feedback off: 不显示行数信息

最基本的shell中调用sqlplus格式
<< EOF ....... EOF: EOF是一对标识符,标识其内的文字传给左右的的命信,不一定必须是EOF
可以是任何两个对应的字符

#!/bin/bash
sqlplus /nolog << EOF
conn scott/tiger
select * from dept;
exit
EOF

[oracle@localhost test]$ ./plus.sh

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 7 12:13:04 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> Connected.
SQL>
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining option
[oracle@localhost test]$ echo $ORACHE_SID

//修改脚本,加上些参数再看输出结果
#!/bin/bash
sqlplus -s /nolog << EOF
set heading off
set feedback off
conn scott/tiger
select * from dept;
exit
EOF

//加上参数的结果已经没了登录提示信息、列名、与受影响的行数信息
[oracle@localhost test]$ ./plus.sh

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL查询结果赋值给shell变量:
以下把dept表的总行赋给了$value这个变量
#!/bin/bash
values=`
sqlplus -s /nolog << EOF
set heading off
set feedback off
conn scott/tiger
select count(*) from dept;
exit
EOF`
echo "$values has retrieve"

[oracle@localhost test]$ ./plus.sh

4 has retrieve

把shell的变量作为参数传给sqlplus
$0指文件名,$1为第一个参数,$n为第n个参数
$#指参数总数

[oracle@localhost test]$ cat plus.sh
#!/bin/bash
if [ $# -ne 1 ];then
echo 'parameter is missing'
exit 0
fi
values=`
sqlplus -s /nolog << EOF
set heading off
set feedback off
conn scott/tiger
select * from dept where DEPTNO=$1;
exit
EOF`
echo "dept data is: $values"

执行这个脚本并传入值,查询了对应的数据

[oracle@localhost test]$ ./plus.sh 10
dept data is:
10 ACCOUNTING NEW YORK