PL/SQL集合

作者：--    发布时间：2019-11-20

在本章中，我们将讨论pl/sql中的集合。集合是具有相同数据类型的有序元素组。 每个元素都由一个唯一的下标来表示它在集合中的位置。

pl/sql提供了三种集合类型 -

• 索引表或关联数组
• 嵌套的表
• 可变大小的数组或varray类型

oracle的每种类型的集合有以下特征 -

集合类型	元素个数	下标类型	密集或稀疏	在哪创建	是否为对象类型属性
关联数组(或索引表)	无界	字符串或整数	任意一种	只在pl/sql块中	no
嵌套表	无界	整数	开始密集，可以变得稀疏	在pl/sql块或模式级别	yes
可变大小数组(varray)	有界	整数	总是密集	在pl/sql块或模式级别	yes

我们已经在“pl/sql数组”一章中讨论了varray。 在本章中，将讨论pl/sql表。

两种类型的pl/sql表(即索引表和嵌套表)具有相同的结构，并且使用下标符号来访问它们的行。 但是，这两种表在一个方面有所不同， 嵌套表可以存储在数据库列中，索引表不能。

索引表

索引表(也称为关联数组)是一组键 - 值对。 每个键都是唯一的，用来定位相应的值。键可以是整数或字符串。
使用以下语法创建索引表。 在这里，正在创建一个名为table_name的索引表，其中的键是subscript_type，关联的值是element_type，参考以下语法 -

type type_name is table of element_type [not null] index by subscript_type; 

table_name type_name;

示例

以下示例显示了如何创建一个表来存储整数值以及名称，然后打印出相同的名称列表。

set serveroutput on size 99999;
declare 
   type salary is table of number index by varchar2(20); 
   salary_list salary; 
   name   varchar2(20); 
begin 
   -- adding elements to the table 
   salary_list('rajnish') := 62000; 
   salary_list('minakshi') := 75000; 
   salary_list('martin') := 100000; 
   salary_list('james') := 78000;  

   -- printing the table 
   name := salary_list.first; 
   while name is not null loop 
      dbms_output.put_line 
      ('salary of ' || name || ' is ' || to_char(salary_list(name))); 
      name := salary_list.next(name); 
   end loop; 
end; 
/

执行上面示例代码，得到以下结果 -

salary of james is 78000
salary of martin is 100000
salary of minakshi is 75000
salary of rajnish is 62000

pl/sql 过程已成功完成。

示例2

索引表的元素也可以是任何数据库表的%rowtype或任何数据库表字段的%type。 以下示例说明了这个概念。我们将使用存储在数据库中的customers表及数据 -

create table customers( 
   id   int not null, 
   name varchar (20) not null, 
   age int not null, 
   address char (25), 
   salary   decimal (18, 2),        
   primary key (id) 
);
insert into customers (id,name,age,address,salary) 
values (1, 'ramesh', 32, 'ahmedabad', 2000.00 );  

insert into customers (id,name,age,address,salary) 
values (2, 'khilan', 25, 'delhi', 1500.00 );  

insert into customers (id,name,age,address,salary) 
values (3, 'kaushik', 23, 'kota', 2000.00 );

insert into customers (id,name,age,address,salary) 
values (4, 'chaitali', 25, 'mumbai', 6500.00 ); 

insert into customers (id,name,age,address,salary) 
values (5, 'hardik', 27, 'bhopal', 8500.00 );  

insert into customers (id,name,age,address,salary) 
values (6, 'komal', 22, 'mp', 4500.00 );

存储过程代码如下 -

set serveroutput on size 99999;
declare 
   cursor c_customers is 
      select name from customers; 

   type c_list is table of customers.name%type index by binary_integer; 
   name_list c_list; 
   counter integer :=0; 
begin 
   for n in c_customers loop 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('customer('||counter||'):'||name_list(counter)); 
   end loop; 
end; 
/

