Oracle程序包

1. 程序包的概念

1.程序包定义

程序包用于将逻辑相关的PL/SQL块或元素(变量、常量、过程、函数等)组织在一起,作为一个完整的单元存储在数据库中,用包名称来标识程序包。程序包类似于面向对象中的类。

2.程序包的结构

Oracle程序包

3.包中可以包含的元素的性质

元素的性质

描   述

在包中的位置

公共的(public)

在整个应用的全过程均有效

包的说明部分说明

私有的(private)

对包以外的过程和函数是不可见的

在包体部分说明和定义

局部的

只在一个过程或函数内使用

在所属过程或函数的内部说明和定义

2. 创建包头

1.创建包头的命令格式:

CREATE [OR REPLACE] PACKAGE <packagename> IS|AS
公共数据类型和对象声明;
公共子程序和函数说明;
END [<数据包名称>];

说明:

  1.     在包头中声明的元素(过程、函数、变量等)是公共元素,只在包体中声明的元素是私有元素。
  2.     公共元素可以在包的外面单独调用,但私有元素只能在包体内定义别的过程函数时被调用。
  3.     局部变量是在包体内过程或函数中定义的变量,该局部变量只能在该过程函数内使用,不能在包体内别的过程函数内使用。

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次数。
上一篇:SQL必知必会 第4课 过滤数据


下一篇:vue-element-admin中如何vuex的使用