Oracle定义联合数组及使用技巧

联合数组以前被称为PL/SQL表。在表中不能使用联合数组,只能将它们用作程序设计的结构体。只能在PL/SQL中访问联合数组。

注意到联合数组带来的一些关键问题是非常重要的。这些问题使我们介绍它们的用法时,需要采取一些特别的方法。这些问题包括:

联合数组不需要初始化,也没有构造函数语法。在对它们进行赋值以前,也不需要专门为其分配存储空间,也就不需要使用集合API的EXTEND方法。

在ORACLE 10G中,以及在ORACLE 10G以前的版本中,都可以使用数字索引联合数组。另外,在ORACLE 10G中,还可以使用具有唯一性的变长字符串作为联合数组的索引。

可以使用任意的整数作为联合数组的索引,这就说明联合数组的索引可以是任意正数、负数或0。
可以显式地将等价的%ROWTYPE、记录类型和对象类型的返回值,转换成联合数组的结构体。

联合数组是使用FORALL语句或BULK COLLECT子句的关键,而后者则允许数据库到程序设计单元的批转换。
在使用了全球化设置,例如NLS_COMP或NLS_SORT初始化参数的数据库中,将字符串用作联合数组索引的时候,需要我们进行特殊的处理。

1、定义联合数组和用作PL/SQL的程序结构体
在PL/SQL语言中定义联合数组的语法有两种,一种是:
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];
可以将正数、负数或者0值用作联合数组的索引。ORACLE 10G中的PLS_INTEGER何BINARY_INTEGER类型都是不受限制的数据类型,这两个数据类型都映射到C/C++、C#和JAVA的调用规范中。
变长字符串的最大长度为4000个字符。
另一种定义联合数组的语法是:
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]
INDEX BY key_type;
其中的key_type允许我们使用VARCHAR2、STRING或LONG类型。使用VARCHAR2和STRING时,都需要定义大小。使用LONG类型时,则不需要定义大小,因为它是通过定义VARCHAR(32760)进行定义的。
联合数组不需要进行初始化,也没有构造函数语法。这是与其他两种集合类型(VARRAYS和嵌套表)有着本质区别的地方。
如果你像下面这样构造一个联合数组,那么会引发PLS-00222异常。


代码如下:

-- Define an associative array of strings.
TYPE card_table IS TABLE OF VARCHAR2(5 CHAR)
INDEX BY BINARY_INTEGER;
-- and attempt to construct an associative array.
cards CARD_TABLE := card_table('A','B','C');
BEGIN
NULL;
END;

在前面的介绍中,我们知道对象的构造函数是完全可以作为一个函数使用的。其他集合类型,例如VARRAYS和嵌套表,都是显式定义构造函数的对象类型。而联合数组只是一个结构体,不是一个对象类型。因此,它不能显式地创建构造函数,也无法调用构造函数。
2、联合数组的初始化
前面已经说过,我们可以将数字或者具有唯一性的变长字符串作为索引,构造联合数组。数字索引比如为整数,可以为正整数、负整数和0值。唯一性的变长字符串可以是VARCHAR2、STRING或LONG数据类型。
1)以数字作为联合数组索引
下面的例子给出了一个向以数字为索引的联合数组中的元素赋值的过程,该示例示范了将VARRAY的内容转移到联合数组的过程。


代码如下:

-- Define a varray of twelve strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY BINARY_INTEGER;
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Initialize a null associative array element.
calendar(i) := '';
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(i) := month(i);
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN calendar.FIRST..calendar.LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
END LOOP;
END IF;
END;
/

在第一个FOR-LOOP循环中,用等于VARRAY类型的month索引的一个索引值,为联合数组类型的calendar变量赋上一个空值。这是为联合数组分配空间的唯一方法。
2)以唯一字符串作为联合数组索引
如下例所示:


代码如下:

-- Define a varray of twelve variable length strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of variable length strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY VARCHAR2(9 CHAR);
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(month(i)) := ''; --i;
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||month(i)||'] is ['||i||']');
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN calendar.FIRST..calendar.LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
END LOOP;
END IF;
END;

运行上面这段代码会出现错误。ORA-06502:PL/SQL:numeric or value error:character to number convertion error。在第一个FOR-LOOP中的初始化是没有任何问题的。可是在第二个FOR-LOOP循环中,程序试图向计数器变量传递一个非数字的值。在上面的程序中,这个计数器变量是i。计数器变量的数据类型被定义为PLS_INTEGER类型。所以,就不能将整个变长字符串的索引值赋给一个整型变量—因为变长字符串不是整数。这样,自然就引发了类型转换错误ORA-06502。该示例之所以会引发错误,是因为在初始化联合数组成员的时候,其中的计数器变量被转换为VARCHAR2类型,而在读联合数组的时候,又将该计数器类型转为INTEGER类型。
这其实给我们提出了一个新问题。非数字索引值需要我们明确的知道索引的开始值以及索引的递增方法。集合API的FIRST何NEXT方法提供了这种工具。
如下例所示:


