包头(Package)与包体(Package body)的应用
1.将有联系的对象打成包,方便使用
2.包中对象包括储存过程,函数,游标,自定义类型和变量,可以在PL_SQL块中应用这些对象.
定义包头:
----------------------------------------------------------------------------------------------
create or replace package <Package_name> is
type <TypeName> is <Datatype>;--定义类型
-- Public constant declarations
<ConstantName> constant <Datatype> := <Value>;--声明常量
-- Public variable declarations
<VariableName> <Datatype>; --数据类型
-- Public function and procedure declarations
function <FunctionName>(<Parameter> <Datatype>) return <Datatype>; --函数
end <Package_name>;
定义包体:
----------------------------------------------------------------------------------------------
create or replace package body <Package_name> is
-- Private type declarations
type <TypeName> is <Datatype>;
-- Private constant declarations
<ConstantName> constant <Datatype> := <Value>
-- Private variable declarations
<VariableName> <Datatype>;
-- Function and procedure implementations
function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is --函数的具体内容
<LocalVariable> <Datatype>;
begin
<Statement>;
return(<Result>);
end;
begin
-- Initialization--初始化包体,每次调用时被初始化
<Statement>;
end <Package_name>;
只有当包头编辑成功后才能编辑包体.其中的函数名与过程名须和包头中的函数过程一样.
学习包应用的一个例子:
包的作用: 根据出生年月返回年龄function Getage,返回工资function Getsalary
--创建环境
Create Table T_PsnSalary --工资表
(
Fpsncode varchar(4) default '', --个人代码
Fpsndesc varchar(20) default '', --描述
FpsnBirth varchar(20) default '', --生日
FpsnSalary number(8,2) --工资
);
--添加数据
Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C001','张三','1986.01.10',1100);
Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C002','李四','1980.10.10',3000);
Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C003','王五','1996.12.10',800);
commit;
--创建包头
create or replace package A_GetData is
function Getage(birthst varchar,birthend varchar) return integer;
function Getsalary(VFpsncode varchar) return number;
end A_Getdata;
--创建包体
create or replace package body A_GETDATA is
function Getage(birthst varchar,birthend varchar) return integer --得到年龄函数
is
V_birth integer;
ToDateEnd Date;
Toyear number(4);
Tomonth number(4);
Fromyear number(4);
Frommonth number(4);
begin
if (birthend='') or (birthend is null) then
select sysdate into ToDateEnd from dual; --得到系统时间
end if;
Toyear := to_number(to_char(ToDateEnd,'YYYY')); --得到最后年月
Tomonth := to_number(to_char(ToDateEnd,'MM'));
Fromyear := to_number(substr(birthst,1,4));--计算的年月
Frommonth := to_number(substr(birthst,6,2));
if Tomonth-Frommonth>0 then V_birth:=Toyear-fromyear;
else V_birth:=Toyear-fromyear-1;
end if;
return(V_birth);
end Getage;
function getSalary(VFpsncode varchar) return number--返回工资情况
is
V_psnSalary number(8,2);
begin
Select FpsnSalary into V_psnSalary from T_PsnSalary where Fpsncode=VFpsncode;
return(V_psnSalary);
end getSalary;
end A_GETDATA;
--测试
select a.*,A_getdata.Getage(Fpsnbirth,'')age from T_psnsalary a; --调用包得到年龄功能
select A_getdata.Getsalary('C001') from dual; --代码得到工资