17,078
社区成员
发帖
与我相关
我的任务
分享
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
TASKID TASKNAME
---------- ----------------------------------------------------------------
19 SSB1S
PL/SQL 过程已成功完成。
from dual connect by level <= :v2;
来做一个类似的循环,避免写PL了spool d:\tmp.txt replace
select '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
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 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
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;