详细解读Oracle程序包

在Oracle中程序包通常由两部分组成:规范(specification)和主体(body)

1:规范
->规范中一般定义公用的变量、需要组织到程序包中的所有函数和存储过程都会出现在规范中.
2:主体
->主体实现规范中定义的函数和存储过程，而且是必须实现

程序包事列

--创建程序包规换
--注意一下几点:
--1:函数不要写is过程的实现
--2:存储过程不要写as的过程的实现
--3:函数和存储过程不需要create or replace命令
--4:声明变量时不要用declare
create or replace package pkg_students as
       student_str varchar2(500);--声明变量时不要用declare
       student_age number :=18;
       function get_student_string return varchar2;
       procedure update_student  --函数和存储过程不需要create or replace命令
                 (
                        in_student_id in number,
                        in_student_name in varchar2,
                        in_student_age in number
                 );
       procedure delete_student(in_student_id in number);
end pkg_students;

--在数据字典中查看程序包的规范信息
select object_name,object_type,status from user_objects where lower(object_name)='pkg_students';

--创建程序包主体
--注意一下几点:
--1:规范中的参数定义要和主体的中的参数定义要一致,参数名也不允许改动
--2:规范中定义的函数和存储过程必须在主体中全部实现
--3:在主体中实现函数时,不需要create or replace命令
create or replace package body pkg_students as
       --规范中函数体的实现get_student_string
       function get_student_string
       return varchar2 is
       begin
              declare cursor cu_pkg_student is
              select student_name 
              from students order by student_id;
              student_name varchar2(20);
              back_row_string varchar2(500);
              begin
                       open cu_pkg_student;             --别忘了打开游标
                       fetch cu_pkg_student into student_name;
                       while cu_pkg_student%found loop
                             back_row_string := student_name || ',' || back_row_string ;
                             fetch cu_pkg_student into student_name;
                       end loop;
                       return substr(back_row_string,1,length(back_row_string)-1);
              end;
       end get_student_string;
       --规范中更新学生信息的存储过程的实现update_student
       procedure UPDATE_STUDENT
                     (
                       in_student_id          in number,--参数的定义必须和主体中参数名称和类型一致
                       in_student_name        in varchar2,
                       in_student_age         in number
                     )as
                     begin
                       update students set 
                                       student_name=in_student_name,
                                       student_age =in_student_age
                                       where student_id =in_student_id;
                       commit;
                     end UPDATE_STUDENT;

       --规范中删除学生信息的存储过程的实现delete_student
       procedure DELETE_STUDENT 
                     (
                       in_student_id   in number
                     )as
                     begin
                       delete from students where student_id = in_student_id;
                       commit;
                     end DELETE_STUDENT;
end pkg_students;

--调用程序包中参数和存储过程
select pkg_students.get_student_string() from dual;
begin
       pkg_students.delete_student(10);
end;

select * from students;