oracle 自定义类型研究

hice2004

一.自定义数组类型

语法：create type [myarray] as varray(数组长度) of varchar2(长度);

create type myarray as varray(10) of varchar2(10);

create table mytable(id number(10), course myarray);

insert into mytable values(1,myarray('java','oracle'));

insert into mytable values(2,myarray('java','oracle','db'));

insert into mytable values(3,myarray('java','oracle','db','c'));

commit;

dump redofile

1.

col  0: [ 2]  c1 02

col  1: [19]  88 01 13 01 01 00 02 04 6a 61 76 61 06 6f 72 61 63 6c 65

2.

col  0: [ 2]  c1 03

col  1: [22]

 88 01 16 01 01 00 03 04 6a 61 76 61 06 6f 72 61 63 6c 65 02 64 62

3.

col  0: [ 2]  c1 04

col  1: [24]

 88 01 18 01 01 00 04 04 6a 61 76 61 06 6f 72 61 63 6c 65 02 64 62 01 63

说明自定义的数组类型 还是会放在一个col里，

88 01

13 col长度

01 01 

00 02 数组长度

04 第一个数组成员长度

6a 61 76 61 第一个数组成员内容

06 第二个数组成员长度

6f 72 61 63 6c 65 第二个数组成员内容

上面的例子里varrary的长度是10，当我把长度设为254时：

如：create type myarray2 as varray(254) of number(10);

insert into mytable values(1,myarray(111,222));

insert into mytable values(2,myarray(111,222,333));

insert into mytable values(3,myarray(111,222,333,444));

插入3行，commit.

观察dump datafile结果：

00 01 00 00 00 00 00 01 00 00 00 19 56 8f 00 1f 09 00 00 00 00 00 00 0f 00

00 00 00 00 01 88 01 0f 01 01 00 02 03 c2 02 0c 03 c2 03 17

00 01 00 00 00 00 00 01 00 00 00 19 56 90 00 23 09 00 00 00 00 00 00 13 00

00 00 00 00 01 88 01 13 01 01 00 03 03 c2 02 0c 03 c2 03 17 03 c2 04 22

00 01 00 00 00 00 00 01 00 00 00 19 56 91 00 27 09 00 00 00 00 00 00 17 00

00 00 00 00 01 88 01 17 01 01 00 04 03 c2 02 0c 03 c2 03 17 03 c2 04 22 03

c2 05 2d

88 01从第31个字节开始，第33个字节是数据长度，第37个字节是有多少个成员。