ITPub博客

首页 > 数据库 > Oracle > PL/SQL中对数组和嵌套表的主要操作。

PL/SQL中对数组和嵌套表的主要操作。

原创 Oracle 作者:fishforwater 时间:2007-09-03 20:58:46 0 删除 编辑

PL/SQL中对数组和嵌套表的主要操作。

[@more@]
--3.2 公用函数。用来将逗号分隔的数字组,解开成数字装载到数组中。
--输入类似于1-2,5,8-9
function get_line_str2type
(p_line_str varchar2,p_okflag out number,p_errormsg out varchar2)
return table_number
is
--在最后拼一个逗号,便于编程
p_str varchar2(30000):=rtrim(p_line_str,',')||',';
p_st_line number(9):=0;
p_end_line number(9);
p_out table_number:=table_number();
begin
p_okflag := 0;
while instr(p_str,',')>0 loop
begin
--若不能正常数字化,则存在不合法字符。
--例如:中文逗号,其它符号,连续逗号
p_end_line := to_number(substr(p_str,1,instr(p_str,',')-1));
if p_end_line is null then
p_errormsg := p_errormsg||'线对串中存在连续逗号。';
end if;
exception
when others then
p_end_line := null;
p_errormsg := p_errormsg||'线对串中存在不合法数据:“'||
nvl(substr(p_str,1,instr(p_str,',')-1),',,')||'”,请检查。';
end;
--后面数字必须大于前面数字
if p_end_line <=p_st_line then
p_errormsg := p_errormsg||' 大数排列在前或数字小于1:“'||p_end_line||'“。';
end if;
p_st_line := p_end_line;
p_str := substr(p_str,instr(p_str,',')+1);
--开始插入对象
p_out.Extend;
p_out(p_out.Last) := p_end_line;
end loop;
-- p_out(1).trim;
p_out.trim;
p_out.trim(1);
--若错误信息为空,则置成功标志;若不为空,则清空对象
if p_errormsg is null then
p_okflag := 1;
else
p_out.delete;
end if;
/* --测试中,打印所有的对象元素
for i in 1..p_out.count loop
dbms_output.put_line(p_out(i));
end loop;
*/ return p_out;
exception
when others then
return null;
end get_line_str2type;
Aug 22, 2006

Oracle PL/SQL Programming 读书笔记(Collections)

Collections


Collections Overview
Types of Collections
Associative arrays
他是同种类型的一维、无边界的稀疏集合,只能用于 PL/SQL
DECLARE TYPE t_name IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER; --创建 Collection
i_name t_name; --创建 instance
l_row PLS_INTEGER;
BEGIN
i_name(202020):='aaa';
i_name(-125):='bbb';
i_name(88):='ccc'; --赋值,row number 可以为任何整数,且可以跳跃(稀疏的),也不用按照顺序赋值,其内部最终按照 row number 排序
l_row := i_name.FIRST; --返回第一个 row number
l_row := i_name.NEXT (l_row); --返回 l_row 之后的一个 row number,会自动跳过为空的行
DBMS_OUTPUT.put_line (i_name (l_row)); --返回指定行的值
END;

Nested tables
也是同种类型的一维无边界集合。起初他是密集的,经过删除操作会变成稀疏。他可以在 PL/SQL 和 Database(某一列为一个嵌套表)中被定义。他是 multisets 的,也就是说 nested table 中的元素没有内在的顺序
DECLARE TYPE t_name IS TABLE OF varchar2(10); --创建 Collection,和 Associative arrays 相差一个 INDEX BY
i_n1 t_name := t_name(); --创建 instance,需要使用构造函数
i_n2 t_name := t_name();
i_n3 t_name := t_name();
BEGIN
i_n1.EXTEND(2); --赋值前要分配空间
i_n1(1):='aaa'; --赋值,row number 最小为 1,最大为 EXTEND 分配的空间数,可以跳过某个行不分配则为 null
i_n1(2):='bbb';
i_n2.EXTEND; --分配一个空间
i_n2(1):='bbb';
i_n3 := i_n1 MULTISET EXCEPT i_n2; --10g 开始提供的功能,将 i_n1 中除去 i_n2 的值,赋值给 i_n3
FOR l_row IN i_n1.FIRST .. i_n1.LAST --从行首到行尾,依次输出
LOOP
DBMS_OUTPUT.put_line (i_n1(l_row));
END LOOP;
END;
VARRAYs
VARRAYs (variable-sized arrays),也是同种类型的一维集合。但他是有界且不稀疏的。在定义 VARRAYs 时要指定他的最大范围。VARRAYs 也可以在 PL/SQL 和 Database 中被定义,但他其中的元素是有顺序的
DECLARE TYPE t_name IS VARRAY (2) OF VARCHAR2 (10); --创建 Collection,需要指定最大范围
i_n1 t_name := t_name(); --创建 instance,需要使用构造函数
BEGIN
i_n1.EXTEND(2); --赋值前要分配空间,不能超过最大范围
i_n1(1):='aaa'; --赋值,row number 最小为 1,最大为 EXTEND 分配的空间数
i_n1(2):='bbb';
END;

