Oracle程序包

程序包由两部分构成：规范（specification）和主体（body）。

 创建表

create table PEOPLE
(
  ID     NUMBER primary key not null,
  NAME   NVARCHAR2(20) not null,
  STATUS NVARCHAR2(3)
)

insert into people  (id,name,status) values(1,'福康','CLX');
insert into people  (id,name,status) values(2,'陆晓','act');
insert into people  (id,name,status) values(3,'王山','CLX');
insert into people  (id,name,status) values(4,'刘迪','CLX');
insert into people  (id,name,status) values(5,'张玉','CLX');
insert into people  (id,name,status) values(6,'金山','CLX');
insert into people  (id,name,status) values(7,'顺溜','CLX');
insert into people  (id,name,status) values(8,'辞海','CLX');
insert into people  (id,name,status) values(9,'陆珣','CLX');
insert into people  (id,name,status) values(10,'晓笑','CLX');

 

规范：

create or replace package pkg_people as
  peopleString varchar2(200);
  peopleStatus nvarchar2(3) := 'CLX';
  function get_people_string return varchar2;
  procedure update_people(in_id in number);
  procedure insert_people(in_id     number,
                          in_name   in nvarchar2,
                          in_status in nvarchar2);
  procedure delete_people(in_id in number);
end pkg_people;

 主体：

create or replace package body pkg_people as

  --函数get_student_string
  function get_people_string return varchar2 is
  begin
    declare
      cursor cu_people is
        select name from people order by id;
      people_name varchar2(20);
      rowString   varchar2(500);
    
    begin
      open cu_people;
      fetch cu_people
        into people_name;
    
      while cu_people%found loop
        rowString := rowString || people_name || '，';
        fetch cu_people
          into people_name;
      end loop;
    
      return substr(rowString, 1, length(rowString) - 1);
    end;
  end get_people_string;

  --存储过程update_people
  procedure update_people(in_id in number) as
  begin
    update people set status = peopleStatus where id = in_id;
    commit;
  end update_people;

end pkg_people;

 执行程序中函数/存储过程

--调用程序包中的函数get_people_string
select pkg_people.get_people_string() from dual ;
--调用程序包中的存储过程update_people()
begin
  pkg_people.update_people(2);
end;