create or replace procedure mytest(p_ename varchar2 ,p_salary number, o_new_sal out number)
is
begin
update emp
set sal=sal+200
where sal <p_salary and and ename= p_ename;
dbms_output.enable(20000);
for myrec in (select deptno,ename,sal from emp where ename= p_ename)
dbms_output.putline(myrec.deptno ¦ ¦' ' ¦ ¦myrec.ename ¦ ¦' ' ¦ ¦to_char(myrec.sal));
o_new_sal :=myrec.sal;
end loop;
commit;
end;
/
然后再外面调用这个过程,从o_new_sal就能返回新的salary,如:
...
a varchar2(30):=null;
b number:=0;
c number:=0;
begin
...
a:='aaa';
b:=2000;
mytest(a,b,c);
--这里C就是新的salary了
...
end;
你的程序基本正確﹕修改如下
create or replace procedure mytest(v_sal in number,v_outsal out number)
is
begin
update emp
set sal=sal+200
where sal < v_sal;
dbms_output.enable(2000);
for myshow in (select deptno,ename,sal from emp order by deptno)
LOOP
dbms_output.put_line(myshow.deptno ||'*'|| myshow.ename ||'*'||to_char(myshow.sal));
end loop;
commit;
end mytest;
create or replace procedure mytest(p_salary number)
is
begin
update emp
set sal=sal+200
where sal<p_salary;
dbms_output.enable(20000);
for myrec in (select deptno,ename,sal from emp order by deptno)
dbms_output.putline(myrec.deptno||' '||myrec.ename||' '||to_char(myrec.sal));
end loop;
commit;
end;
/