oracle自定义的记录类型,oracle记录类型 集合类型

-------------20140114------------

Oracle记录类型介绍

RECORD:用户自定义数据类型，由单行多列的标量构成的复合数据类型，它将一个或多个标量封装成一个对象进行操作

记录不可以总体拿来比较也不可以整体判断为空，可以整体拿来赋值

declare

--记录的创建

type type_1 is record (v_1 varchar2(100));

type t_record is record(

firstcode varchar2(100),

firstname varchar2(100),

secondcode varchar2(100),

secondname varchar2(100)

);

--记录类型变量的声明

v_record t_record;

cursor c1 is select FIRSTCODE,FIRSTNAME,SECONDCODE,SECONDNAME

from (

select 'AAA' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_01'

SECONDCODE,'主诉' SECONDNAME from dual

union all

select 'BBB' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_02'

SECONDCODE,'入院情况' SECONDNAME from dual

union all

select 'CCC' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_03'

SECONDCODE,'入院诊断' SECONDNAME from dual

union all

select 'DDD' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_04'

SECONDCODE,'诊疗经过' SECONDNAME from dual

);

v_count number:=0;

begin

open c1;

loop

v_count:=v_count+1;

--记录类型变量值得填充

fetch c1 into v_record;

--记录成员的访问

dbms_output.put_line('FIRSTCODE是：'||v_record.firstcode||',SECONDCODE是：'||v_record.SECONDCODE);

if v_count>3 then

v_record.firstcode:='EEE';

v_record.SECONDCODE:='EM000_07_05';

end if;

--使用记录更新数据

--update test set row=v_record;

--使用记录插入数据

--insert into test

values(v_record.firstcode,v_record.firstname,v_record.secondcode,v_record.secondname);

exit when v_count=5;

end loop;

end;

/

FIRSTCODE是：AAA,SECONDCODE是：EM000_07_01

FIRSTCODE是：BBB,SECONDCODE是：EM000_07_02

FIRSTCODE是：CCC,SECONDCODE是：EM000_07_03

FIRSTCODE是：DDD,SECONDCODE是：EM000_07_04

FIRSTCODE是：EEE,SECONDCODE是：EM000_07_05

PL/SQL procedure successfully completed

上面的例子包含了记录的创建，记录类型变量的声明，记录类型变量值得填充，记录成员的访问

理解记录可以与%rowtype比较，两者用法一致，可以将%rowtype理解为记录的简化升级

针对单列多行的数据就需要是用另一种变量类型：集合

oracle的集合包括：关联数组 即：索引表(plsql table)、嵌套表(nested

table)、变长数组(varry)

三种集合类型的区别：索引表只能在plsql块级别中使用，其余二者可以在数据库中直接使用(schema级别)，也可以在plsql中使用

索引表：下表无限制，可为负数；元素个数无限制；

--使用binary_integer作为索引类型

declare

type type_table is table of varchar2(30) index by

binary_integer;

v_table type_table;

begin

v_table(10):='EM000_07_04';

v_table(-10):='EM000_07_01';

dbms_output.put_line('元素个数为：'||v_table.count);

v_table(-1):='EM000_07_02';

dbms_output.put_line('第一个元素为：'||v_table.first);

dbms_output.put_line('最后一个元素为：'||v_table.last);

dbms_output.put_line('元素个数为：'||v_table.count);

end;

/

元素个数为：2

第一个元素为：-10

最后一个元素为：10

元素个数为：3

PL/SQL procedure successfully completed

--使用varchar2作为索引类型

declare

type t_record is record(

firstcode varchar2(100),

firstname varchar2(100),

secondcode varchar2(100),

secondname varchar2(100)

);

v_record t_record;

type type_table is table of varchar2(100) index by

varchar2(100);

v_table type_table;

begin

select FIRSTCODE,FIRSTNAME,SECONDCODE,SECONDNAME into v_record

from (

select 'AAA' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_01'

SECONDCODE,'主诉' SECONDNAME from dual

union all

select 'BBB' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_02'

SECONDCODE,'入院情况' SECONDNAME from dual

union all

select 'CCC' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_03'

SECONDCODE,'入院诊断' SECONDNAME from dual

union all

select 'DDD' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_04'

SECONDCODE,'诊疗经过' SECONDNAME from dual

) where firstcode='AAA';

v_table(v_record.firstcode):=v_record.SECONDCODE;

select FIRSTCODE,FIRSTNAME,SECONDCODE,SECONDNAME into v_record

from (

select 'AAA' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_01'

SECONDCODE,'主诉' SECONDNAME from dual

union all

select 'BBB' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_02'

SECONDCODE,'入院情况' SECONDNAME from dual

union all

select 'CCC' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_03'

SECONDCODE,'入院诊断' SECONDNAME from dual

union all

select 'DDD' FIRSTCODE,'交班记录' FIRSTNAME,'EM000_07_04'

SECONDCODE,'诊疗经过' SECONDNAME from dual

) where firstcode='BBB';

