1. 程序包的概念
1.程序包定义
程序包用于将逻辑相关的PL/SQL块或元素(变量、常量、过程、函数等)组织在一起,作为一个完整的单元存储在数据库中,用包名称来标识程序包。程序包类似于面向对象中的类。
2.程序包的结构
3.包中可以包含的元素的性质
元素的性质 | 描 述 | 在包中的位置 |
公共的(public) | 在整个应用的全过程均有效 | 包的说明部分说明 |
私有的(private) | 对包以外的过程和函数是不可见的 | 在包体部分说明和定义 |
局部的 | 只在一个过程或函数内使用 | 在所属过程或函数的内部说明和定义 |
2. 创建包头
1.创建包头的命令格式:
CREATE [OR REPLACE] PACKAGE <packagename> IS|AS
公共数据类型和对象声明;
公共子程序和函数说明;
END [<数据包名称>];
说明:
- 在包头中声明的元素(过程、函数、变量等)是公共元素,只在包体中声明的元素是私有元素。
- 公共元素可以在包的外面单独调用,但私有元素只能在包体内定义别的过程函数时被调用。
- 局部变量是在包体内过程或函数中定义的变量,该局部变量只能在该过程函数内使用,不能在包体内别的过程函数内使用。
2.创建包规范的语法:
CREATE [OR REPLACE] PACKAGE package_name{IS | AS}
type_definition|
procedure_specification|
function_specification|
variable_declaration|
exception_declaration|
cursor_declaration|
pragma_declaration
END [ package_name];
例1 :创建一个包头pkg_score,在包头中(参考函数案例)
声明一个函数,用于查询某课程的课程类型;
声明一个存储过程,用于查询某学生的成绩信息及课程类型,要求必修课输出百分制成绩,选修课或考查课输出五分制成绩。
CREATE OR REPLACE PACKAGE pkg_score IS
--声明一个公有函数,用于查询某课程的课程类型
FUNCTION course_type(cid char)
RETURN nvarchar2 ;
--声明一个公有存储过程,用于查询某学生的成绩信息及课程类型,要求必修课输出百分制成绩,选修课或考查课输出五分制成绩。
PROCEDURE sele_stuscore(sid char);
END pkg_score;
例2 :创建一个包头prodcut_pkg,在包头中
声明一个记录类型prod_row(包含产品编号、产品名称、产品类别、单价、折扣信息字段);
声明一个存储过程sele_prod,用于查询某产品编号的产品信息;
声明一个存储过程update_prod,用于对折扣信息为1的产品价格打9.5折;
声明一个函数max_price,用于计算某类产品的最高价格;
声明一个存储过程out_prod,用于输出产品表中某类产品的产品编号、产品名称、产品类别、单价、折扣信息。
CREATE OR REPLACE PACKAGE product_pkg IS
--声明公有记录类型存放产品信息
TYPE prod_row IS RECORD
(prodid products.productid%type,
prodname products.productname%type,
cid products.categoryid%type,
uprice products.unitprice%type,
pdisc products.discontinued%type);
规范定义代码:
--声明公有存储过程,查某产品编号的产品信息
PROCEDURE sele_prod(pid IN products.productid%type,
sele_prodrow OUT prod_row);
--声明公有存储过程更新打折产品单价
PROCEDURE update_prod;
--声明公有函数求某类产品的最高单价
FUNCTION max_price(cateid products.categoryid%type) RETURN number;
--声明公有存储过程输出某类产品的信息
PROCEDURE out_prod(cateid products.categoryid%type);
END product_pkg;
3. 创建包体
创建包体的语法格式:
CREATE [OR REPLACE] PACKAGE BODY [Schema.]package_name
{IS|AS}
私有变量的定义|
私有类型的定义|
私有例外出错处理的定义|
私有游标的定义|
函数定义|
过程定义
[[BEGIN]
[实例化代码]] --只在用户第一次调用程序包时运行一次
END [ package_name];
- 1、声明范围对于数据包主体是局部的
- 2、除了在数据包主体内将不能访问到声明的类型和对象
例1(与创建包头例1为同一道题) :创建一个包体pkg_score,在包体中包含:
用于查询某课程的课程类型的公有函数;
用于根据学生成绩返回’优、良、中、及、不及’五级分制的私有函数;
用于查询某学生的成绩信息及课程类型的公有存储过程,要求必修课输出百分制成绩,选修课或考查课输出五分制成绩。
CREATE OR REPLACE PACKAGE BODY pkg_score
IS
--用于查询某课程的课程类型的公有函数;
FUNCTION course_type(cid char) RETURN nvarchar2 IS
v_ctype nvarchar2(10);
v_str char(1);
BEGIN
v_str:=substr(cid, 3, 1);
CASE v_str
WHEN '1' THEN v_ctype:='必修课';
WHEN '2' THEN v_ctype:='考查课';
WHEN '3' THEN v_ctype:='选修课';
ELSE
v_ctype:='课程类型错误';
END CASE;
RETURN v_ctype;
END course_type;
--用于根据学生成绩返回’优、良、中、及、不及’五级分制的私有函数;
FUNCTION five_score(s_score number)
RETURN varchar2
IS
ss nvarchar2(10);
BEGIN
CASE
WHEN s_score>=90 and s_score<=100 THEN ss:='优秀';
WHEN s_score>=60 and s_score<90 THEN ss:='良好';
WHEN s_score>=70 and s_score<60 THEN ss:='中等';
WHEN s_score>=60 and s_score<70 THEN ss:='及格';
ELSE ss:='不及格';
END CASE;
Return ss;
END five_score;
--用于查询某学生的成绩信息及课程类型的公有存储过程,要求必修课输出百分制成绩,选修课或考查课输出五分制成绩。
PROCEDURE sele_stuscore(sid char) IS
BEGIN
FOR i IN (select * from stu_scores where stu_id=sid) LOOP
IF course_type(i.course_id) ='必修课' THEN
dbms_output.put_line('学号:'|| i.stu_id || ' 课程:'||i.course_id||' 课程类型:'||course_type(i.course_id)||' 成绩:'||i.score);
ELSE
dbms_output.put_line('学号:'|| i.stu_id || ' 课程:'||i.course_id||' 课程类型:'||course_type(i.course_id)||' 成绩:'||five_score(i.score));
END IF;
END LOOP;
END sele_stuscore;
END pkg_score;
例2(与创建包头例2为同一道题) :创建一个包体prodcut_pkg,在包体中实现以下功能:
创建一个存储过程sele_prod,用于查询某产品编号的产品信息;
创建一个存储过程update_prod,用于对折扣信息为1的产品价格打9.5折;
创建一个函数max_price,用于计算某类产品的最高价格;
创建一个存储过程out_prod,用于输出产品表中某类产品的产品编号、产品名称、产品类别、单价信息。
CREATE OR REPLACE PACKAGE BODY product_pkg AS
--查询某产品编号的产品信息
PROCEDURE sele_prod(pid IN products.productid%type, p_row OUT prod_row) IS
BEGIN
SELECT productid, productname, categoryid, unitprice, discontinued INTO p_row
FROM products WHERE productid=pid;
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20000, '产品不存在');
END sele_prod;
--对折扣信息为1的产品价格打9.5折
PROCEDURE update_prod IS
no_update EXCEPTION;
BEGIN
UPDATE products
SET unitprice=unitprice*0.95
WHERE discontinued='1';
IF SQL%NOTFOUND THEN
RAISE no_update;
END IF;
EXCEPTION
WHEN no_update THEN
raise_application_error(-20006, '没有打折的产品');
END update_prod;
--计算某类产品的最高价格
FUNCTION max_price(cateid products.categoryid%type) RETURN number IS
mprice number; --局部变量
BEGIN
SELECT avg(unitprice) INTO mprice
FROM products WHERE categoryid=cateid
Group by categoryid;
RETURN mprice;
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20002, '该类产品不存在');
RETURN 0;
END max_price;
--输出产品表中某类产品的产品编号、产品名称、产品类别、单价信息
PROCEDURE out_prod(cateid products.categoryid%type)
IS
BEGIN
FOR p_row IN (SELECT * FROM products WHERE categoryid=cateid) LOOP
dbms_output.put_line('产品编号:'||p_row.productid);
dbms_output.put_line('产品名称:'||p_row.productname);
dbms_output.put_line(' 类别编号:'||p_row.categoryid);
dbms_output.put_line(' 单价:'||p_row.unitprice);
END LOOP;
END out_prod;
END product_pkg;
4.包中函数与过程的执行
1.调用包中过程的语法格式:
包名. 过程名[(参数……)]
例:在SQL*PLUS中执行pkg_score包中的存储过程sele
SQL> EXEC pkg_score.sele_stuscore(1)
2.调用包中函数的语法格式为:
declare 变量名 数据类型(长度)
begin
变量名:=包名.函数名(参数);
dbms_output.put_line(变量名);
end;
3.程序包的删除
语法:drop package 包名;
5.包的优点
1、规范化应用程序的开发
2、方便对存储过程和函数的组织
- 将相关的过程和函数组织在一起
- 在一个用户的环境中解决命名冲突
3、方便对存储过程和函数的安全性管理
- 整个包的访问权限只需要一次性授权
- 区分公共过程和私有过程。公共过程在包外可以被调用,私有过程在包外不能被调用。
4、为用户会话提供状态确认信息
- 在各种环境和过程中均可引用标识符(即包内的公共变量)
- 在用户整个会话中保留标识符的状态(即在整个会话中公共变量的值一直保留,在一个新的会话中公共变量的值又被初始化)
5、改善性能
- 包在首次被调用时。作为一个整体全部调入内存,不必一个过程一个过程调入内存。
- 减少多次调入时的磁盘I/O次数。