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_department in classes.department%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_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_department in classes.department%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 package BODY ClassPackage AS ---------------添加学生------------------------------ procedure AddStudent ( p_studentID in students.id%type, p_department in classes.department%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_department in classes.department%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_course in classes.course%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_Department in classes.department%type, p_Course in classes.course%type ) ..... end ClassPackage; create or replace packageBODY ClassPackage AS ----重载:第一种方式添加学生 procedure AddStudent ( p_StudentId in students.id%type, p_Department in classes.department%type, p_Course in classes.course ) IS begin insert into register_student(student_id,department,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_Department in classes.department%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,department,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:文本文件的输入输出