过程或者函数参数的传递模式有3中:in、out与in out三种。
out模式的参数就是作为结果输出的参数。
---创建一个使用out参数模式过程:
--切换到Scott用户:
scott@PROD>show user
USER is "SCOTT"
--查看emp表的表结构与数据:
--表结构:
scott@PROD>desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
--表记录:
scott@PROD>select empno,ename,job,sal,deptno
2 from emp;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 20
7499 ALLEN SALESMAN 1600 30
7521 WARD SALESMAN 1250 30
7566 JONES MANAGER 2975 20
7654 MARTIN SALESMAN 1250 30
7698 BLAKE MANAGER 2850 30
7782 CLARK MANAGER 2450 10
7788 SCOTT ANALYST 3000 20
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 1500 30
7876 ADAMS CLERK 1100 20
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7900 JAMES CLERK 950 30
7902 FORD ANALYST 3000 20
7934 MILLER CLERK 1300 10
14 rows selected.
--通过员工号,根据不同工作来确定该员工加薪,president加1000,manager加500,
salesman加300,其他加100,最后并显示员工的姓名与工资,使用in与out参数:
scott@PROD>create or replace procedure display_wage(
2 v_id in emp.empno%type,
3 v_name out emp.ename%type,
4 v_wage out emp.sal%type)
5 as
6 v_work emp.job%type;
7 begin
8 select job into v_work
9 from emp where empno=v_id;
10 case
11 when v_work='PRESIDENT' then
12 update emp set sal = sal+1000
13 where empno = v_id;
14 when v_work ='MANAGER' then
15 update emp set sal = sal+500
16 where empno = v_id;
17 when v_work ='SALESMAN' then
18 update emp set sal = sal+300
19 where empno = v_id;
20 else
21 update emp set sal = sal+100
22 where empno = v_id;
23 end case;
24 select ename,sal into v_name,v_wage
25 from emp where empno = v_id;
26 end display_wage;
27 /
Procedure created.
#过程已经成功创建。
---调用过程:
scott@PROD>call display_wage(7839,:v_name,:v_wage);
Call completed.
scott@PROD>variable v_name varchar2(10)
scott@PROD>variable v_wage number
scott@PROD>call display_wage(7839,:v_name,:v_wage);
Call completed.
scott@PROD>print v_name v_wage
V_NAME
--------------------------------
KING
V_WAGE
----------
6000
---执行过程后与直接SQL查询对比:
scott@PROD>select ename,job,sal
2 from emp where empno=7839;
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 6000
#这个显然是执行过程后作了更新的数据。
--更新之前的数据:
7839 KING PRESIDENT 5000 10
---再次查看另外员工号为7698员工的姓名与工资:
--执行过程更新后的数据:
scott@PROD>execute display_wage(7698,:v_name,:v_wage);
PL/SQL procedure successfully completed.
scott@PROD>print v_name v_wage
V_NAME
--------------------------------
BLAKE
V_WAGE
----------
3350
---回滚与执行过程前的对比:
Rollback complete.
scott@PROD>select ename,job,sal
2 from emp where empno=7698;
ENAME JOB SAL
---------- --------- ----------
BLAKE MANAGER 2850
#可以看出增加了过程中执行的添加500元。
#因为之前已经明确过v_name与v_wage两个参数变量。
scott@PROD>variable v_name varchar2(10)
scott@PROD>variable v_wage number
--本例子中,同时使用了in与out两种模式,这里作了一个很好的对比。
in模式就是通过输入参数值给过程调用,而out就是过程通过调用过程把
结果传递给形参:v_name,:v_wage,然后形参再传递给实参 v_name v_wage,
而这两个就是作为输出结果。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2128471/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2128471/