在shell脚本中调用sql语句

时间：2022-04-25 17:57:01

查询员工信息

-S:静默登录

[[email protected] shells]$ cat shell1.sh 
#!/bin/bash

#查询员工信息
sqlplus -S /nolog <<EOF
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
[[email protected] 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

使用代码块

[[email protected] shells]$ cat shell2.sh
#!/bin/bash
sqlplus -S scott/scott<<EOF
set feedback off
set serveroutput on
begin
  dbms_output.put_line('hello world');
end;
/
exit
EOF
[[email protected] shells]$ bash ./shell2.sh 
hello world

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

[[email protected] shells]$ cat shell3.sh 
#!/bin/bash

if [ $# -lt 1 ];then
  echo 请传入部门编号
  exit
fi
dno=$1
sqlplus -S scott/scott<<EOF
set feedback off
select ename from emp where deptno=${dno};
exit
EOF
[[email protected] shells]$ bash shell3.sh 10

ENAME
----------
CLARK
KING
MILLER

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

在sqlplus的EOF中,
单引号中的取变量符号和外面不同
它可以取到变量值

[[email protected] shells]$ cat shell4.sh 
#!/bin/bash
if [ $# -lt 1 ];then
  echo 请输入部门编号
  exit
fi
sqlplus -S scott/scott<<EOF
set heading off
set feedback off
select ename from emp where deptno='$1';
exit
EOF
[[email protected] shells]$ bash shell4.sh 
请输入部门编号
[[email protected] shells]$ bash shell4.sh 20

SMITH
JONES
SCOTT
ADAMS
FORD

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

传入一个部门编号,查询除部门的员工人数
并将sqlplus的结果传到shell脚本的变量中

[orac[email protected] shells]$ cat shell5.sh
#!/bin/bash

if [ $# -lt 1 ];then
  echo 请输入部门编号
  exit
fi
dno=$1
num=`sqlplus -S scott/scott<<EOF
set heading off
set feedback off
select count(*) from emp where deptno=${dno};
exit
EOF`
echo $num
[[email protected] shells]$ bash shell5.sh 20
5
[[email protected] shells]$ bash shell5.sh 10
3

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

[[email protected] shells]$ cat shell6.sh 
#!/bin/bash

if [ $# -lt 1 ];then
  echo 请输入部门编号
  exit
fi
dno=$1
informations=`sqlplus -S scott/scott<<EOF
set heading off
set feedback off
select ename from emp where deptno=${dno};
exit
EOF`

for information in $informations
do
  echo $information
done
[[email protected] shells]$ bash shell6.sh 20
SMITH
JONES
SCOTT
ADAMS
FORD
[[email protected] shells]$ cat shell7.sh 
#!/bin/bash
if [ $# -lt 1 ];then
  echo 请输入部门编号
  exit
fi
dno=$1
names=`sqlplus -S scott/scott<<EOF
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
[[email protected] shells]$ bash shell7.sh 10
CLARK
KING
MILLER

保存到文件

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

[[email protected] shells]$ cat shell8.sh 
#!/bin/bash
if [ $# -lt 1 ];then
  echo 请输入部门编号
  exit
fi
dno=$1
sqlplus -S scott/scott > emp.txt<<EOF
set feedback off
select empno,job from emp where deptno=$dno;
exit
EOF
[[email protected] shells]$ bash shell8.sh 10
[[email protected] shells]$ cat emp.txt 

     EMPNO JOB
---------- ---------
      7782 MANAGER
      7839 PRESIDENT
      7934 CLERK

读取文件

[[email protected] shells]$ cat shell8.sh 
#!/bin/bash
if [ $# -lt 1 ];then
  echo 请输入部门编号
  exit
fi
dno=$1
file=emp.txt
sqlplus -S scott/scott > $file<<EOF
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
[[email protected] shells]$ bash shell8.sh 20
编号: 7369, 工作: CLERK
编号: 7566, 工作: MANAGER
编号: 7788, 工作: ANALYST
编号: 7876, 工作: CLERK
编号: 7902, 工作: ANALYST

将数据导入到文件中

将emp表中的所有列的数据,导出到文件中,
列和列之间用逗号隔开

[[email protected] shells]$ cat shell9.sh 
#!/bin/bash

file=emp.txt
sqlplus -S scott/scott > $file<<EOF
set heading off
set feedback off
set pagesize 100
set linesize 300
select empno||','||ename||','||job||','||mgr||','||sal||','||comm||','||deptno from emp;
exit
EOF
[[email protected] 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

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

[[email protected] shells]$ cat shell10.sh 
#!/bin/bash
if [ $# -lt 1 ];then
  echo 请输入一个表名
  exit
fi
tab=$1
sqlplus -S scott/scott<<EOF
set feedback off
set pagesize 100
set linesize 300
select * from $tab;
exit
EOF
[[email protected] shells]$ bash shell10.sh dept

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

脚本备份数据库

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

[[email protected] ~]$ 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 [email protected]:/home/oracle/dmp/
[[email protected] ~]$ bash shell11.sh 
[email protected]'s password: 
[[email protected] ~]$ tree
.
├── data
│?? ├── exp.dump
│?? └── exp.log
├── dmp
│?? └── exp.dump
└── shell11.sh

优化上面的语句

[[email protected] ~]$ 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 [email protected]$desip:$dir/
[[email protected] ~]$ bash shell12.sh 
[email protected]'s password: 
[[email protected] ~]$ 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<<EOF
set heading off
set feedback off
set pagesize 100
set linesize 300
select deptno||'|'||dname||'|'||loc from dept;
exit
EOF
[[email protected] ~]$ bash dept.sh
[[email protected] ~]$ 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;

执行导入脚本

[[email protected] ~]$ 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<<EOF
insert into dept_bak(deptno,dname,loc) values($dno,'$name','$l');
exit
EOF
done < ./dept.txt
[[email protected] ~]$ bash impdept.sh 
SQL> select * from dept_bak;

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

相关文章