08 使用复合数据类型

1、,使用复合数据类型,目标,完成本课后，应该可以做下列事情： 使用PL/SQL记录和ROWTYPE属性 使用索引表、嵌套表和变长数组(VARRAY) 使用PL/SQL记录表 使用FORALL语句和BULK COLLECT子句 在嵌套表上使用集合操作符、在FOR ALL语句中使用INDICES OF子句和VALUES OF子句,概念总结,单行单列：标量 单行多列：记录(record) 多行单列：索引表、嵌套表、VARRY 多行多列：记录表,PL/SQL记录,PL/SQL记录(Record)类似于高级语言中的结构 使用PL/SQL记录可以简化单行多列的数据处理 PL/SQL记录由一组相关的记录成员(

2、Field)组成 定义PL/SQL记录有两种方法： 自定义记录 使用ROWTYPE属性定义,方法1：自定义PL/SQL记录,当使用自定义的PL/SQL记录时，需要分别定义PL/SQL记录类型和记录变量 语法如下：,TYPE type_name IS RECORD( field_declaration, field_declarartion ); identifier type_name;,方法2：使用ROWTYPE属性定义记录变量,ROWTYPE属性可以基于表或视图定义记录变量 简化了表或视图所有列数据的处理语法如下：,identifier table_name%ROWTYPE; 或 iden

3、tifier view_name%ROWTYPE;,在SELECT INTO语句中使用PL/SQL记录 -使用记录变量,DECLARE TYPE emp_record_type IS RECORD( name emp.ename%TYPE, salary emp.sal%TYPE, dno emp.deptno%TYPE); emp_record emp_record_type; BEGIN SELECT ename, sal, deptno INTO emp_record FROM emp WHERE empno=,定义记录类型,定义记录变量,DECLARE TYPE emp_record_

4、type IS RECORD( name emp.ename%TYPE, salary emp.sal%TYPE, dno emp.deptno%TYPE); emp_record emp_record_type; BEGIN SELECT ename, sal, INTO emp_, emp_record.sal FROM emp WHERE empno=,在SELECT INTO语句中使用PL/SQL记录 -使用记录成员,DECLARE dept_record dept%ROWTYPE; BEGIN dept_record.deptno := 50; dept_rec

5、ord.dname := ADMINISTRATOR; dept_record.loc := ZHENGZHOU; INSERT INTO dept VALUES dept_record ; END;,在Insert语句中使用PL/SQL记录 -在VALUES子句使用记录变量,当在VALUES子句中使用记录变量插入数据库时，列的顺序、个数、类型必须要与记录成员的顺序、个数、类型完全匹配。,DECLARE dept_record dept%ROWTYPE; BEGIN dept_record.deptno := 60; dept_record.dname := SALES; INSERT INT

6、O dept (deptno, dname) VALUES (dept_record.deptno, dept_record.dept); END;,在Insert语句中使用PL/SQL记录 -在VALUES子句使用记录成员,DECLARE dept_record dept%ROWTYPE; BEGIN dept_record.deptno := 30; dept_record.dname := SALES; dept_record.loc := XINXIANG; UPDATE dept SET ROW=dept_record WHERE deptno=30; END;,在UPDATE语句中

7、使用PL/SQL记录 -在SET子句中使用记录变,DECLARE dept_record dept%ROWTYPE; BEGIN dept_record.loc := LOUYANG; UPDATE dept SET loc = dept_record.loc WHERE deptno = 10; END;,在UPDATE语句中使用PL/SQL记录 -在SET子句中使用记录变,在SET子句中使用记录成员,DECLARE dept_record dept%ROWTYPE; BEGIN dept_record.deptno := 50 DELETE FRIN dept WHERE deptno =

8、 dept_record.deptno; END;,在DELETE语句中使用PL/SQL记录,在SET子句中使用记录成员,PL/SQL集合,索引表 嵌套表 Nested Table 变长数组 VARRAY,索引表示例使用BINARY_INTEGER和PLS_INTEGER,DECLARE TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; ename_table ename_table_type; BEGIN SELECT ename INTO ename_table(-1) FROM emp WH

9、ERE empno = /,索引表示例使用VARCHAR2,DECLARE TYPE area_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10); area_table area_table_type; BEGIN area_table(郑州):=1; area_table(开封):=2; area_table(洛阳):=3; dbms_output.put_line(第一个元素:|area_table.first); dbms_output.put_line(最后一个元素:|area_table.last); END;,索引表,嵌套表的元

10、素下标从1开始，且元素个数没有限制 嵌套表数组元素值可以是稀疏的 即可以在PL/SQL块中使用，也可以用于定义表列 语法： 当使用嵌套表元素时，必须首先使用其构造方法初始化嵌套表，如下所示：,TYPE type_name IS TABLE OF element_type; identifier type_name;,DECLARE TYPE ename_table_type IS TABLE OF emp.ename%TYPE; ename_table ename_table_type:=ename_table_type(A,B); .,索引表示例 在PL/SQL块中使用嵌套表,DECLARE

