Oracle集合类型_oracle force关键字是

所谓集合是一种类似于列表或者一维数组的数据结构。PL/SQL提供了三种集合类型：关联数据组（索引表），嵌套表和VARRAY（可变长数组）。

 

1.集合类型

1.1关联数组

关联数组（也称为索引表）是一组键值对。每个密钥都是唯一的，并且被用于定位相应的值。键可以是整数或字符串。只能用于PL/SQL环境。

 

1.2嵌套表

从概念上讲，嵌套表像一个元素数量任意的一维数组。

在数据库中，嵌套表是存储一组值的列类型，数据库存储嵌套表的行是没有特定顺序的。当你从数据中提取嵌套表到PL/SQL变量时，该行给出连续从1开始的下标。通过这些类似数组下标访问独立的行。

嵌套表不同于数组的重要方面：

• 数组需要声明元素的个数，而嵌套表不需要。嵌套表的大小可以动态增加。
• 数组总是密集的，嵌套表刚开始是密集的，但是后面有可能会变成稀疏的。因为你会从嵌套表中删除元素。

 

1.3 可变长的数组

可变长的数组是一个VARRAY数据类型的集合。当你声明VARRAY类型的时候，就必须指定同时指定它能够包含的最大元素个数。VARRAY可以包含可变数据的元素，从零到最大值。VARRAY索引有一个固定定的下限1和一个可扩展的上限。和嵌套表类型一样的是，它们都可以用于PL/SQL和数据库。但是和嵌套表不一样的是，在向VARRAY中保存数据或者提取数据时，它的元素是有序的。

 

2.声明集合类型

在使用一个集合之前，我们必须先声明它。有两种方法可以申明一个集合类型：

• 通过TYPE语句在一个PL/SQL程序中声明集合类型。
• 通过CREATE TYPE语句在数据中定义一个嵌套表或者VARRAY类型，这个类型就是一个模式级别的对象。这种类型就可以用作数据库表的列的数据类型，可以用作对象类型的属性，也可以用于声明PL/SQL变量。

 

2.1声明关联数组

关联数组的TYPE语句的语法如下：

TYPE table_type_name IS TABLE OF datatype [ NOT NULL] INDEX BY index_type;

其中：

    table_type_name是你所创建的集合类型的名字，datatype是集合中唯一一列的数据类型，index_type是用来组织集合内容的索引的数据类型。而集合唯一以列的数据类型可以是下面这些：

• 标量数据类型：任何被PL/SQL支持的标量数据类型，比如VARCHAR2，CLOB，POSITIVE，DATE，或者BOOLEAN。
• 锚定数据类型：这种数据类型是从一个数据库表的列，之前已经定义的变量或者带有%TYPE属性的游标表达式推导出来的数据类型。我们也可以定义用%ROWTYPE声明或者根据一个用户定义的记录类型来定义一个记录的集合。
• 复杂的数据类型：从Oracle 9i数据库R2版本开始，你也可以把对象类型和集合类型作为集合的数据类型。

集合语法中的index_type定义索引下标的数据类型。在Oracle 9i数据库版本R2之前，只能是INDEX BY PLS_INTEGER。从Oracle 9i数据库版本R2开始，INDEX BY的数据类型可以是BINARY_INTEGER、及它的子类型、VARCHAR2(N)或者VARCHAR2列或变量的%TYPE锚定类型。

 

2.2声明嵌套表

可以数据库内或者PL/SQL代码块中声明嵌套表类型。

在数据库内创建一个嵌套表类型：

CREATE [OR REPLACE] TYPE type_name AS | IS TABLE OF element_datatype [ NOT NULL ];

删除数据库内的嵌套表类型：

DROP TYPE type_name [FORCE];

 

在PL/SQL中声明一个嵌套表类型：

TYPE type_name IS  TABLE OF element_datatype [ NOT NULL ];

其中：