Where You Can Use Collections 作为 record 的成分
作为程序的参数
由于 Oracle 没有预定的集合类型,当作为参数使用前,需要定义集合类型:
1.用 CREATE TYPE 定义 schema-level 类型
2.在 package specification 中声明
3.在外层作用域定义
作为函数的返回值
1.返回集合直接赋值给 collection variable,这时这个 collection variable 不需要初始化
2.将返回集合中的一个元素赋值给一个类型兼容的变量
variable_of_element_type := function() (subscript);
如果函数返回空值,在赋值时会产生 COLLECTION_IS_NULL 异常,应该捕获并适当处理该异常
作为数据库表的列
当使用 nested table datatype 作为列时,必须指定 store table 的名字:
CREATE TABLE personality_inventory (
person_id NUMBER,
favorite_colors Color_tab_t,
date_tested DATE,
test_results BLOB)
NESTED TABLE favorite_colors STORE AS favorite_colors_st;
不能对 store table 进行维护或者试图直接查询、存储数据,只能通过 outer table 来获取他的属性。也不能指定他的 storage parameters,他继承与 outermost table。
nested tables 和 VARRAYs 的区别在于,VARRAY 和其他数据一起存于表内,而 nested table 存于表外,VARRAY 适合 "small" arrays,nested table 适合 "large" arrays。
作为 object type 的属性

Choosing a Collection Type
1.如果要用稀疏 array,那么只能使用 associative array,虽然可以先分配 nested table 在删除其中的项目,但效率很低
2.如果在 PL/SQL 中要用负数的下标,只能用 associative array
3.如果使用 10g,希望使用 set 层面的操作,那么选择 nested tables + MULTISET EXCEPT 的方法
4.如果要限制存储的行数,使用 VARRAYs
5.如果要在 column 中存储大数据量集合,那么使用 nested table,Oracle可以使用单独的表来存储他
6.如果你想将存在 collection column 中的数据保持原有顺序,并且数据量很小,可以使用 VARRAY,小的概念可以按照 BLOCK 的大小来判断,如果数据量超过一个 BLOCK,将会产生行连接
7.还有些情况适合使用 VARRAY:you don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.


Collection Methods (Built-Ins)
Collection methods 只能用于 PL/SQL,不能在 SQL 中使用

The COUNT Method
用于计算 associative array, nested table, or VARRAY 中元素的个数,使用 DELETEd or TRIMmed 将减少 COUNT
定义:
FUNCTION COUNT RETURN PLS_INTEGER;
对于初始化了,但不包含元素的 collection 返回 0,对于空的 associative array 也返回 0
如果对未初始化的 nested table or a VARRAY 使用 COUNT 将返回 COLLECTION_IS_NULL 异常,associative arrays 无需初始化,故不会返回异常

The DELETE Method
DELETE 用于删除 collection 中的一个、多个或者全部元素:
1.不加参数,删除所有元素,对于 VARRAYs 只能使用这种方法,因为 VARRAYs 不能使稀疏的,要删除一行,只能用 TRIM 删除最后一行
2.DELETE(i) 删除第 i 行
3.DELETE(i,j) 删除第 i~j 行
对于使用参数的 DELETE,其实使用一个占位符来代替删除的元素,以后还可以为他们再赋值,赋值之后在 COUNT 中又开始计数。从物理角度来考虑,PL/SQL 只有在删除足够多数量的元素,以至于可以释放 entire page of memory 时才释放内存,但不加参数的 DELETE 是立即释放所有内存的
定义:
PROCEDURE DELETE;
PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)]);
PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)],
j [BINARY_INTEGER | VARCHAR2(size_limit)]);
如果 i,j 超出范围,不会出现异常,只是删除在范围内的,超出部分忽略
如果对未初始化的 nested table or a VARRAY 使用 DELETE 将返回 COLLECTION_IS_NULL 异常

