PL/SQL --> 包重载、初始化

--==========================

-- PL/SQL --> 包重载、初始化

--==========================

包的重载功能类似于C++中函数的重载功能，即拥有多个同名的子程序，每个同名子程序使用不同的参数。用户可以传递不同的参数来调

用同名但参数不同的子程序，此即为包的重载功能。简言之，不管传递什么样的参数，所完成的任务是相同的。假定需要查询部门所在的位置

，输入参数部门编号或部门名称都会返回同样的结果。对外部程序而言，似乎是调用的同一个子程序，但其始质调用了不同的子程序，执行了

不同的代码。

有关包的创建与管理请参考：PL/SQL --> 包的创建与管理

一、使用重载特性建立包头

在包中，具有重载特性的子程序必须使用不同的输入参数。同名函数返回值数据类型必须完全相同。

以下情况不能实现重载

a.如果两个子程序的参数仅在名称和类型上不同, 这两个程序不能重载。

PROCEDURE overloadproc(o_parameter IN NUMBER);

PROCEDURE overloadproc(o_parameter OUT NUMBER);

IN ,OUT 为参数类型,NUMBER 为数据类型. 两个过程仅在类型上不同时不能重载。

b.函数使用不同的返回类型时不能进行重载

FUNCTION overloadfunc(f_parameter NUMBER) RETURN DATE;

FUNCTION overloadfunc(f_parameter VARCHAR2) RETURN NUMBER;

c.重载子程序的参数的类族必须不同,如由于NUMBER 和INTEGER 属性同一类族,所以不能实现重载。

PROCEDURE overloadproc(o_parameter NUMBER);

PROCEDURE overloadproc(o_parameter INTEGER);

--下面使用重载特性建立包头，包含了重载函数get_sal，以及重载过程fire_employee

CREATE OR REPLACE PACKAGE overload IS

FUNCTION get_sal(eno NUMBER) RETURN NUMBER;

FUNCTION get_sal(name VARCHAR2) RETURN NUMBER;

PROCEDURE fire_employee(eno NUMBER);

PROCEDURE fire_employee(name VARCHAR2);

END;

二、创建重载特性的包体

对于包中具有重载特性的函数或过程，需要依次对其创建不同的包体，即使用不同的执行代码。

对前面创建的包头，我们对其创建如下包体

通过调用get_sal函数来返回雇员的薪水，可以使用雇员编号或雇员名字作为参数

通过调用fire_employee来解雇雇员，可以使用雇员编号或雇员名字作为参数

CREATE OR REPLACE PACKAGE BODY overload IS

FUNCTION get_sal(eno NUMBER) RETURN NUMBER IS

v_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v_sal FROM emp WHERE empno = eno;

RETURN v_sal;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20020, 'The Employee is not exist !');

END;

FUNCTION get_sal(name VARCHAR2) RETURN NUMBER IS

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(-20020, 'The Employee is not exist !');

END;

PROCEDURE fire_employee(eno NUMBER) IS

BEGIN

DELETE FROM emp WHERE empno = eno;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20020, 'The Employee is not exist !');

END IF;

END;

PROCEDURE fire_employee(name VARCHAR2) IS

BEGIN

DELETE FROM emp WHERE UPPER(ename) = UPPER(name);

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20020, 'The Employee is not exist !');

END IF;

END;

END;

三、重载子程序的调用

在对使用了重载特性的子程序进行调用时，PL/SQL会自动根据所提供的参数寻找同名且参数相符的子程序来执行其代码

scott@ORCL> var sal_1 number;

scott@ORCL> var sal_2 number;

scott@ORCL> exec :sal_1:=overload.get_sal('king');

scott@ORCL> exec :sal_2:=overload.get_sal(7788);

scott@ORCL> print sal_1 sal_2;

SAL_1

----------

5800

SAL_2

----------

3900

四、包的初始化

包的初始化，也称之为包的构造过程。即当包被首次使用时，会自动执行其构造过程，并且该构造过程在同一会话内仅仅被执行一次。

对于包的初始化，其通常的办法是包体的末尾增加一段匿名SQL代码。如下

CREATE OR REPLACE PACKAGE BODY package_name

IS

PROCEDURE procedure_name

····

FUNCTION function_name

····

BEGIN

Initialization_code;-- 要运行的初始化代码

END

--下面首先声明包头

CREATE OR REPLACE PACKAGE emp_package IS

minsal NUMBER(6, 2); --定义公共变量minsal，用于存放雇员最低薪水

maxsal NUMBER(6, 2); --定义公共变量maxsal，用于存放雇员最高薪水

PROCEDURE add_employee(eno NUMBER, name VARCHAR2, salary NUMBER, dno NUMBER);

PROCEDURE upd_sal(eno NUMBER, salary NUMBER); --对upd_sal过程实现重载

PROCEDURE upd_sal(name VARCHAR2, salary NUMBER);

