oracle 程序包基础之基础

程序包可以将若干个函数和存储过程组织起来，作为一个对象进行存储。
程序包由两部分构成：规范(specification) 和主体(body)

规范：是程序包的公共接口。类似于面向对象编程中的接口-只定义方法名称和参数，并不真正实现方法。

1.创建程序包规范(create or replace package)
  演示创建基于表students的规范
  create or replace package pkg_students as
         studentString varchar2(500);
         studentAge number :=18;
         function getStudentNameString return varchar2;
         procedure update_student(in_student_id in varchar2);
         procedure insert_student(in_student_id in varchar2,in_student_name in varchar2,
           in_student_age in number);
          procedure delete_student(in_student_id in varchar2);
   end pkg_students;
   
   说明：StudentString varchar2(500) 用于定义一个名为studentString 的可变字符串类型的变量；
   studentAge number :=18;用于定义一个名为studentAge的数值型变量，并将变量赋值为3；
   function getStudentNameString return varchar2;声明了一个函数；
   procedure update_student(in_student_id in varchar2);声明了存储过程；
     
   主体
   主体是真正实现功能的地方。主体必须实现规范中定义的函数和过程。
   1、创建程序包主体(create or replace body)
       create or replace package body pkg_students as
       ........       
       end pkg_students;
       
       具体
       
       create or replace package body pkg_students as

            function getStudentNameString return varchar2 as
              begin
                declare cursor cu_student is
                select student_name from students order by student_id;
                student_name varchar2(180);
                rowString varchar2(1000);

                begin
                  open cu_student;
                  fetch cu_student into student_name;
                  while cu_student%found loop
                    rowString :=rowString || student_name || ',';
                    fetch cu_student into student_name;
                   end loop;
                   return substr(rowString,1,length(rowString)-1);
               end;
              end getStudentNameString;


             procedure update_student(in_student_id in varchar2)  as
              begin
                update students set student_age = studentAge where student_id = in_student_id;
                commit;
              end update_student;

             procedure insert_student(in_student_id in varchar2,
               in_student_name in varchar2,
               in_student_age in number) as
               begin
                 insert into students values(
                        in_student_id,in_student_name,in_student_age
                 );
                 commit;
                end insert_student;

             procedure delete_student(in_student_id in varchar2) as
               begin
                 delete from students where student_id = in_student_id;
               commit;
               end delete_student;
         end pkg_students;

     2、调用程序包中 的函数/存储过程
     
         调用程序包中的函数
              select pkg_students.getStudentNameString() from dual;
              
         调用程序包中的存储过程
               begin pkg_students.delete_student(3);
               end;
         存储过程中使用程序包中变量
               begin pkg_students.update_student(9);
               end;
               将学生id=9的学生年龄改为18
           studentAge number :=18;在程序包规范中声明的变量 可以在主体存储过程中使用