Oracle之PL/SQL学习笔记之包

2019独角兽企业重金招聘Python工程师标准>>>

Oracle之PL/SQL学习笔记之包

1. 认识包(package)

1.1 什么是包

         包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，它具有面向对象程序设计语言的特点，是对这些PL/SQL 程序设计元素的封装。包类似于C++和JAVA语言中的类，其中变量相当于类中的成员变量，过程和函数相当于类方法。把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。

与类相同，包中的程序元素也分为公用元素和私用元素两种，这两种元素的区别是他们允许访问的程序范围不同，即它们的作用域不同。公用元素不仅可以被包中的函数、过程所调用，也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程序所访问。

在PL/SQL程序设计中，使用包不仅可以使程序设计模块化，对外隐藏包内所使用的信息（通过使用私用变量），而且可以提高程序的执行效率。因为，当程序首次调用包内函数或过程时，ORACLE将整个包调入内存，当再次访问包内元素时，ORACLE直接从内存中读取，而不需要进行磁盘I/O操作，从而使程序执行效率得到提高 

1.2 包的组成

一个包由两个分开的部分组成：

   包定义（PACKAGE）：包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。

   包主体（PACKAGE BODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。

   包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中。

1.3 包的定义

包定义的语法如下：

CREATE [OR REPLACE] PACKAGE package_name
{IS | AS}
		[公有数据类型定义[公有数据类型定义]…]
		[公有游标声明[公有游标声明]…]
		[公有变量、常量声明[公有变量、常量声明]…]
		[公有子程序声明[公有子程序声明]…]
END [package_name];

1.4 包体的定义

 包体定义的格式:

CREATE [OR REPLACE] PACKAGE BODY package_name
{IS | AS}
		[私有数据类型定义[私有数据类型定义]…]
		[私有变量、常量声明[私有变量、常量声明]…]
		[私有子程序声明和定义[私有子程序声明和定义]…]
		[公有游标定义[公有游标定义]…]
		[公有子程序定义[公有子程序定义]…]
[BEGIN
		PL/SQL 语句]
END [package_name];

其中：在包主体定义公有程序时，它们必须与包定义中所声明子程序的格式完全一致

1.5 包开发步骤

与开发存储过程类似，包的开发需要几个步骤 ：
    将每个存储过程调试正确 
    用文本编辑软件将各个存储过程和函数集成在一起 
    按照包的定义要求将集成的文本的前面加上包定义 
    按照包的定义要求将集成的文本的前面加上包主体 
    使用开发工具进行调式

2. 创建包应用实例

实例1-包定义：

CREATE OR REPLACE PACKAGE demo_pack
   IS
        DeptRec dept%ROWTYPE;--定义变量
        FUNCTION add_dept(
          dept_no NUMBER, dept_name VARCHAR2,
           location VARCHAR2)
          RETURN NUMBER;--定义函数
        FUNCTION remove_dept(dept_no NUMBER)
          RETURN NUMBER;--定义函数
        PROCEDURE query_dept(dept_no IN NUMBER);--定义存储过程
  END demo_pack;

实例1- 包体定义:

create or replace package body demo_pack is

       function add_dept(dept_no number,dept_name varchar2,location varchar2)
         return number is
         exp_remaining exception;
         pragma exception_init(exp_remaining,-1);/*非预定义错误，-1是违反唯一约束*/
       begin 
         insert into dept values(dept_no,dept_name,location);
         if sql%found then /*隐式游标，sql*/
           return 1;
         else 
           return 0;
         end if;
       exception 
         when exp_remaining then
           dbms_output.put_line('违反唯一约束.');
       end add_dept;
       
       function  remove_dept(dept_no number) return number
         is
         begin 
           delete from dept where deptno=dept_no;
           if sql%found then 
             return 1;
           else 
             return 0;
           end if;
         end remove_dept;
       procedure query_dept(dept_no number) is
         begin
           select * into  DeptRec from dept where deptno=dept_no;
           dbms_output.put_line('部门编号:'||DeptRec.deptno||',部门名称:'||DeptRec.dname);
         exception 
           when no_data_found then
             dbms_output.put_line('没有对应部门编号');
           when too_many_rows then
             dbms_output.put_line('查找到了很多条数据');
         end query_dept;
     end demo_pack;

实例1-测试:

     begin
         -- Call the procedure
         demo_pack.query_dept(dept_no => &no);--&no弹出对话框，输入值
     end;

实例2- 分页-包定义:

 create or replace package pageUtil is
 
  type page_cursor is ref cursor;--定义一个游标类型
  
  procedure pager(
        tName in varchar2, --表名
        pageNum in number, --页数
        pageSize in number,--每页记录数
        totalRecord out number,--总记录数
        totalPage out number,--总页数
        p_cursor out page_cursor);
        
end pageUtil;

实例2- 分页-包体定义:

create or replace package body pageUtil is 
       
    procedure pager(
        tName in varchar2, --表名
        pageNum in number, --页数
        pageSize in number,--每页记录数
        totalRecord out number,--总记录数
        totalPage out number,--总页数
        p_cursor out page_cursor) is
        
        --定义sql语句字符串
        v_sql varchar2(1000);
        --分页开始位置，与结束位置
        v_begin number:=(pageNum-1)*pageSize+1;
        v_end number:=pageNum*pageSize;
    begin
      v_sql:='select * from ( select t.*,rownum rn from '
        ||tName||' t where rownum<='||v_end||') where rn>='||v_begin;
      --把游标和sql关联
      dbms_output.put_line(v_sql);
      open p_cursor for  v_sql;
      --计算totalRecord与totalPage
      v_sql:='select count(*) from '||tName;
      --
      execute immediate v_sql into totalRecord;
      if mod(totalRecord,pageSize)=0 then 
        totalPage:=totalRecord/pageSize;
      else
        totalPage:=totalRecord/pageSize+1;
      end if;
      --关闭游标,这儿需要注意，如果我们在java程序中使用cursor,那么就一定不能关闭cursor
      --否则关闭cursor后，java程序中返回的结果集就是null
      --close p_cursor;
    end pager;
end pageUtil;

    这儿只是一个粗糙的分页，自己可以根据需求改进。

3. 子程序的重载

      PL/SQL 允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同的名称，但拥有不同的参数变量、参数顺序或参数数据类型

CREATE OR REPLACE PACKAGE demo_pack1
    IS
	DeptRec dept%ROWTYPE;
	FUNCTION query_dept(dept_no IN NUMBER)
		RETURN INTEGER;
	FUNCTION query_dept(dept_no IN VARCHAR2)
		RETURN INTEGER;
    END demo_pack1;

注意：

 如果两个子程序的参数只是名称和方式不同时，不能重载他们。

例：PROCEDURE OverlodeMe（P_parameter in number）

           PROCEDURE OverlodeMe（P_parameter out number）

 不能只根据两个函数的返回类型进行重载

       例：FUNCTION Overlodeme RETURN date

           FUNCTION Overlodeme RETURN boolean

 重载函数的参数必须在类型系列方面有所不同，既不能再同一类 型系列上重载

       例：PROCEDURE OverlodeMe（P_parameter in char）

           PROCEDURE OverlodeMe（P_parameter out varchar2)

4. 删除包

    

可以使用 DROP PACKAGE 命令对不需要的包进行删除，语法如下： 

DROP PACKAGE [BODY] [user.]package_name

        例：DROP PACKAGE emp_package; 

包所涉及到的数据字典视图： 

        DBA_SOURCE, USER_SOURCE, USER_ERRORS, DBA_OBJECTS 

转载于:https://my.oschina.net/KingPan/blog/311306