oracle - type

浏览数：14 / 时间：2015年06月12日

本文转载自：http://www.cnblogs.com/o-andy-o/archive/2012/05/25/2517741.html

type定义：

oracle中自定义数据类型
oracle中有基本的数据类型，如number，varchar2，date，numeric，float....但有时候我们需要特殊的格式，

如将name定义为（firstname,lastname）的形式，我们想把这个作为一个表的一列看待，这时候就要我们自己定义一个数据类型
格式：create or replace type type_name as object(parameter1 varchar2(20),parameter2 varchar2(30));
这样子定义之后我们就能像使用基本类型一样使用自定义类型去建表:
如：
create or replace type name_format as object(firstname varchar2(20),lastname varchar2(30));
定义了类型之后，我们就可以将它当作普通的基本类型用了，建表，写function等等，如用自定义数据类型建表：
create table testtable(id number primary key,name name_format);

插入数据时，构造我们的这个自定义列时，就像构造对象一样，如上面的表插入数据的sql语句如下：
insert into testtable values(1,name_format(‘hust‘,‘lkq‘));
查询自定义列的某个值可以用.来访问,如上面我要查询testtable表中name列的firstname的值可以按照如下查询：
select t.name.firstname from testtable t;

自定义数据类型也可以是集合
例如：create or replace type numers_set as array(10) of number;
这就表示创建了一个包含10个number数据的自定义类型，
注：这里的集合里面的基本元素也可以是你自定义的类型，也就是说支持嵌套定义
create or replace type name_list as array(10) of name_format:（此处的name_format就是我们在上面定义的一个自定义类型

将表中查出来的值赋值给ob:

select rec_planratemain(planrgroupmainid,
                                pl.productid,
                                planrateversion,
                                pl.createuserid,
                                pl.createdate,
                                pl.updateuserid,
                                pl.updatedate,
                                ratedatetype,
                                startdate,
                                enddate,
                                p.branchid,
                                channeltype)
          into g_rec_planmainrate -- 实例化后的名字
          from planrgroupmain pl,productbranch_property p
         where pl.productid = ‘PROG0000003001024‘
         and p.productid = pl.productid 
              --and startdate <= p_date
              --and ENDDATE >= p_date
           and p.branchid = ‘BRA0000000000011‘
           and rownum < 2;

OB:

CREATE OR REPLACE TYPE "OB_TEST"    AS OBJECT (
  ca01          VARCHAR2(64),
  ca02          VARCHAR2(32),
  ca03          VARCHAR2(32),
  ca04          VARCHAR2(32),
  ca05          VARCHAR2(32),
  ca06          VARCHAR2(32),
  ca07          VARCHAR2(32),
  ca08          VARCHAR2(32)
  CONSTRUCTOR FUNCTION OB_TEST RETURN SELF AS RESULT
);

NT:

CREATE OR REPLACE TYPE "NT_TEST" is table of OB_TEST;

调用：

