首页 > 代码库 > oracle包详解（二）【weber出品】
oracle包详解（二）【weber出品】
一、重载子程序
PL/SQL中的重载功能:
1. 能够使用户创建两个或多个同名的子程序
2. 要求子程序的形式参数的数量，顺序或者数据类型不一样
3. 能够使用户使用不同的数据灵活的调用子程序
4. 对已经存在的代码的功能的扩展
注意: 重载可以对本地子程序，包，包中的子程序，方法进行重载，不能对标准的子程序进行重载
示例：先创建表和序列
conn scott/tigerdrop table d purge;create table d as select * from dept where 1=2create sequence s1increment by 10 start with 10maxvalue 100cyclenocache;
创建包头：
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 completedSQL> select * from d;DEPTNO DNAME LOC------ -------------- ------------- 10 dname chengduSQL> exec dept_pkg.add_dept(‘RESEARCH‘,‘DALLAS‘);PL/SQL 过程已成功完成。SQL> execute dept_pkg.add_dept(‘dname2‘,‘chengdu2‘);PL/SQL procedure successfully completedSQL> select * from d;DEPTNO DNAME LOC------ -------------- ------------- 10 dname chengdu 10 dname2 chengdu2
重载和标准包:
标准包是oracle内置的包
大部分内置的包被重载，比如函数：TO_CHAR
如果在本地子程序使用了与标准包子程序相同的名，本地子程序必须使用包名
二、使用前置的声明
块结构语言(比如PL/SQL) 在引用之前必须先声明
创建新表：
conn scott/tigerdrop 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 onSQL> exec admin_salary.add_emp(5,‘ZSAN‘,10);invalidate dataPL/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 8ORA-06512: 在 line 1SQL> 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 23ORA-06512: 在 line 1SQL> 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 20SQL> 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.plbsqlplus hr/hr@/u01/pkg.plbselect text from user_source where name=‘EMP_PKG‘ and type=‘PACKAGE‘;select text from user_source where name=‘EMP_PKG‘ and type=‘PACKAGE BODY‘;
包头包体头加密
封装的规则：
可以检测语法错误，不能检测语义错误
输出文件不能被编辑，只能对最初的源代码进行维护，然后再次封装
oracle包详解（二）【weber出品】
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。