📄 第五章 pl-sql集合与记录（1） - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> population_type <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG><BR> <STRONG>INDEX</STRONG> <STRONG>BY</STRONG> <STRONG>VARCHAR2</STRONG>(64);<BR><BR> country_population population_type;<BR> continent_population population_type;<BR> howmany <STRONG>NUMBER</STRONG>;<BR> which <STRONG>VARCHAR2</STRONG>(64);<BR><STRONG>BEGIN</STRONG><BR> country_population(<EM>'Greenland'</EM>) := 100000;<BR> country_population(<EM>'Iceland'</EM>) := 750000;<BR> howmany := country_population(<EM>'Greenland'</EM>);<BR> continent_population(<EM>'Australia'</EM>) := 30000000;<BR> continent_population(<EM>'Antarctica'</EM>) := 1000; <EM>-- Creates new entry</EM><BR> continent_population(<EM>'Antarctica'</EM>) := 1001; <EM>-- Replaces previous value</EM><BR> which := continent_population.FIRST;<BR> <EM>-- Returns 'Antarctica'</EM><BR> <EM>-- as that comes first alphabetically.</EM><BR> which := continent_population.LAST;<BR> <EM>-- Returns 'Australia'</EM><BR> howmany :=<BR> continent_population(continent_population.LAST);<BR><EM> -- Returns the value corresponding to the last key, in this</EM><BR><EM> -- case the population of Australia.</EM><BR><STRONG>END</STRONG>;<BR>/
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>关联数组能帮我们存放任意大小的数据集合，快速查找数组中的元素。它像一个简单的SQL表，可以按主键来检索数据。
</P>
<P>因为关联数组的作用是存放临时数据，所以不能对它应用像INSERT和SELECT INTO这样的SQL语句。
</P>
<P class=title2>4、全球化设置对使用VARCHAR2类型作主键的关联数组的影响</P>
<P>如果在使用VARCHAR2作为键的关联数组的会话中改变国家语言或全球化设置，就有可能引起一个运行时异常。例如，在一个会话中改变初始化参数
NLS_COMP或NLS_SORT的值后，再调用NEXT和PRIOR就可能会引起异常。如果我们必须在会话中更改这些设置的话，那么，在重新使用关联数组的相关操作之前，就必须确保参数值被改回原值。
</P>
<P>在用字符串作为关联数组的键的时候，声明时必须使用VARCHAR2、STRING或LONG类型，但使用的时候可以使用其他类型，如NVARCHAR2，VARCHAR2等，甚至是DATE，这些类型值都会被TO_CHAR函数转成VARCHAR2。
</P>
<P>但是，在使用其他类型作为键的时候一定要慎重。这里举几个例子：当初始化参数NLS_DATE_FORMAT发生改变时，函数SYSDATE转成字符串的值就可能发生改变，这样的话，array_element(SYSDATE)的结果就和先前的结果不一样了；两个不同的NVARCHAR2类型值转成VARCHAR2值之后可能得出的结果是相同的，这样，数组array_element(national_string1)和
array_element(national_string2)可能引用同一个元素。 </P>
<P>当我们使用数据库连接(database
link)把关联数组作为参数传递给远程数据库时，如果两个数据库的全球化设置不一致，远程数据库会使用自己的字符顺序来调用FIRST和NEXT操作，即使该顺序与原集合顺序不同。由于字符集的不同，就可能出现在一个数据库中两个不同的键在另一个数据库被当作同一个键处理的情况，这时程序就会收到一个
VALUE_ERROR异常。 </P>
<P class=title1>二、集合类型的选择</P>
<P>如果我们有用其他语言编写的代码或业务逻辑，通常可以把其中的数组或集合直接转成PL/SQL的集合类型。</P>
<OL>
<LI>其他语言中的数组可以转成PL/SQL中的VARRAY。
<LI>其他语言中的集合和包(bags)可以转成PL/SQL中的嵌套表。
<LI>哈希表和其他无序查找表(unordered lookup
table)可以转成PL/SQL中的关联数组。 </LI></OL>
<P>当编写原始代码或从头开始设计业务逻辑的时候，我们应该考虑每种类型的优势，然后决定使用哪个类型更加合适。</P>
<P class=title2>1、嵌套表与关联数组间的选择</P>
<P>嵌套表和关联数组(原来称为索引表)都使用相似的下标标志，但它们在持久化和参数传递上有些不同的特性。
</P>
<P>嵌套表可以保存到数据表字段中，而关联数组不可以。嵌套表适于存放能够被持久化的重要数据。 </P>
<P>关联数组适用于存放较小量的数据，每次调用过程或包初始化时在内存中构建出来。它能够保存容量不固定的信息，因为它的长度大小是可变的。关联数组的索引值很灵活，可以是负数，不连续的数字，适当的时候还可以使用字符串代替数字。</P>
<P>PL/SQL能自动地将使用数字作为键的关联数组和主数组(host
array)进行转换。集合和数据库服务器间数据传输的最有效的方法就是使用匿名PL/SQL块进行批量绑定数据绑定。
</P>
<P class=title2>2、嵌套表与变长数组间的选择</P>
<P>在数据个数能够预先确定的情况下，使用变长数组是一个很好的选择。在存入数据库的时候，变长数组会保持它们原有的顺序和下标。
</P>
<P>无论在表内(变长数组大小不到4k)还是在表外(变长数组大小超过4k)，每个变长数组都被作为独立的一个对象对待。我们必须对变长数组中的所有元素进行一次性检索或更新。但对于较大量的数据来说，变长数组就不太适用了。
</P>
<P>嵌套表是可以有间隙的：我们可以任意地删除元素，不必非得从末端开始。嵌套表数据是存放在系统生成的数据表中，这就使嵌套表适合查询和更新集合中的部分元素。我们不能依赖于元素在嵌套表中的顺序和下标，因为这些顺序和下标在嵌套表存到数据库时并不能被保持。
</P>
<P class=title1>三、定义集合类型</P>
<P>要使用集合，我们首先要创建集合类型，然后声明该类型的变量。我们可以在任何PL/SQL块、子程序或包的声明部分使用TABLE和VARRAY类型。
</P>
<P>集合的作用域和初始化规则同其他类型和变量一样。在一个块或子程序中，当程序进入块或子程序时集合被初始化，退出时销毁。在包中，集合在我们第一次引用包的时候初始化，直至会话终止时才销毁。
</P>
<UL>
<LI>嵌套表 </LI></UL>
<P>对于嵌套表，可以使用下面的语法来进行定义： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>TYPE</STRONG> type_name <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> element_type [<STRONG>NOT</STRONG> <STRONG>NULL</STRONG>];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>其中type_name是在集合声明使用的类型标识符，而element_type可以是除了REF
CURSOR类型之外的任何PL/SQL类型。对于使用SQL声明的全局嵌套表来说，它的元素类型受到一些额外的限制。以下几种类型是不可以使用的：
</P>
<OL>
<LI>BINARY_INTEGER, PLS_INTEGER
<LI>BOOLEAN
<LI>LONG, LONG RAW
<LI>NATURAL, NATURALN
<LI>POSITIVE, POSITIVEN
<LI>REF CURSOR
<LI>SIGNTYPE
<LI>STRING </LI></OL>
<UL>
<LI>变长数组 </LI></UL>
<P>对于变长数组类型，可以使用下面的语法进行定义： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>TYPE</STRONG><BR> type_name <STRONG>IS</STRONG> {VARRAY | VARYING <STRONG>ARRAY</STRONG>} (size_limit) <STRONG>OF</STRONG> <BR> element_type [<STRONG>NOT</STRONG> <STRONG>NULL</STRONG>];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>type_name和element_type的含义与嵌套表相同。size_limit是正整数，代表数组中最多允许存放元素的个数。在定义VARRAY时，我们必须指定它的长度最大值。下例中，我们定义了一个存储366个DATE类型的VARRAY：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG> <STRONG><BR> TYPE</STRONG> Calendar <STRONG>IS</STRONG> VARRAY(366) <STRONG>OF</STRONG> <STRONG>DATE</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>关联数组 </LI></UL>
<P>对于关联数组，可以使用下面的语法进行定义： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>TYPE</STRONG> type_name <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> element_type [<STRONG>NOT</STRONG> <STRONG>NULL</STRONG>]<BR> <STRONG>INDEX</STRONG> <STRONG>BY</STRONG> [<STRONG>BINARY_INTEGER</STRONG> | <STRONG>PLS_INTEGER</STRONG> | <STRONG>VARCHAR2</STRONG>(size_limit)];<BR> <STRONG>INDEX</STRONG> <STRONG>BY</STRONG> key_type;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>key_type可以是BINARY_INTEGER或PLS_INTEGER，也可以是VARCHAR2或是它的子类型VARCHAR、
STRING或LONG。在用VARCHAR2做键的时候，我们必须指定VARCHAR2的长度，但这里不包括LONG类型，因为LONG等价于
VARCHAR2(32760)。而RAW、LONG
RAW、ROWID、CHAR和CHARACTER都是不允许作为关联数组的键的。在引用一个使用VARCHAR2类型作为键的关联数组中的元素时，我们还可以使用其他类型，如DATE或TIMESTAMP，因为它们自动地会被TO_CHAR函数转换成VARCHAR2。索引表可以使用不连续的键作下标索引。如下例中，索引表的下标是7468而不是1：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> emptabtyp <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp%<STRONG>ROWTYPE</STRONG><BR> <STRONG>INDEX</STRONG> <STRONG>BY</STRONG> <STRONG>BINARY_INTEGER</STRONG>;<BR><BR> emp_tab emptabtyp;<BR><STRONG>BEGIN</STRONG><BR> <EM>/* Retrieve employee record. */</EM><BR> <STRONG>SELECT</STRONG> *<BR> <STRONG>INTO</STRONG> emp_tab(7468)<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> empno = 7468;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>1、定义与PL/SQL集合类型等价的SQL类型</P>
<P>要把嵌套表或变长数组存到数据表中，我们必须用CREATE
TYPE来创建SQL类型。SQL类型可以当作数据表的字段或是SQL对象类型的属性来使用。 </P>
<P>我们可以在PL/SQL中声明与之等价的类型，或在PL/SQL变量声明时直接使用SQL类型名。 </P>
<UL>
<LI>嵌套表的例子 </LI></UL>
<P>下面的SQL*Plus脚本演示了如何在SQL中创建嵌套表，并把它作为对象类型的属性来使用： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> CourseList <STRONG>AS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>VARCHAR2</STRONG>(10) <EM>-- define type</EM><BR>/<BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> Student <STRONG>AS</STRONG> OBJECT ( <EM>-- create object</EM><BR> id_num <STRONG>INTEGER</STRONG>(4),<BR> name <STRONG>VARCHAR2</STRONG>(25),<BR> address <STRONG>VARCHAR2</STRONG>(35),<BR> status <STRONG>CHAR</STRONG>(2),<BR> courses CourseList) <EM>-- declare nested table as attribute</EM><BR>/
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -