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();
end;
上一篇:【甘道夫】HBase(0.96以上版本号)过滤器Filter具体解释及实例代码


下一篇:application.properties详解 --springBoot配置文件【转载】