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