function getagentbroker(p_branchid  IN VARCHAR2,
                          p_month     in VARCHAR2,
                          p_agentcode in varchar2,
                          p_type        in varchar2) return nt_test is
    v_Count   SMALLINT DEFAULT 0;
    v_NT_test NT_test;
    v_num     smallint default 10;
  begin
 
     if p_type = ‘B‘  then
        v_num  := 0;
     end if;    
    v_NT_test := NEW nt_test();
   
    for c_ag in (select a.agentid,
                        a.recommendagentid,
                        getagentstatus(a.agentstatus) agentstatus,
                        a.agentcode,
                        a.agentname,
                        a.agentlevelcode,
                        a.hiredate
                   from agenthis a
                  where a.bizyearmonth = p_month
                    and a.agentstatus = ‘AGENTSTATUS_09‘
                    and a.branchid = p_branchid
                    and (a.agentcode =p_agentcode or p_agentcode is null)
                    and (rownum <= v_num or v_num = 0)
                  
                 ) loop
     
      v_Count := v_Count + 1;
      v_NT_test.Extend;
      v_NT_test(v_Count) := ob_test;
     
      v_NT_test(v_Count).ca01 := c_ag.agentname;
      v_NT_test(v_Count).ca02 := c_ag.agentcode;
      v_NT_test(v_Count).ca03 := c_ag.agentlevelcode;
      v_NT_test(v_Count).ca04 := c_ag.agentstatus;
      v_NT_test(v_Count).ca05 := c_ag.hiredate;
     
      ----一代管理人
      if c_ag.recommendagentid is not null then
        begin
          select agentcode,
                 agentname,
                 getagentstatus(agentstatus) agentstatus,
                 agentlevelcode,
                 recommendagentid
            into v_NT_test(v_Count).ca06,
                 v_NT_test(v_Count).ca07,
                 v_NT_test(v_Count).ca08,
                 v_NT_test(v_Count).ca09,
                 v_NT_test(v_Count).ca10
            from agenthis
           where bizyearmonth = ‘201203‘
                -- and agentstatus=‘AGENTSTATUS_09‘
             and agentid = c_ag.recommendagentid;
        exception
          when others then
            v_NT_test(v_Count).ca10 := ‘‘;
        end;
        ----2代管理人
        if v_NT_test(v_Count)
         .ca10 != ‘‘ or v_NT_test(v_Count).ca10 is not null then
          begin
            select agentcode,
                   agentname,
                   getagentstatus(agentstatus) agentstatus,
                   agentlevelcode,
                   recommendagentid
              into v_NT_test(v_Count).ca11,
                   v_NT_test(v_Count).ca12,
                   v_NT_test(v_Count).ca13,
                   v_NT_test(v_Count).ca14,
                   v_NT_test(v_Count).ca15
              from agenthis
             where bizyearmonth = ‘201203‘
                  -- and agentstatus=‘AGENTSTATUS_09‘
               and agentid = v_NT_test(v_Count).ca10;
          exception
            when others then
              v_NT_test(v_Count).ca15 := ‘‘;
          end;
          ----3代管理人
         
          if v_NT_test(v_Count)
           .ca15 != ‘‘ or v_NT_test(v_Count).ca15 is not null then
            begin
              select agentcode,
                     agentname,
                    getagentstatus(agentstatus) agentstatus,
                     agentlevelcode,
                     recommendagentid
                into v_NT_test(v_Count).ca16,
                     v_NT_test(v_Count).ca17,
                     v_NT_test(v_Count).ca18,
                     v_NT_test(v_Count).ca19,
                     v_NT_test(v_Count).ca20
                from agenthis
               where bizyearmonth = ‘201203‘
                    --  and agentstatus=‘AGENTSTATUS_09‘
                 and agentid = v_NT_test(v_Count).ca15;
            exception
              when others then
                v_NT_test(v_Count).ca20 := ‘‘;
            end;
  
          end if;
        end if;
      end if;
    end loop;
    return v_NT_test;
  end;

Type:

CREATE OR REPLACE TYPE "OB_SMSSEND"                                                                          AS OBJECT (
    SMSSENDID          VARCHAR2(64),     -- N ??????
    CREATEUSERID          VARCHAR2(32),     -- Y ????
    CREATEDATE          DATE,     -- N ????
    UPDATEUSERID          VARCHAR2(32),     -- Y ????
    UPDATEDATE          DATE,     -- N ????
    BIZNO          VARCHAR2(32),     -- Y ??????
    SMSBIZCODE          VARCHAR2(16),     -- Y ??????
    SMSTEXTDESC          VARCHAR2(1024),     -- Y ????
    RECVMOBILENO          VARCHAR2(32),     -- Y ????
    APPROVESTATUS          VARCHAR2(32),     -- Y ??????
    SENDSTATUS          VARCHAR2(32),     -- Y ??????
    SENDDATE          DATE,     -- Y ????
    RECVCUSTOMERID          VARCHAR2(64),     -- Y ????
    RECVBACK          VARCHAR2(64),     -- Y ??????
    RECVBACKDATE          DATE,     -- Y ????
    CONSTRUCTOR FUNCTION OB_SMSSEND RETURN SELF AS RESULT
);