The EXISTS Method
判断 collection 中的指定行是否存在,如果以前存在,后来被 DELETE 了,那也返回 false
定义:
FUNCTION EXISTS (i IN [BINARY_INTEGER | VARCHAR2(size_limit)]) RETURN BOOLEAN;

The EXTEND Method
用于为 nested table or VARRAY 分配空间
1.不加参数,分配一个 null element
2.EXTEND(n),分配 n 个 null element
3.EXTEND(n,i),分配 n 个和第 i 值相同的 element,这对于有 NOT NULL 限制的 element 是必要的
定义:
PROCEDURE EXTEND (n PLS_INTEGER:=1);
PROCEDURE EXTEND (n PLS_INTEGER, i PLS_INTEGER);
如果用 DELETEd or TRIMmed 删除了 collection 的最后一个元素,再 EXTEND 时,会跳过这个,在后面分配空间
如果对未初始化的 nested table or a VARRAY 使用 DELETE 将返回 COLLECTION_IS_NULL 异常,如果 EXTEND 超过 VARRAY 的最大限制,将返回 SUBSCRIPT_BEYOND_LIMIT 异常

The FIRST and LAST Methods
分别返回 COLLECTION 中可用的最低和最高下标,被 DELETE 的不会显示
定义:
FUNCTION FIRST RETURN PLS_INTEGER;
FUNCTION LAST RETURN PLS_INTEGER;
对于初始化了,但没有元素的 COLLECTION,都返回 null;对于至少有一个 element 的 VARRAYs,FIRST 总为 1,LAST 总等于 COUNT
如果对未初始化的 nested table or a VARRAY 使用 FIRST and LAST 将返回 COLLECTION_IS_NULL 异常

The LIMIT Method
返回 VARRAY 的最大数目限制,如果用于 nested tables or to associative arrays 将返回 NULL
定义:
FUNCTION LIMIT RETURN PLS_INTEGER;
如果对未初始化的 nested table or a VARRAY 使用 LIMIT 将返回 COLLECTION_IS_NULL 异常

The PRIOR and NEXT Methods
用于遍历 COLLECTION 中的内容
定义:
FUNCTION PRIOR (i [BINARY_INTEGER | VARCHAR2(size_limit)])
RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];
FUNCTION NEXT (i [BINARY_INTEGER | VARCHAR2(size_limit)])
RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];
如果应用于初始化了但为空的 COLLECTION 时,返回 null;如果 i 大于等于 COUNT,则 NEXT 返回 null;如果 i 小于等于 FIRST,则 PRIOR 返回 null
就目前来说,如果 i 大于 COUNT,则 PRIOR 返回 LIMIT;如果 i 小于 FIRST,则 NEXT 返回 LIMIT,但以后的版本不知道是不是这样了
如果对未初始化的 nested table or a VARRAY 使用 PRIOR and NEXT 将返回 COLLECTION_IS_NULL 异常

The TRIM Method
由于删除 nested table or VARRAY 末尾的 n 行,如果不加参数,删除最后一行,如果用于 associative array 将产生编译时错误
DELETE and TRIM 联合使用将产生重复删除的问题,比如 DELETE 删除最后一行之后,再用 TRIM 删除最后 n 行时,其实 TRIM 重复操作了被 DELETE 删除的那行(因为删除之后变成了占位符),使得实际上少删除一行,因此 Oracle 建议两者不要混合使用
定义:
PROCEDURE TRIM (n PLS_INTEGER:=1);
如果 n 为 null,TRIM 不做任何事情
如果对未初始化的 nested table or a VARRAY 使用 TRIM 将返回 COLLECTION_IS_NULL 异常
如果 TRIM 的 n 大于实际存在的元素个数,那么返回 SUBSCRIPT_BEYOND_COUNT 异常


Working with Collections
Declaring Collection Types
两种声明方法:
1.在 PL/SQL 中使用 TYPE 语句,如果定义在 package 中,对于使用该类型的用户要将 package 的 EXECUTE 权限授予该用户
2.对于 nested table type or VARRAY type 在 schema-level 使用 CREATE TYPE 命令,并将该类型的 EXECUTE 权限授予需要使用的用户

Declaring an associative array collection type
TYPE table_type_name IS TABLE OF datatype [ NOT NULL ]
INDEX BY index_type;
datatype 包括:
1.Scalar datatype:VARCHAR2, CLOB, POSITIVE, DATE, or BOOLEAN 等等
2.Anchored datatype:使用 %TYPE、%ROWTYPE
3.Complex datatype:9i R2 开始可以使用 object types and collection types
index_type 包括:
INDEX BY BINARY_INTEGER;(9i R2 之前只能使用该类型)
INDEX BY PLS_INTEGER;
INDEX BY POSITIVE;
INDEX BY NATURAL;
INDEX BY SIGNTYPE;
INDEX BY VARCHAR2(32767);
INDEX BY table.column%TYPE;
INDEX BY cursor.column%TYPE;
INDEX BY package.variable%TYPE;
INDEX BY package.subtype;

