oracle包详解（二）【weber出品】

一、重载子程序

PL/SQL中的重载功能:

1. 能够使用户创建两个或多个同名的子程序

2. 要求子程序的形式参数的数量，顺序或者数据类型不一样

3. 能够使用户使用不同的数据灵活的调用子程序

4. 对已经存在的代码的功能的扩展

注意: 重载可以对本地子程序，包，包中的子程序，方法进行重载，不能对标准的子程序进行重载

示例：先创建表和序列

conn scott/tiger

drop table d purge;

create table d as select * from dept where 1=2

create sequence s1
increment by 10 
start with 10
maxvalue 100
cycle
nocache;

　　创建包头：

create or replace package dept_pkg is
  procedure add_dept(v_deptno d.deptno%type,
                     v_dname  d.dname%type,
                     v_loc    d.loc%type);
  procedure add_dept(v_dname d.dname%type, v_loc d.loc%type);
end;

　　创建包体：

create or replace package body dept_pkg is
  procedure add_dept(v_deptno d.deptno%type,
                     v_dname  d.dname%type,
                     v_loc    d.loc%type)
  
   is
  begin
    insert into d values (v_deptno, v_dname, v_loc);
    commit;
  end;

  procedure add_dept(v_dname d.dname%type, v_loc d.loc%type) is
  begin
    insert into d values (s1.nextval, v_dname, v_loc);
    commit;
  end;
end;

　　调用包：

SQL> execute dept_pkg.add_dept(10,'dname','chengdu');
PL/SQL procedure successfully completed

SQL> select * from d;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 dname          chengdu

SQL> exec dept_pkg.add_dept('RESEARCH','DALLAS');

PL/SQL 过程已成功完成。

SQL> execute dept_pkg.add_dept('dname2','chengdu2');
PL/SQL procedure successfully completed

SQL> select * from d;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 dname          chengdu
    10 dname2         chengdu2

重载和标准包:
　　　标准包是oracle内置的包

　　　　大部分内置的包被重载，比如函数：TO_CHAR

　　　如果在本地子程序使用了与标准包子程序相同的名，本地子程序必须使用包名

二、使用前置的声明

块结构语言(比如PL/SQL) 在引用之前必须先声明

 

创建新表：

conn scott/tiger

drop table e purge;

create table e as select empno,ename,deptno from emp where 1=2

创建包头：

create or replace package admin_salary is
  procedure add_emp(eno number, name varchar2, dno number);
end;

创建包体：

create or replace package body admin_salary is
  function check_empno(eno number) return boolean;
  procedure add_emp(eno number, name varchar2, dno number) is
  begin
    if check_empno(eno) then
      insert into e values (eno, name, dno);
      commit;
    else
      dbms_output.put_line('invalidate data');
    end if;
  end;
  function check_empno(eno number) return boolean is
  begin
    if eno between 10 and 50 then
      return true;
    else
      return false;
    end if;
  end;
end;

调用包：

SQL> set serveroutput on

SQL> exec admin_salary.add_emp(5,'ZSAN',10);

invalidate data

PL/SQL 过程已成功完成。

SQL> exec admin_salary.add_emp(20,'ZSAN',10);

PL/SQL 过程已成功完成。

SQL> select * from e;

EMPNO ENAME  DEPTNO
----- ------ ------
   20 ZSAN       10

三、包中的初始化块

这种块在包体内只执行一次，用于初始化公共和私有变量

创建包头：

create or replace package emp_package is
  minsal number(6);
  maxsal number(6);
  procedure add_emp(eno number, name varchar2, salary number);
  procedure upd_sal(eno number, salary number);
  procedure upd_sal(name varchar2, salary number);
end;

创建包体：

create or replace package body emp_package is
  procedure add_emp(eno number, name varchar2, salary number) is
  begin
    if salary between minsal and maxsal then
      insert into emp (empno, ename, sal) values (eno, name, salary);
      commit;
    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 ename = name;
      if sql%notfound then
        raise_application_error(-20003, '不存在该雇员');
      end if;
    else
      raise_application_error(-20001, '工资不在范围之内');
    end if;
  end;
begin
  select min(sal), max(sal) into minsal, maxsal from emp;  ----初始化块
end;

执行过程：

SQL> exec emp_package.add_emp(7777,'ZSAN',80000);

BEGIN emp_package.add_emp(7777,'ZSAN',80000); END;

*
第 1 行出现错误:
ORA-20001: 工资不在范围之内
ORA-06512: 在 "SCOTT.EMP_PACKAGE", line 8
ORA-06512: 在 line 1


SQL> exec emp_package.add_emp(7777,'ZSAN',800);

PL/SQL 过程已成功完成。

SQL> select * from emp;

