不知道是不是你想要的。Procedure? Function?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; /sql>set serveroutput on sql>exec mytest(2000);
你的程序基本正確﹕修改如下 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; 其中如何 通過傳出參數v_outsal﹐將值傳回給存儲過程
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;
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;
/sql>set serveroutput on
sql>exec mytest(2000);
改成
dbms_output.put_line(myrec.deptno ¦ ¦' ' ¦ ¦myrec.ename ¦ ¦' ' ¦ ¦to_char(myrec.sal));
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; 其中如何 通過傳出參數v_outsal﹐將值傳回給存儲過程
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;