oracle - type
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:
NT:
调用:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
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:
1 CREATE OR REPLACE TYPE "OB_SMSSEND" AS OBJECT ( 2 SMSSENDID VARCHAR2(64), -- N ?????? 3 CREATEUSERID VARCHAR2(32), -- Y ???? 4 CREATEDATE DATE, -- N ???? 5 UPDATEUSERID VARCHAR2(32), -- Y ???? 6 UPDATEDATE DATE, -- N ???? 7 BIZNO VARCHAR2(32), -- Y ?????? 8 SMSBIZCODE VARCHAR2(16), -- Y ?????? 9 SMSTEXTDESC VARCHAR2(1024), -- Y ???? 10 RECVMOBILENO VARCHAR2(32), -- Y ???? 11 APPROVESTATUS VARCHAR2(32), -- Y ?????? 12 SENDSTATUS VARCHAR2(32), -- Y ?????? 13 SENDDATE DATE, -- Y ???? 14 RECVCUSTOMERID VARCHAR2(64), -- Y ???? 15 RECVBACK VARCHAR2(64), -- Y ?????? 16 RECVBACKDATE DATE, -- Y ???? 17 CONSTRUCTOR FUNCTION OB_SMSSEND RETURN SELF AS RESULT 18 );
转自:http://www.cnblogs.com/o-andy-o/archive/2012/05/25/2517741.html