EMPNO ENAME  JOB         MGR HIREDATE         SAL       COMM DEPTNO
----- ------ --------- ----- -------------- ----- ---------- ------
 7369 SMITH  CLERK      7902 17-12月-80       800                20
 7499 ALLEN  SALESMAN   7698 20-2月 -81      1600        300     30
 7521 WARD   SALESMAN   7698 22-2月 -81      1250        500     30
 7566 JONES  MANAGER    7839 02-4月 -81      2975                20
 7654 MARTIN SALESMAN   7698 28-9月 -81      1250       1400     30
 7698 BLAKE  MANAGER    7839 01-5月 -81      2850                30
 7782 CLARK  MANAGER    7839 09-6月 -81      2450                10
 7788 SCOTT  ANALYST    7566 19-4月 -87      3000                20
 7839 KING   PRESIDENT       17-11月-81      5000                10
 7844 TURNER SALESMAN   7698 08-9月 -81      1500          0     30
 7876 ADAMS  CLERK      7788 23-5月 -87      1100                20
 7900 JAMES  CLERK      7698 03-12月-81       950                30
 7902 FORD   ANALYST    7566 03-12月-81      3000                20
 7934 MILLER CLERK      7782 23-1月 -82      1300                10
 7777 ZSAN                                    800

已选择15行。

SQL> exec emp_package.upd_sal(7788,80000);
BEGIN emp_package.upd_sal(7788,80000); END;

*
第 1 行出现错误:
ORA-20001: 工资不在范围之内
ORA-06512: 在 "SCOTT.EMP_PACKAGE", line 23
ORA-06512: 在 line 1


SQL> exec emp_package.upd_sal(7788,4000);

PL/SQL 过程已成功完成。

SQL> select * from emp where empno=7788;

EMPNO ENAME  JOB         MGR HIREDATE         SAL       COMM DEPTNO
----- ------ --------- ----- -------------- ----- ---------- ------
 7788 SCOTT  ANALYST    7566 19-4月 -87      4000                20

SQL> exec emp_package.upd_sal('SCOTT',3000);

PL/SQL 过程已成功完成。

SQL> select * from emp where empno=7788;

EMPNO ENAME  JOB         MGR HIREDATE         SAL       COMM DEPTNO
----- ------ --------- ----- -------------- ----- ---------- ------
 7788 SCOTT  ANALYST    7566 19-4月 -87      3000                20

四、在SQL中使用包函数和限制

包函数可以用于SQL语句中：

CREATE OR REPLACE PACKAGE taxes_pkg IS
  FUNCTION tax (value IN NUMBER) RETURN NUMBER;
END taxes_pkg;
/
CREATE OR REPLACE PACKAGE BODY taxes_pkg IS
  FUNCTION tax (value IN NUMBER) RETURN NUMBER IS
    rate NUMBER := 0.08;
  BEGIN
    RETURN (value * rate);
  END tax;
END taxes_pkg;
/

SELECT taxes_pkg.tax(salary), salary, last_name--引用包中的函数，必须加包名
FROM   employees;

五、在包中使用PL/SQL记录表：

创建包头和包体：

create or replace package emp_pkg is
  type emp_table_type is table of employees%rowtype index by binary_integer;
  procedure get_emp(emps out emp_table_type);
end;

create or replace package body emp_pkg is
  procedure get_emp(emps out emp_table_type) is
    i binary_integer;
  begin
    for emp_record in (select * from employees) loop
      emps(i) := emp_record;
      i := i + 1;
    end loop;
  end;
end;

SQL中调用：

declare
      employees emp_pkg.emp_table_type;
    begin
      emp_pkg.get_emp(employees);
      for i in employees.first..employees.last loop
      dbms_output.put_line('员工的名：' || employees(i).last_name);
      end loop;
    end;

六、PL/SQL封装

PL/SQL wrapper 是一个单独的工具，通过将PL/SQL源代码转换为其他代码，用来隐藏应用程序内部组件

Wrapping 有以下功能:

1. 平台独立性

2. 动态加载

3. 动态绑定

4. 依赖性检测

当调用时，正常导入和导出

运行Wrapper：

语法：

WRAP INAME=input_file_name [ONAME=output_file_name]

INAME参数是必须的

输入文件默认的扩展名.sql,

ONAME 参数是可选的

输出文件默认的扩展名.plb,

示例:
cd /u01
vi pkg.sql写入如下内容：

create or replace package emp_pkg is
  type emp_table_type is table of employees%rowtype index by binary_integer;
  procedure get_emp(emps out emp_table_type);
end;
/
create or replace package body emp_pkg is
  procedure get_emp(emps out emp_table_type) is
    i binary_integer;
  begin
    for emp_record in (select * from employees) loop
      emps(i) := emp_record;
      i := i + 1;
    end loop;
  end;
end;
/

进行加密：

wrap iname=/u01/pkg.sql oname=/u01/pkg.plb

sqlplus hr/hr

@/u01/pkg.plb

select text from user_source where name='EMP_PKG' and type='PACKAGE';

select text from user_source where name='EMP_PKG' and type='PACKAGE BODY';

包头包体头加密

封装的规则：

可以检测语法错误，不能检测语义错误

输出文件不能被编辑，只能对最初的源代码进行维护，然后再次封装