+ -
当前位置:首页 → 问答吧 → oracle 存储过程返回数组问题 求教

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;

作者: yyy269954107   发布时间: 2011-10-24

必须显示建立一个 type:

create type ....

这样才能在存储过程里面用

作者: guostong   发布时间: 2011-10-25

热门下载

更多