如何创建一个 oracle sql 脚本假脱机文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8590826/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a oracle sql script spool file
提问by user987900
I have a question about spooling the the results of my program. My sample sql script looks like this.
我有一个关于假脱机程序结果的问题。我的示例 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;
The above script does not work, but I want to do something like this where in the begin end block we compute some values and i want to spool those results.
上面的脚本不起作用，但我想做这样的事情，在开始结束块中我们计算一些值，我想假脱机这些结果。
Thanks.
谢谢。
回答by Ollie
This will spool the output from the anonymous block into a file called output_<YYYYMMDD>.txtlocated in the root of the local PC C: drive where <YYYYMMDD>is the current date:
这会将匿名块的输出假脱机到一个名为的文件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
Hope it helps...
希望能帮助到你...
EDIT:
编辑：
After your comment to output a value for every iteration of a cursor (I realise each value will be the same in this example but you should get the gist of what i'm doing):
在您为游标的每次迭代输出一个值的评论之后（我意识到在此示例中每个值都相同，但您应该了解我正在做的事情的要点）：
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;
回答by Florin Ghita
With 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;
回答by Sarath Avanavu
To spool from a BEGINENDblock is pretty simple. For example if you need to spool result from two tables into a file, then just use the for loop. Sample code is given below.
从一个BEGINEND块中进行假脱机非常简单。例如，如果您需要将两个表中的结果假脱机到一个文件中，则只需使用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;
/
回答by sweety
In order to execute a spool file in plsql Go to File->New->command window -> paste your code-> execute. Got to the directory and u will find the file.
为了在 plsql 中执行假脱机文件转到文件->新建->命令窗口->粘贴您的代码->执行。进入目录，你会找到文件。