11、 TYPE ename_table_type IS TABLE OF emp.ename%TYPE; ename_table ename_table_type; BEGIN ename_table:=ename_table_type(张三,李四,王二); SELECT ename INTO ename_table(2) FROM emp WHERE EMPNO=,索引表示例 在表列中使用嵌套表,首先必须使用CREATE TYPE命令建立嵌套表类型 同时必须要为嵌套表列指定专门的存储表(自动创建),CREATE TYPE phone_type IS TABLE of VARCHAR2(20);

12、/ CREATE TABLE emp2( id NUMBER(4), ename VARCHAR2(10), sal NUMBER(7,2), phone phone_type )NESTED TABLE phone STORE AS phone_table;,索引表示例 在PL/SQL块中为嵌套表列插入数据,DECLARE phone_table phone_type:=phone_type0371-65796377); BEGIN UPDATE emp2 SET phone=phone_table WHERE id = 1; END;,变长数组(VARRAY),与

13、高级语言数组非常类似，其元素下标以1开始，并且元素的最大个数是有限制的 即可以用于PL/SQL块，也可以用于定义表列 语法：,TYPE type_name IS VARRAY(size_limit) OF element_type NOT NULL; identifier type_name;,变长数组示例 在PL/SQL块中使用VARRAY,DECLARE TYPE ename_table_type IS VARRAY(20) of emp.ename%TYPE; ename_table ename_table_type:=ename_table_type(MARY); BEGIN SELE

14、CT ename INTO ename_table(1) FROM emp WHERE empno=,变长数组示例 在表列中使用VARRAY,CREATE TYPE phone_type IS VARRAY(20) OF VARCHAR2(20); / CREATE TABLE emp3 ( id NUMBER(4), name VARCHAR2(10), sal NUMBER(6,2), phone phone_type);,PL/SQL记录表,DECLARE TYPE ename_table_type IS TABLE of emp%ROWTYPE INDEX BY BINARY_INTE

15、GER; ename_table ename_table_type; BEGIN SELECT * INTO ename_table(1) FROM emp WHERE empno=,用于处理多行多列数据,多级集合,多级集合是指嵌套了集合类型的集合类型 通过使用多级集合，可以在PL/SQL中实现类似于多维数组的功能 9i以后的新特性,多级集合 在PL/SQL块中使用多级VARRAY,DECLARE - 定义一维VARRAY TYPE a1_varray_type IS VARRAY(10) OF INT; - 定义二维VARRA集合 TYPE na1_varray_type IS VARRAY

