ways to invoke procedure in Oracle

/*

Sample procedure:

create or replace procedure runbyparmeters (isal in emp.sal%type,

sname out varchar,sjob in out varchar)

2 as icount number;

3 begin

4 select count(*) into icount from emp where sal>isal and job=sjob;

5 if icount=1 then

6 ....

9 else

10 ....

12 end if;

13 exception

14 when too_many_rows then

15 DBMS_OUTPUT.PUT_LINE('返回值多于1行');

16 when others then

17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');

18 end;

*/

--call procedure in oracle

--1. first way

declare

realsal emp.sal%type;

realname varchar(40);

realjob varchar(40);

begin

realsal:=1100;

realname:='';

realjob:='CLERK';

runbyparmeters(realsal,realname,realjob); --必须按顺序

DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);

END;

--2. second way

declare

realsal emp.sal%type;

realname varchar(40);

realjob varchar(40);

begin

realsal:=1100;

realname:='';

realjob:='CLERK';

runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); --指定值对应变量顺序可变

DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);

END;