ORACLE

/*

创建包

*/

createorreplacepackagezhl_packageis

functionzhl_getcustomer(zhl_flowtypeidininteger,zhl_owneridininteger)

returnzhl_customerinfo;

endzhl_package;

-----------------------------------------------------------------------------

/*

创建类型续于基类

*/

createorreplacetypezhl_customerinfoistableofzhl_first_customerinfo;

-----------------------------------------------------------------------------

/*

创建基类型

*/

createorreplacetypezhl_first_customerinfoasobject

(

customeridvarchar2(6),

owneridinteger,

ownernamevarchar2(8),

customernamevarchar2(20),

flowtypeidinteger,

flowtypenamevarchar2(10),

enterupdatetimedate,

constructorfunctionzhl_first_customerinforeturnselfasresult

)

-----------------------------------------------------------------------------

/*

创建类型体

*/

createorreplacetypebodyzhl_first_customerinfois

constructorfunctionzhl_first_customerinforeturnselfasresultis

begin

return;

end;

end;

-----------------------------------------------------------------------------

/*创建包体

*/

createorreplacepackagebodyzhl_packageis

/*

创建函数

*/

functionzhl_getcustomeris

(zhl_flowtypeidininteger,

zhl_owneridininteger)

returnzhl_customerinfois

get_zhl_customerinfozhl_customerinfo;

iSMALLINT;

begin

get_zhl_customerinfo:=zhl_customerinfo();

i:=0;

forcinforin(withzhl_customeras(select*fromcustomerwheredeleteflag=0)

select

a.customerid,

a.customername,

a.ownerid,

c.ownername,

a.flowtypeid,

b.flowtypename,

a.enterupdatetime

fromzhl_customera,

(selectz.customeridid,d.nameflowtypenamefromzhl_customerz,datadictionarydwherez.flowtypeid=d.idandd.deleteflag=0)b,

(selectz.customeridid,m.usernameownernamefromzhl_customerz,mp_usermwherez.ownerid=m.idandm.deleteflag=0)c

wherea.customerid=b.idanda.customerid=c.idanda.ownerid=zhl_owneridanda.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;

endloop;

returnget_zhl_customerinfo;

endzhl_getcustomer;

endzhl_package;

-----------------------------------------------------------------------------

/*

创建简单的类型

*/

declare

typezhl_stringistableofvarchar2(10);

z_stringzhl_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;