linux脚本调用sql,在shell脚本中调用sql语句

查询员工信息

-S:静默登录

[oracle@localhost shells]$ cat shell1.sh

#!/bin/bash

#查询员工信息

sqlplus -S /nolog <

conn scott/scott

set feedback off

set linesize 300

set pagesize 100

col empno for 99999

col ename for a12

col mgr for 9999

col hiredate for a20

col comm for 9999

col deprno for 99999

select * from emp;

exit

EOF

[oracle@localhost shells]$ bash ./shell1.sh

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ------------ --------- ----- -------------------- ---------- ----- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

使用代码块

[oracle@localhost shells]$ cat shell2.sh

#!/bin/bash

sqlplus -S scott/scott<

set feedback off

set serveroutput on

begin

dbms_output.put_line('hello world');

end;

/

exit

EOF

[oracle@localhost shells]$ bash ./shell2.sh

hello world

传入一个部门编号查询出该部门下的员工姓名

[oracle@localhost shells]$ cat shell3.sh

#!/bin/bash

if [ $# -lt 1 ];then

echo 请传入部门编号

exit

fi

dno=$1

sqlplus -S scott/scott<

set feedback off

select ename from emp where deptno=${dno};

exit

EOF

[oracle@localhost shells]$ bash shell3.sh 10

ENAME

----------

CLARK

KING

MILLER

输入一个工作,根据工作查询员工的姓名

在sqlplus的EOF中,

单引号中的取变量符号和外面不同

它可以取到变量值

[oracle@localhost shells]$ cat shell4.sh

#!/bin/bash

if [ $# -lt 1 ];then

echo 请输入部门编号

exit

fi

sqlplus -S scott/scott<

set heading off

set feedback off

select ename from emp where deptno='$1';

exit

EOF

[oracle@localhost shells]$ bash shell4.sh

请输入部门编号

[oracle@localhost shells]$ bash shell4.sh 20

SMITH

JONES

SCOTT

ADAMS

FORD

将sql中的查询结果,传给shell脚本

传入一个部门编号,查询除部门的员工人数

并将sqlplus的结果传到shell脚本的变量中

[oracle@localhost shells]$ cat shell5.sh

#!/bin/bash

if [ $# -lt 1 ];then

echo 请输入部门编号

exit

fi

dno=$1

num=`sqlplus -S scott/scott<

set heading off

set feedback off

select count(*) from emp where deptno=${dno};

exit

EOF`

echo $num

[oracle@localhost shells]$ bash shell5.sh 20

5

[oracle@localhost shells]$ bash shell5.sh 10

3

循环传入部门编号,查询除部门下员工的编号和姓名

[oracle@localhost shells]$ cat shell6.sh

#!/bin/bash

if [ $# -lt 1 ];then

echo 请输入部门编号

exit

fi

dno=$1

informations=`sqlplus -S scott/scott<

set heading off

set feedback off

select ename from emp where deptno=${dno};

exit

EOF`

for information in $informations

do

echo $information

done

[oracle@localhost shells]$ bash shell6.sh 20

SMITH

JONES

SCOTT

ADAMS

FORD

[oracle@localhost shells]$ cat shell7.sh

#!/bin/bash

if [ $# -lt 1 ];then

echo 请输入部门编号

exit

fi

dno=$1

names=`sqlplus -S scott/scott<

set heading off

set feedback off

select ename from emp where deptno=$dno;

exit

EOF`

for((i=1;i<=100;i++))

do

name=`echo $names | cut -f $i -d ' '`

if [ -z $name ];then

break

fi

echo $name

done

[oracle@localhost shells]$ bash shell7.sh 10

CLARK

KING

MILLER

保存到文件

传入部门编号,查询部门下的员工编号和姓名

[oracle@localhost shells]$ cat shell8.sh

#!/bin/bash

if [ $# -lt 1 ];then

echo 请输入部门编号

exit

fi

dno=$1

sqlplus -S scott/scott > emp.txt<

set feedback off

select empno,job from emp where deptno=$dno;

exit

EOF

[oracle@localhost shells]$ bash shell8.sh 10

[oracle@localhost shells]$ cat emp.txt

EMPNO JOB

---------- ---------

7782 MANAGER

7839 PRESIDENT

7934 CLERK

读取文件

[oracle@localhost shells]$ cat shell8.sh

#!/bin/bash

if [ $# -lt 1 ];then

echo 请输入部门编号

exit

fi

dno=$1

file=emp.txt

sqlplus -S scott/scott > $file<

set heading off

set feedback off

select empno,job from emp where deptno=$dno;

exit

EOF

while read line

do

if [[ -z $line ]];then

continue

fi

mpno=`echo $line | cut -f 1 -d ' '`

name=`echo $line | cut -f 2 -d ' '`

echo "编号: ${mpno}, 工作: ${name}"

done < $file

rm -rf $file

[oracle@localhost shells]$ bash shell8.sh 20

编号: 7369, 工作: CLERK

编号: 7566, 工作: MANAGER

编号: 7788, 工作: ANALYST

编号: 7876, 工作: CLERK

编号: 7902, 工作: ANALYST

将数据导入到文件中

将emp表中的所有列的数据,导出到文件中,

列和列之间用逗号隔开

[oracle@localhost shells]$ cat shell9.sh

#!/bin/bash

file=emp.txt

sqlplus -S scott/scott > $file<

set heading off

set feedback off

set pagesize 100

set linesize 300

select empno||','||ename||','||job||','||mgr||','||sal||','||comm||','||deptno from emp;

exit

EOF

[oracle@localhost shells]$ cat emp.txt

7369,SMITH,CLERK,7902,800,,20

7499,ALLEN,SALESMAN,7698,1600,300,30

7521,WARD,SALESMAN,7698,1250,500,30

7566,JONES,MANAGER,7839,2975,,20

7654,MARTIN,SALESMAN,7698,1250,1400,30

7698,BLAKE,MANAGER,7839,2850,,30

7782,CLARK,MANAGER,7839,2450,,10

7788,SCOTT,ANALYST,7566,3000,,20

7839,KING,PRESIDENT,,5000,,10

7844,TURNER,SALESMAN,7698,1500,0,30

7876,ADAMS,CLERK,7788,1100,,20

7900,JAMES,CLERK,7698,950,,30

7902,FORD,ANALYST,7566,3000,,20

7934,MILLER,CLERK,7782,1300,,10

传入一个表名,查询该表的所有数据

[oracle@localhost shells]$ cat shell10.sh

#!/bin/bash

if [ $# -lt 1 ];then

echo 请输入一个表名

exit

fi

tab=$1

sqlplus -S scott/scott<

set feedback off

set pagesize 100

set linesize 300

select * from $tab;

exit

EOF

[oracle@localhost shells]$ bash shell10.sh dept

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

脚本备份数据库

将导出文件放到备份服务器或者目标服务器

[oracle@localhost ~]$ cat shell11.sh

#!/bin/bash

exp system/"oracle"@192.168.0.33:1521/orcl file='/home/oracle/data/exp.dump' log='/home/oracle/data/exp.log' owner=scott indexes=n

scp -r /home/oracle/data/exp.dump oracle@192.168.0.33:/home/oracle/dmp/

[oracle@localhost ~]$ bash shell11.sh

oracle@192.168.0.33's password:

[oracle@localhost ~]$ tree

.

├── data

│?? ├── exp.dump

│?? └── exp.log

├── dmp

│?? └── exp.dump

└── shell11.sh

优化上面的语句

[oracle@localhost ~]$ cat shell12.sh

#!/bin/bash

#导出配置

dbuser=system

passwd=oracle

dbip=192.168.0.33

port=1521

sid=orcl

dumppath=/home/oracle/data

dt=`date "+%Y%m%d%H%M%S"`

logpath=/home/oracle/data

schema=scott

dumpfile=exp_${dt}.dump

logfile=exp_${dt}.log

#备份服务器配置

desuser=oracle

desip=oracle

desip=192.168.0.33

dir=/home/oracle/dmp

#将数据库中的数据导出

exp $dbuser/"$passwd"@$dbip:$port/$sid file=$dumppath/$dumpfile log=$dumppath/$logfile owner=$schema indexes=n

#将导出文件放到备份服务器或者目标服务器

scp -r $dumppath/$dumpfile $desuser@$desip:$dir/

[oracle@localhost ~]$ bash shell12.sh

oracle@192.168.0.33's password:

[oracle@localhost ~]$ tree

.

├── data

│?? ├── exp_20200307030903.dump

│?? ├── exp_20200307030903.log

│?? ├── exp.dump

│?? └── exp.log

├── dmp

│?? ├── exp_20200307030903.dump

│?? └── exp.dump

├── shell11.sh

└── shell12.sh

将dept中的数据导出为dept.txt文件,并导入到另一张表中(dept_bak和dept表结构相同)

导出到dept.txt

sqlplus -S scott/scott > dept.txt<

set heading off

set feedback off

set pagesize 100

set linesize 300

select deptno||'|'||dname||'|'||loc from dept;

exit

EOF

[oracle@localhost ~]$ bash dept.sh

[oracle@localhost ~]$ cat dept.txt

10|ACCOUNTING|NEW YORK

20|RESEARCH|DALLAS

30|SALES|CHICAGO

40|OPERATIONS|BOSTON

在oracle中新建一张表

SQL> create table dept_bak as select * from dept where 1=0;

执行导入脚本

[oracle@localhost ~]$ cat impdept.sh

#!/bin/bash

while read line

do

if [[ -z $line ]];then

continue

fi

dno=`echo $line | cut -f 1 -d '|'`

name=`echo $line | cut -f 2 -d '|'`

l=`echo $line | cut -f 3 -d '|'`

sqlplus -S scott/scott > /dev/null<

insert into dept_bak(deptno,dname,loc) values($dno,'$name','$l');

exit

EOF

done < ./dept.txt

[oracle@localhost ~]$ bash impdept.sh

SQL> select * from dept_bak;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

在shell脚本中调用sql语句

标签：rm -rf   res   sea   arc   符号   oracle   set   port   rac

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/inmeditation/p/12443357.html