sqlplus中调用shell_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