Declaring a nested table or VARRAY
CREATE [ OR REPLACE ] TYPE type_name AS | IS
TABLE OF element_datatype [ NOT NULL ];
CREATE [ OR REPLACE ] TYPE type_name AS | IS
VARRAY (max_elements) OF element_datatype [ NOT NULL ];
DROP TYPE type_name [ FORCE ];
element_datatype 包括:大部分 scalar datatypes, an object type, or a REF object type,如果是 object type,那么他的属性不能是 collection 类型;在 PL/SQL 中,如果是 RECORD,那么他只能包含 scalars or objects 类型
FORCE:即使该 collection type 被其他类型引用,也 drop 他

Changing nested table or VARRAY characteristics
1.改变 VARRAY 元素最大个数:
ALTER TYPE list_t MODIFY LIMIT 100 INVALIDATE | CASCADE
2.改变元素类型为 variable character, RAW, or numeric 的类型长度或精度
CREATE TYPE list_t AS VARRAY(10) OF VARCHAR2(80);
ALTER TYPE list_t MODIFY ELEMENT TYPE VARCHAR2(100) INVALIDATE | CASCADE
INVALIDATE:所有使用该类型的对象失效
CASCADE:将改变传递给所有使用该类型的对象

Declaring and Initializing Collection Variables
collection_name collection_type [:= collection_type (...)];
对于 nested table or VARRAY 需要用构造函数初始化,构造函数可以可以接受用逗号分割的初始值作为参数,如果不初始化就使用会得到错误:ORA-06531: Reference to uninitialized collection

Initializing implicitly during direct assignment
同一 collection type (必须同一个类型,哪怕元素类型相同的不同 collection type 也不行)的不同变量,可以通过直接赋值进行隐式初始化
DECLARE
earth_colors Color_tab_t := Color_tab_t('BRICK', 'RUST', 'DIRT');
wedding_colors Color_tab_t; --必须同一类型,都是 Color_tab_t,这里没有初始化
BEGIN
wedding_colors := earth_colors; --赋值时同时初始化
wedding_colors(3) := 'CANVAS';
END;

Initializing implicitly via FETCH
如果表中的列是 Collection type,那么可以像一般类型一样通过 select ... into、FETCH 直接将值传递给相同类型的变量,并可以自动给该变量初始化

Populating Collections with Data
Using the assignment operator
1.单个赋值:
countdown_test_list (43) := 'Internal pressure';
2.整个 record (结构要一致)赋值:
DECLARE
TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;
l_emps emp_copy_t := emp_copy_t( );
l_emprec emp%ROWTYPE;
BEGIN
l_emprec.ename := 'Steven';
l_emprec.salary := 10000;
l_emps.EXTEND
l_emps (l_emps.LAST) := l_emprec;
END;
相同的还可以使用 select ... into、cursor FOR loop、select ... BULK COLLECT into(9i R2 之后,可将整个表赋值给 Collection,不需要初始化) 进行赋值
3.相同类型的 Collection 直接赋值(不需要初始化)

Accessing Data Inside a Collection
需要注意几点:
1.对于稀疏 Collection,当访问不存在的元素时会产生 NO_DATA_FOUND 异常
2.访问超出 EXTEND 分配空间的元素时产生 ORA-06533: Subscript beyond count 异常
3.对于 VARRAY,访问大于最大元素个数的元素时产生 ORA-06532: Subscript outside of limit 异常

Collections of Complex Datatypes
Collections of records
可以定义元素类型为 record 的 collection,使用 %ROWTYPE or a programmer-defined record type。适用于 PL/SQL,如果是定义在表的列上,不能使用 %ROWTYPE

Multilevel collections
9i R2 开始可以定义 collection 的元素类型也为 collection,既 collection 嵌套,被称为 multilevel collections,通过这种方法,可以构造多维 collection。

Using String-Indexed Collections
9i R2 开始可以使用 strings 作为 index type,FIRST, LAST, PRIOR, and NEXT 方法返回的也是 strings

Working with Collections in SQL
Oracle8 开始提供 collection pseudo-functions,他们可以使数据表和 collection 的数据互相转换,只能用于 SQL 语句中

