首先创建一个游标变量
CREATE OR REPLACE PACKAGE GLOBAL_CURSOR
IS
type rct is ref cursor;
END;创建临时表
create global temporary table temp_partinfo
(
partname NUMBER(20),
moneylevel VARCHAR2(50) default '无',
num number(5) default 0
)
on commit preserve rows;CREATE OR REPLACE PROCEDURE part_proc (o_article OUT global_cursor.rct)
IS
BEGIN
delete from temp_partinfo;
insert into temp_partinfo(partname,moneylevel,num)
select partname,'1000以下',count(*)from table1 where money<=1000
group by partname; insert into temp_partinfo(partname,moneylevel,num)
select partname,'1000-3000元之间',count(*)from table1 where money>1000 and money<=3000
group by partname; insert into temp_partinfo(partname,moneylevel,num)
select partname,'3000元以上',count(*)from table1 where money>1000
group by partname; OPEN o_article
FOR
SELECT partname,money,num
FROM temp_partinfo;
commit;
END;
CREATE OR REPLACE PACKAGE GLOBAL_CURSOR
IS
type rct is ref cursor;
END;创建临时表
create global temporary table temp_partinfo
(
partname NUMBER(20),
moneylevel VARCHAR2(50) default '无',
num number(5) default 0
)
on commit preserve rows;CREATE OR REPLACE PROCEDURE part_proc (o_article OUT global_cursor.rct)
IS
BEGIN
delete from temp_partinfo;
insert into temp_partinfo(partname,moneylevel,num)
select partname,'1000以下',count(*)from table1 where money<=1000
group by partname; insert into temp_partinfo(partname,moneylevel,num)
select partname,'1000-3000元之间',count(*)from table1 where money>1000 and money<=3000
group by partname; insert into temp_partinfo(partname,moneylevel,num)
select partname,'3000元以上',count(*)from table1 where money>1000
group by partname; OPEN o_article
FOR
SELECT partname,money,num
FROM temp_partinfo;
commit;
END;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货