开发Oracle 函数

╰+攻爆jí腚メ 2021-12-22 05:13 222阅读 0赞

函数用于返回特定的数据。如果在应用程序中，经常需要执行SQL语句来返回特定数据，那么可以基于这些操作建立特定的函数。建立函数的语法如下：

CREATE [OR REPLACE]  FUNCTION function_name
          (argument1 [model] datatype1,
            argument2 [model] datatype2,
            ....)
       RETURN datatype
     IS|AS
         PL/SQL  Block;

如上所示，function\_name用于指定函数的名称；argument1、argument2等则用于指定函数的参数，注意当指定参数的数据类型时，不能指定其长度；RETURN子句用于指定函数返回值的数据类型；IS|AS用于开始一个PLS/QL块。**注意，当建立函数时，在函数头部必须要带有RETURN子句，在函数体内必须要包含一条RETURN语句**。另外，当建立函数时，既可以指定输入参数（IN），也可以指定输出参数（OUT），以及输入输出参数（IN OUT）。下面通过实例说明建立函数，以及使用各种参数模式的方法。

## 1、建立函数，不带任何参数 ##

当建立函数时，既可以带有参数，也可以不带参数。下面以建立用于显示当前数据库用户名的函数为例，说面建立该种函数的方法。示例如下：

CREATE OR REPLACE FUNCTION get_user
      RETURN VARCHAR2
    IS
       v_user VARCHAR2(100);
    BEGIN
    	  SELECT username INTO v_user FROM user_users;
    	  RETURN v_user;
    END;

建立了函数get\_user之后，就可以在应用程序中调用该函数了。因为函数有返回值，所以它只能作为表达式的一部分来调用。调用示例如下：

**示例一：使用变量接收函数返回值**

SQL> var v1 varchar2(100);
    SQL> exec :v1:=get_user;
     
    PL/SQL procedure successfully completed
    v1
    ---------
    SCOTT
     
    SQL> print v1;
    v1
    ---------
    SCOTT

**示例二：在SQL语句中直接调用函数**

SQL> select get_user from dual;
     
    GET_USER
    --------------------------------------------------------------------------------
    SCOTT

**示例三：使用包DBMS\_OUTPUT调用函数**

SQL> set serveroutput on;
    SQL> exec dbms_output.put_line('当前数据库用户:'||get_user);
     
    当前数据库用户:SCOTT
     
    PL/SQL procedure successfully completed

## 2、建立函数：带有IN参数 ##

当建立函数时，通过使用输入参数，可以将应用程序的数据传递到函数中，最终通过执行函数可以将结果返回到应用程序中。下面以建立返回雇员工资的函数为例，说明建立带有输入参数函数的方法。示例如下：

CREATE OR REPLACE FUNCTION  get_sal(name IN VARCHAR2)
    RETURN NUMBER
    AS
      v_sal emp.sal%TYPE;
    BEGIN
        SELECT sal INTO v_sal  FROM emp
          WHERE upper(ename)=upper(name);
          RETURN v_sal;
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
          raise_application_error(-20000,'该雇员不存在');       
    END;

在建立了get\_sal之后，就可以在应用程序中调用该函数了。调用该函数的示例如下：

**示例一：输入存在的雇员**

SQL> var sal NUMBER; SQL> exec :sal:=get_sal('scott'); PL/SQL procedure successfully completed sal --------- 1200 SQL> print sal; sal --------- 1200

**示例二：输入不存在的雇员**

SQL> exec :sal:=get_sal('mary');
     
    begin :sal:=get_sal('mary'); end;
     
    ORA-20000: 该雇员不存在
    ORA-06512: 在 "SCOTT.GET_SAL", line 11
    ORA-06512: 在 line 2

##    3、建立函数：带有OUT参数 ##

一般情况下，函数只需要返回单个数据。如果希望使用函数同同时返回多个数据，例如同时返回雇员名和工资那么就需要使用输出参数了。为了在函数中使用输出参数，必须要指定OUT参数模式。下面以建立用于返回雇员所在部门名和岗位的函数为例，说明建立带有OUT参数函数的方法。示例如下：

CREATE OR REPLACE FUNCTION get_info
      (name VARCHAR2,title OUT VARCHAR2)
      RETURN VARCHAR2
    AS
      deptname dept.dname%TYPE;
    BEGIN 
       SELECT a.job,b.dname INTO title,deptname
         FROM emp a,dept b
         WHERE a.deptno=b.deptno
         AND upper(a.ename)=upper(name);     
        RETURN  deptname;
    EXCEPTION
         WHEN NO_DATA_FOUND  THEN
          raise_application_error(-20000,'该雇员不存在');   
    END;

当建立了函数get\_info 后，就可以在应用程序中调用该函数了。注意，因为该函数，带有OUT参数，所以不能在SQL语句中，调用该函数，而必须要定义变量接收OUT参数和函数的返回值。在SQL\*PLUS中调用函数get\_info的示例如下：

SQL> var job varchar2(20)
    SQL> var dname varchar2(20)
    SQL> exec :dname:=get_info('scott',:job);
    PL/SQL procedure successfully completed
    dname
    ---------
    ANALYST
    job
    ---------
    ANALYST

如上所示：除了输出函数自定义返回的值，也返回了OUT参数的返回值。

## 4、建立IN OUT参数 ##

建立函数时，不仅可以指定IN和OUT参数，也可以指定IN OUT参数。IN OUT 参数，也被成为输入输出参数。使用这种参数时，在调用函数之前需要通过变量给该种参数传递数据，在调用结束之后Oracle会将函数的部分结果通过该变量传递给应用程序。下面以计算两个数值相除的结果的函数Result为例，说明在函数中使用IN OUT参数的方法。示例如下：

CREATE OR REPLACE FUNCTION result_num
    (num1 NUMBER,num2 IN OUT NUMBER)
    RETURN NUMBER
    IS
      v_result NUMBER(6);
      v_remainder NUMBER;
    BEGIN 
        v_result:=num1/num2;
        v_remainder:=MOD(num1,num2);  
        num2:=v_remainder;
        RETURN v_result;
     EXCEPTION 
          WHEN 
             ZERO_DIVIDE  THEN
              raise_application_error(-20000,'不能除0');
     END;

注意，因为该函数带有IN OUT 参数，所以不能再SQL语句中调用该函数，而必须使用变量为IN OUT参数传递数值并接收数据。另外，还需要定义变量接收函数返回值。调用该函数的示例如下：

SQL> var result2 number;
    SQL> var result1 number;
    SQL> var result2 number;
    SQL> exec :result2:=30;
     
    PL/SQL procedure successfully completed
    result2
    ---------
    30
     
    SQL> exec :result1:=result_num(100,:result2);
     
    PL/SQL procedure successfully completed
    result1
    ---------
    3
    result2
    ---------
    10