ORACLE

/*
创建包
*/
create or replace package zhl_package is
function zhl_getcustomer(zhl_flowtypeid in integer, zhl_ownerid in integer)
return zhl_customerinfo;
end zhl_package;

-----------------------------------------------------------------------------
/*
创建类型续于基类
*/
create or replace type zhl_customerinfo is table of zhl_first_customerinfo;
-----------------------------------------------------------------------------
/*
创建基类型
*/
create or replace type zhl_first_customerinfo as object
(
customerid varchar2(6),
ownerid integer,
ownername varchar2(8),
customername varchar2(20),
flowtypeid integer,
flowtypename varchar2(10),
enterupdatetime date,
constructor function zhl_first_customerinfo return self as result
)
-----------------------------------------------------------------------------
/*
创建类型体
*/
create or replace type body zhl_first_customerinfo is
constructor function zhl_first_customerinfo return self as result is
begin
return ;
end;
end;

-----------------------------------------------------------------------------
/* 创建包体
*/
create or replace package body zhl_package is
/*
创建函数
*/
function zhl_getcustomer is
(zhl_flowtypeid in integer ,
zhl_ownerid in integer)
return zhl_customerinfo is
get_zhl_customerinfo zhl_customerinfo;
i SMALLINT;
begin
get_zhl_customerinfo := zhl_customerinfo();
i := 0;
for cinfor in ( with zhl_customer as (select * from customer where deleteflag = 0)
select
a.customerid,
a.customername,
a.ownerid,
c.ownername,
a.flowtypeid,
b.flowtypename,
a.enterupdatetime
from zhl_customer a,
(select z.customerid id,d.name flowtypename from zhl_customer z,datadictionary d where z.flowtypeid = d.id and d.deleteflag = 0) b,
(select z.customerid id,m.username ownername from zhl_customer z,mp_user m where z.ownerid = m.id and m.deleteflag = 0) c
where a.customerid = b.id and a.customerid = c.id and a.ownerid = zhl_ownerid and a.flowtypeid = zhl_flowtypeid)
loop
get_zhl_customerinfo.extend;
i := i+1;
get_zhl_customerinfo(i) := zhl_first_customerinfo();
get_zhl_customerinfo(i).customerid := cinfo.customerid;
get_zhl_customerinfo(i).customername := cinfo.customername;
get_zhl_customerinfo(i).ownerid := cinfo.ownerid;
get_zhl_customerinfo(i).ownername := cinfo.ownername;
get_zhl_customerinfo(i).flowtypeid := cinfo.flowtypeid;
get_zhl_customerinfo(i).flowtypename := cinfo.flowtypename;
get_zhl_customerinfo(i).enterupdatetime := cinfo.enterupdatetime;
end loop;
return get_zhl_customerinfo;
end zhl_getcustomer;
end zhl_package;
-----------------------------------------------------------------------------

/*
创建简单的类型
*/
declare
type zhl_string is table of varchar2(10);
z_string zhl_string;
begin
z_string := zhl_string('z','h','l');
z_string.extend;
z_string(1) := 'ang';
z_string(2) := 'ing';
z_string(3) := 'qaz';
dbms_output.put_line(z_string(3));
end;