专栏首页乐沙弥的世界PL/SQL --> 包重载、初始化

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 触发器

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • PL/SQL --> 动态SQL的常见错误

    动态SQL在使用时,有很多需要注意的地方,如动态SQL语句结尾处不能使用分号(;),而动态PL/SQL结尾处需要使用分号(;),但不能使用正

    Leshami
  • PL/SQL --> 流程控制

    类似于高级语言,流程控制语句是PL/SQL语言的重要组成部分。这些流程控制语句使得PL/SQL加大了代码的灵活性和多样性,大大简化了

    Leshami
  • SQL基础-->过滤和排序

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

    Leshami
  • 存在漏洞的Java及Flash版本使用者众多

    众所周知的是Java和Flash历来被攻击者所青睐,这多亏了它们巨大的装机量和众多的安全问题。但与此同时被攻击者所定为目标的用户们却没有这么乐观,...

    安恒信息
  • 《Learning Scrapy》(中文版)第10章 理解Scrapy的性能

    通常,很容易将性能理解错。对于Scrapy,几乎一定会把它的性能理解错,因为这里有许多反直觉的地方。除非你对Scrapy的结构有清楚的了解,你会发现努力提升Sc...

    SeanCheney
  • Unity基础系列(一)——创建一个时钟(GameObjects与Scripts)

    这篇教程是一个基础教程,会和大家一起创建一个简单的时钟,并且给它加上一些组件脚本用来显示当前时间。这篇教程的意义并不在于怎么学会写出一个时钟,而是教你认识Uni...

    放牛的星星
  • Flutter Widgets 之 Opacity 和AnimatedOpacity

    Flutter中移除一个控件非常容易,只需要在重新创建中移除即可,如果想要移除控件同时它的位置依然保留,类似于Android中View的invisible,比如...

    老孟Flutter
  • 景深效果(Depth of Field)

    逍遥剑客
  • Redis专题(六)——Redis高可用(复制篇)

    Redis专题(六) ——Redis高可用(复制篇) (原创内容,转载请注明来源,谢谢) 一、单台服务器 单台redis服务器,会出现单点故障,且需要承受所...

    用户1327360
  • 无人机配送、原产地直采、研习社 超级物种的进化逻辑:长成“年轻人要的样子”

    商业观察家:在与腾讯云的合作中,永辉结合零售场景,搭建了永辉云计算中心,逐步切入数字化改造,更好的进行商品和门店的运营管理。 腾讯云推出智能门店选址、智能选品...

    腾讯云智慧零售

扫码关注云+社区

领取腾讯云代金券