代码如下:

-- Define variables to traverse an associative array that
-- uses variable length strings for index values.
current VARCHAR2(9 CHAR);
element INTEGER;
-- Define a varray of twelve variable length strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of variable length strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY VARCHAR2(9 CHAR);
-- and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(month(i)) := TO_CHAR(i);
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||month(i)||'] is ['||i||']');
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN 1..calendar.COUNT LOOP
-- Check if the first element in the loop.
IF i = 1 THEN
-- Assign the first character index to a variable.
current := calendar.FIRST;
-- Use the derived index to find the next index.
element := calendar(current);
ELSE
-- Check if next index value exists.
IF calendar.NEXT(current) IS NOT NULL THEN
-- Assign the character index to a variable.
current := calendar.NEXT(current);
-- Use the derived index to find the next index.
element := calendar(current);
ELSE
-- Exit loop since last index value is read.
EXIT;
END IF;
END IF;
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||current||'] is ['||element||']');
END LOOP;
END IF;
END;

3、与BULK COLLECT和FORALL结合使用联合数组
使用BULK COLLECT和FORALL胃我们打开了消除行级处理之门。使用BULK COLLECT可以获取存储在联合数组或嵌套表中的记录集。使用FORALL可以成批的发送DML语句。FORALL可以插入、更新和删除数据。这些方法减少了PL/SQL引擎和SQL引擎之间来回切换上下文环境的次数。如果没有这些方法,就会有太多的解析或取值过程。
你应该还记得行级处理实际上使用的是%ROWTYPE和%TYPE。前者可以直接映射到记录类型上。BULK COLLECT可以将%ROWTYPE或%TYPE类型的值的一个集合作为联合数组或嵌套表的一个集合进行赋值。FORALL提供了一种可以将联合数组或嵌套表中的内容转移到数据库对象的方法。
联合数组和嵌套表集合类型可以与BULK COLLECT和FORALL结合使用。使用嵌套表时,需要将嵌套表构造为空元素的集合。BULK COLLECT会显式地分配嵌套表的存储空间。不需要对联合数组进行构造,只要一个批赋值就可以了。同样,联合数组和嵌套表都可以作为SQL命令FORALL的源结构。
如下示例所示:


代码如下:

-- Create a table for the example.
CREATE TABLE bulk_numbers
(number_id NUMBER NOT NULL
,CONSTRAINT number_id_pk PRIMARY KEY (number_id));
-- Define an associative array of integers.
TYPE number_table IS TABLE OF bulk_numbers.number_id%TYPE
INDEX BY BINARY_INTEGER;
-- Define a variable of the associative array type.
number_list NUMBER_TABLE;
BEGIN
-- Loop from 1 to a million and increment associative array.
FOR i IN 1..10000 LOOP
-- Assign number value.
number_list(i) := i;
END LOOP;
-- Loop through all to do a bulk insert.
FORALL i IN 1..number_list.COUNT
INSERT
INTO bulk_numbers
VALUES (number_list(i));
-- Commit records.
COMMIT;
END;
-- Use a BULK COLLECT to retrieve a table into an
-- associative array.
-- Define an associative array of integers.
TYPE number_table IS TABLE OF bulk_numbers.number_id%TYPE
INDEX BY BINARY_INTEGER;
-- Define a variable of the associative array type.
number_list NUMBER_TABLE;
BEGIN
-- Check if calendar has no elements.
SELECT number_id
BULK COLLECT
INTO number_list
from bulk_numbers;
-- Print a title
DBMS_OUTPUT.PUT_LINE('Bulk Collected:');
DBMS_OUTPUT.PUT_LINE('---------------');
-- Loop through to print elements.
--只打印前两条和最后两条记录
FOR i IN number_list.FIRST..number_list.LAST LOOP
-- Print only the first and last two.
IF i <= 2 OR i >= 9999 THEN
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE('Number ['||number_list(i)||']');
END IF;
END LOOP;
END;

在BULK COLLECT子句中使用了ORDER BY,保证得出的结果是按照数字升序排列的。如果不对元素进行排序,就会发现它们是按照随机的顺序获取的,而不是按它们的数字顺序进行获取的。

(0)

