Oracle数据库之PL/SQL包

Oracle数据库之PL/SQL包

1. 简介

包(PACKAGE)是一种数据对象,它是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合,作为一个完整的单元存储在数据库中,用名称来标识。

包类似于Java或C#语言中的类,包中的变量相当于类中的成员变量,过程和函数相当于类方法。

通过使用包,可以简化应用程序设计,提高应用性能,实现信息隐藏、子程序重载等面向对象语言所具有的功能。

与高级语言中的类相同,包中的程序元素也分为公用元素和私用元素两种,这两种元素的区别是他们允许访问的程序范围不同。公用元素不仅可以被包中的函数、过程所调用,也可以被包外的PL/SQL程序访问,而私有元素只能被包内的函数和过程序所访问。

一般是先编写独立的过程与函数,待其较为完善或经过充分验证无误后,再按逻辑相关性组织为程序包。

2. 包的优点

  1. 模块化:使用包,可以封装相关的类型、对象和子程序。把一个大的功能模块划分成多个小的功能模块,分别完成各自的功能,这样组织的程序易于编写,理解和管理。
  2. 更轻松的应用程序设计:包规范部分和包体部分可以分别创建并编译。换言之,我们可以在没有编写包体的情况下编写包规范的代码并进行编译。
  3. 信息隐藏:包中的元素可以分为公有元素和私有元素,公有元素可被程序包内的过程、函数等访问,还可以被包外的PL/SQL访问。但对于私有元素只能被包内的过程、函数等访问。对于用户,只需知道包规范,不用了解包体的具体细节。
  4. 性能更佳:应用程序第一次调用程序包中的某个元素时,就将整个程序包加载到内存中,当第二次访问程序包中的元素时,ORACLE将直接从内在中读取,而不需要进行磁盘I/O操作而影响速度,同时位于内存中的程序包可被同一会话期间的其它应用程序共享。因此,程序包增加了重用性并改善了多用户、多应用程序环境的效率。

3. 包的定义

PL/SQL中的包由包规范和包体两部分组成。建立包时,首先要建立包规范,然后再建立对包规范的实现–包体。

包规范用于声明包的公用组件,如变量、常量、自定义数据类型、异常、过程、函数、游标等。包规范中定义的公有组件不仅可以在包内使用,还可以由包外其他过程、函数使用。但需要说明与注意的是,为了实现信息的隐藏,建议不要将所有组件都放在包规范处声明,只应把公共组件放在包规范部分。

包体是包的具体实现细节,它实现在包规范中声明的所有公有过程、函数、游标等。也可以在包体中声明仅属于自己的私有过程、函数、游标等。

3.1 建立包规范

语法:

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
PACKAGE [ schema. ] package_name
[ invoker_rights_clause ]
{ IS | AS } item_list_1 END [ package_name ] ;

完整语法结构见:http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_package.htm#LNPLS01371

说明:

package_name:包名。

invoker_rights_clause:使用谁的权限运行,格式如下:

AUTHID { CURRENT_USER | DEFINER }

item_list_1:声明包的公用组件列表

{ type_definition -- 数据类型
| cursor_declaration -- 游标
| item_declaration -- 变量、常量等
| function_declaration -- 函数
| procedure_declaration -- 过程
}
[ { type_definition
| cursor_declaration
| item_declaration
| function_declaration
| procedure_declaration
| pragma
}
]...

示例:

CREATE OR REPLACE PACKAGE emp_mgmt AS
-- 函数
FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,
manager_id NUMBER, salary NUMBER,
commission_pct NUMBER, department_id NUMBER)
RETURN NUMBER;
FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER;
-- 过程
PROCEDURE remove_emp(employee_id NUMBER);
PROCEDURE remove_dept(department_id NUMBER);
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
-- 异常
no_comm EXCEPTION;
no_sal EXCEPTION;
END emp_mgmt;

3.2 建立包体

语法:

CREATE [ OR REPLACE ] PACKAGE BODY [ schema. ] package_name
{ IS | AS }
BEGIN statement [ statement | pragma ]...
[ EXCEPTION exception_handler [ exception_handler ]... ]
[ initialize_section ]
END [ package_name ] ;

详细语法结构见:http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_package_body.htm#LNPLS01372

