Oracle存储过程及函数

示例所要用到的表结构及相关数据：

 1 SQL> desc emp;
 2 Name     Type         Nullable Default Comments 
 3 -------- ------------ -------- ------- -------- 
 4 EMPNO    NUMBER(4)                              
 5 ENAME    VARCHAR2(10) Y                         
 6 JOB      VARCHAR2(9)  Y                         
 7 MGR      NUMBER(4)    Y                         
 8 HIREDATE DATE         Y                         
 9 SAL      NUMBER(7,2)  Y                         
10 COMM     NUMBER(7,2)  Y                         
11 DEPTNO   NUMBER(2)    Y                         
12  
13 SQL> select * from emp;
14  
15 EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
16 ----- ---------- --------- ----- ----------- --------- --------- ------
17  7369 SMITH      CLERK      7902 1980/12/17     880.00               20
18  7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
19  7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
20  7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
21  7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
22  7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
23  7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
24  7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
25  7839 KING       PRESIDENT       1981/11/17    5000.00               10
26  7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
27  7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
28  7900 JAMES      CLERK      7698 1981/12/3      950.00               30
29  7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
30  7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
31  
32 14 rows selected

创建过程语句如下：

　　CREATE [OR REPLACE] PROCEDURE procedure_name

      [(parameter_name [IN | OUT | IN OUT] type [, ...])]

      {IS | AS}

      BEGIN

     　　procedure_body

      END procedure_name;

• OR REPLACE 说明如果过程已经存在，则替换已有的过程
• procedure_name 指定过程名。
• parameter_name 指定传递给过程的函数名。可以向一个过程传递多个参数。
• IN | OUT | IN OUT 定义了参数的模式。每一个参数都可以选择下列模式之一:
  • IN 是参数的默认模式。这种模式定义的参数在程序运行的时候已经具有值，在过程体中不能改变IN参数的值。
  • OUT模式定义的参数只在过程体内部赋值。
  • IN OUT模式定义的参数当过程运行是可能已经具有值，但是在过程体中也可以修改此值。
• type指定参数的类型
• procedure_body包含过程的实际代码。

eg: 根据传入的员工姓名将对应员工的薪水进行调整，调整规则为如果有传入薪水值则以传入值为标准，如果没有传入薪水值则在原来的幅度上上涨10%，最后返回员工的boss的姓名。 

 1 create or replace procedure procedure_demo (
 2        vname in varchar2,
 3        vbossname out varchar2,
 4        vsal in out number
 5 )
 6 as
 7     v_count number;
 8 begin
 9   select count(1) into v_count from emp t   where t.ename=vname;
10   
11   /*如果输入姓名有误则返回*/
12   if v_count = 0 then
13     dbms_output.put_line('输入姓名有误.');
14     return;
15    end if;
16 
17    /*如果提供了salary则将对应员工的薪水改为传入的薪水值否则在原来的基础上添加10%*/
18    if vsal is null then
19      update emp t set t.sal=t.sal*1.1 where t.ename=vname;
20     else
21       update emp t set t.sal=vsal where t.ename=vname;
22     end if;
23 
24     select case when t2.ename is null then '自己' else t2.ename end , t1.sal into vbossname, vsal from emp t1, emp t2 where t1.mgr=t2.empno(+) and t1.ename=vname ;
25 
26     dbms_output.put_line('员工' || vname || '薪水是' || vsal || '他的boss是' || vbossname);
27     
28     commit;
29 
30 end  procedure_demo;

调用过程

    CALL procure_name(parameter1, parameter2,...);    

 1 SQL> var ina varchar2;
 2 SQL> var outb varchar2;
 3 SQL> var inoutc number;
 4 SQL> exec :ina := 'SMITH';
 5  
 6 PL/SQL procedure successfully completed
 7 ina
 8 ---------
 9 SMITH
10  
11 SQL> exec :inoutc := 800;
12  
13 PL/SQL procedure successfully completed
14 inoutc
15 ---------
16 800
17  
18 SQL> call procedure_demo(vname => :ina, vbossname => :outb, vsal => :inoutc);
19  
20 Method called
21 ina
22 ---------
23 SMITH
24 outb
25 ---------
26 FORD
27 inoutc
28 ---------
29 800
30  
31 SQL> select * from emp t where t.ename='SMITH';
32  
33 EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
34 ----- ---------- --------- ----- ----------- --------- --------- ------
35  7369 SMITH      CLERK      7902 1980/12/17     800.00               20
36  

删除过程

     DROP PROCEDURE procedure_name;

 eg:    

  
2 SQL> drop procedure procedure_demo;
3  
4 Procedure dropped
5  
6 SQL> view procedure_demo;
7 Unknown object: procedure_demo
8  
9 SQL> 

创建函数语句如下：

     　　CREATE [OR REPLACE] FUNCTION fuction_name

　　　　[(parameter_name [IN | OUT  | IN OUT] type [, ...])]

　　　　RETURN type

　　　　{IS | AS}

　　　　BEGIN

　　　　　　function_body

　　　　END function_name;

• OR REPLACE表示如果函数已经存在，则替换现有的函数。
• function_name指定函数名。
• parameter_name指定传递给函数的参数名。
• IN | OUT | IN OUT指定参数的模式。
• type 指定参数的类型。
• function_body 包含函数的实际代码。函数体不像过程体，它必须有返回值，其类型在RETURN子句中指定。

eg:查出emp表中的总人数。

 1 create or replace function tarbitrary return number
 2 
 3 as 
 4  v_count number;
 5 begin
 6    select count(1) into v_count from emp;
 7    
 8    return v_count;
 9 end;
10 /

   

 调用函数

    select function_name([parmater1, parameter2, ...]) from dual; 

 SQL> select tarbitrary() from dual;
2  
3 TARBITRARY()
4 ------------
5           14

删除函数

   DROP FUNCTION function_name; 

 SQL> drop function tarbitrary;
2  
3 Function dropped