END;

--下面定义包体

CREATE OR REPLACE PACKAGE BODY emp_package IS

PROCEDURE add_employee(eno NUMBER, name VARCHAR2, salary NUMBER, dno NUMBER) IS

BEGIN

IF salary BETWEEN minsal AND maxsal THEN

INSERT INTO emp (empno, ename, sal, deptno) VALUES(eno, name, salary, dno);

ELSE

RAISE_APPLICATION_ERROR(-20001, 'The salary is over specified range.');

END IF;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

RAISE_APPLICATION_ERROR(-20002, 'The employee is exists.');

END;

PROCEDURE upd_sal(eno NUMBER, salary NUMBER) IS

BEGIN

IF salary BETWEEN minsal AND maxsal THEN

UPDATE emp SET sal = salary WHERE empno = eno;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20003, 'The employee is not exists.');

END IF;

ELSE

RAISE_APPLICATION_ERROR(-20001, 'The salary is over specified range.');

END IF;

END;

PROCEDURE upd_sal(name VARCHAR2, salary NUMBER) IS

BEGIN

IF salary BETWEEN minsal AND maxsal THEN

UPDATE emp SET sal = salary WHERE UPPER(ename) = UPPER(name);

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20004, 'The employee is not exists.');

END IF;

ELSE

RAISE_APPLICATION_ERROR(-20001, 'The salary is over specified range.');

END IF;

END;

BEGIN

SELECT min(sal), max(sal) INTO minsal, maxsal FROM emp; --初始化公共变量minsal, maxsal

END;

--调用

scott@ORCL> exec emp_package.add_employee(1234,'Henry',3500,20);

scott@ORCL> exec emp_package.upd_sal('Henry',3500);

scott@ORCL> exec emp_package.upd_sal('Henry',100); --当范围超出最高和最小薪水则返回错误信息，且更新失败

BEGIN emp_package.upd_sal('Henry',100); END;

*

ERROR at line 1:

ORA-20001: The salary is over specified range.

ORA-06512: at "SCOTT.EMP_PACKAGE", line 34

ORA-06512: at line 1

五、前置声明

前置声明指的是在包体内，假定过程A调用了过程B，而B在A之后定义，这样的话，将会收到错误信息。对此，我们可以不改变过程A，B的

书写顺序及其代码，而将B事先声明，此之为前置声明。如下面的例子：

--未使用前置声明时的代码

CREATE OR REPLACE PACKAGE BODY forward_pack IS

PROCEDURE award_bonus(...)

IS

BEGIN

cal_rating(...); --在此例中过程cal_rating在过程award_bonus之后定义，这样即为非法调用

END;

PROCEDURE cal_rating(...)

IS

BEGIN

...

END;

END forward_pack;

--使用前置声明后的代码

CREATE OR REPLACE PACKAGE BODY forward_pack IS

PROCEDURE cal_rating(...)； --在此处增加一行用于声明过程cal_rating，仅仅列出过程名及参数信息

PROCEDURE award_bonus(...)

IS

BEGIN

cal_rating(...);

END;

PROCEDURE cal_rating(...)

IS

BEGIN

...

END;

END forward_pack;

六、函数纯度级别

Oracle函数可以在SQL语句中调用，也可以作为表达式的一部分，基于函数的一些特殊性，在包中使用SQL语句调用公共函数时，同样也存

在一些限制，其限制主要如下：

公用函数不能包含DML语句

公用函数不能读写远程包变量

对此可以使用纯度级别来现在公用函数的某些操作

定义语法

PRAGMA RESTRICT_REFERENCES(function_name,WNDS[,WNPS][,RNDS][RNPS]);

WNDS:限制函数不能修改数据库(即执行DML操作)

WNPS：限制函数不能修改包变量，即不能给包变量赋值

RNDS：限制函数不能读取数据库数据(即禁止SELECT操作)

RNPS：限制函数不能读取包变量，即不能将包变量赋值给其它变量

--下面的代码创建使用纯度即被的包头

CREATE OR REPLACE PACKAGE purity IS

minsal NUMBER(6, 2); --定义公共变量minsal

maxsal NUMBER(6, 2); --定义公共变量maxsal

FUNCTION max_sal RETURN NUMBER; --定义公共函数

FUNCTION min_sal RETURN NUMBER;

PRAGMA RESTRICT_REFERENCES(max_sal, WNPS); --指定函数所使用的纯度级别

PRAGMA RESTRICT_REFERENCES(min_sal, WNPS);

END;

--下面的代码创建使用纯度级别的包体

CREATE OR REPLACE PACKAGE BODY purity IS

FUNCTION max_sal RETURN NUMBER IS

BEGIN

SELECT max(sal) INTO maxsal FROM emp;

RETURN maxsal;

END;

FUNCTION min_sal RETURN NUMBER IS

BEGIN

