Oracle中包的使用
包头
创建语法
|
create [or replace] package package_name {IS|AS}
type_definition |
procedure_specification |
function_specification |
variable_declaration |
exception_declaration |
cursor_declaration |
pragma_declaration
end [package_name];
简单案例
包作用：
可以把一些过程和函数组织到一起，把PL/SQL代码模块化，构建其他人员重用的代码
包的说明
也叫包头，包含了有关包内容的信息
create or replace ClassPackage As
--Add a new student into the specified class
PROCEDURE AddStudent (p_studentID in students.id%type,
p_department in classes.department%type,
p_course in classes.course%type);
--Remove the specified student from the specified class
PROCEDURE RemoveStudent (p_studentID in students.id%type,
p_course in classes.course%type);
--Exception raised by RemoveStudent
e_studentNotRegistered EXCEPTION;
--Table type used to hold student info
TYPE t_studentIDTable is table of students.id%type
index by binary_integer;
--Return a PL.SQL table containing the students.id%type
--in the specified class
PROCEDURE ClassList(p_department in classes.department%type,
p_course in classes.course%type,
p_IDs out t_studentIDTable,
p_NumStudents in out binary_integer);
end ClassPackage;
包体
包体是独立于包头的数据字典对象，包头完成编译之后才能进行编译，包体中带有实现包头中描述的前向子程序的代码段。
------------------创建包头----------------------------
create or replace ClassPackage As
--Add a new student into the specified class
PROCEDURE AddStudent (p_studentID in students.id%type,
p_NumStudents in out binary_integer);
end ClassPackage;
---------------创建包体------------------------------
create or replace package BODY ClassPackage AS
---------------添加学生------------------------------
procedure AddStudent
(
p_studentID in students.id%type,
p_course in classes.course%type
) IS
begin
insert into registered_students (student_id,department,couse)
values (p_studentID,p_department,p_course);
end AddStudent;
---------------删除学生-------------------------------
procedure RemoveStudent
(
p_studentID in students.id%type,
p_course in classes.course%type
) IS
begin
delete from registered_students
where student_id = p_studentID
and department = p_department
and couse = p_course
if SQL%NOTFOUND then
raise e_StudentNotRegistered;
endif;
end RemoveStudent;
-------------这里可以使用包头中声明的变量，异常等----
procedure ClassList
(
p_department in classes.department%type,
p_IDs out t_studentIDTable,
p_NumStudents in out binary_integer
) IS
v_studentID registered_students.student_id%type;
CURSOR c_registeredStudents IS
select student_id
from registered_students
where department = p_dapartment
and course = p_course;
begin
p_NumStudents:=0;
open c_registeredStudents;
loop
fetch c_registeredStudents into v_StudentID;
exit when c_registeredStudents%NOTFOUND;
p_NumStudents:=p_NumStudents+1;
p_IDs(p_NumStudents):=v_StudentID;
end loop;
end ClassList;
end ClassPackage;
重载
在包的内部，过程和函数可以被重载，也就是说，可以存在多个名称相同的，但是参数不同的过程和函数。重载允许相同的操作执行在不同的对象上。
重载过程
create or replace package ClassPackage AS
----方式一：
procedure AddStudent
(
p_StudentId in students.id%type,
p_Department in classes.department%type,
p_Course in classes.course
)
----方式二：
procedure AddStudent
(
p_FirstName in students.first_name%type,
p_LastName in students.last_name,
p_Course in classes.course%type
)
.....
end ClassPackage;
create or replace packageBODY ClassPackage AS
----重载：第一种方式添加学生
procedure AddStudent
(
p_StudentId in students.id%type,
p_Course in classes.course
) IS
begin
insert into register_student(student_id,course)
value (p_StudentId,p_Department,p_Course);
end AddStudent;
----重载：第二种方式添加学生
procedure AddStudent
(
p_FirstName in students.first_name%type,
p_LastName in students.last_name%type,
p_Course in classes.course%type
) IS
v_StudentID students.ID%type;
begin
select ID into v_StudentID
where first_name=p_FirstName
and last_name=p_LastName;
insert into register_student(student_id,course)
value (v_StudentID,p_Deparment,p_Course);
end AddStudent;
....
end ClassPackage;
使用重载
----过程一：
Begin
ClassPackage.AddStudent('10001','财务','6666');
end;
----过程二：
Begin
ClassPackage.AddStudent('hello','world','事业','7777');
end;
Oracle的内置包
1. DBMS_ALERT:数据库报警，允许会话间通讯
2. DBMS_JOB：任务调度服务
3. DBMS_LOB：大型对象操作
4. DBMS_PIPE：数据库管道，允许会话间通讯
5. DBMS_SQL：执行动态SQL
6. UTL_FILE：文本文件的输入输出
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
