如何创建Oracle SQL脚本Spool文件

Question

我有一个关于程序结果的缓存问题，我的示例 SQL 脚本如下：

（我希望将程序的结果进行缓存处理）

  whenever sqlerror exit failure rollback
  set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

 declare
 ab varchar2(10) := 'Raj';
 cd varchar2(10);
 a number := 10;
 c number;
 d number;
 begin
 c := a+10;
 select ab,c into cd,d from dual;
 end;

 SPOOL 
 select cd,d from dual;
 SPOOL OFF
 EXIT;

上面的脚本无法使用，但我想做类似这样的事情，在begin end块中计算一些值，然后将这些结果输出到文件中。

谢谢。

- user987900

Answer 1

这将把匿名块的输出记录到名为output_<YYYYMMDD>.txt的文件中，该文件位于本地PC C驱动器的根目录下，其中<YYYYMMDD>是当前日期：

SET SERVEROUTPUT ON FORMAT WRAPPED
SET VERIFY OFF

SET FEEDBACK OFF
SET TERMOUT OFF

column date_column new_value today_var
select to_char(sysdate, 'yyyymmdd') date_column
  from dual
/
DBMS_OUTPUT.ENABLE(1000000);

SPOOL C:\output_&today_var..txt

DECLARE
   ab varchar2(10) := 'Raj';
   cd varchar2(10);
   a  number := 10;
   c  number;
   d  number; 
BEGIN
   c := a+10;
   --
   SELECT ab, c 
     INTO cd, d 
     FROM dual;
   --
   DBMS_OUTPUT.put_line('cd: '||cd);
   DBMS_OUTPUT.put_line('d: '||d);
END; 

SPOOL OFF

SET TERMOUT ON
SET FEEDBACK ON
SET VERIFY ON

PROMPT
PROMPT Done, please see file C:\output_&today_var..txt
PROMPT

希望能对你有所帮助...

编辑：

根据您的评论，要在游标的每次迭代中输出一个值（我意识到在这个示例中每个值都相同，但您应该能够理解我正在做什么）：

BEGIN
   c := a+10;
   --
   FOR i IN 1 .. 10
   LOOP
      c := a+10;
      -- Output the value of C
      DBMS_OUTPUT.put_line('c: '||c);
   END LOOP;
   --
END; 

- Ollie

---

如果在begin-end块中有一个游标并且它在循环内部循环，会发生什么情况。例如： 'loop c := a+10; select c into :d from dual; end loop; end; SPOOL select :d from dual; SPOOL OFF EXIT;' 那么，现在它会给出所有结果还是只有存储在:d中的最终输出。 如果有循环，如何进行spool。 - user987900

你可以在游标循环中使用DBMS_OUTPUT来输出每次迭代的值。请参见编辑。 - Ollie

嗨，Ollie，我还有一个问题。当我在SQL*plus中运行上面的代码时，它可以正常工作。但是，我有一个Shell脚本调用这个SQL脚本，并将输出保存到文本文件中。我已经在Oracle应用程序中注册了这个Shell脚本，但是当我从应用程序提交请求运行时，它显示为正常和运行中，但从未完成。所以，当从Shell脚本调用时，我需要对上面的脚本进行任何更改吗？ - user987900

如果您正在后台运行它，我会担心您希望输出到哪里。在这种情况下，最好使用UTL_FILE包直接从PL/SQL块中将输出写入文件，然后可以使用任何工具（PL/SQL，Java，UNIX等）读取该文件。这很简单，比通过SQLPlus和shell脚本等传递输出要简单得多。http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm - Ollie

要在 SQL Plus 脚本中运行此代码（例如 @myscript.sql），我必须在匿名块的 "END;" 后添加 "/"。 - Dzyann

有没有人有一个超级简单的 spool 示例？我只想导出一行表格，但是我将不得不多次执行相同的导出，所以希望不必每次都使用（右键单击->导出...）。我找不到不涉及30多行代码的示例。 - J.D

Answer 2

使用spool：

  set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

variable cd varchar2(10);
variable d number;

 declare
 ab varchar2(10) := 'Raj';
 a number := 10;
 c number;
 begin
 c := a+10;
 select ab,c into :cd,:d from dual;
 end;

 SPOOL 
 select :cd,:d from dual;
 SPOOL OFF
 EXIT;

- Florin Ghita

---

如果在块内有光标并且它在begin-end内循环，会发生什么呢？例如：'loop c := a+10; select c into :d from dual; end loop; end; SPOOL select :d from dual; SPOOL OFF EXIT;' 现在，它会给出所有的结果还是只有最终输出存储在:d中。如果有循环，如何进行记录。 - user987900

那样做不起作用。您可以将 SQL 查询缓冲，但不能缓冲 PL/SQL。对于 PL/SQL 代码的输出，您可以使用 dbms_output 包，正如 Ollie 所指示的那样。 - Florin Ghita

Answer 3

从一个 BEGIN END 块中进行 spool 是非常简单的。例如，如果您需要将两个表的结果 spool 到一个文件中，那么只需使用 for loop 即可。下面是示例代码。

BEGIN

FOR x IN 
(
    SELECT COLUMN1,COLUMN2 FROM TABLE1
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLEB
)    
LOOP
    dbms_output.put_line(x.COLUMN1 || '|' || x.COLUMN2);
END LOOP;

END;
/

- Sarath Subramanian

Answer 4

为了在PLSQL中执行一个spool文件 进入 文件->新建->命令窗口 -> 粘贴你的代码-> 执行。转到目录，你会找到这个文件。

- sweety