•  OR REPLACE：允许我们重创建一个已经存在的类型。通过在语法中加上REPLACE的方式来重建类型，而不是先删除后再重新创建，可以把所有已经授予的权限都完整的保留下来。
• type_name：一个合法的SQL或者PL/SQL标志符。这也是我们以后在声明变量或者列时会用到的标识符。
• element_datatype:这是集合元素的数据类型。集合内所有元素都是一种类型的，可以是大部分标量数据类型、对象类型、或者REF对象类型。如果集合的元素是对象，对象类型本身不能再带有一个集合属性。如果你创建了一个其元素是RECORD类型的集合，记录的字段只能是标量或者是独享。明确的不可用于集合的数据类型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL数据类型)。
• NOT NULL：表明这种类型的变量不能有任何空元素。不过，集合本身可可以是原子级的空（未初始化）。
• FORCE：这个关键字告诉数据库的是，当要删除这个类型时，就算是其他类型中还有对这个类型的引用，也要强行删除这个类型。比如，如果在一个对象类型的定义中用到了某个特殊的集合类型，你可以使用FORCE关键字来强行删除这个集合类型。

 

2.3声明VARRAY

和嵌套表类型的声明一样，可以数据库内或者PL/SQL代码块中声明VARRAY类型。

在数据库内创建一个VARRAY类型：

CREATE [OR REPLACE] TYPE type_name AS | IS VARRAY (max_elements) OF element_datatype [ NOT NULL ];

删除数据库内的VARRAY类型：

DROP TYPE type_name [FORCE];

在PL/SQL中声明一个VARRAY类型：

TYPE type_name IS  VARRAY (max_elements) OF element_datatype [ NOT NULL ];

其中：

• OR REPLACE：允许我们重创建一个已经存在的类型。通过在语法中加上REPLACE的方式来重建类型，而不是先删除后再重新创建，可以把所有已经授予的权限都完整的保留下来。
• type_name：一个合法的SQL或者PL/SQL标志符。这也是我们以后在声明变量或者列时会用到的标识符。
• element_datatype:这是集合元素的数据类型。集合内所有元素都是一种类型的，可以是大部分标量数据类型、对象类型、或者REF对象类型。如果集合的元素是对象，对象类型本身不能再带有一个集合属性。如果你创建了一个其元素是RECORD类型的集合，记录的字段只能是标量或者是独享。明确的不可用于集合的数据类型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL数据类型)。
• NOT NULL：表明这种类型的变量不能有任何空元素。不过，集合本身可可以是原子级的空（未初始化）。
• max_elements:VARRAY中元素的最大数量，这个值一旦声明就不能更改。
• FORCE：这个关键字告诉数据库的是，当要删除这个类型时，就算是其他类型中还有对这个类型的引用，也要强行删除这个类型。比如，如果在一个对象类型的定义中用到了某个特殊的集合类型，你可以使用FORCE关键字来强行删除这个集合类型。

 

3.集合变量的声明和初始化

3.1集合变量的声明

一旦我们创建好了集合类型，我们就可以根据这个集合类型声明该类型的变量。一个集合变量声明格式如下：

collection_name collection_type [:=collection_type(...)];

其中，collection_name是集合变量的名字，collection_type具有两层含义，它即代表着一个先前已经声明的集合类型的名字，同时也代表着（如果是嵌套表或者VARRAY的话）和该类型同名的构造函数。

构造函数的名字和类型的名字是相同的，并且接收一个用逗号分隔的元素列表作为参数。如果我们声明的是一个嵌套表或者VARRAY变量，我们在使用之前必须要先对这个变量进行初始化。

 

3.2集合变量的初始化

对于嵌套表类型集合变量和VARRAY类型集合变量，在使用集合变量之前必须要进行初始化，而对于关联数组类型不需要初始化。下面主要讨论嵌套表和VARRY的初始化。

3.2.1通过构造函数的显示初始化

通过构造函数显示地给集合变量初始。例如：

declare

vnt_employee nt_employee :=nt_employee(); --不带参数的构造函数初始化

vnt_employee nt_employee :=nt_employee('张三','李四','王五'); --带参数的构造函数初始化

begin

   null;

end;

 

