Oracle 存储过程、函数的创建和调用

àì夳堔傛蜴生んèń 2022-04-04 08:11 815阅读 0赞

### 一、Oracle 创建和调用存储过程 ###

**1、基本语法**

create or replace procedure update_emp_sal
    (Name in out type,
     Name in out type, ...
    ) is
    begin
      
    end update_emp_sal;

可能不了解 in out 参数，  
**Oracle 过程中定义了 in / out / in out 三种参数模式。**  
（1）in 就是传入但存储过程里面处理的参数，传进去供查询语句使用。  
（2）out 参数就是返回值的参数。返回查询的结果集。  
（3）in out 输入输出模式：能接收传入的实参值；在子程序内部可以修改； 可以输出（必须用实参变量调用）

in 参数就像 c++ 语言里函数一般的参数那样，而 out 函数就像 c++ 里函数的引用类型参数一样，不知道我比喻是否正确，请高手指点。

**如果不知道参数类型，可以参考：**

create or replace procedure DEPT_AVG_SAL(p_deptno emp.dept_id%TYPE)
    as v_sal emp.salary%TYPE;
    
    begin
          Select avg(salary)  into v_sal
          from emp
        where dept_id = p_deptno;
          dbms_output.put_line(p_deptno || ' : ' || 'average salary is:' || v_sal);
    
    end DEPT_AVG_SAL;

**2、写一个简单的例子修改 emp 表的 ename 字段：**

create or replace procedure update_emp
    (
    v_empno  varchar2,
    v_ename  varchar2
    ) is
    begin
    
    update emp set ename=v_ename where empno=v_empno;  
    
    end update_emp;

调用方法如下：

SQL>exec  update_emp('7935','test');

**2、有返回值的存储过程**

就写一个简单的返回 empno=7935 的 sal 值

create or replace procedure emp_out_sal
     (
     v_empno in varchar2,
     v_sal out number
     ) is
     vsal number(7,2);
     begin
     
     select sal into vsal from emp where empno=v_empno;
     v_sal:=vsal;
     end;

调用有返回值的过程方法（1）

SQL> var vsal number
    SQL> exec emp_out_sal('7935',:vsal);
    
    PL/SQL procedure successfully completed
    vsal

调用有返回值的过程方法（2）

SQL> var vsal number
    SQL> call emp_out_sal('7935',:vsal);
    
    Method called
    vsal

### 二、Oracle 创建和调用函数（function） ###

**1、基本语法规则如下：**

create or replace function (Name in type, Name in type, ...) return number is
      Result number;
    begin
      
      return (Result);
    end ;

**2、写一个简单的查询例子查询出 empno=7935 的 sal 值**

create or replace function ret_emp_sal(v_ename varchar2)
    return number
    is
    v_sal number(7,2);
    begin
    select nvl(sal,0) into v_sal from emp where lower(ename)=lower(v_ename);
    return v_sal;
    end;

调用此函数：

SQL> var vsla number
    SQL> call ret_emp_sal('7935') into :vsal;
    
    Method called
    vsal