`

精通Oracle10编程SQL(12)开发包

阅读更多
/*
 *开发包
 *包用于逻辑组合相关的PL/SQL类型(例如TABLE类型和RECORD类型)、PL/SQL项(例如游标和游标变量)和PL/SQL子程序(例如过程和函数)
 */
--包用于逻辑组合相关的PL/SQL类型、项和子程序,它由包规范和包体两部分组成
--建立包规范:包规范实际是包与应用程序之间的接口,它用于定义包的公用组件,包括常量、变量、游标、过程和函数等
--在包规范中所定义的公用组件不仅可以在包内引用,而且也可以由其他的子程序引用
--示例
CREATE OR REPLACE PACKAGE emp_package IS
  g_deptno number(3):=30;
  procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno);
  procedure fire_employee(eno number);
  function get_sal(eno number) return number;
END emp_package;

--建立包体
--应用程序只能直接调用该包内的所有公用组件,而私有函数VALIDATE_DEPTNO则不能被应用程序调用
CREATE OR REPLACE PACKAGE BODY emp_package 
IS
  FUNCTION validate_deptno(v_deptno number)
     return boolean
  is
     v_temp int;
  begin
     select 1 into v_temp from dept where deptno=v_deptno;
     return true;
  exception
     when NO_DATA_FOUND THEN
        RETURN FALSE;
  end;
  
  PROCEDURE add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
  is
  begin
       if validate_deptno(dno) then
         insert into emp(empno,ename,sal,deptno) values(eno,name,salary,dno);
       else
         raise_application_error(-20010,'不存在该部门');
       end if;
  exception
       when DUP_VAL_ON_INDEX THEN
          raise_application_error(-20011,'该雇员已存在');
  end;
  
  PROCEDURE fire_employee(eno number)
  is
  begin
     delete from emp where empno=eno;
     if SQL%NOTFOUND then
        raise_application_error(-20012,'该雇员不存在');
     end if;
  end;
  
  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(-20012,'该雇员不存在');
  end;
end emp_package;

--调用包组件
--注意,当在其他应用程序中调用包的组件时,必须要加包名作为前缀(包名.组件名)
--示例一:在同一个包内调用包组件,如上如示
  PROCEDURE add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
  is
  begin
       if validate_deptno(dno) then
         insert into emp(empno,ename,sal,deptno) values(eno,name,salary,dno);
       else
         raise_application_error(-20010,'不存在该部门');
       end if;
  exception
       when DUP_VAL_ON_INDEX THEN
          raise_application_error(-20011,'该雇员已存在');
  end;

--示例二:调用包公用变量
--在SQL*Plus中调用包公用变量
--当在其他应用程序中调用包的公用变量时,必须要在公用变量名前加包名作为前缀,并且注意其数值在当前会话内一直生效
exec emp_package.g_deptno:=20

--示例三:调用包公用过程
--在SQL*Plus中调用包公用过程
exec emp_package.add_employee(1111,'MARY',2000)

select * from dept;
select * from emp;

--示例四:调用包公用函数
var salary number
exec :salary:=emp_package.get_sal(7788)
print salary

--示例五:以其他用户身份调用包公用组件
--当以其他用户身份调用包的公用组件时,必须在组件名前加用户名和包名作为前缀(用户名.包名.组件名)
exec haiya1.emp_package.add_employee(1155,'SCOTT',1200)

--调用远程数据库包的公用组件
--当调用远程数据库包的公用组件时,在组件名之前加包名作为前缀,在组件名之后需要带有数据库链名作为后缀(包名.组件名@数据库存链名)
exec emp_package.add_employee@orasrv(1116,'SCOTT',1200)

--查看包源代码
select text from user_source where name='EMP_PACKAGE' AND type='PACKAGE';

--删除包
--如果只删除包体,那么可以使用命令DROP PACKAGE BODY;如果同时删除包规范和包体,那么可以使用命令DROP PACKAGE
DROP PACKAGE emp_package;

--使用包重载
--重载是指多个具有相同名称的子程序
--下面以建立使用雇员号和雇员名取得雇员工资、解雇雇员的包规范为例,说明定义重载过程和重载函数的方法
--建立包规范
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;

--建立包体
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,'该雇员不存在');
   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,'该雇员不存在');
   END;
   
   PROCEDURE fire_employee(eno number) IS 
   BEGIN
      DELETE FROM emp where empno=eno;
      if SQL%NOTFOUND then
         raise_application_error(-20020,'该雇员不存在');
      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,'该雇员不存在');
      end if;
   END;
END;

--调用重载过程和重载函数
--在调用重载过程和重载函数时,PL/SQL执行器会自动根据输入参数值的数据类型确定要调用的过程和函数
var sal1 number
var sal2 number
exec :sal1:=overload.get_sal('scott')
exec :sal2:=overload.get_sal(7788)
print sal1 sal2


--使用包构造过程
--在包中定义了全局变量之后,有些情况下,会话中可能还需要初始化全局变量,此时可以使用包的构造过程
--下面以限制老员工工资不能低于雇员的最低工资,并且不能超过雇员的最高工资为例,说明使用包构造过程的方法
--建立包规范
CREATE OR REPLACE PACKAGE emp_package IS
  minsal NUMBER(6,2);
  maxsal number(6,2);
  procedure add_employee(eno number,name varchar2,salary number,dno number);
  procedure upd_sal(eno number,salary number);
  procedure upd_sal(name varchar2,salary number);
END;

--建立包体
--为了运行包组件时将雇员的最低工资和最高工资分别赋值给全局变量minsal和maxsal,需要在包体内编写构造过程。
--包的构造过程没有任何名称,它是在实现了包的其他过程之后,以BEGIN开始,以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,'工资不在范围内');
    end if;
  exception
    when dup_val_on_index then
       raise_application_error(-20002,'该雇员已经存在');
  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,'不存在该雇员号');
       end if;
    else
       raise_application_error(-20001,'工资不在范围内');
    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,'不存在该雇员名');
        end if;
     else
        raise_application_error(-20001,'工资不在范围内');
     end if;
  end;
  
begin
  select min(sal),max(sal) into minsal,maxsal from emp;
end;

--调用包公用组件
--当在同一会话中第一次调用包的公用组件时,会自动执行其构造过程
--而将来调用其他组件时则不会再调用其构造过程,所以构造过程也称为"只调用一次"的过程
exec emp_package.add_employee(1111,'MARY',3000,2)

exec emp_package.upd_sal('mary',2000)

--当工资不在最低工资和最高工资之间时,则会提示错误信息
exec emp_package.upd_sal('mary',5500)

select * from emp;


--使用纯度级别
--当使用包的公用函数时,它既可以作为表达式的一部分使用,也可以在SQL语句中使用。
--但如果要在SQL语句中引用包的公用函数,那么该公用函数不能包含DML语句(INSERT、UPDATE和DELETE),也不能读写远程包的变量。
--为了对包的公用函数加以限制,在定义包规范时可以使用纯度级别(purity level)限制公用函数
--PRAGMA RESTRICT_REFERENCES(function_name,WNDS[,WNPS][,RNDS][,RNPS]);
--如上所示,function_name用于指定已经定义的函数名
--WNDS用于限制函数不能修改数据库数据(也即禁止执行DML操作)
--WNPS用于限制函数不能修改包变量(也即不能给包变量赋值)
--RNDS用于限制函数不能读取数据库数据(也即禁止执行SELECT操作)
--RNPS用于限制函数不能读取包变量(也即不能将包变量赋值给其他变量)

--以下以限制函数不能修改包变量为例,说明使用纯度级别的方法
--建立包规范
--当使用纯度级别限制包的公用函数时,必须首先在包规范中定义函数,然后指定该函数的纯度级别
CREATE OR REPLACE PACKAGE purity IS
  minsal number(6,2);
  maxsal number(6,2);
  function max_sal return number;
  function min_sal return number;
  pragma restrict_references(max_sal,WNPS);
  pragma restrict_references(min_sal,WNPS);
END;

--建立包体
--因为在定义包规范时为函数max_sal和min_sal指定了纯度级别WNPS,所以在这两个函数内不能给变量minsal和maxsal赋值
--错误示例如下
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;

--尽管在函数体内不能为全局变量minsal和maxsal赋值,但却可以读取它们的数据。
--在函数体内正确引用这两个变量的包体如下
--尽管在函数体内不能修改包变量minsal和maxsal,但却可以读取它们的数据(RETURN语句)
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;

--调用包的公用函数
--在SQL*Plus中引用包的全局变量和包的公用函数的示例如下
var minsal number
var maxsal number
exec :minsal:=purity.minsal
exec :maxsal:=purity.max_sal()
print minsal maxsal

 

分享到:
评论

相关推荐

    精通Oracle10编程SQL(1-3)PLSQL基础

    "精通Oracle10编程SQL(11)开发子程序.sql"和"精通Oracle10编程SQL(12)开发包.sql"则转向了PL/SQL编程,这是Oracle的面向过程的编程语言。在这里,你将学习如何编写存储过程、函数、触发器等,它们是数据库中的可重用...

    精通Oracle 10g SQL和PL SQL.zip

    本资源“精通Oracle 10g SQL和PL SQL.zip”提供了全面的学习指南,帮助用户从基础到高级进阶,掌握这两个重要组件的精髓。 SQL(Structured Query Language)是用于管理关系数据库的标准语言,它允许用户创建、查询...

    精通Oracle PLSQL编程

    通过阅读《精通Oracle PLSQL编程》这本书,你将有机会掌握这些核心概念,并逐步成为一个熟练的Oracle数据库开发者。书中的实例和练习将帮助你更好地理解和应用这些技术,为你的职业生涯打下坚实的基础。

    Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(一)

     本书是专门为oracle应用开发人员提供的sql和pl/sql编程指南。通过学习本书,读者不仅可以掌握oracle常用工具oracle universal installer、net comfiguration assistant、sql developer、sql*plus的作用及使用方法...

    《精通Oracle PL/SQL》源码

    《精通Oracle PL/SQL》这本书深入探讨了这个语言的各个方面,旨在帮助读者掌握其精髓并提升在数据库开发中的效率。书中提供的源码示例是学习和理解PL/SQL语法、功能以及最佳实践的重要资源。 在"2174_...

    精通Oracle10g SQL/PL编程.PDF

    总之,《精通Oracle10g SQL/PL编程》是一本全面覆盖Oracle10g数据库管理和开发的教程,无论你是初入数据库领域的新人,还是寻求技术提升的资深开发者,都能从中受益匪浅。通过深入阅读和实践,你将能够熟练运用SQL和...

    精通Oracle10编程

    《精通Oracle10编程》这本书是为那些希望深入理解和掌握Oracle 10及PL/SQL编程的开发者量身定制的指南。 在Oracle 10中,PL/SQL(Procedural Language/Structured Query Language)是一种结合了SQL的声明式编程与...

    精通Oracle10g PL_SQL编

    通过学习《精通Oracle10g PL_SQL编程》,你将能够熟练地运用PL/SQL进行数据库应用程序的开发,解决实际工作中遇到的各种问题,从而提升你的数据库管理和编程能力。无论是初学者还是有经验的开发者,都可以从本教程中...

    精通ORACLE 10G SQL和PL_SQL

    本资源"精通ORACLE 10G SQL和PL_SQL"旨在帮助用户深入理解并熟练掌握这两门语言在实际应用中的技巧。 SQL,全称为结构化查询语言,是用于管理关系数据库的标准语言。在Oracle 10g中,SQL主要分为以下几个部分: 1....

    精通Oracle 10g PL/SQL编程

    总之,《精通Oracle 10g PL/SQL编程》是一本全面的教程,它涵盖了从基础到高级的PL/SQL编程技术,适用于数据库管理员、开发人员以及希望提升Oracle数据库应用技能的IT专业人士。通过深入学习,读者可以掌握在Oracle ...

    精通Oracle 10g PLSQL编程

    Oracle 10g PLSQL编程是数据库管理员和开发人员必须掌握的关键技能之一。PL/SQL,全称为...在实际工作中,不断优化和调整代码,以适应不断变化的业务需求,是成为一名精通Oracle 10g PLSQL编程专家的必经之路。

    Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(二)

     本书是专门为oracle应用开发人员提供的sql和pl/sql编程指南。通过学习本书,读者不仅可以掌握oracle常用工具oracle universal installer、net comfiguration assistant、sql developer、sql*plus的作用及使用方法...

    精通Oracle 10g PL SQL编程

    本文旨在深入解析“精通Oracle 10g PL/SQL编程”这一主题,不仅涵盖基础概念,还将探讨其高级应用,为读者提供一个全面的学习框架。 ### Oracle 10g概述 Oracle 10g(Generation 10)是Oracle公司推出的一款企业级...

    精通Oracle.10g.PLSQL编程

    br>精通Oracle 10g PL/SQL编程 <br> 【作 者】王海亮 林立新 于三禄 郑建茹 【丛 书 名】 万水Oracle技术丛书 <br>http://images.china-pub.com/ebook20001-25000/21975/shupi.jpg<br><br>PL/SQL是...

    oracle pl/sql从入门到精通 配套源代码

    Oracle PL/SQL是一种强大的编程...每个示例都是一次学习的机会,通过分析和运行这些代码,你可以逐步精通Oracle数据库的编程。记住,理论知识固然重要,但实践是检验真理的唯一标准,因此动手实践是掌握PL/SQL的关键。

    ORACLE PL/SQL从入门到精通

    ORACLE PL/SQL是从入门到精通的专业知识,涵盖了数据库开发与管理的多个方面,包括触发器、过程、函数、软件包、异常处理、游标、循环、分支、变量使用、数据库安装等关键知识点。 触发器是数据库中用来保证数据...

    精通Oracle 10g PL.SQL编程

    在提供的"精通Oracle.10g.Pl.SQL编程.doc"和"精通Oracle.10g.Pl.SQL编程.pdf"文件中,应该包含了对这些主题的详细讲解和示例,帮助读者逐步掌握Oracle 10g的PL/SQL编程技巧。通过深入学习和实践,你将能够编写出高效...

    《精通Oracle 10g Pro*C/C++编程》源代码与学习笔记

    《精通Oracle 10g Pro*C/C++编程》是一本专为数据库开发人员设计的教程,作者王海亮和张立民通过这本书深入探讨了如何使用C或C++语言与Oracle 10g数据库进行高效交互。源代码与学习笔记的提供,为读者提供了实践操作...

Global site tag (gtag.js) - Google Analytics