一个关于存储过程的问题？

Question

请问这段存储过程错在那里呀？CREATE or replace PROCEDURE wa_avg_real 
(lluserid in char,
waterreal out char)
AS
oldcopydate1 date;
oldcopydate2 date;
oldcopydate3 date;
llwaterreal1 char;
llwaterreal2 char;
llwaterreal3 char;
newcopydate date;
llreal      number;
llreal1      number;
llreal2      number;
llreal3      number;
Begin 
select inputdate 
into newcopydate
from copydate; 
select copydate,waterreal
into oldcopydate1,llwaterreal1
from waterpay
where userid=lluserid and rownum<=1
ORDER BY year DESC, UseMonth DESC ;

select copydate,waterreal
into oldcopydate2,llwaterreal2
from waterpay
where userid=lluserid and rownum=2
ORDER BY year DESC, UseMonth DESC ; select copydate,waterreal
into oldcopydate3,llwaterreal3
from waterpay
where userid=lluserid and rownum=3
ORDER BY year DESC, UseMonth DESC;
llreal1=to_number(llwaterreal1)/(to_date(newcopydate) - to_date(oldcopydate1));
llreal2=to_number(llwaterreal2)/(to_date(oldcopydate1) - to_date(oldcopydate2));
llreal3=to_number(llwaterreal2)/(to_date(oldcopydate2) - to_date(oldcopydate3))
select greatest(llreal1,llreal2,llreal3) 
into llreal
from dual;
llreal=llreal*(to_date(newcopydate) - to_date(oldcopydate1));
waterreal:=to_char(round(llreal));
end;

Answer 1

rownum用法错误，只能用rownum<n的形式；
把下列语句
select copydate,waterreal
into oldcopydate3,llwaterreal3
from waterpay
where userid=lluserid and rownum=3
改成：
select copydate,waterreal
into oldcopydate3,llwaterreal3
from (select rownum rn,copydate,waterreal from waterpay) 
where userid=lluserid and rn=3

Answer 2

不对吧？rownum 可以这么用呀！我在SQL PLUS实验过，而且我照你写的该了，还是报编译过程中有错误，还有人知道吗？

Answer 3

用是可以用,只是查不到值,你的过程触发了:NO_DATA_FOUND
在EXCEPTION里面处理一下吧.

Answer 4

llreal1 := to_number(llwaterreal1)/(to_date(newcopydate) - to_date(oldcopydate1));
llreal2 := to_number(llwaterreal2)/(to_date(oldcopydate1) - to_date(oldcopydate2));
llreal3 := to_number(llwaterreal2)/(to_date(oldcopydate2) - to_date(oldcopydate3));

Answer 5

那写成这样可以得到值吗？
select copydate,waterreal
into oldcopydate3,llwaterreal3
from (select rownum rn,copydate,waterreal from waterpay) 
where userid=lluserid and rn=3
                  ORDER BY year DESC, UseMonth DESC;

Answer 6

select copydate,waterreal
from (select rownum rn,copydate,waterreal,year,usemonth,userid from waterpay) 
where userid=123456 and rn=3
                  ORDER BY year DESC, UseMonth DESC;
写成这样可以在SQL PLUS中通过，
请问可以得到值吗？

Answer 7

llreal1=to_number(llwaterreal1)/(to_date(newcopydate) - to_date(oldcopydate1));
llreal2=to_number(llwaterreal2)/(to_date(oldcopydate1) - to_date(oldcopydate2));
llreal3=to_number(llwaterreal2)/(to_date(oldcopydate2) - to_date(oldcopydate3))
number 不可以去除日期型吧!!
而且也可以看一写你的SELECT 语句检索出来的值是否唯一!

Answer 8

现在按下面写编译通过了！不知道能不能正确执行！CREATE or replace PROCEDURE wa_avg_real 
(lluserid in char,
waterreal out char)
AS
oldcopydate1 date;
oldcopydate2 date;
oldcopydate3 date;
llwaterreal1 char;
llwaterreal2 char;
llwaterreal3 char;
newcopydate date;
llreal      number;
llreal1      number;
llreal2      number;
llreal3      number;
Begin 
select inputdate 
into newcopydate
from copydate;                   select copydate,waterreal
into oldcopydate1,llwaterreal1
from waterpay
where userid=lluserid and rownum<=1
ORDER BY year DESC, UseMonth DESC ;

select copydate,waterreal
into oldcopydate2,llwaterreal2
from (select rownum rn,copydate,waterreal,year,usemonth,userid from waterpay) 
where userid=lluserid and rn=2
ORDER BY year DESC, UseMonth DESC ;

select copydate,waterreal
into oldcopydate3,llwaterreal3
from (select rownum rn,copydate,waterreal,year,usemonth,userid from waterpay) 
where userid=lluserid and rn=3
ORDER BY year DESC, UseMonth DESC;

llreal1:=to_number(llwaterreal1)/(to_date(newcopydate) - to_date(oldcopydate1));

llreal2:=to_number(llwaterreal2)/(to_date(oldcopydate1) - to_date(oldcopydate2));

llreal3:=to_number(llwaterreal2)/(to_date(oldcopydate2) - to_date(oldcopydate3));

select greatest(llreal1,llreal2,llreal3) 
into llreal
from dual;
llreal:=llreal*(to_date(newcopydate) - to_date(oldcopydate1));
waterreal:=to_char(round(llreal));
end;