oracle 存储过程返回数组问题 求教
时间:2011-10-24
来源:互联网
create or replace procedure MSMonthData(currentyear in number ,allresult out countarray) is
红色部分提示必须声明标识符‘countarray’,郁闷啊,下面明明声明了啊
meterid char(32);
type c_cursor is ref cursor;
allmeterid c_cursor;
TYPE countarray IS TABLE OF float; -----这里不是声明了吗,求教
currentyearmax countarray := countarray();
currentyearmin countarray := countarray();
allresult countarray := countarray();
allmax float := 0;
allmin float := 0;
addyear varchar2(19);
begin
for x in 1..2 loop
for i in 1 .. 12 loop
addyear := (currentyear+1-x) || '-';
if i < 10 then
addyear := addyear || '0' || i || '-%';
else
addyear := addyear || i || '-%';
end if;
open allmeterid for
SELECT distinct tr.meter_id
FROM US_PHYSICAL_ADDRESS py, us_meter_info m, bs_treateddata tr
WHERE py.addr_id = m.addr_id
and m.addr_id = tr.addr_id
and py.addr_code like '_________';
loop
fetch allmeterid
into meterid;
exit when allmeterid%notfound;
--dbms_output.put_line(meterid);
currentyearmax.extend;
select max(treat.cplpoint_value)
into currentyearmax(i)
from bs_treateddata treat
where treat.meter_id = meterid
AND treat.treate_date like addyear
AND treat.colpoint_code = '1';
--dbms_output.put_line(currentyearmax(i));
if currentyearmax(i) is null then currentyearmax(i):=0;
end if;
allmax := allmax + currentyearmax(i);
--dbms_output.put_line(allmax);
currentyearmin.extend;
select min(treat.cplpoint_value)
into currentyearmin(i)
from bs_treateddata treat
where treat.meter_id = meterid
AND treat.treate_date like addyear
AND treat.colpoint_code = '1';
if currentyearmin(i) is null then currentyearmin(i):=0;
end if;
allmin := allmin + currentyearmin(i);
--dbms_output.put_line(allmin);
end loop;
allresult.extend;
allresult(i):=allmax-allmin;
dbms_output.put_line(allresult(i));
allmax:=0;
allmin:=0;
--for x in 1 .. 12 loop
--allresult.extend;
--allresult(x) := allmax - allmin;
--dbms_output.put_line(allresult(x));
--allmax := 0;
--allmin := 0;
--end loop;
close allmeterid;
end loop;
end loop;
end MSMonthData;
红色部分提示必须声明标识符‘countarray’,郁闷啊,下面明明声明了啊
meterid char(32);
type c_cursor is ref cursor;
allmeterid c_cursor;
TYPE countarray IS TABLE OF float; -----这里不是声明了吗,求教
currentyearmax countarray := countarray();
currentyearmin countarray := countarray();
allresult countarray := countarray();
allmax float := 0;
allmin float := 0;
addyear varchar2(19);
begin
for x in 1..2 loop
for i in 1 .. 12 loop
addyear := (currentyear+1-x) || '-';
if i < 10 then
addyear := addyear || '0' || i || '-%';
else
addyear := addyear || i || '-%';
end if;
open allmeterid for
SELECT distinct tr.meter_id
FROM US_PHYSICAL_ADDRESS py, us_meter_info m, bs_treateddata tr
WHERE py.addr_id = m.addr_id
and m.addr_id = tr.addr_id
and py.addr_code like '_________';
loop
fetch allmeterid
into meterid;
exit when allmeterid%notfound;
--dbms_output.put_line(meterid);
currentyearmax.extend;
select max(treat.cplpoint_value)
into currentyearmax(i)
from bs_treateddata treat
where treat.meter_id = meterid
AND treat.treate_date like addyear
AND treat.colpoint_code = '1';
--dbms_output.put_line(currentyearmax(i));
if currentyearmax(i) is null then currentyearmax(i):=0;
end if;
allmax := allmax + currentyearmax(i);
--dbms_output.put_line(allmax);
currentyearmin.extend;
select min(treat.cplpoint_value)
into currentyearmin(i)
from bs_treateddata treat
where treat.meter_id = meterid
AND treat.treate_date like addyear
AND treat.colpoint_code = '1';
if currentyearmin(i) is null then currentyearmin(i):=0;
end if;
allmin := allmin + currentyearmin(i);
--dbms_output.put_line(allmin);
end loop;
allresult.extend;
allresult(i):=allmax-allmin;
dbms_output.put_line(allresult(i));
allmax:=0;
allmin:=0;
--for x in 1 .. 12 loop
--allresult.extend;
--allresult(x) := allmax - allmin;
--dbms_output.put_line(allresult(x));
--allmax := 0;
--allmin := 0;
--end loop;
close allmeterid;
end loop;
end loop;
end MSMonthData;
作者: yyy269954107 发布时间: 2011-10-24
必须显示建立一个 type:
create type ....
这样才能在存储过程里面用
create type ....
这样才能在存储过程里面用
作者: guostong 发布时间: 2011-10-25
相关阅读 更多
热门阅读
-
office 2019专业增强版最新2021版激活秘钥/序列号/激活码推荐 附激活工具
阅读:74
-
如何安装mysql8.0
阅读:31
-
Word快速设置标题样式步骤详解
阅读:28
-
20+道必知必会的Vue面试题(附答案解析)
阅读:37
-
HTML如何制作表单
阅读:22
-
百词斩可以改天数吗?当然可以,4个步骤轻松修改天数!
阅读:31
-
ET文件格式和XLS格式文件之间如何转化?
阅读:24
-
react和vue的区别及优缺点是什么
阅读:121
-
支付宝人脸识别如何关闭?
阅读:21
-
腾讯微云怎么修改照片或视频备份路径?
阅读:28