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