Oracle复合数据类型示例
- WBOYOriginal
- 2016-06-07 17:13:12947browse
--Recode数据类型 declare v_deptinfo scott.dept%rowtype; type dept_record is RECORD( v1 scott.dept.deptno%type, v2 sc
--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;
更多Oracle相关信息见Oracle 专题页面 ?tid=12
