oracle程序包介绍,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;