spool脚本里想打印出PL里执行SQL结果

Question

spool1.sql：var v1 number;
exec select max(TASK_ID) into :v1 from SQL_RESULT;
var v2 number;
exec select count(distinct FILENAME) into :v2 from SQL_TIME where TASKID = :v1 ;spool d:\1.txt
select taskid ,taskname from test_task where taskid=:v1;declare
sql1 varchar2(512);
begin
for i in 1..:v2 loop
sql1 :='select INSQLID "文件内SQL编号",sum(SQLTIME) "Time(ms)" from SQL_TIME where TASKID = '||:v1||' and FILENAME=''./result/SQL'||i||'.format'' group by rollup(INSQLID) order by INSQLID';
dbms_output.put_line('SQL文件'||i);
dbms_output.put_line(sql1);
execute immediate sql1;
end loop;
end;
/spool off
spool的结果1.txt，如下：    TASKID TASKNAME                                                                                                                                                                                     
---------- ----------------------------------------------------------------                                                                                                                             
        19 SSB1S                                                                                                                                                                                        
PL/SQL 过程已成功完成。虽然用了execute immediate sql1;但select的结果并没打到1.txt里，怎么才能将结果打出来呢？
spoolpl过程函数

Answer 1

spool d:\1.txt 后面加：
set serveroutput on;

Answer 2

serveroutput 是一直都开着的

Answer 3

SQL> set serveroutput on;
SQL> spool d:\aa.txt
SQL> select * from a;STUID      CLASSD     ST      RTIME
---------- ---------- -- ----------
001        001        01   20130101
002        002        02   20130101
003        002        02   20120101
004        001        01   20120101SQL> declare
  2  TestCursor SYS_REFCURSOR;
  3  v_city test_a.city%type;
  4  v_price test_a.price%type;
  5  begin
  6    test(TestCursor);
  7    loop
  8      begin
  9        fetch TestCursor into v_city,v_price;
 10        exit when TestCursor%notfound;
 11             DBMS_OUTPUT.put_line(v_city||' , '||v_price);
 12      end;
 13    end loop;
 14  end;
 15  /
SH , 2
SH , 3
SH , 4
SH , 5
SH , 6
SH , 7
SH , 8
SH , 9
SH , 10
SH , 14
SH , 2
SH , 3
SH , 4
SH , 5
SH , 6
SH , 7
SH , 8
SH , 9
aa.txt文件：
SQL> set serveroutput on;
SQL> select * from a;STUID      CLASSD     ST      RTIME                                             
---------- ---------- -- ----------                                             
001        001        01   20130101                                             
002        002        02   20130101                                             
003        002        02   20120101                                             
004        001        01   20120101                                             SQL> declare
  2  TestCursor SYS_REFCURSOR;
  3  v_city test_a.city%type;
  4  v_price test_a.price%type;
  5  begin
  6    test(TestCursor);
  7    loop
  8      begin
  9        fetch TestCursor into v_city,v_price;
 10        exit when TestCursor%notfound;
 11             DBMS_OUTPUT.put_line(v_city||' , '||v_price);
 12      end;
 13    end loop;
 14  end;
 15  /
SH , 2                                                                          
SH , 3                                                                          
SH , 4                                                                          
SH , 5                                                                          
SH , 6                                                                          
SH , 7                                                                          
SH , 8                                                                          
SH , 9                                                                          
SH , 10                                                                         
SH , 14                                                                         
SH , 2                                                                          
SH , 3                                                                          
             
PL/SQL 过程已成功完成。SQL> spool off;

Answer 4

我是用spool到html文件里的，DBMS_OUTPUT.put_line打出来的不能放到html的表格里....
写成spool到txt只是方便大家理解....

Answer 5

PL里面没法spool吧，spool是sqlplus的指令

Answer 6

quote=引用 5 楼 aio_o 的回复:]
我是用spool到html文件里的，DBMS_OUTPUT.put_line打出来的不能放到html的表格里....
写成spool到txt只是方便大家理解....谁说DBMS_OUTPUT.put_line打出来的不能放到html的表格里的？ 就只要想把输出的信息格式化好，是可以按照表格输出的[

Answer 7

两层spool搞定了
spool d:\tmp.txt replaceselect 'select n.SQLNAME,INSQLID "SqlOrder",sum(SQLTIME) "Time(ms)" from SQL_TIME t,sql_name n where t.TASKID = '||:v1||' and n.TASKID = '||:v1||' and to_number(substr(SQLNAME,2,instr(SQLNAME,''_'')-2))= '||level||' and FILENAME=''./result/SQL'||level||'.format'' group by rollup(INSQLID),n.SQLNAME order by INSQLID;' 
from dual connect by level <= :v2;spool off @d:\tmp.txt 

Answer 8

用
from dual connect by level <= :v2;
来做一个类似的循环，避免写PL了