应用要求:
只有shell和sqlplus,怎样在sqlplus中实现循环
比如说数据库表中学生信息,只有shell,和sqlplus,我想弄个批处理,实现输入学号即可输出该生信息至以该生名字命名的文本文件中,假如学号从01-50,如何实现将01-50的学生信息全部输出,如何实现只输出固定的如02、03、08、25等学生信息。
解答:
1.SQl语句中没有循环的语句。
正确。但pl/sql有。
2.这是shell 和 sqlplus 交互的问题。有多种实现。
途径:
1)可以在shell中处理sql,
2)也可以在plsql中处理调用shell.
1)只需写shell脚本即可。
2)需要配extproc,虽然有效,比较麻烦。不建议。
3.如果让sqlplus 在shell循环,要重复连接很多次,不建议。建议先将所有学号的学生先一次性导出,再每条单独输出处理。
-------------------------------利用sequence-改造emp表为student表---------
利用scott.emp表改变一下:
SQL>create sequence seq1 increment by 1 maxvalue 10000 minvalue 1 cycle cache 20;
SQL > select seq1.nextvalue from dual;
SQL > select seq1.nextvalue from dual;
SQL > select seq1.nextvalue from dual;
SQL > select seq1.nextvalue from dual;
nextvalue
----------------
3
SQL > create table student as select * from emp;
SQL > create or replace procedure update_student
is
cursor stu_cur is select * from student for update;
begin
for rec in stu_cur loop
update student set empno=seq1.next_value where current of stu_cur;
end loop;
end ;
/
SQL > exec update_student;
SQL > SQL> select * from student;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
3 SMITH CLERK 7902 17-DEC-80 800 20
4 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
5 WARD SALESMAN 7698 22-FEB-81 1250 500 30
6 JONES MANAGER 7839 02-APR-81 2975 20
7 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
8 BLAKE MANAGER 7839 01-MAY-81 2850 30
9 CLARK MANAGER 7839 09-JUN-81 2450 10
10 SCOTT ANALYST 7566 19-APR-87 3000 20
11 KING PRESIDENT 17-NOV-81 5000 10
12 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
13 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
14 JAMES CLERK 7698 03-DEC-81 950 30
15 FORD ANALYST 7566 03-DEC-81 3000 20
16 MILLER CLERK 7782 23-JAN-82 1300 10
------------------先用function spool_out 3 15 输出文件spool_file.txt-----
function spool_out()
{
sqlplus -S ${UnP} < /dev/null
set pagesize 0 heading off echo off termout off feedback off linesize 200 colsep "," trimspool on trimout on
spool spool_file.txt
select * from student where empno between $1 and $2;
spool off
exit
EOF
}
#$1 $2 is the position parameter of the file output_student.sh
spool_out $1 $2
#cut out the whitespace in the spool_file.txt
sed -i 's/ //g' spool_file.txt
-----------------再用output_student 3 15 生成<人名>.txt文件------------
function output_student()
{
# $1 $2 is the position parameter of the funcion output_emp
for each in `seq $1 $2` ;do
#create file under the diretory ./student/
#awk -v v_out=$each 'BEGIN {FS="," ; FS=","}
#{ print $0 >"student/" $2".txt"}' spool_file.txt #$1 is the first field by sepearator ',' in the awk file
awk -v v_out=$each 'BEGIN {FS="," ; FS=","}
{if(v_out==$1) print $0 > "student/"$2".txt" }' spool_file.txt
done
}
#$1 $2 is the file output_emp.sh position parameter
output_student $1 $2
------------------------综合上述,完整的脚步如下:-------------------------------
#!/bin/bash
#file_name: output_student.sh
#Usage : output_student.sh ${start_value} ${end_value}. For example ./output_student.sh 3 15
if [ $# -le 2 ] ; then
echo 'Usage:output_student.sh ${start_value} ${end_value}. For example ./output_student.sh 3 15'
exit
fi
# database's username and password
UnP='scott/oracle'
if [ ! -d student ] ;then
mkdir student
fi
function spool_out()
{
sqlplus -S ${UnP} < /dev/null
set pagesize 0 heading off echo off termout off feedback off linesize 200 colsep "," trimspool on trimout on
spool spool_file.txt
select * from student where empno between $1 and $2;
spool off
exit
EOF
}
#$1 $2 is the position parameter of the file output_emp.sh
spool_out $1 $2
#cut out the whitespace in the spool_file.txt
sed -i 's/ //g' spool_file.txt
function output_student()
{
# $1 $2 is the position parameter of the funcion output_emp
for each in `seq $1 $2` ;do
#create file under the diretory ./student/
#awk -v v_out=$each 'BEGIN {FS="," ; FS=","}
#{ print $0 >"student/" $2".txt"}' spool_file.txt #$1 is the first field by sepearator ',' in the awk file
awk -v v_out=$each 'BEGIN {FS="," ; FS=","}
{if(v_out==$1) print $0 > "student/"$2".txt" }' spool_file.txt
done
}
#$1 $2 is the file output_emp.sh position parameter
output_student $1 $2
------------------------------------语法着色截图如下(可放大)----------------------




output1.jpg
output2.jpg
spool.jpg
student.jpg