v_table(v_record.firstcode):=v_record.secondcode;

dbms_output.put_line('元素个数为：'||v_table.count);

dbms_output.put_line('元素为：'||v_table(v_record.firstcode));

end;

/

元素个数为：2

元素为：EM000_07_02

PL/SQL procedure successfully completed

嵌套表：nested table

下表从1开始；元素个数无限制，使用时需初始化；可以使用extend属性扩展元素个数；

创建了嵌套表定义类型后可以作为表定义数据类型，嵌套表索引固定式int型，这也是与索引表的区别之一(index

by)

declare

type nest_type_table is table of varchar2(100) ;

v_table nest_type_table:=nest_type_table();

begin

v_table.extend;

v_table(1):='EM000_07_04';

v_table.extend;

v_table(2):='EM000_07_01';

dbms_output.put_line('元素个数为：'||v_table.count);

v_table.extend;

v_table(3):='EM000_07_02';

dbms_output.put_line('第一个元素为：'||v_table.first);

dbms_output.put_line('最后一个元素为：'||v_table.last);

dbms_output.put_line('元素个数为：'||v_table.count);

end;

/

元素个数为：2

第一个元素为：1

最后一个元素为：3

元素个数为：3

PL/SQL procedure successfully completed

嵌套表在数据表中使用，嵌套表类型列单独

首先创建类型：create or replace type nest_table_type is table of

varchar2(30);

然后创建表：

-- Create table

create table TEST_NEST_TABLE

(

ID  INTEGER,

DEPART_NO NEST_TABLE_TYPE

)

--创建该类型表必须指定存储嵌套表列数据表

nested table DEPART_NO store as NEST_TABLE

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64

next 1

minextents 1

maxextents

unlimited

);

-- Create/Recreate indexes

create unique index SYS_C0011360 on TEST_NEST_TABLE

(DEPART_NO)

tablespace USERS

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents

unlimited

);

插入数据：

insert into test_nest_table

values(1,nest_table_type('aaa','bbb','ccc','ddd'));

查询数据：

varry可变数组，下标从1开始，定义时先指定最大元素个数；使用时也必须初始化

declare

type test_varray is varray(10) of int ;

v_varray test_varray:=test_varray();

begin

for i in 1..10 loop

v_varray.extend;

v_varray(i):=i*(i+1);

dbms_output.put_line('输出结果为：'||v_varray(i));

end loop;

end;

/

输出结果为：2

输出结果为：6

输出结果为：12

输出结果为：20

输出结果为：30

输出结果为：42

输出结果为：56

输出结果为：72

输出结果为：90

输出结果为：110

PL/SQL procedure successfully completed

集合类型选择：通常优先选择索引表类型，它不需要extend、初始化等操作。但美中不足的是该类型只能适用于plsql

block级别

集合的方法汇总：

EXISTS方法，检查元素是否存在

v_table.exists(n)=true/false

COUNT方法，获取集合的元素个数  v_table.count

LIMIT方法，获取集合的最大元素个数，对于内嵌表和关联数组，因为没有声明大小，所以LIMIT返回NULL值

v_varray.limit

FIRST和LAST方法，获取集合的首下标和尾下标

v_table.first/v_table.last

PRIOR和NEXT方法，获取前一个元素或后一个元素的下标

v_table.next(n)/v_table.next(n)

EXTEND方法，增加集合的元素,有3种形式

EXTEND: 追加一个NULL元素

EXTEND(n): 追加n个NULL元素

EXTEND(n, i): 追加n个第i个元素

TRIM方法，减少集合的元素个数，有2种形式

TRIM：在集合尾部删除一个元素

TRIM(n)：在集合尾部删除n个元素

DELETE方法，删除集合的元素

不带参数的DELETE会删除所有元素，并设置COUNT为0

DELETE(n)会删除内嵌表或以数值为主键的关联数组的第n个元素

DELETE(m,n)会删除内嵌表或关联数组的所有范围在m..n之间的元素，如果m>n，那么DELETE(m,n)不起作用

declare

type type_table is table of varchar2(30) index by

binary_integer;

v_table type_table;

begin

v_table(10):='EM000_07_04';

v_table(-10):='EM000_07_01';

dbms_output.put_line('元素个数为：'||v_table.count);

dbms_output.put_line('aa：'||v_table.next(1));

v_table(-1):='EM000_07_02';

if v_table.exists(1)=true then

--判断第一个元素是否存在

--v_table.delete(2);  删除第二个元素

dbms_output.put_line('第一个元素为：'||v_table.first);

dbms_output.put_line('最后一个元素为：'||v_table.last);

dbms_output.put_line('元素个数为：'||v_table.count);

end if;

end;