shell脚本：oracle导出存储过程为sql脚本

#!/bin/bash
basepath=`pwd`
echo "excute file path :"${basepath}
sqlplus -S user/passwd <<EOF
set echo off;
set heading off;
set feedback off;
set pagesize 0;
set trimspool on; --这个要加，可以去除导出脚本行末自动补齐的多余空格
set linesize 2000; 
spool '${basepath}/proc_bak_`date +%Y%m%d`.sql' REPLACE
SELECT CASE WHEN LINE = 1 THEN 'CREATE OR REPLACE ' || TEXT
            WHEN LINE = MAX_LINE THEN TEXT || CHR(10 ) || '/'
            ELSE TEXT
        END
  FROM USER_SOURCE A
  LEFT JOIN (SELECT A.NAME,A.TYPE, MAX(LINE) MAX_LINE
               FROM USER_SOURCE A
              WHERE TYPE IN ( 'PROCEDURE', 'PACKAGE' ,'PACKAGE BODY' )
              GROUP BY A.NAME,A.TYPE ) B ON A.NAME||A.TYPE = B.NAME||B.TYPE
 WHERE A.TYPE IN ('PROCEDURE') AND A.NAME IN
 (
 'STORAGE_PROCEDURE1',
 'STORAGE_PROCEDURE2',
 )
ORDER BY A.NAME||A.TYPE,LINE;

spool off	       
EOF