Question

oracle执行存储过程不显示执行结果怎么办

create or replace procedure p_selTabSpace as
cursor tt is
select a.a1 tabSpaceName,
b.b2 / 1024 / 1024 tabSpaceSize,
round((b.b2 - a.a2) / 1024 / 1024, 2) tabSpaceUsed,
substr((b.b2 - a.a2) / b.b2, 1, 7) tabSpaceVused
from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
from dba_free_space
group by tablespace_name) a,
(select tablespace_name b1, sum(bytes) b2
from dba_data_files
group by tablespace_name) b,
(select tablespace_name c1, contents c2, extent_management c3
from dba_tablespaces) c
where a.a1 = b.b1
and c.c1 = b.b1;
begin
for i in tt loop
DBMS_OUTPUT.PUT_LINE('tabSpaceName is ' || UPPER(i.tabSpaceName) || ' ' ||
'tabSpaceSize: ' || i.tabSpaceSize || ' ' ||
'tabSpaceUsed: ' || i.tabSpaceUsed || ' ' ||
'tabSpaceVused: ' || i.tabSpaceVused);
end loop;
END;
我这个存储过程有错误吗？为什么执行的时候总不显示执行结果呢

我是这样在PL/SQL中执行的：
begin

p_selTabSpace;
end; 应该怎样执行能行显示出结果呢

Answer 1

你的这个执行结果就为空，换个试试。

追问

那应该怎样改啊，原始SQL的作用是查询表空间的利用情况

Answer 2

如果你是在 SQL Plus 里面执行的话， 需要提前加一句话 set serveroutput on

SQL> set serveroutput on
SQL> BEGIN
2 dbms_output.put_line('Hello World');
3 END;
4 /
Hello World
PL/SQL procedure successfully completed.

Answer 3

first: set serveroutput on
second:
begin
open tt;
loop
fetch tt into XXX;
exit when tt%notfound;
dbms_output.put_line(XXX);
end loop;
end;