PL/SQL 基础---函数、存储过程、包

PL/SQL 基础—函数、存储过程、包

在匿名块的基础上进行一定的封装得到函数、存储过程、包

---

函数(FUNCTION) – 有RETURN的程序块

定义格式

CREATE [OR REPLACE] FUNCTION [schema.]name [( parameter [, parameter ...] ) ]
   RETURN return_datatype  --返回值类型
   [AUTHID DEFINER | CURRENT_USER]
IS
   [declaration statements]

BEGIN
   executable statements

[EXCEPTION
   exception handler statements]

END [ name ];

调用方式

    1.SELECT FUN(N) FROM dual;
    2.在PL/SQL程序段中调用；

注：

在SQL语句中可以使用用户自定义的函数，那么这样的用户定义函数有一定的限制：

1. 只能用IN 模式的参数（不能有OUT, IN OUT 模式的参数）

2. 只能接收SQL数据类型的参数，不能接收PLSQL 中特有的参数（比如记录、 PLSQL内存表）

3. 函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型

4. 在SQL中使用的函数，其函数体内部不能有DML语句

5. 在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语句

6. 在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit， Rollback)

存储过程(PROCEDURE) – 无RETURN，功能比FUNCTION强大

CREATE [OR REPLACE] PROCEDURE [schema.]name [( parameter [, parameter ...] ) ]
   [AUTHID DEFINER | CURRENT_USER]
IS
   [declarations]

BEGIN
   executable statements

[ EXCEPTION
     exception handlers]

END [name];

注：

1. 返回参数通过 p OUT/IN OUT datatype 定义；

* 调用方式 *

1.在PL/SQL程序段中调用，需要提前声明返回值对应的变量；

包(PACKAGE) – 类似于C++中类的封装

包括两个部分：

包头：包括公有变量的声明、函数/存储过程说明；

包体：包括私有变量的声明、私有函数/存储过程的实现，公有函数/存储过程的实现，以及BEGIN…END初始化成员变量；

--包头
   CREATE OR REPLACE PACKAGE employee_pkg
   AS
       SUBTYPE fullname_t IS VARCHAR2 (200);

       FUNCTION fullname (
          last_in  employee.last_name%TYPE,
          first_in  employee.first_name%TYPE)
          RETURN fullname_t;

        FUNCTION fullname (
           employee_id_in IN employee.employee_id%TYPE)
           RETURN fullname_t;
    END employee_pkg;


--包体

1    CREATE OR REPLACE PACKAGE BODY employee_pkg
2    AS
3       FUNCTION fullname (
4          last_in employee.last_name%TYPE,
5          first_in employee.first_name%TYPE
6       )
7          RETURN fullname_t
8       IS
9       BEGIN
10          RETURN    last_in || ',' || first_in;
11       END;
12     
13       FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
14          RETURN fullname_t
15       IS
16          retval   fullname_t;
17       BEGIN
18          SELECT fullname (last_name, first_name) INTO retval
19            FROM employee
20           WHERE employee_id = employee_id_in;
21     
22          RETURN retval;
23       EXCEPTION
24          WHEN NO_DATA_FOUND THEN RETURN NULL;
25     
26          WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop;
27       END;
      DEGIN
        -- 变量初始化
28    END employee_pkg;

注：

1. 注意成员的可见性（定义在包头可见，只在包体定义不可见）；

2. 包是会话持久型（Session-Persistence），作用域为当前会话，不同会话之间互不影响；