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

数据库  /  管理员 发布于 5年前   298

/* *开发包 *包用于逻辑组合相关的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 numberexec :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 PACKAGEDROP 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 numbervar sal2 numberexec :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 numbervar maxsal numberexec :minsal:=purity.minsalexec :maxsal:=purity.max_sal()print minsal maxsal