16、(10) OF a1_varray_type; - 初始化二维集合变量 nv1 na1_varray_type:=na1_varray_type( a1_varray_type(58,100,102), a1_varray_type(55,6,73), a1_varray_type(2,4); BEGIN dbms_output.put_line(显示二维数组所有元素); FOR i IN 1.nv1.COUNT LOOP FOR j IN 1.nv1(i).COUNT LOOP dbms_output.put_line( nv1( | i |, | j |)= | nv1(i)(j); EN

17、D LOOP; END LOOP; END;,多级集合 在PL/SQL块中使用多级嵌套表,DECLARE - 定义一维嵌套表 TYPE a1_table_type IS TABLE OF INT; - 定义二维嵌套表集合 TYPE na1_table_type IS TABLE OF a1_table_type; - 初始化二维集合变量 nv1 na1_table_type:=na1_table_type( a1_table_type(58,100,102), a1_table_type(55,6,73), a1_table_type(2,4); BEGIN dbms_output.put_l

18、ine(显示二维数组所有元素); FOR i IN 1.nv1.COUNT LOOP FOR j IN 1.nv1(i).COUNT LOOP dbms_output.put_line( nv1( | i |, | j |)= | nv1(i)(j); END LOOP; END LOOP; END;,多级集合 在PL/SQL块中使用多级索引表,DECLARE - 定义一维table TYPE a1_table_type IS TABLE OF INT INDEX BY BINARY_INTEGER; - 定义二维table集合 TYPE na1_table_type IS TABLE OF

19、a1_table_type INDEX BY BINARY_INTEGER; - 初始化二维集合变量 nv1 na1_table_type; BEGIN nv1(1)(1):=10; nv1(1)(2):=5; nv1(2)(1):=100; nv1(2)(2):=50; dbms_output.put_line(显示二维数组所有元素); FOR i IN 1.nv1.COUNT LOOP FOR j IN 1.nv1(i).COUNT LOOP dbms_output.put_line( nv1( | i |, | j |)= | nv1(i)(j); END LOOP; END LOOP;

20、 END;,集合方法,集合方法用于操纵集合变量的内置函数或过程 EXISTS 确定集合元素是否存在 COUNT 返回当前集合变量中的元素总个数 LIMIT 返回集合元素的最大个数 FIRST 返回集合变量第一个元素的下标 LAST 返回集合变量最后一个元素的下标 PRIOR 返回当前集合元素的前一个元素的下标 NEXT 返回当前集合元素的后一个元素的下标 EXTEND 扩展集合变量的尺寸，并为它们增加元素 TRIM 从集合尾部删除元素 DELETE 该方法用于删除集合元素,集合赋值,将一个集合的数据赋值给另一个集合 使用赋值语句(:=)或SQL语句将源集合中的数据赋值给目标集合时 自动清除目标

21、集合原有的数据，并将源集合中的数据赋值给该目标集合 给集合赋NULL值，有两种方法： 使用集合方法DELETE和TRIM 将一个NULL集合变量赋值给目标集合变量,集合赋值 给嵌套表赋值,SET操作符：取消特定嵌套表中的重复值 MULTISET UNION：取得两个嵌套表的并集 MULTISET UNION DISTINCT：取得两个嵌套表的并集，并取消重复结果 MULTISET INTERSECT：取得两个嵌套表的交集 MULTISET EXCEPT：取得两个嵌套表的差集,集合赋值 比较集合,IS NULL：检测集合是否为NULL 10g新特性 ：比较嵌套表是否相同 CARDINALITY:

22、 返回嵌套表变量的元素个数 SUBMULTISET OF：一个嵌套表是否为另一个嵌套表的子集 MEMBER OF：检测特定数据是否为嵌套表的元素 IS A SET：检测嵌套表是否包含重复的元素值,批量绑定,批量绑定的优点 FORALL语句 BULK COLLECT,批量绑定的优点,通过实验对比(见备注)，可看出： 批量绑定的速度要远远优于不使用批量绑定 批量绑定可能通过以下语句来完成 BULK COLLECT 用于取得批量数据 只能用于SELECT语句、FETCH语句和DML返回子句中 FORALL 只适用于执行批量的DML操作中,FORALL语句,FOR语句是循环语句，而FORALL语句不是

23、循环语句 Oracle 10g以后，FORALL有三种执行语句,FORALL index IN lower_bound, upper_bound sql_statement;,FORALL index IN INDICES OF collection bwtween lower_bound, upper_bound sql_statement;,FORALL index IN VALUES OF collection sql_statement;,在INSERT语句上使用批量绑定,DECLARE TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY

24、 BINARY_INTEGER; TYPE name_table_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; id_table id_table_type; name_table name_table_type; BEGIN FOR i IN 1 . 10 LOOP id_table(i) := i; name_table(i) := Name | to_char(i); END LOOP; FORALL i IN 1 . id_table.COUNT INSERT INTO demo VALUES (id_table(i),

25、name_table(i); END;,在UPDATE语句上使用批量绑定,DECLARE TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER; TYPE name_table_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; id_table id_table_type; name_table name_table_type; BEGIN FOR i IN 1.10 LOOP id_table(i) := i; name_table(i) := N | to

26、_char(i); END LOOP; FORALL i IN 1.id_table.COUNT UPDATE demo SET name=name_table(i) WHERE id=id_table(i); END;,在DELETE语句上使用批量绑定,DECLARE TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER; id_table id_table_type; BEGIN FOR i IN 1 . 3 LOOP id_table(i) := i; END LOOP; FORALL i IN 1 . id_t

27、able.COUNT DELETE FROM demo WHERE id = id_table(i); END;,在FORALL语句中使用部分集合元素,DECLARE TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER; id_table id_table_type; BEGIN FOR i IN 1 . 10 LOOP id_table(i) := 11-i; END LOOP; FORALL i IN 8.10 INSERT INTO demo (id) VALUES (id_table(i); END;,在FO

28、RALL语句中使用INDICS OF子句,DECLARE TYPE id_table_type IS TABLE OF NUMBER(6); id_table id_table_type; BEGIN id_table := id_table_type(l, NULL, 3, NULL,5 ); FORALL i IN INDICES OF id_table DELETE FROM demo WHERE id =id_table(i); END;,10g的新特性 该子句用于跳过NULL集合元素,在FORALL语句中使用values OF子句,DECLARE TYPE id_table_type

29、 IS TABLE OF demo.id%TYPE; TYPE name_table_type IS TABLE OF %TYPE; id_table id_table_type; name_table name_table_type; TYPE index_pointer_type IS table of PLS_INTEGER; index_pointer index_pointer_type; BEGIN SELECT * BULK COLLECT INTO id_table, name_table FROM demo; index_pointer:=indx_pointer_type(6,8,10) FORALL i IN VALUES OF index_pointer INSERT INTO new_demo VALUES (id_table(i), name_table(i); END;,10g的新特性 用于从其他集合变量中取得集合下标(Index)的值,将