存储过程中的错误

[j284197330]

set serveroutput on size 10000;
create or replace procedure set_sal(p_empno emp.empno%type)
as
p_sal emp.sal%type;
p_deptno emp.deptno%type;
begin
select sal , deptno into p_sal,p_deptno from emp where empno=p_empno;
case p_deptno
when 10 then update emp set p_sal=p_sal+150;
when 20 then update emp set p_sal=p_sal+200;
when 30 then update emp set p_sal=p_sal+250;
else set p_sal=p_sal+300;
end case;
commit;
exception
when no_data_found then
dbms_output.put_line('the department doesn''t exists!');
end set_sal;


警告：Warning: compiled but with compilation errors

[tangren]

你的业务逻辑是要做什么

create or replace procedure set_sal(p_empno emp.empno%type)
as
p_sal emp.sal%type;
p_deptno emp.deptno%type;
begin
select sal , deptno into p_sal,p_deptno from emp where empno=p_empno;
case p_deptno
when 10 then update emp set sal=p_sal+150; --update中set后面应该是要被更新的列名
when 20 then update emp set sal=p_sal+200; --同上
when 30 then update emp set sal=p_sal+250; --同上
else update emp set sal=p_sal+300; --少了update emp
end case;
commit;
exception
when no_data_found then
dbms_output.put_line('the department doesn''t exists!');
end set_sal;

[vber1010]

还是老兵厉害，我还以为不能执行dml语句。原来是可以的啊。。。

[秋雨飘落]

else set p_sal=p_sal+300;

这里有ELSE，你的IF呢

[shadow1986a]

else set p_sal=p_sal+300;

这一句有问题。根据需要　要么改成 else update emp set p_sal=p_sal+300; 要么是
else p_sal:=p_sal+300;

[j284197330]

set serveroutput on size 10000;
create or replace procedure set_sal(p_empno emp.empno%type)
as
p_sal emp.sal%type;
p_deptno emp.deptno%type;
begin
select sal , deptno into p_sal,p_deptno from emp where empno=p_empno;
case p_deptno
when 10 then update emp set p_sal=p_sal+150;
when 20 then update emp set p_sal=p_sal+200;
when 30 then update emp set p_sal=p_sal+250;
else update emp set p_sal=p_sal+300;
end case;
commit;
exception
when no_data_found then
dbms_output.put_line('the department doesn''t exists!');
end set_sal;
改完后还是同样的错误

[vber1010]

case when 里不能执行dml。
你用if语句替代试试

[jym2002]

一条SQL

update emp set p_sal=decode(deptno,10,p_sal+150,20,p_sal+200,30,p_sal+250,p_sal+300) where empno=p_empno;

[vber1010]

case 要返回值的可以这样，用上面一条或者这样：
create or replace procedure set_sal(p_empno emp.empno%type) as
p_sal emp.sal%type;
p_deptno emp.deptno%type;
begin
select sal,
case deptno
when 10 then
sal + 150
when 20 then
sal + 200

when 30 then
sal + 250
else
sal + 300
end
into p_sal, p_deptno
from emp
where empno = p_empno;

update emp ....

commit;
exception
when no_data_found then
dbms_output.put_line('the department doesn''t exists!');
end set_sal;

[j284197330]

各位高手可以加我QQ吗？
以后一起学习，可以吗？？

284197330 我的QQ

[kingtiy]

set p_sal=p_sal+150;--sqlserver的赋值语句
 p_sal:=p_sal+150;--oracle的赋值语句写法.

[jielovehuan]

楼上正解