相关推荐

  • Oracle定义联合数组及使用技巧

    联合数组以前被称为PL/SQL表.在表中不能使用联合数组,只能将它们用作程序设计的结构体.只能在PL/SQL中访问联合数组. 注意到联合数组带来的一些关键问题是非常重要的.这些问题使我们介绍它们的用法时,需要采取一些特别的方法.这些问题包括: 联合数组不需要初始化,也没有构造函数语法.在对它们进行赋值以前,也不需要专门为其分配存储空间,也就不需要使用集合API的EXTEND方法. 在ORACLE 10G中,以及在ORACLE 10G以前的版本中,都可以使用数字索引联合数组.另外,在ORACLE

  • ASP定义数组方法的技巧

    数组是有序数据的集合.数组中的元素可以不属于同一个数据类型.用一个统一的数组名和下标来唯一地确定数组中的元素,更改其中一个元素并不会影响其它元素.数组的下标是有界的,分为下界和上界.数组可以用Dim.Private.Public或Static来声明,它们的语法格式相同.下面只介绍用Dim声明数组的方法. 1.数组的定义与声明 数组的定义语法如下: Dim 数组名( [[下标下界 To ] 下标上界] ) [As 数据类型] 例如(假设在当前模块中 数组的缺省下界为0)): ① Dim A(10)

  • javascript中的对象和数组的应用技巧

    javascript已经用了有三年多了,但是对一些细节的东西还是一知半解,比如对象和数组,一直都在用一些最基本的操作.这是我学习的一个坏习惯--懒,很多东西都是了解而已,看到稍微复杂点的逻辑就没有耐心.就像学asp时不知道asp跟脚本的关系,学html不知道什么是DOM...结果是学得慢且不扎实,碰到稍复杂的问题就歇菜(扯远了). 今天继续优化脚本,尝试着合并一些数组时碰到问题,才算去仔细看了手册,做了一些测试. javascript1.2之后允许使用[]创建数组: var firstArray

  • JS数组中常用方法技巧学会进阶成为大佬

    目录 splice()方法 join()方法 reverse()方法 every()方法 reduce()方法 filter()方法 findIndex()方法 和find()方法 findIndex() find() forEach()方法 some()方法 indexOf()方法 sort ()方法 push()方法 pop()方法 unshift()方法 shift()方法 splice()方法 截取替换数组 第一个参数是起始位置,第二个是截取的个数,第三是替换的元素 ,返回值是截取的元素

  • js 定义对象数组(结合)多维数组方法

    在php中定义数组很简单,但是在js中如果搞成字符为下标会出错,所以结合对象来搞 var top_ = { 'index':'首页', 'user':'用户', 'tree':'模块树' } ; var all_list = [ { "index": [ {'name':'网站栏目管理','url':'1.html'}, {'name':'网站栏目管理2','url':'2.html'} ] }, { "user": [ {'name':'用户管理','url':'

  • JavaScript中数组常见操作技巧

    效果图如下所示: Tip: 右键在新标签中打开查看清晰大图 下面介绍JavaScript中的数组对象遍历.读写.排序等操作以及与数组相关的字符串处理操作 创建数组 一般使用数组字面量[]创建新数组,除非想要创建指定长度的数组 // good var arr = []; var arr = ['red', 'green', 'blue']; var arr = [ ['北京', 90], ['上海', 50], ['广州', 50] ]; // bad var arr = new Object()

  • 在VBS中定义字节数组Byte()介绍

    晚上在 QQ 上看到昵称为"乱码"的好友回答了搜搜问问里一个问题: 在VBS中有办法定义字节数组么? 在VBS中有办法定义字节数组么?就是字节子类型数组(VarType是8209的那种)注意不是VB! 但是回答实在是不堪入目,连别人问什么都没搞懂,而且那人居然还满意,真不知道他在想什么. 闲来没事,我来简单回答一下.首先要搞清楚别人在问什么,VBS 是弱类型脚本语言,只有一种数据类型,称为 Variant,但是 Variant 可以进一步分为几种子类型,这里不展开,详见<VBSc

  • Oracle SqlPlus设置Login.sql的技巧

    sqlplus在启动时会自动运行两个脚本:glogin.sql.login.sql这两个文件 执行顺序为 1.默认在在$ORACLE_HOME/sqlplus/admin路径下查找glogin.sql文件执行 2.默认在在当前路径下查找login.sql文件执行,若未查找到对应文件则执行3 3.判断是否设置SQLPATH环境变量,如果设置了该变量则在对应路径下查找并执行,未找到则停止查找 可以在login.sql文件中加入一些常用设置使用SQLPLUS时更便捷,以下试验在ORACLE 11.2.

  • javascript如何定义对象数组

    问题如下,已经完成单个对象的简单应用,希望定义一个数组,能包含多个student. 复制代码 代码如下: var student = new Object(); student.name = "Lanny"; student.age = "25"; student.location = "China"; var json = JSON.stringify(student); 方法一: 复制代码 代码如下: var students = []; s

  • 详解PHP对数组的定义以及数组的创建方法

    传统上把数组(array)定义为一组有某种共同特性的元素,这里的共同特性包括相似性(车模.棒球队.水果类型等)和类型(例如所有元素都是字符串或整数)等,每个元素由一个特殊的标识符来区分,这称为健(key).请注意,上面这句话中的传统上一词,因为现在可以摒弃这种定义,数组结构中可以包括完全无关的元素.PHP则更进一步,数组中的元素甚至可以不属于同一种类型.例如,一个数组可能包含州名.邮政编码.考试成绩或扑克牌等元素. 每个实体包含两个项:前面提到的健(key)和值(value).可以通过查询键来获

随机推荐