如果预言机中仅存在数据，如何假脱机文件?

Question

我正在使用以下脚本来生成csv文件。如果脚本运行并获得结果，我们将与列标题一起写入 CSV 文件。如果我们没有得到任何结果，则只有标头被写入CSV文件。如果我们没有得到任何结果，我不想将任何东西写入 CSV 文件。 如何在下面的脚本文件中实现它？

脚本

SET FEEDBACK OFF;
SET TERMOUT OFF;
SET SQLFORMAT csv;
spool status.csv;
select 
batch_id,   
batch_type,
bu_code,
bu_type,
status,
error_text,
cre_user_id,
to_char(upd_dtime,'DD-MON-YYYY HH.MI.SS AM') as upd_dtime,
to_char(status_created_dtime,'DD-MON-YYYY HH.MI.SS AM') as status_created_dtime,
to_char(status_queued_dtime,'DD-MON-YYYY HH.MI.SS AM') as status_queued_dtime,
to_char(status_running_dtime,'DD-MON-YYYY HH.MI.SS AM') as status_running_dtime     
from cox_item;
spool off;
disconnect;
exit;

Answer 1

这里有一个选项：

• 声明一个变量
• 检查表中是否存在某些内容并将结果放入该变量中
• 在where子句中使用它(变量(

像这样：我想假脱机这两个表的内容;一个包含行，另一个不包含：

SQL> select * from dept;
DEPTNO DNAME                LOC
---------- -------------------- --------------------
10 ACCOUNTING           NEW YORK
20 RESEARCH             DALLAS
30 SALES                CHICAGO
40 OPERATIONS           BOSTON
SQL> select * from cars;
no rows selected
SQL>

您的脚本(我将其保存为p.sql(

SET FEEDBACK OFF;
SET TERMOUT OFF;
SET SQLFORMAT csv;
spool status.txt;
var l_cnt number;
exec select max(1) into :l_cnt from dual where exists (select null from dept);
select *
from dept
where :l_cnt = 1;

exec select max(1) into :l_cnt from dual where exists (select null from cars);
select *
from cars
where :l_cnt = 1;
spool off;

执行和结果：

SQL> @p
SQL> $type status.txt
DEPTNO DNAME                LOC
---------- -------------------- --------------------
10 ACCOUNTING           NEW YORK
20 RESEARCH             DALLAS
30 SALES                CHICAGO
40 OPERATIONS           BOSTON
SQL>

对我来说看起来不错;第一个表在这里，另一个没有(甚至不是它的标题(。

Answer 2

找到记录时，会生成假脱机 sql 和数据文件 set feedback off termout on autoprint on serverout on echo off head off pages 0 newp 0 host del c:sql1.txt spool c:sql1.sql select ' set head on newp 0 termout on autoprint on serverout on echo off ' ||chr(10) ||'spool c:sql1.txt ' || chr(10) || 'select 1,2,3,4,5 from user_tables where rownum<10 ;' || chr(10) || 'spool off ' from dual where exists (select 1,2,3,4,5 from user_tables where rownum<10 ); spool off; @c:sql1.sql /

当找不到记录时，后台打印 sql 文件将为空，并且不会生成/假脱机任何数据文件

set feedback off termout on autoprint on serverout on echo off head off pages 0 newp 0 host del c:sql1.txt spool c:sql1.sql select ' set head on newp 0 termout on autoprint on serverout on echo off ' ||chr(10) ||'spool c:sql1.txt ' || chr(10) || 'select 1,2,3,4,5 from user_tables where rownum<10 ;' || chr(10) || 'spool off ' from dual where exists (select 1,2,3,4,5 from user_tables where rownum<10 and 1=2 ); spool off; @c:sql1.sql /