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），作用域为当前会话，不同会话之间互不影响；