执行上面示例代码，得到以下结果 -

嵌套表

嵌套表就像一个具有任意数量元素的一维数组。但是，嵌套表与数组在以下几个方面不同 -

• 数组是一个有声明数量的元素集合，但是一个嵌套的表没有。嵌套表的大小可以动态增加。
• 数组总是密集的，即它总是具有连续的下标。 嵌套数组最初是密集的，但是当从其中删除元素时，它可能变得稀疏。

使用以下语法创建嵌套表 -

type type_name is table of element_type [not null]; 

table_name type_name;

这个声明类似于索引表的声明，只是没有index by子句。

嵌套表可以存储在数据库列中。 它可以进一步用于简化sql操作，您可以使用更大的表来连接单列表。关联数组不能存储在数据库中。

示例1
下面的例子说明了嵌套表的使用 -

set serveroutput on size 99999;
declare 
   type names_table is table of varchar2(10); 
   type grades is table of integer;  
   names names_table; 
   marks grades; 
   total integer; 
begin 
   names := names_table('kavita', 'pritam', 'ayan', 'rishav', 'aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('total '|| total || ' students'); 
   for i in 1 .. total loop 
      dbms_output.put_line('student:'||names(i)||', marks:' || marks(i)); 
   end loop; 
end; 
/

当上面的代码在sql提示符下执行时，它会产生以下结果 -

total 5 students
student:kavita, marks:98
student:pritam, marks:97
student:ayan, marks:78
student:rishav, marks:87
student:aziz, marks:92

pl/sql 过程已成功完成。

示例2

嵌套表的元素也可以是任何数据库表的%rowtype或任何数据库表字段的%type。以下示例说明了这个概念。我们将使用存储在数据库中的customers表，参考以下代码的实现 -

set serveroutput on size 99999;
declare 
   cursor c_customers is  
      select  name from customers;  
   type c_list is table of customers.name%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
begin 
   for n in c_customers loop 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('customer('||counter||'):'||name_list(counter)); 
   end loop; 
end; 
/

执行上面示例代码，得到以下结果 -

集合方法

pl/sql提供了内置的集合方法，使集合更易于使用。下表列出了方法及其用途 -

编号	方法	目的
1	exists(n)	如果集合中的第n个元素存在，则返回true; 否则返回false。
2	count	返回集合当前包含的元素的数量。
3	limit	检查集合的最大容量(大小)。
4	first	返回使用整数下标的集合中的第一个(最小)索引编号。
5	last	返回使用整数下标的集合中的最后(最大)索引编号。
6	prior(n)	返回集合中索引n之前的索引编号。
7	next(n)	返回索引n成功的索引号。
8	extend	追加一个空(null)元素到集合。
9	extend(n)	将n个空(null)元素追加到集合中。
10	extend(n,i)	将第i个元素的n个副本追加到集合中。
11	trim	删除一个集合末尾的元素。
12	trim(n)	删除集合末尾的n个元素。
13	delete	删除集合中的所有元素，将count设置为0。
14	delete(n)	使用数字键或嵌套表从关联数组中删除第n个元素。 如果关联数组有一个字符串键，则删除键值对应的元素。 如果n为空，则delete(n)不执行任何操作。
15	delete(m,n)	从关联数组或嵌套表中移除m..n范围内的所有元素。 如果m大于n，或者m或n为空，则delete(m，n)将不执行任何操作。

集合异常

下表提供了集合异常情况以及何时引发 -

编号	集合异常	引发的情况
1	collection_is_null	尝试在一个原子空集合上进行操作。
2	no_data_found	下标指定被删除的元素或关联数组中不存在的元素。
3	subscript_beyond_count	下标超出了集合中元素的数量。
4	subscript_outside_limit	下标超出允许的范围。
5	value_error	下标为空或不能转换为键类型。如果键定义为pls_integer范围，并且下标超出此范围，则可能会发生此异常。