Oracle复合数据类型示例

--Recode数据类型

declare

v_deptinfo scott.dept%rowtype;

type dept_record is RECORD(

  v1 scott.dept.deptno%type,

  v2 scott.dept.dname%type,

  v3 scott.dept%rowtype--可以声明ROWTYPE类型

);

v_deptrecord dept_record;

begin

--一下的赋值方式错误：因为V3是ROWTYPE类型，而查询的一行记录有五列，给v3赋值时会发生类型不匹配

select deptno,dname,t.* into v_deptrecord from dept t where deptno=10;

--解决方法：可以对v1,v2赋值后，再写另外一条语句对v3赋值。

dbms_output.put_line(v_deptrecord.v3.dname||' '||v_deptrecord.v3.deptno);

end;

--索引表1

declare

type my_index_table1 is table of scott.dept.dname%type--可以使任意数据类型，代表此索引表所存储数据的类型。

index by binary_integer;

my1 my_index_table1;

c number(2);

begin

select count(*) into c from dept;

for i in 1..c loop

  select dname into my1(i) from

   (select rownum rn,t.* from dept t) x

     where x.rn=i;

end loop;

--每个集合都有COUNT属性，代表此集合存储的有效元素总个数。

for i in 1..my1.count loop

  dbms_output.put_line(my1(i));

end loop;

end;

--索引表2

declare

type my_index_table1 is table of scott.dept.dname%type

index by varchar2(20);--oracle 9i以上的版本，索引表的下表可以为3中数据类型（BINARY_INTEGER、PLS_INTEGER、VVARCHAR2(length)）;

my1 my_index_table1;

begin

select loc into my1('南昌') from dept where deptno=10;

dbms_output.put_line(my1('南昌'));

end;

--嵌套表1

declare

type my_index_table1 is table of scott.dept.dname%type;

my1 my_index_table1:=my_index_table1(null,null,null,null);--初始化可以使用null值

begin

select dname into my1(1) from dept where deptno=10;

select dname into my1(2) from dept where deptno=20;

select dname into my1(3) from dept where deptno=30;

select dname into my1(4) from dept where deptno=40;

--嵌套表删除元素后，下标依然存在，依然可以重新进行赋值.

my1.delete(3);

dbms_output.put_line(my1.count);

select dname into my1(3) from dept where deptno=30;

dbms_output.put_line(my1.count);

for i in 1..my1.count loop

  dbms_output.put_line(my1(i));

end loop;

end;

--嵌套表2

create type phone_type is table of varchar2(20);

create table employee (

   eid number(4),

   ename varchar2(10),

   phone phone_type

) nested table phone store as phone_table;

insert into employee

values(1,'xx',phone_type('0791-111','123454545'));

insert into employee

values(2,'xx',phone_type('0791-111','123454545','saaasf'));

--变长数组

declare

type my_index_table1 is varray(3) of scott.dept.dname%type;

my1 my_index_table1:=my_index_table1('a','b','c');--初始化

begin

select dname into my1(1) from dept where deptno=10;

select dname into my1(3) from dept where deptno=20;

for i in 1..my1.count loop

  dbms_output.put_line(my1(i));

end loop;

end;

--记录表2

declare

--自定义RECORD可以存放自己想要的列，脱离了ＲＯＷＴＹＰＥ的死板，可以灵活的自定义存放哪些列。

type dept_record is RECORD(

  v1 scott.dept.deptno%type,

  v2 scott.dept.dname%type,

  v3 scott.dept.loc%type

);

type my_index_table1 is table of dept_record

index by binary_integer;

my1 my_index_table1;

c number(2);

--查询出dept表中的所有数据并放进自定义的数据类型

begin

-先查询出表中的记录总数，以记录总数作为循环条件对dept表、以rownum作为WHERE条件对dept表进行逐条查询并存贮进自定义数据类型

select count(*) into c from dept;

for i in 1..c loop

  select x.deptno,x.dname,x.loc into my1(i) from

   (select rownum rn,t.* from dept t) x

     where x.rn=i;

end loop;

--循环输出my1类型中的v2字段在DEPT表中代表的数据；

for i in 1..my1.count loop

  dbms_output.put_line(my1(i).v2);

end loop;

end;