oracle 三種集合類型比較（index-by表，嵌套表，數組）
Oracle三种集合数据类型(索引表，嵌套表，VARRAY 数组)的比较-PLSQL—之三
PL/SQL中没有数组的概念，他的集合数据类型和数组是相似的。在7.3以前的版本中只有一种集合，称为PL/SQL表，在这之后又有两种集合数据类型：嵌套表和varray。其中varray集合中的元素是有数量限制的，index_by表和嵌套表是没有这个限制的。index-by表是稀疏的，也就是说下标可以不连续，varray类型的集合则是紧密的，他的下标没有间隔。index_by表不能存储在数据库中，但是嵌套表和varray可以被存储在数据库中。
集合在使用时必须先使用type进行定义方可使用
1.index_by表
type type_name is table of element_type [NOT NULL] index by binary_integer
2.嵌套表
type type_name is table of element_type [NOT NULL]
3.varray
type type_name is [varray |varying array](max_size) of element_type[NOT NULL]
一，index_by表
TYPE TYPE1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
1.使用的时候需要先赋值后读取，至少也要先初期化一下，否则会出现异常：ORA-01403: no data found。
2.这种数组不需要事先指定上限，下标可以不连续，可以是0或负数。
例：v1 TYPE1;
v1(-1) := '-1';
v1(0) := '0';
v1(1) := '1';
DBMS_OUTPUT.put_line(v1(-1)); --访问合法
DBMS_OUTPUT.put_line(v1(2)); --访问非法
二，嵌套表
TYPE TYPE2 IS TABLE OF VARCHAR2(10);
54com
1.必须进行初期化，否则会出现异常：ORA-06531: Reference to uninitialized collection
2.初期化方法：
54ne
v1 TYPE2 := TYPE2(); --声明时初期化数组为空
54com
v2 TYPE2 := TYPE2('1','2','3','4','5'); --声明时初期化数组为5个元素
v1 := TYPE2(); --初期化后数组为空
v2 := TYPE2('1','2','3','4','5'); --初期化后数组为5个元素
3.数组元素的访问：
下标从1开始，不能超过数组所有元素的总和，当下标超出允许范围时，出现异常：ORA-06532: Subscript outside of limit
因为不能访问空数组，所以空数组的场合，必须进行数组扩展。 54ne
例：v1.EXTEND;
V1(1):= ‘1’; --访问合法 54com
v1(2):= ‘2’; --访问非法，之前必须再次执行v1.EXTEND; 网管网bitsCN_com
例：v2的下标范围是1～5。
v2(5):= ‘Hello’; --访问合法
DBMS_OUTPUT.put_line(v2(6)); --访问非法
三，Varray
feedom
TYPE TYPE3 IS ARRAY(5) OF VARCHAR2(10);
由于类型定义时的元素个数限制，所以TYPE3的变量在使用时最大的元素个数不能超过5个。
与嵌套表基本相同（略）
四，集合内建函数
集合还有很多内建函数，这些函数称为方法，调用方法的语法如下： 54ne
下表中列出oracle中集合的方法
方法 描述 使
用限制
COUNT 返回集合中元素的个数 网管网bitsCN
DELETE 删除集合中所有元素 54com
DELETE(x) 删除元素下标为x的元素，如果x为null，则集合保持不变 对VARRAY非法
DELETE(x,y) 删除元素下标从X到Y的元素，如果X>Y集合保持不变 对VARRAY非法
EXIST(x) 如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE 中国网管联盟www_bitscn_com
EXTEND 在集合末尾添加一个元素 对Index_by非法 54com
EXTEND(x) 在集合末尾添加x个元素 对Index_by非法
EXTEND(x,n) 在集合末尾添加元素n的x个副本 对Index_by非法
FIRST 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。 feedom
LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT。
LIMIT 返回VARRY集合的最大的元素个数，对于嵌套表和Index_by集合无用。
NEXT(x) 返回在元素x之后及紧挨着它的元素的值，如果该元素是最后一个元素，则返回null。
PRIOR(x) 返回集合中在元素x之前紧挨着它的元素的值，如果该元素是第一个元素，则返回null。
TRIM 从集合末端开始删除一个元素 对index_by不合法
TRIM(x) 从集合末端开始删除x个元素 对index_by不合法
第二種解釋：
/* 回顾oracle 复合类型 (记录和集合) */
/* 1.记录是为了更方便的处理单行多列，而集合是为了更方便的处理单列多行 */
-- （一 ）记录
/* 1. 首先创建一个简单表开始 */
create table test_complex(
id varchar2(20),
username varchar2(20)
);
-
- 向test_complex 表插入几条记录
insert into test_complex values('1111111','aaaaa');
insert into test_complex values('2222222','bbbbb');
insert into test_complex values('3333333','ccccc');
/* 2. 创建一个记录类型 record
记录类型有两种定义方式：显式定义和隐式定义。
*/
--显示定义
declare
--1.显示定义
type t_record is record (
id test_complex.id%type,
username test_complex.username%type
);
r_record t_record ; -- 声明一个记录类型变量r_record
--2.隐式定义
r_record test_complex%rowtype;
--游标定义
cursor c_cursor(v_id) is select id,username from test_complex t where t.id=v_id;
--3.游标定义记录变量
r_record2 c_cursor%rowtype;
begin
for v in (select * from test_complex) loop
r_record.id := v.id;
r_record.username := v.username;
dbms_output.put_line('id='|| r_record.id ||'username='||r_record.username);
end loop;
exception when others then
raise_application_error(-20201,'程序错误 for !');
oracle10g程序异常终止end;
--- select * from tab;
--- select * from test_complex
-- （二） 集合
/
* 集合主要包括三种: 索引表，嵌套表，以及数组(array)
1. Index表不能存储在数据库中（存储在内存），但嵌套表和VARRAY可以被存储在数据库中。
索引表语法如下；
TYPE type_name IS TABLE OF el
ement_type [NOT NULL] INDEX BY BINARY_INTEGER;
关键字是INDEX BY BINARY_INTEGER，没有这个关键字，那么集合将是一个嵌套表。由于不存储在数据库中，
element_type可以是任何合法的PL/SQL数据类型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。
索引表的下标可以为负，并且元素个数没有限制（索引有三种类型 pls_integer,binary_integer 10g开始允许使用varchar2 ）
一 ：pls_integer 和 binary_integer 一样可以存储 -2^31-2^31
1.存储性能高
2.pls_integer 溢出时会抛出异常，而binary_integer 如果指派给number 则不抛出异常
3.pls_integer 直接由cpu 运算比binary_integer（由oracle 模拟执行）快
*/
type t_tab is table of test_complex%rowtype index by binary_integer;
r_tab t_tab;
SELECT id,username INTO r_tab(0) FROM test_complex WHERE id='1111111';
---------------------------------------------------------------------------------------------------------------
/* 2.嵌套表定义语法如下：
嵌套表非常类似于Index_by表，创建的语法也非常相似。只是没有INDEX BY BINARY_INTEGER子串
TYPE type_name IS TABLE OF element_type [NOT NULL];
存储在一个数据库中的嵌套表并不与表中的其它数据存放在同一个数据块中，它们实际上被存放在第二个表中。
从数据库中取回的嵌套表也不保证元素的顺序。集合数据是离线存储的，所以嵌套表适合大型集合。
区别1：嵌套表下标从1开始并且元素格式不限
区别2：索引表类型不能作为表列的数据类型使用，但嵌套表可以
区别3：嵌套表类型变量使用是必须先被构造方法初始化才能使用
当使用嵌套表作为表列是必须用 eg: create type t_tbl is table of varchar2(2)，并且必须要为列指定专门的存储表
create type t__tbl is table of varchar2(10);
/
create table tab_t (
username varchar2(8),
phone t__tab1
) nested table phone store as t__tab1;
如果要处理多行多列则可以采用记录表
eg: type t__tbl is table of tab_t%rowtype index by binary_integer;
*/
type t_n_tab is table of test_complex.id%type ;
r_n_tab t_n_tab;
r_n_tab := r_n_tab('11111','22222');--初始化赋值
---------------------------------------------------------------------------------------------------------------
/* 3.VARRAY定义语法如下：（定长数组，下标从1开始）
TYPE type_name IS [VARRAY|VARYING ARRAY] (max_size) OF element_type [NOT NULL];
max_size是一个整数，用于标示VARRAY集合拥有的最多元素数目。VARRAY集合的元素数量可以低于max_size，但不能超过max_size。
element_type是一维元素的数据类型，如果element_type是记录，那么这个记录只能使用标量数据字段（与嵌套标相似）。
VARRAY存储在数据库中时与表中的其他数据存放在同一个数据块中，元素的顺序保存在VARRAY中。
集合是在线存储的，VARRAY很适合于小型集合。
嵌套表和VARRAY都能作为列存储在数据库表中，所以集合自身可以为NULL，当集合为NULL时，所以也不能引用集合中的元素。
可以使用IS NULL操作符检测集合是否为NULL。
*/
type t_array is array(100) of test_complex.id%type ; --可以作为表列
r_array t_array;
r_array := r_array('11111','22222');--初始化赋值
-------------------------------------------------------------------------------------------------------------------
/* 4： 批量绑定：执行单次sql 操作能完成所有元素额数据 ，可以提高性能
forall 有三种语法如下
1.forall index in low_bound,up_bount sql_statement;
2.forall index int indices of collection [between low_bound and up_bound] sql_statement;
3.forall index in values of index_collection sql_statement;
for i in indices of t_tabl -- indices of (oracle 10g 特性)用于跳过 'null'
1.bulk collect into 子句用于取得批量数据，只试用于 select into ,fetch into ,dml语句
2.forall 语句上使用values of 子句 用于从其他集合中集合下标的值(目的是可以指定插入)子集
forall i in values of index_tab sql_statement
3.sql%bulk_rowcount 用于第i个元素所作用的函数
eg; sql%bulk_rowcount(2) 第二个元素作用的行是多少
*/
----------常用函数------------------------------------------------------------------------------------------------
/* COUNT===返回集合中元素的个数
DELETE========删除集合中所有元素
DELETE(x)=====删除元素下标为x的元素=====对VARRAY非法
DELETE(x,y)===删除元素下标从X到Y的元素====对VARRAY非法
EXIST(x)======如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
EXTEND========在集合末尾添加一个元素=======对Index_by非法
EXTEND(x)=====在集合末尾添加x个元素================对Index_by非法
EXTEND(x,n)===在集合末尾添加元素n的x个副本==============对Index_by非法
FIRST=========返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST==========返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.
LIMIT=========返回VARRY集合的最大的元素个数===========Index_by集合和嵌套表无用
NEXT(x)=======返回在第x个元素之后及紧挨着它的元素的值，如果x是最后一个元素，返回null.
PRIOR(x)======返回在第x个元素之前紧挨着它的元素的值，如果x是第一个元素，则返回null。
TRIM==========从集合末端开始删除一个元素=====对于index_by不合法
TRIM(x)=======从集合末端开始删除x个=====对index_by不合法
*/</p>

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。