示例:

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
tot_emps NUMBER;
tot_depts NUMBER;
FUNCTION hire
(last_name VARCHAR2, job_id VARCHAR2,
manager_id NUMBER, salary NUMBER,
commission_pct NUMBER, department_id NUMBER)
RETURN NUMBER IS new_empno NUMBER;
BEGIN
SELECT employees_seq.NEXTVAL
INTO new_empno
FROM DUAL;
INSERT INTO employees
VALUES (new_empno, 'First', 'Last','first.example@example.com',
'(415)555-0100','18-JUN-02','IT_PROG',90000000,00,
100,110);
tot_emps := tot_emps + 1;
RETURN(new_empno);
END;
FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER IS
new_deptno NUMBER;
BEGIN
SELECT departments_seq.NEXTVAL
INTO new_deptno
FROM dual;
INSERT INTO departments
VALUES (new_deptno, 'department name', 100, 1700);
tot_depts := tot_depts + 1;
RETURN(new_deptno);
END;
PROCEDURE remove_emp (employee_id NUMBER) IS
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
PROCEDURE remove_dept(department_id NUMBER) IS
BEGIN
DELETE FROM departments
WHERE departments.department_id = remove_dept.department_id;
tot_depts := tot_depts - 1;
SELECT COUNT(*) INTO tot_emps FROM employees;
END;
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
curr_sal NUMBER;
BEGIN
SELECT salary INTO curr_sal FROM employees
WHERE employees.employee_id = increase_sal.employee_id;
IF curr_sal IS NULL
THEN RAISE no_sal;
ELSE
UPDATE employees
SET salary = salary + salary_incr
WHERE employee_id = employee_id;
END IF;
END;
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
curr_comm NUMBER;
BEGIN
SELECT commission_pct
INTO curr_comm
FROM employees
WHERE employees.employee_id = increase_comm.employee_id;
IF curr_comm IS NULL
THEN RAISE no_comm;
ELSE
UPDATE employees
SET commission_pct = commission_pct + comm_incr;
END IF;
END;
END emp_mgmt;

4. 调用包的组件

包的名称是唯一的,但对于两个包中的公有组件的名称可以相同,用“包名.公有组件名“加以区分。

示例:

DECLARE
new_dno NUMBER; -- 部门编号
BEGIN
-- 调用emp_mgmt包的create_dept函数创建部门:
new_dno := emp_mgmt.create_dept(85, 100);
DBMS_OUTPUT.PUT_LINE('部门编号:' || new_dno); -- 调用emp_mgmt包的increase_sal过程为员工加薪:
emp_mgmt.increase_sal(23, 800);
END;

5. 包中的游标

在包中使用无参游标,示例:

--定义包规范
CREATE OR REPLACE PACKAGE PKG_STU IS
CURSOR getStuInfo RETURN stuInfo%ROWTYPE;
END PKG_STU; --定义包体
CREATE OR REPLACE PACKAGE BODY PKG_STU AS
CURSOR getStuInfo RETURN stuInfo%ROWTYPE IS
SELECT * FROM stuInfo;
END PKG_STU; --调用包组件
BEGIN
FOR stu_Record IN PKG_STU.getStuInfo LOOP
DBMS_OUTPUT.PUT_LINE('学员姓名:'||stu_Record.name||',学号:'||stu_Record.id||',年龄:'||stu_Record.age);
END LOOP;
END;

在包中使用有参数的游标,示例:

--定义包规范
CREATE OR REPLACE PACKAGE PKG_STU IS
CURSOR getStuInfo(studentNo VARCHAR2) RETURN stuInfo%ROWTYPE;
END PKG_STU; --定义包体
CREATE OR REPLACE PACKAGE BODY PKG_STU AS
CURSOR getStuInfo(studentNo VARCHAR2) RETURN stuInfo%ROWTYPE IS
SELECT * FROM stuInfo WHERE id=studentNo;
END PKG_STU; --调用包组件
BEGIN
FOR stu_Record IN PKG_STU.getStuInfo(2) LOOP
DBMS_OUTPUT.PUT_LINE('学员姓名:'||stu_Record.name||',学号:'||stu_Record.id||',年龄:'||stu_Record.age);
END LOOP;
END;

由于游标变量是一个指针,其状态是不确定的,因此它不能随同包存储在数据库中,即不能在PL/SQL包中声明游标变量。但在包中可以创建游标变量参照类型,并可向包中的子程序传递游标变量参数。

示例:

