首页 > 代码库 > (转)oracle - type

(转)oracle - type

本文转载自: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);

一步一个脚印,方便自己复习,该出手时就出手,有错误,一定要指正,非常感谢,共同进步!

(转)oracle - type