1.The CAST pseudo-function
SQL 中,用于将 built-in datatype or collection type 转换为另一种 built-in datatype or collection type
CREATE TYPE Color_nt AS TABLE OF VARCHAR2(30);
CREATE TYPE Color_vat AS VARRAY(16) OF VARCHAR2(30);
CREATE TABLE color_models (
model_type VARCHAR2(12),
colors Color_vat);
SELECT COLUMN_VALUE FROM
TABLE (SELECT CAST(colors AS Color_nt)
FROM color_models
WHERE model_type = 'FGB');

2.The MULTISET pseudo-function
MULTISET 必须和 CASTs 一起使用,MULTISET 将数据集转换为 collection,SQL MULTISET function 和操作 nested tables 的 PL/SQL MULTISET 是完全不同的
SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)
FROM DUAL;
As with the CAST pseudo-function, MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.

3.The TABLE pseudo-function
TABLE 将 collection 转换为可以 SELECT from 的结果集,既将 collection 中的内容像数据表一样 select 出来
例如,表中含有 collection 字段,现在要找出 collection 中包含某个内容的所有数据:
SELECT *
FROM color_models c
WHERE 'RED' IN
(SELECT COLUMN_VALUE FROM TABLE(c.colors));

MODEL_TYPE COLORS
------------ ------------------------------------------------------
RGB COLOR_TAB_T('RED', 'GREEN', 'BLUE')

COLUMN_VALUE 是 TABLE operator 创建的 system-defined 列名


Nested Table Multiset Operations
10g 之后针对 nested tables 提供了 high-level set operations,将其作为 multisets 对待

Operation Return value Description
= BOOLEAN 比较两个 nested tables,如果 named type、cardinality、elements 相等,返回 TRUE,如果 nested table 中包含 null 则必不相等。10g 之前要比较,只能通过一个个元素来比较
<> or != BOOLEAN 比较两个 nested tables 是否不同
[NOT] IN ( ) BOOLEAN 判断左边的 nested table 是否在右边括号中的 nested tables list 中
x MULTISET EXCEPT NESTED TABLE 返回一个 NESTED TABLE,他的元素在 x 中,但不在 y 中,所有的 NESTED TABLE 都必须是相同类型的。DISTINCT 表示返回的 nested table 中除去重复元素
[DISTINCT] y
x MULTISET INTERSECT NESTED TABLE 返回一个 NESTED TABLE,他的元素是 x 和 y 中元素的交集
[DISTINCT] y
x MULTISET UNION NESTED TABLE 返回一个 NESTED TABLE,他的元素是 x 和 y 中元素的并集
[DISTINCT] y
SET(x) NESTED TABLE 返回一个 NESTED TABLE,包含 x 中不重复的所有元素
x IS [NOT] A SET BOOLEAN 判断 x 中的元素是否有重复值
x IS [NOT] EMPTY BOOLEAN 判断 x 是否为空
e [NOT] MEMBER BOOLEAN 判断表达式 e 是否包含在 x 的元素中
[OF] x
y [NOT] SUBMULTISET BOOLEAN 判断 y 的元素是否全都包含在 x 的元素中
[OF] x


Maintaining Schema-Level Collections
Necessary Privileges
如果创建 Schema-Level 的 nested tables and VARRAYS,其他 Schema 如果需要使用该类型必须有该类型的 EXECUTE 权限:
GRANT EXECUTE on Color_tab_t TO JOE;
对于包含 Schema-Level Collections 的 packages, procedures, functions 只要有这些过程的 EXECUTE 权限就可以使用这些类型了,但对于 PL/SQL anonymous blocks 中使用这些类型,只能直接将这些类型的 EXECUTE 权限授予执行的用户
如果某表的一列为 Schema-Level Collection 类型,对该表要有 SELECT, INSERT, UDPATE, and DELETE 权限才能做相应的操作,其次在 INSERT or UPDATE 时还需要有该 Collection 类型的 EXECUTE 权限

Collections and the Data Dictionary
1.USER_TYPES
所有创建的类型
SELECT type_name FROM user_types
WHERE typecode ='COLLECTION';
2.USER_SOURCE
类型的定义
SELECT text FROM user_source
WHERE name = 'FOO_T' AND type = 'TYPE'
ORDER BY line;
3.USER_DEPENDENCIES
依赖创建的类型的对象
SELECT name, type FROM user_dependencies
WHERE referenced_name='FOO_T';

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8643300/viewspace-967781/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    18
  • 访问量
    16721