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
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
else p_sal:=p_sal+300;
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;
改完后还是同样的错误
你用if语句替代试试
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;
以后一起学习,可以吗??284197330 我的QQ
set p_sal=p_sal+150;--sqlserver的赋值语句
p_sal:=p_sal+150;--oracle的赋值语句写法.
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;