create or replace procedure p_selTabSpace as
var1 varchar2(50);
var2 number;
var3 number;
var4 number;
begin
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" into var1,into var2,into var3, into var4
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;
请问我的这个应该如何改一下才可忙正确执行,输出的应该是一个结果集
可能是我的代码比较乱吧,里面的SQL语句是用来查询表空间利用率的,因为直接写SQL在程序中响应太慢了,本身执行SQL也比较慢,所以想通过存储过程来优化一下,我再在用的游标
create or replace procedure p_selTabSpace(o_cur out sys_refcursor) as
begin
open o_cur for select a.a1 "tabSpaceName" ,
b.b2 "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) / 1024 / 1024 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;
end p_selTabSpace;
但是怎样在IBATIS中执行呢,
oracle高手快来看看,我想写一个输出结果集的存储过程并执行
答案:3 悬赏:60
解决时间 2021-03-03 07:37
- 提问者网友:呆萌心雨
- 2021-03-02 18:35
最佳答案
- 二级知识专家网友:随心随缘不随便
- 2021-03-02 19:22
create or replace procedure p_selTabSpace(v1
dba_data_files.tablespace_name%type)
as
var1 dba_data_files.tablespace_name%type;
var2 number;
var3 number;
var4 number;
begin
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" into var1,var2,
var3,var4
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=UPPER(v1) and a.a1 = b.b1 and c.c1 = b.b1;
DBMS_OUTPUT.PUT_LINE('tabSpaceName is'||UPPER(VAR1)||'
'||'tabSpaceSize'||var2||' '||'tabSpaceUsed'||var3||'
'||'tabSpaceVused'||var4);
END;
--------------------------------TEST-------------------------
EXECUTE p_selTabSpace('system');
你想得到结果集是用来存放单行的数据还是存放多行的数据,如果是后者的话,单纯的select...into...这种方法是实现不了的,原因是你不可以将一列的多个结果都付给同一个变量,这也就是使用select...into...这种方式的一种局限,如果想用来存放多行结果就要向楼上说的那样使用游标或者是复合数据类型才可以。
dba_data_files.tablespace_name%type)
as
var1 dba_data_files.tablespace_name%type;
var2 number;
var3 number;
var4 number;
begin
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" into var1,var2,
var3,var4
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=UPPER(v1) and a.a1 = b.b1 and c.c1 = b.b1;
DBMS_OUTPUT.PUT_LINE('tabSpaceName is'||UPPER(VAR1)||'
'||'tabSpaceSize'||var2||' '||'tabSpaceUsed'||var3||'
'||'tabSpaceVused'||var4);
END;
--------------------------------TEST-------------------------
EXECUTE p_selTabSpace('system');
你想得到结果集是用来存放单行的数据还是存放多行的数据,如果是后者的话,单纯的select...into...这种方法是实现不了的,原因是你不可以将一列的多个结果都付给同一个变量,这也就是使用select...into...这种方式的一种局限,如果想用来存放多行结果就要向楼上说的那样使用游标或者是复合数据类型才可以。
全部回答
- 1楼网友:傲娇菇凉
- 2021-03-02 21:02
乱哄哄的,又没有条件测试,输出的是结果集的话,你好像定义了4个局部参数变量,好像没有out输出参数,有了也不好使,因为输出的是结果集,应该使用游标来实现,定义一个package,定义游标,接收查询
- 2楼网友:偏爱自由
- 2021-03-02 19:46
定义一个游标,给你个参考
create or replace procedure p_checkname
(
cname in varchar2,
data_cursor out sys_refcursor
)
as
v_cid int;
begin
select cid into v_cid from t_classes where classesname = cname;
open data_cursot for select name from t_student where cid = v_cid;
end;
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