-- 创建包规范
CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS
TYPE dept_cur_type IS REF CURSOR RETURN dept%ROWTYPE; --强类型 TYPE cur_type IS REF CURSOR;-- 弱类型 PROCEDURE proc_open_dept_var(
dept_cur IN OUT dept_cur_type,
choice INTEGER DEFAULT 0,
dept_no NUMBER DEFAULT 50,
dept_name VARCHAR DEFAULT '%');
END; -- 创建包体
CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG
AS
PROCEDURE proc_open_dept_var(
dept_cur IN OUT dept_cur_type,
choice INTEGER DEFAULT 0,
dept_no NUMBER DEFAULT 50,
dept_name VARCHAR DEFAULT '%')
IS
BEGIN
IF choice = 1 THEN
OPEN dept_cur FOR SELECT * FROM dept WHERE deptno = dept_no;
ELSIF choice = 2 THEN
OPEN dept_cur FOR SELECT * FROM dept WHERE dname LIKE dept_name;
ELSE
OPEN dept_cur FOR SELECT * FROM dept;
END IF;
END proc_open_dept_var;
END CURROR_VARIBAL_PKG;

定义一个过程,打开弱类型的游标变量:

--定义过程
CREATE OR REPLACE PROCEDURE proc_open_cur_type(
cur IN OUT CURROR_VARIBAL_PKG.cur_type,
first_cap_in_table_name CHAR)
AS
BEGIN
IF first_cap_in_table_name = 'D' THEN
OPEN cur FOR SELECT * FROM dept;
ELSE
OPEN cur FOR SELECT * FROM emp;
END IF;
END proc_open_cur_type;

测试包中游标变量类型的使用:

DECLARE
dept_rec Dept%ROWTYPE;
emp_rec Emp%ROWTYPE;
dept_cur CURROR_VARIBAL_PKG.dept_cur_type;
cur CURROR_VARIBAL_PKG.cur_type;
BEGIN
DBMS_OUTPUT.PUT_LINE('游标变量强类型:');
CURROR_VARIBAL_PKG.proc_open_dept_var(dept_cur, 1, 30);
FETCH dept_cur INTO dept_rec;
WHILE dept_cur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||':'||dept_rec.dname);
FETCH dept_cur INTO dept_rec;
END LOOP;
CLOSE dept_cur; DBMS_OUTPUT.PUT_LINE('游标变量弱类型:');
CURROR_VARIBAL_PKG.proc_open_dept_var(cur, 2, dept_name => 'A%');
FETCH cur INTO dept_rec;
WHILE cur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||':'||dept_rec.dname);
FETCH cur INTO dept_rec;
END LOOP; DBMS_OUTPUT.PUT_LINE('游标变量弱类型—dept表:');
proc_open_cur_type(cur, 'D');
FETCH cur INTO dept_rec;
WHILE cur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||':'||dept_rec.dname);
FETCH cur INTO dept_rec;
END LOOP; DBMS_OUTPUT.PUT_LINE('游标变量弱类型—emp表:');
proc_open_cur_type(cur, 'E');
FETCH cur INTO emp_rec;
WHILE cur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||':'||emp_rec.ename);
FETCH cur INTO emp_rec;
END LOOP;
CLOSE cur;
END;

6. 子程序重载

所谓重载时指两个或多个子程序有相同的名称,但拥有不同的参数变量、参数顺序或参数数据类型。

在调用重载子程序时,主程序将根据实际参数的类型和数目,自动确定调用哪个子程序。

PL/SQL允许对包内子程序和本地子程序进行重载。

示例:

-- 定义包规范
CREATE OR REPLACE PACKAGE PKG_EMP AS
FUNCTION get_salary(eno NUMBER) RETURN NUMBER;
FUNCTION get_salary(empname VARCHAR2) RETURN NUMBER;
END PKG_EMP; -- 定义包体
CREATE OR REPLACE PACKAGE BODY PKG_EMP AS
FUNCTION get_salary(eno NUMBER) RETURN NUMBER
IS
v_salary NUMBER(10, 4);
BEGIN
SELECT sal INTO v_salary FROM emp WHERE empno=eno;
RETURN v_salary;
END; FUNCTION get_salary(empname VARCHAR2) RETURN NUMBER
IS
v_salary NUMBER(10, 4);
BEGIN
SELECT sal INTO v_salary FROM emp WHERE ename=empname;
RETURN v_salary;
END;
END PKG_EMP;

测试:

DECLARE
v_sal NUMBER(10, 4);
BEGIN
v_sal := PKG_EMP.get_salary(7499);
DBMS_OUTPUT.PUT_LINE('工资:' || v_sal);
v_sal := PKG_EMP.get_salary('MARTIN');
DBMS_OUTPUT.PUT_LINE('工资:' || v_sal);
END;
上一篇:can't run as root without the -u switch


下一篇:Core Data 更新某条指定记录数据