About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文（169）
分类： Oracle
2020-07-07 16:08:34
1-1 使用集合的优点
集合类型包括index by table,nested table,varray三种，集合是存放同类元素的一个容器，使用集合有许多优点。
1.保持列表的维护轨迹。也可以使用临时表或分割字符来获得列表轨迹等，但是使用集合是高效代码干净的。
2.双向关联和自动访问的游标。在plsql中游标只允许向前fetch，若将游标的信息存到集合中，则可以向前后操作。
3.提高查询的性能。
4.缓存数据库信息。
1-2 内容简介
1.集合类型概览，对此三种类型做一般性介绍。
2.集合的方法。
3.使用集合。初始化，不同方式使用以及结合sql的使用。
4.nested table的multiset操作。在oracle10g中增加了multiset操作符，允许对nested table进行集合操作(union,intersect,minus等)。
5.操作schema级别的集合。使用oracle 提供的数据字典视图来操作集合类型。
1-3 集合类型概览
集合类型定义好之后，可以在真实的表中使用，也就是自定义类型的表。用create or replace type
1.集合类型
Associative arrays 一维,没有大小空间限制的,元素之间是[d1] 存储的，在oracle9i之前是index by表，9i正式改名为associative array。Index by语句可以使用”associate”或者index by varchar2(length)或pls_integer来代替。Index by是存放元素的索引。初始化只要给一个元素赋值就行，和其他两种需要构造器初始化不一样。定义schema级别的类型不能是index by表类型，其他两者都可以。shema级别的定义好处是外部可以使用。接受索引-231 + 1 and 231 – 1
key ----value，稀疏存储。
2.nested table 和index by表一样，没有index by关键字，初始化必须用构造器，默认下标是数字，大小可以自动增长。元素无顺序排列。
虽然可以通过key查找，但是不保证顺序。
3.varray 一维，但是元素之间是紧密排列的，下标必须是数字，通过构造器初始化。和nested table不一样，元素是有序排列的。
Oracle目前不支持多维集合，要使用多维集合，只能申明一个集合的类型是一个集合。如my_collection(12,22)一维,my_collection(12)(24)多维，但是oracle不直接支持，可以申明集合的集合来实现。集合中存放同种元素，使用集合变量的类型申明为预先定义的集合类型，然后初始化使用。
集合例子：
1.index by表集合
|
/** author:dingjun date:2008-4-13 一个使用index by表的例子 **/ declare /* 申明一个index by表集合类型，在oracle10g之后，index by表之后可以是varchar2，也可以是pls_integer,含义是一个记录的下标 */ type list_of_names is table of student.name%type index by binary_integer; --定义一个list_of_names集合变量 s_name list_of_names; --记录下标 l_row binary_integer; begin /* 初始化，index by表不需要用构造器初始化，而且是稀疏的，只要赋值就可以使用 */ s_name(-10):='dj1'; s_name(1):='dj2'; s_name(2):='dj3'; s_name(4):='dj4';
l_row:=s_name.first;--first无参，可以不需要括号 --循环迭代数据 while (l_row is not null) loop dbms_output.put_line('the name is '||s_name(l_row)); l_row:=s_name.next(l_row);--next(x),返回下一个下标 end loop; end;
--注意，10g允许index by表的下标是varchar2,那么最好不要用for i in low .. high了，因为它要求i是数值类型，如果是字符串，会自动转换类型，不成功则报错，用while循环，见下面例子 --下标为字符串的index by表迭代方式常规用法 declare type idx_table is table of varchar2(10) index by varchar2(100); var idx_table; idx varchar2(10); begin var('a'):='a'; var('b'):='b'; idx := var.first; while idx is not null loop dbms_output.put_line(var(idx)); idx := var.next(idx); end loop; end; /
--for i in low .. high要求i是数值类型，类型转换不成功 declare type idx_table is table of varchar2(10) index by varchar2(100); var idx_table; begin var('a'):='a'; var('b'):='b'; for idx in var.first .. var.last loop dbms_output.put_line(var(idx)); end loop; end; /
--转换成功 declare type idx_table is table of varchar2(10) index by varchar2(100); var idx_table; begin var('1'):='a'; var('2'):='b'; for idx in var.first .. var.last loop dbms_output.put_line(var(idx)); end loop; end; / |
2.nested table集合
|
--用create type建立一个nested table类型 create or replace type list_of_name is table of varchar2(1000); --schema级别的集合类型(nested table和varray)不能机遇表或视图。当然object类型是可以的，可以定义一个object类型 SQL> create or replace type list_of_name is table of emp.ename%type; 2 /
Warning: Type created with compilation errors
SQL> show err Errors for TYPE DINGJUN123.LIST_OF_NAME:
LINE/COL ERROR -------- -------------------------------------------- 1/31 PLS-00201: 必须声明标识符 'EMP.ENAME' 0/0 PL/SQL: Compilation unit analysis terminated /** author:dingjun date:2008-4-13 声明三个集合变量，然后在happyfamily变量中增加4个元素，在children中增加两个元素， oracle 10g支持对nested table使用multiset操作符，进行集合操作，然后通过差集求出 prents元素的值，打印结果 |
|
|
** 直接赋值也可以.extend,然后happyfamily(happyfamily.count):=…. / declare
happyfamily list_of_name:=list_of_name();--必须在使用的时候初始化，此处初始化为一个空元素,此处若不初始化，必须在体部初始化 children list_of_name:=list_of_name(); parents list_of_name:=list_of_name(); begin happyfamily.extend(4);--extend(4)是在最后一个元素后面增加4个元素 happyfamily(1):='dingjun'; happyfamily(2):='jianjun'; happyfamily(3):='renhua'; happyfamily(4):='xiaozhao';
children.extend;--默认在末尾增加一个元素 children(1):='dingjun'; children.extend; children(2):='jianjun';
parents:=happyfamily multiset except children;--multiset except求差集
for l_row in parents.first .. parents.last loop--循环得到结果,通过下标访问 dbms_output.put_line('parents name is '||parents(l_row)); end loop; end; |
|
3.varray集合
|
/** author:dingjun date:2008-4-13 申明两个varray类型，然后用于真实的表，用匿名过程操作插入内容到表中 **/ create or replace type first_name_t is varray(2) of varchar2(100);
create or replace type child_name_t is varray(1) of varchar2(100);
create table family( surname varchar2(100), parent_names first_name_t, child_names child_name_t );
|
|
declare parents first_name_t:=first_name_t();--varray和nested表一样，必须用构造器初始化 children child_name_t:=child_name_t(); begin parents.extend(2); parents(1):='jj'; parents(2):='dd';
children.extend; children(1):='son';
insert into family values('aa',parents,children); commit; end; / declare parents first_name_t:=first_name_t('a','b'); --初始化两个元素，索引自动为整数 begin for i in parents.first .. parents.last loop dbms_output.put_line(parents(i)); end loop; end; select * from family; |
1-4 集合的使用场合
数据库自身可以通过create type定义两种类型的集合(nested table and varray)，index by 表不可以单独定义。
1.集合可以作为record域的类型。
2.集合可以作为过程或函数等的参数传递。如下面声明一个schema级别的集合，然后作为参数传递
CREATE TYPE yes_no_t IS TABLE OF CHAR(1);
/
CREATE OR REPLACE PROCEDURE act_on_flags (flags_in IN yes_no_t)
IS
BEGIN
...
END act_on_flags;
/
3.集合作为函数的返回值类型。
4.集合作为一个object的属性，如
CREATE TYPE Auto_spec_t AS OBJECT (
make VARCHAR2(30),
model VARCHAR2(30),
available_colors Color_tab_t—一个集合
);
1-5 集合的内置方法
如果不需要参数，可以省略括号。其中x,y…为下标,下标列出此方法对哪个集合实用以及相应的操作用法。
|
方法(函数或过程) |
描述 |
|
Count方法 |
返回集合中元素的个数 |
|
Delete过程 |
删除一个或多个或所有元素，所有删除：delete,一个删除：delete(x),如果x为null，则集合保持不变。删除x到y的元素： delete(x,y)，如果x>y则集合保持不变，对varray，只能做全部删除操作。 |
|
Exists函数 |
返回true或false，指定元素是否存在。使用是：collection.exists(x),必须有且只有一个参数，下标 |
|
Extend过程 |
在集合元素末尾增加n个元素，对index by表非法。增加一个元素collection.extend;增加n个元素：collection.extend(n),增加指定元素的副本n次：collection.extend(n,x);增加x的副本n次在末尾。 |
|
First和last |
返回集合的最小下标和最大下标，对varray而言，first始终是1，last始终等于count |
|
Limit |
返回varray中允许的元素最大个数，nested table为null，对index by表不起作用。 |
|
Prior,next |
返回指定元素x之前的元素下标，若元素x是第一个元素，则prior返回null,若是最后一个元素，next返回null，使用：collection.next(x) |
|
Trim过程 |
从集合末尾删除一个元素或多个元素。删除一个:collection.trim,删除n个collection.trim(n) |
方法举例：
|
declare happyfamily list_of_name:=list_of_name();--必须在使用的时候初始化，此处初始化为一个空元素,此处若不初始化，必须在体部初始化 children list_of_name:=list_of_name(); parents list_of_name:=list_of_name(); begin happyfamily.extend(4);--extend(4)是在最后一个元素后面增加4个元素 happyfamily(1):='dingjun'; happyfamily(2):='jianjun'; happyfamily(3):='renhua'; happyfamily(4):='xiaozhao';
happyfamily.extend(4,3);--extend(x,y)中x指增加了几个复制的值，y指赋值下标为y 的元素的值。也就是在末尾赋值元素下标为y的元素值x次。 dbms_output.put_line('总共有元素数量为：'||happyfamily.count);--返回数量 dbms_output.put_line(happyfamily(happyfamily.prior(2))||'返回值');--happyfamily.prior(2)返回紧跟下标为2的元素之前的一个元素的下标 dbms_output.put_line(happyfamily.next(2)||'返回下标');
--末尾删除一个元素 happyfamily.trim; dbms_output.put_line('末尾删除一个元素之后还有：'||happyfamily.count); --再次在末尾删除两个元素 happyfamily.trim(2); dbms_output.put_line('末尾删除两个元素之后还有：'||happyfamily.count); --一次删除1-3的所有三个元素 happyfamily.delete(1,3); dbms_output.put_line('末尾删除两个元素之后还有：'||happyfamily.count); if happyfamily.exists(1) then dbms_output.put_line(happyfamily(1)||' 存在！'); end if;
children.extend;--默认在末尾增加一个元素 children(1):='dingjun'; children.extend; children(2):='jianjun';
parents:=happyfamily multiset except children;--multiset except求差集
for l_row in parents.first .. parents.last loop--循环得到结果,通过下标访问 dbms_output.put_line('parents name is '||parents(l_row)); end loop; end; |
|
结果是： 总共有元素数量为：8 dingjun返回值 3返回下标 末尾删除一个元素之后还有：7 末尾删除两个元素之后还有：5 末尾删除两个元素之后还有：2 parents name is xiaozhao parents name is renhua
--遍历索引是字符串的，因为for i 是数字的，不能用 DECLARE TYPE tp_test IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
lists tp_test; l_row VARCHAR2(100); BEGIN lists('a') := 'aa'; lists('b') := 'bb'; lists('c') := 'cc'; lists('d') := 'dd';
dbms_output.put_line(lists.FIRST); l_row := lists.FIRST; WHILE (l_row IS NOT NULL) LOOP dbms_output.put_line('the name is ' || lists(l_row)); l_row := lists.NEXT(l_row); --next(x),返回下一个下标 END LOOP; END;
|
未完待续，见PART2：http://blog.chinaunix.net/uid-7655508-id-5835535.html