Oracle—存储函数（转）

最新推荐文章于 2019-12-29 12:05:23 发布

Nice-man

最新推荐文章于 2019-12-29 12:05:23 发布

阅读量492

点赞数 1

分类专栏：软件技术-OracleDB 文章标签： oracle 存储函数

软件技术-OracleDB 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

存储过程和存储函数—简介:

存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比，存储过程有很多优点，具体归纳如下：

* 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。
* 存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。
* 存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。
* 像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。

存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。

一、创建存储函数：

创建函数，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建存储函数的语法和创建存储过程的类似，即：

CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型...)]
RETURN 数据类型
{AS|IS}
[说明部分]
BEGIN
可执行部分
RETURN (表达式)
[EXCEPTION
    错误处理部分]
END [函数名];

其中，参数是可选的，但只能是IN类型(IN关键字可以省略)。
在定义部分的RETURN 数据类型，用来表示函数的数据类型，也就是返回值的类型，此部分不可省略。
在可执行部分的RETURN(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句，但只有一个RETURN语句会被执行，一旦执行了RETURN语句，则函数结束并返回调用环境。

二、删除存储函数:

一个存储函数在不需要时可以删除，但删除的人应是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人。其语法如下：

DROP FUNCTION 函数名；

三、重新编译存储函数:

重新编译一个存储函数时，编译的人应是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。重新编译一个存储函数的语法如下：

ALTER PROCEDURE 函数名 COMPILE；

函数的调用者应是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人，或是被函数的拥有者授予了函数执行权限的账户。

函数的引用和存储过程不同，函数要出现在程序体中，可以参加表达式的运算或单独出现在表达式中，其形式如下：

变量名:=函数名(...)

四、练习训练

【训练1】 创建一个通过雇员编号返回雇员名称的函数GET_EMP_NAME。

步骤1：登录SCOTT账户。
步骤2：在SQL*Plus输入区中输入以下存储函数并编译：

CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)  
RETURN VARCHAR2  
 AS  
V_ENAME VARCHAR2(10);  
BEGIN  
 SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;  
 RETURN(V_ENAME);  
EXCEPTION  
 WHEN NO_DATA_FOUND THEN  
  DBMS_OUTPUT.PUT_LINE('没有该编号雇员！');  
  RETURN (NULL);  
 WHEN TOO_MANY_ROWS THEN  
  DBMS_OUTPUT.PUT_LINE('有重复雇员编号！');  
  RETURN (NULL);  
 WHEN OTHERS THEN  
  DBMS_OUTPUT.PUT_LINE('发生其他错误！');  
  RETURN (NULL);  
END;

步骤3：调用该存储函数，输入并执行以下程序：

BEGIN  
  DBMS_OUTPUT.PUT_LINE('雇员7369的名称是：'|| GET_EMP_NAME(7369));  
  DBMS_OUTPUT.PUT_LINE('雇员7839的名称是：'|| GET_EMP_NAME(7839));  
END;

显示结果：

雇员7369的名称是：SMITH  
        雇员7839的名称是：KING  
        PL/SQL 过程已成功完成。

说明： 函数的调用直接出现在程序的DBMS_OUTPUT.PUT_LINE语句中，作为字符串表达式的一部分。如果输入了错误的雇员编号，就会在函数的错误处理部分输出错误信息。试修改雇员编号，重新运行调用部分。
存储过程和函数的查看
可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询当前用户的存储过程或函数的源代码，可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下：

DESCRIBE USER_SOURCE

结果为：

    名称                                      是否为空? 类型  
            ------------------------------------------------------------- ------------- -----------------------  
     NAME                                               VARCHAR2(30)  
     TYPE                                               VARCHAR2(12)  
     LINE                                               NUMBER  
     TEXT                                               VARCHAR2(4000)

说明：里面按行存放着过程或函数的脚本，NAME是过程或函数名，TYPE 代表类型(PROCEDURE或FUNCTION)，LINE是行号，TEXT 为脚本。