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;