在shell脚本中调⽤sql语句查询员⼯信息
-S：静默登录
[oracle@localhost 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
[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
使⽤代码块
#!/bin/bash
sqlplus -S scott/scott<<EOF
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<<EOF
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<<EOF
set heading off
set feedback off
select ename from emp where deptno='$1';
exit
请输⼊部门编号
[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<<EOF
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<<EOF
set heading off
set feedback off
select ename from emp where deptno=${dno};
shell最简单脚本exit
EOF`
for information in $informations
do
echo $information
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<<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
[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 > <<EOF
set feedback off
select empno,job from emp where deptno=$dno; exit
EOF
[oracle@localhost shells]$
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
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
[oracle@localhost shells]$ bash shell8.sh 20
编号: 7369, ⼯作: CLERK
编号: 7566, ⼯作: MANAGER
编号: 7788, ⼯作: ANALYST
编号: 7876, ⼯作: CLERK
编号: 7902, ⼯作: ANALYST
将数据导⼊到⽂件中
将emp表中的所有列的数据，导出到⽂件中，列和列之间⽤逗号隔开
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论