开发者社区> leshami> 正文

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

简介: --========================== -- PL/SQL --> 包重载、初始化 --==========================       包的重载功能类似于C++中函数的重载功能,即拥有多个同名的子程序,每个同名子程序使用不同的参数。
+关注继续查看

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

-- 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,而BA之后定义,这样的话,将会收到错误信息。对此,我们可以不改变过程AB

书写顺序及其代码,而将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 触发器

   

 

       

   

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql 协议的初始化DB命令包及解析
git https://github.com/sea-boat/mysql-protocol 概况 mysql连接如果想要改变该连接默认的schema,则客户端可以向服务端发送初始化DB命令包。
933 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
14207 0
PL/SQL --> 包重载、初始化
--========================== -- PL/SQL --> 包重载、初始化 --==========================       包的重载功能类似于C++中函数的重载功能,即拥有多个同名的子程序,每个同名子程序使用不同的参数。
701 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
27732 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
12972 0
Docker容器启动时初始化Mysql数据库
1. 前言 Docker在开发中使用的越来越多了,最近搞了一个Spring Boot应用,为了方便部署将Mysql也放在Docker中运行。那么怎么初始化 SQL脚本以及数据呢? 我这里有两个传统方案。 第一种方案是在容器启动后手动导入,太low了不行。第二种在Spring Boot客户端连接Mysql容器时初始化数据库,你可以参考使用flyway进行数据库版本控制一文,但是这依赖客户端的能力。能不能做到Mysql容器启动时就自己初始化数据库呢?当然可以!今天就来演示一下。全部代码见文末。
308 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
19980 0
+关注
leshami
传播知识,分享快乐!十年以上数据库,系统运维与管理,性能优化经验。全部文章,欢迎扩散,转载请注明出处!
639
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载