oracle cursor问题
declare
cursor cr1 is select Avgsalary
from (select E.grade,
E.TestLevel,
(select AA * 2 BB
from (select avg(Salarys) AA
from andy_shen_salary_information S)) Avgsalary
from andy_shen_emp_infomation E
where E.GRADE >= 5
and E.Grade <= 7
and E.TestLevel > 4);
i cr1%rowtype;
begin
open cr1;
for i in cr1 loop
select decode(AA,' ',i.Avgsalary),Salarys,salarymonth,Id from (select A.*, ' ' AA from andy_shen_salary_information A where Id in
(select empid from andy_shen_emp_infomation E where E.GRADE >= 5 and E.Grade <= 7 and E.Testlevel > 4)
and A.SALARYMONTH=6) where AA =' ';
end loop;
close cr1;
end;
这段语句报ora-06550:line-22,column 4:
pls 00428:an INTO clause is expected in this SELECT statement的错误
求解!!!! Oracle Cursor select
[解决办法]
单独的select语句需要跟into配合。。基本常识了
[解决办法]
这个查询返回是多行的记录
bulk collect
------解决方案--------------------
你以前是SQL SERVER ORACLE select 必须和INTO 一起使用 但select的结果没有或者为多条都会报错,如果返回多行,可以用游标获取结果,或者用集合元素存值
[解决办法]
在select into语句中使用bulk collect
代码如下:
DECLARE
TYPE sallist IS TABLE OF employees.salary%TYPE;
sals sallist;
BEGIN
SELECT salary BULK COLLECT INTO sals
FROM employees
where rownum< 1000--接下来使用集合中的数据
END;