我查询数据的语句是:select titleid,name,creator,createtime,filename from titles t where createtime >= trunc(sysdate)-15 and createtime < trunc(sysdate) order by createtime desc请问我要将查询到的数据导出成CSV文件的存储过程要怎么写?因为我需要和JOB结合,所以需要SP
调试欢乐多
写个shell脚本:
sqlplus -s $1/$2@$3 <<EOF
SET COLSEP '|';
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET LINESIZE 1000;
SET NUMWIDTH 20;
SET PAGESIZE 0;
SET TERMOUT OFF;
SET TRIMSPOOL ON;
SET TRIMOUT OFF;
SET VERIFY OFF;
SET ARRAYSIZE 5000;
SET SQLBLANKLINE OFF;
alter session set nls_date_format='YYYY-MM-DD hh:mi:ss';
spool /home/titles.txt;
select titleid,name,creator,createtime,filename from titles t where createtime >= trunc(sysdate)-15 and createtime < trunc(sysdate) order by createtime desc
spool off;
exit;
EOF然后你再执行这脚本,传入相应的连接字符串$1:用户名 $2:密码 $3:数据库SID