Oracle存储过程及函数

2022-10-18 14:40:48

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

 

 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:    

    1  
    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; 

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

     

    删除函数

       DROP FUNCTION function_name; 

    1 SQL> drop function tarbitrary;
    2  
    3 Function dropped

     

    Oracle存储过程及函数

    上一篇：SQL字符串转换为数组


    下一篇：spoon kettle连接数据库失败解决方法