Oracle复合数据类型
当使用标量变量处理oracle数据时,每个标量只能存放单个值,如果要处理多个类型的数据,则要声明多个标量变量;为了解决这种问题,可以使用复合数据类型。复合数据类型包括记录——用于处理单行多列数据,集合——用于处理多行单列数据,记录表——用于处理多行多列数据。
一.记录:
pl/sql记录用于处于单行多列数据.类似于高级语言中的结构,可以自定义记录,也可以通过%rowtype属性定义记录变量. 明显的,自定义更加灵活,因为它可以跨越多个表,可以任意取字段,而rowtype属性则不能,它只能是固定表/视图的字段。虽然自定义表的代码多点,但是在只需要某几列时,还是得自定义记录,因为它能节省内存空间,多写点代码是非常值得的。
declare
--自定义方式
type dept_record_type is record(
name emp.ename%type,
dname dept.dname%type,
sal emp.sal%type);
dept_record dept_record_type;
begin
select ename, dname, sal
--into dept_record.name, dept_record.dname, dept_record.sal
--只要是类型合适,便会按顺序插入,而不用保证select的字段和记录的字段同样的名称。
into dept_record
from emp
join dept
on emp.deptno = dept.deptno
where emp.empno = &no;
dbms_output.put_line(dept_record.name);
dbms_output.put_line(dept_record.sal);
exception
when no_data_found then
dbms_output.put_line('没有此员工');
end;
在select ,insert,update,delete语句中均可使用记录
如:
如果插件的数据与表字段的顺序、个数、类型完全相同,则可以直接使用。
如果只是使用记录中的某几个字段,则需要使用记录成员,如dept_record.dname;
Declare
--%rowtype属性方式
dept_record dept%rowtype;
begin
dept_record.deptno := 60;
dept_record.dname := 'anaylize';
dept_record.loc := 'beijing';
insert into dept values dept_record;
end;
在update中使用记录:
dept_record dept%rowtype;
begin
dept_record.deptno := 60;
dept_record.dname := 'anaylize';
dept_record.loc := 'shanghai';
update dept set row=dept_record where deptno=60;
end;
当然,直接使用记录成员也可以。
上面稍微一改:
declare
dept_record dept%rowtype;
begin
dept_record.deptno := 60;
dept_record.dname := 'anaylize';
dept_record.loc := 'jinan';
update dept set loc=dept_record.loc where deptno=60;
end;
当在delete语句中使用记录时,只能是在where子句中使用记录成员。
如:
declare
dept_record dept%rowtype;
begin
dept_record.deptno := 60;
delete from dept where deptno=dept_record.deptno;
end;
二.Pl/sql集合
Pl/sql集合可以用来处理多行单列数据,如多个雇员的姓名。它类似于高级语言(如java)的数组,可以存储多个类型一样的数据。
Pl/sql集合包括索引表(pl/sql表),嵌套表(Nested table),变长数组(VARRAY)三种类型,应该根据不同的需求正确的选择它们。
-
索引表
索引表的元素个数没有限制,且下标可以为负值。
定义索引表的结构如下:
TYPE type_name is table of element_type
Index by key_value;
Identifier type_name;
Is table …index是定义索引表的固定格式。Type_name是用户自定义数据类型的名称,element_type是存储元素的数据类型,key_Value是下标的类型(可以是Binary_integer,pls_integer,varchar2)。
如下:
set serveroutput on;
declare
type emp_table_type is table of emp.ename%type index by binary_integer;
emp_table emp_table_type;
cursor emp_cursor is
select ename from emp;
i binary_integer:=1;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_table(i);
i:=i+1;
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
for i in 1 .. emp_table.count loop
dbms_output.put_line(emp_table(i));
end loop;
end;
-
嵌套表
嵌套表的下标是从1开始的,元素个数可以无限,但最重要的特点还是嵌套表的元素值是可以稀疏的。
定义嵌套表的格式如下:
Type type_name is table of element_name;
Identifier type_name;
在pl/sql块中使用嵌套表变量时,必须首先使用构造函数方法初始化嵌套表变量,然后才能在pl/sql块内引用嵌套表元素,如下:
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
--初始化几个数据,下标到几
ename_table := ename_table_type('a', 'a', 'a');
select ename into ename_table(2) from emp where emp.empno = 7788;
dbms_output.put_line(ename_table(2));
dbms_output.put_line(ename_table(1));
--下面这句会报错:下标超出数据,因为在前面只初始化了3个元素,所有下标只能到3.
--dbms_output.put_line(ename_table(4));
end;
嵌套表可以在表列中使用。当表中一个字段中的值的个数不确定的时候,非常有用。如person表的phone字段,可以是家庭电话、个人手机、办公电话。
对于这个特性,在这里不做详解,有兴趣的同学可以去查看官方文档或去google百度一下。
3变长数组
该数据类型与高级语言的数组非常相似,下标从1开始,而且无数个数有限制,即可以处理pl/sql数组,也可以当作表列。
定义如下:
Type type_name is varray(size) of element_type;
Identifier type_name;
同Nested Table一样,在使用前一定要先用构造函数初始化。
declare
type ename_varray_type is varray(20) of emp.ename%type;
ename_varray ename_varray_type := ename_varray_type(' ');
begin
select ename into ename_varray(1) from emp where emp.empno = &no;
dbms_output.put_line('姓名:' || ename_varray(1));
--dbms_output.put_line('姓名:' || ename_varray(2));
end;
三.Pl/sql记录表
记录表可以用来处理多行多列数据,它结合了pl/sql记录和pl/sql集合的优点,从而可以有效的处理多行多列数据。
declare
type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * into emp_table(1) from emp where emp.empno = &no;
dbms_output.put_line(emp_table(1).ename);
end;
declare
type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * bulk collect into emp_table from emp;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).ename);
end loop;
end;
declare
type emp_record_type is record
(
name emp.ename%type,
sal emp.sal%type
);
type emp_table_type is table of emp_record_type index by binary_integer;
emp_table emp_table_type;
begin
select ename,sal bulk collect into emp_table from emp;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).name||' '||emp_table(i).sal);
--dbms_output.put_line(emp_table(i).sal);
end loop;
end;
赋予用户调试权限:grant debug connect session to user