3.2.2直接赋值时的隐式初始化

如果两个集合实例是基于同一集合类型，我们可以把其中一个实例的全部内容拷贝给另一个，这就相当于进行了初始化。例如：

declare

vnt_employee nt_employee :=nt_employee('James','Lucy','Jordan');

vnt_foregin_employee nt_employee;

begin

     vnt_foregin_employee := vnt_employee；

end;

 

3.2.3通过FETCH操作的隐式初始化

在使用FETCH或者 SELECT INTO语句从数据库提取提取一个集合并保存到一个集合变量时，集合变量会自动初始化，就像直接赋值一样。

declare

vnt_colors nt_color;

begin

select colors into vnt_colors from color_models;  --表color_models的color列是嵌套表类型。

end;

 

3.2.4通过BULK COLLECT语句的隐式初始

使用BULK COLLECT INTO语句批量提取数据并保存到一个集合变量，集合变量会自动初始化，就像直接赋值一样。

declare

vnt_employee nt_employee; --未初始化

begin

   select e.ename bulk collect intovnt_employee  from emp e;

end;

 

declare

cursor  cur_employee is select e.ename from emp e;

vnt_employee  nt_employee; --未初始化

begin

   open cur_employee;

   fetch cur_employee bulk collect into vnt_employee;

   close cur_employee;

end;

 

4.集合方法

Oracle提供了提供许多内置的函数和过程可以用于获取集合的信息或者修改集合的内容，这些方法也叫做集合方法。下面给出这些方法的完整列表：

 

之所以把这些过程叫做方法，是因为使用这些集合内置程序的语法不同于调用过程和函数的正规语法。

 

5.集合类型对比

 

6.集合示例

6.1关联数组示例

declare
  type nt_foregn_employee is table of varchar2(30) index by binary_integer;
  vnt_foregn_employees nt_foregn_employee;
  v_row                number;
begin
  vnt_foregn_employees(-230002) := 'SCOTT';
  vnt_foregn_employees(-23) := 'JONES';
  vnt_foregn_employees(1) := 'ALLEN';
  vnt_foregn_employees(5934) := 'CLARK';
  vnt_foregn_employees(13342) := 'ADAMS';
  vnt_foregn_employees(8234223) := 'KING';

  --使用FIRST方法获取集合中的一个行号
  v_row := vnt_foregn_employees.first;

  while (v_row is not null) loop
    dbms_output.put_line(vnt_foregn_employees(v_row));
    v_row := vnt_foregn_employees.next(v_row);
  end loop;

end;

 

在稀疏集合中，经常需要使用NEXT方法遍历集合，提取数据。

 

6.2嵌套表示例

declare
  type nt_employee is table of emp%rowtype;
  vnt_employees nt_employee := nt_employee(); --构造函数显示初始化
  c_big_number  number := power(2, 31);
  l_start_time  pls_integer;

  cursor cur_employee is
    select * from emp;
  vrt_employees cur_employee%rowtype;

begin
  open cur_employee;
  loop
    fetch cur_employee
      into vrt_employees;
    exit when cur_employee%notfound;
    vnt_employees.extend;
    vnt_employees(vnt_employees.last) := vrt_employees;
  end loop;
  close cur_employee;
 
  dbms_output.put_line(vnt_employees.count);

end;

 

6.3VARRAY实例

create or  replace type nt_course is varray(5) of varchar2(100);
create table students( student_name varchar2(20) , cource nt_course);

declare
  vnt_nt_courses nt_course := nt_course();
begin
  vnt_nt_courses.extend(2);
  vnt_nt_courses(1) := 'English';
  vnt_nt_courses(2) := 'Chinese';

  insert into students
    (student_name, cource)
  values
    ('chiclewu', vnt_nt_courses);
     
end;

可以使用TABLE函数把一个集合映射成数据库表.例如，要获取student表中课程列的记录。

select * from table (select s.cource from students s);

 

参考：

Oracle PL/SQL程序设计（第五版） Steven Feuersterin & Bill Pribyl著 张晓明译