一、重载子程序
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';
包头包体头加密
封装的规则：
可以检测语法错误，不能检测语义错误
输出文件不能被编辑，只能对最初的源代码进行维护，然后再次封装