以下是代码!
1 declare
2 type empcursor is ref cursor;
3 emp1 empcursor;
4 avgSal number;
5 deptno number;
6 empid number;
7 empSalary number;
8 begin
9 deptno:=&deptno;
10 if deptno=2 then
11 select avg(salary) into avgSal from emp where dept=2;
12 open emp1 for select id salary from emp where dept=2;
13 loop
14 fetch emp1 into empid,empSalary;
15 exit when emp1%notfound;
16 if empSalary>avgSal then
17 update emp set salary=salary-100 where id=empid;
18 elsif empSalary
19 update emp set salary=salary+100 where id=empid;
20 end if;
21 end loop;
22 if deptno=3 then
23 select avg(salary) into avgSal from emp where dept=3;
24 open emp1 for select id salary from emp where dept=3;
25 loop
26 fetch emp1 into empid,empSalary;
27 exit when emp1%notfound;
28 if empSalary>avgSal then
29 update emp set salary=salary-100 where id=empid;
30 elsif empSalary
31 update emp set salary=salary+100 where id=empid;
32 end if;
33 end loop;
34 end if;
35 end if;
36* end;
QL> /
输入 deptno 的值: 2
原值 9: deptno:=&deptno;
新值 9: deptno:=2;
eclare
第 1 行出现错误:
RA-06504: PL/SQL: 结果集变量或查询的返回类型不匹配
RA-06512: 在 line 14