unix 脚本

#!/usr/bin/ksh
export ORACLE_SID=mac3db
func1(){
sqlplus -s mac3_data/oracle<<EOF>tmp.txt
set heading off ;
set feedback off ;
set linesize 2000;
set trimspool on;
set trimout on;
set pagesize 0;
select distinct name||','||type||','||max(line)over(partition by name,type) lstr from user_source;
exit;
EOF
}
func2(){
sqlplus -s mac3_data/oracle<<EOF>/backup/proc/$1.$2
set heading off;
set feedback off;
set pagesize 0;
set linesize 2000;
set trimout on;
set trimspool on;
select case when line=1 then 'CREATE OR REPLACE ' ELSE '' END||text||case when line=$3 then chr(10)||'/' else '' end from user_source where name='$1' and type='$4';
exit;
EOF
}
#tmp1 program_name
#tmp2 program_type
#file_t program_extension_name
#tmp3 program_text_length
mkdir -p /backup/proc
func1
while read str
do
    tmp1=`echo $str | cut -f 1 -d ','`
    tmp2=`echo $str | cut -f 2 -d ','`
    tmp3=`echo $str | cut -f 3 -d ','`
    if [ "$tmp2" = "PACKAGE" ]
    then
        file_t="spec"
    elif [ "$tmp2" = "PACKAGE BODY" ]
    then
        file_t="bdy"
    else
        file_t="prc"
    fi
   func2 $tmp1 $file_t $tmp3 "$tmp2"
done<tmp.txt
rm tmp.txt