SELECT min(sal) INTO minsal FROM emp;

RETURN minsal;

END;

END;

--创建包体后，收到了如下的错误信息，因为两个公共函数指定了纯度级别为WNPS，而且函数内的代码对变量进行了赋值

scott@ORCL> show errors package body purity;

Errors for PACKAGE BODY PURITY:

LINE/COL ERROR

-------- -----------------------------------------------------------------

2/1 PLS-00452: Subprogram 'MAX_SAL' violates its associated pragma

8/1 PLS-00452: Subprogram 'MIN_SAL' violates its associated pragma

--下面使用初始化包的方法来为变量赋值

CREATE OR REPLACE PACKAGE BODY purity IS

FUNCTION max_sal RETURN NUMBER IS

BEGIN

RETURN maxsal; --函数可以读取包初始化后变量的值

END;

FUNCTION min_sal RETURN NUMBER IS

BEGIN

RETURN minsal; --函数可以读取包初始化后变量的值

END;

BEGIN

SELECT min(sal), max(sal) INTO minsal, maxsal FROM emp; --对公共变量进行初始化

END;

--下面调用限定的公用函数

scott@ORCL> var minsal number;

scott@ORCL> var maxsal number;

scott@ORCL> exec :minsal:=purity.minsal;

scott@ORCL> exec :maxsal:=purity.maxsal;

scott@ORCL> print minsal maxsal;

MINSAL

----------

800

MAXSAL

----------

5800

七、包内游标一致性状态

可以在包内定义一个公共游标，该包内的所有子程序调用该游标来实现相应的功能。如何确保子程序调用游标采取顺序一致性性调用，

而不会出现获得重复的游标记录，下面给出的例子中说明了包内游标一致性状态的使用。

--创建包头，并且定义了一个公共游标，两个公共过程

CREATE OR REPLACE PACKAGE pack_cur

IS

CURSOR cur IS

SELECT empno,ename FROM emp ORDER BY empno;

PROCEDURE return1_3rows;

PROCEDURE return4_6rows;

END pack_cur;

/

--创建包体

CREATE OR REPLACE PACKAGE BODY pack_cur

IS

v_empno emp.empno%TYPE; --定义用于存储游标结果的变量

v_ename emp.ename%TYPE; --定义用于存储游标结果的变量

PROCEDURE return1_3rows IS

BEGIN

OPEN cur; --在第一个过程中打开游标

DBMS_OUTPUT.PUT_LINE('Empno Ename');

LOOP

FETCH cur INTO v_empno,v_ename;

DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_ename);

EXIT WHEN cur%ROWCOUNT >= 3; --指定游标退出的条件

END LOOP;

END return1_3rows;

PROCEDURE return4_6rows IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Empno Ename');

LOOP

FETCH cur INTO v_empno,v_ename; --因为在第一个过程中游标已打开，在此可以直接从游标提取数据

DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_ename);

EXIT WHEN cur%ROWCOUNT >= 6; --指定游标退出的条件

END LOOP;

CLOSE cur; --关闭游标

END return4_6rows;

END;

/

--调用示例及其结果

scott@ORCL> set serveroutput on;

scott@ORCL> exec pack_cur.return1_3rows;

Empno Ename

1234 Henry

3333 Jackson

4444 Richard

scott@ORCL> exec pack_cur.return4_6rows;

Empno Ename

7369 SMITH

7499 ALLEN

7521 WARD

八、在包内使用自定义类型

--创建包头

CREATE OR REPLACE PACKAGE cust_type IS

TYPE emp_tb_type IS TABLE OF emp%ROWTYPE --定义一个PL/SQL索引表

INDEX BY BINARY_INTEGER;

PROCEDURE read_emp_table(p_emp_table OUT emp_tb_type); --定义一个过程

END cust_type;

/

--创建包体

CREATE OR REPLACE PACKAGE BODY cust_type IS

PROCEDURE read_emp_table(p_emp_table OUT emp_tb_type) IS --定义了输出参数的类型为emp_tb_type

i BINARY_INTEGER:=0;

BEGIN

FOR emp_record IN (SELECT * FROM emp) --提取记录使用FOR循环

LOOP

p_emp_table(i):=emp_record; --将提取的记录存放到PL/SQL索引表

i:= i + 1;

END LOOP;

END read_emp_table;

END cust_type;

/

--下面使用匿名的PL/SQL块来过程来调用包

DECLARE

v_emp_table cust_type.emp_tb_type;

BEGIN

cust_type.read_emp_table(v_emp_table);

DBMS_OUTPUT.PUT_LINE('An example: '||v_emp_table(3).ename);

END;

An example: WARD

九、更多参考

有关SQL请参考

SQL 基础--> 子查询

SQL 基础-->多表查询

SQL基础-->分组与分组函数

SQL 基础-->常用函数

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

有关PL/SQL请参考

PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL -->隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL记录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器