shell获取sqlplus变量_sqlplus与shell互相传值的几种情况

爱被打了一巴掌 2022-12-31 08:27 238阅读 0赞

http://nbmonster.blog.51cto.com/1977736/665767 原文。

sqlplus与shell互相传值的几种情况

情况一：在shell中最简单的调用sqlplus

$cat test.sh

\#!/bin/sh

sqlplus oracle/oracle@oracle>file.log <

select \* from test;

exit

EOF\#注意EOF要顶格写

$sh test.sh

$cat file.log

\--省略若干系统提示信息-------

SQL>

EMPNOEMPNAMESALDEPTNO

\-----------------------------

10002Frank Naude50020

10001Scott Tiger100040

\--省略若干系统提示信息-------

将执行过程重定向入文件file.log，可通过cat file.log查看

情况二：直接将sqlplus的值赋值给shell变量

$cat test.sh

\#!/bin/sh

\#将sqlplus的结果输出给变量VALUE

\# set命令的使用可查询手册

\#注意shell中等号两边不能有空格

VALUE=\`sqlplus -S /nolog <

set heading off feedback off pagesize 0 verify off echo off

conn oracle/oracle@oracle

select count(\*) from test;

exit

EOF\`

\#输出记录数

echo "The number of rows is $VALUE."

$sh test.sh

The number of rows is2.

显示结果正确，表test共2条记录

情况三：间接将sqlplus的值赋值给shell变量

$cat test.sh

\#!/bin/sh

\#利用COL column NEW\_VALUE variable定义变量

\#sqlplus执行完后最后返回值为v\_coun

\#利用$?将最后返回值赋值给VALUE,也即为test的记录数

sqlplus -S /nolog <

set heading off feedback off pagesize 0 verify off echo off

conn oracle/oracle@oracle

col coun new\_value v\_coun

select count(\*) coun from test;

exit v\_coun

EOF

VALUE="$?"

echo "The number of rows is $VALUE."

$sh test.sh

The number of rows is 2.

脚本执行结果中第一个2为sqlplus返回值，第二个2为VALUE的值

情况四：将shell变量的值传给sqlplus使用

$cat test.sh

\#!/bin/sh

\#sqlplus引用shell变量TABLENAME的值

\#注意赋值时，等号两边不能有空格

TABLENAME="test"

sqlplus -S oracle/oracle@oracle <

select \* from $\{TABLENAME\};

exit

$sh test.sh

EMPNO EMPNAMESAL DEPTNO

\----- -------------------------------------------------- ---------- ------

10002 Frank Naude500 20

10001 Scott Tiger1000 40

脚本执行结果为：select \* from test；的结果

情况五：通过交互方式手工输入shell变量值

$cat test.sh

\#!/bin/sh

\#将手工输入变量值读入变量TABLENAME

echo"Enter the tablename you want to select:"

read TABLENAME

sqlplus -S oracle/oracle@oracle <

select \* from $\{TABLENAME\};

exit

$sh test.sh

\#按提示输入表名test

Enter the tablename you want to select:

test

EMPNO EMPNAMESAL DEPTNO

\----- -------------------------------------------------- ---------- ------

10002 Frank Naude500 20

10001 Scott Tiger1000 40

脚本执行结果为select \* from test的执行结果

\-------------------------------------------------------------------------------------------------------------

\---------------------------------------------------------------------------------------------------------------

但是发觉

sqlplus -S /nolog <

于是用另一种方法：vi sql\_2.sh

\#!/bin/bash

VALUE=\`sqlplus -S database\*\*\*\*/password\*\*@\\"//172.20.\*\*.\*\*:1521/sid\*\*\*\\"  <

set heading off feedback off pagesize 0 verify off echo off

select count(1)  from coltable;

commit

EOF\`

echo "The number of rows is" $VALUE

$ ./sql\_2.sh

The number of rows is 40741

\--------1.最简单直接sql--------------------------------------------------------

cat sql\_3.sh

\#!/bin/bash

sqlplus database\*\*\*\*/password\*\*@\\"//172.20.\*\*.\*\*:1521/sid\*\*\*\\">tmp\_log <

select \* from \*\*\*\*\*version;

exit

EOF

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*结果：

cat tmp\_log

SQL\*Plus: Release 10.2.0.4.0 - Production on Thu Jul 25 07:09:07 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL>

VERSIONSTRING                                              ID

\-------------------------------------------------- ----------

RELEASE\_2013.0.0\_\*\*\*\*\*\*\*\*\*\*\*\*\*                             1

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

\----------------------------------------------------------------------------------------

\--------2.手动输入table----------------

cat sql\_4.sh

\#!/bin/bash

\#set -x

echo  "Enter the tablename you want to select:"

read TABLENAME

sqlplus -S database\*\*\*\*/password\*\*@\\"//172.20.\*\*.\*\*:1521/sid\*\*\*\\" <

select \* from $\{TABLENAME\};

exit

\#set +x

\------------------------------------------执行结果：

./sql\_4.sh

Enter the tablename you want to select:

\*\*\*version

VERSIONSTRING                                              ID

\-------------------------------------------------- ----------

RELEASE\_2013.0.0\_\*\*\*\*\*\*\*\*\*\*\*\*\*\*                            1

shell获取sqlplus变量_sqlplus与shell互相传值的几种情况

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle SQLPLUS中DEFINE在SHELL中的调用

相关 Shell 与 SQLPLUS 的交互 - 参数传递返回值

相关 shell获取sqlplus变量,如何在shell脚本中捕获SQLPlus退出代码？

相关 shell获取sqlplus变量_sqlplus与shell互相传值的几种情况

相关 sqlplus系统变量

相关 shell 与 sqlplus 交互

相关 Linux SQLPLUS

相关 Oracle Sqlplus命令登录的几种方式

相关 shell调用shell的几种方式

相关 sqlplus