Oracle之数组

[Oracle整理]Oracle之数组

说明：本内容是工作用到的知识点整理，来自工作中和网络。
代码于Oracle9上测试。

 

Oracle数组一般可以分为固定数组和可变数组

集合：是具有相同定义的元素的聚合。Oracle有两种类型的集合：

可变长数组（VARRAY）：可以有任意数量的元素，但必须预先定义限制值。

嵌套表：视为表中之表，可以有任意数量的元素，不需要预先定义限制值。

在PL/SQL中是没有数组(Array)概念的。但是如果程序员想用Array的话，就得变通一下，用TYPE   和Table   of   Record来代替多维数组，一样挺好用的。

固定数组

-- ============ char array ===============
DECLARE
  -- declare fixed array
  TYPE arry_var IS VARRAY(2) OF VARCHAR2(10);
  arry_name arry_var;
BEGIN
  -- init array
  arry_name := arry_var('tom', 'jim','tim');

  dbms_output.put_line(arry_name(1));
  dbms_output.put_line(arry_name(2));
END;
-- ================ number array ========
DECLARE
  -- declare fixed array
  TYPE arry_num IS VARRAY(10) OF NUMBER;
  arry_top arry_num;
BEGIN
  -- init array
  arry_top := arry_num(1,2,3);

  dbms_output.put_line(arry_top(1));
  dbms_output.put_line(arry_top(2));
END;

1 VARRAY(10)变数数组大小为10

2OF NUMBER 表示数值类型是number

可变数组

一维数组

DECLARE
  TYPE t_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
  v_table t_table;
  v_cnt   NUMBER;
BEGIN
  v_table(1) := '1';
  v_table(2) := '3';
  v_table(3) := '9';

  v_cnt := v_table.COUNT;
  FOR i IN 1 .. v_cnt LOOP
    dbms_output.put_line(v_table(i));
    END LOOP;
END;

多维数组

1 Create Table

create table XXUSER
(
  USER_ID   NUMBER,
  USER_NAME VARCHAR2(255),
  SEX       VARCHAR2(2),
  AGE       NUMBER(3),
  ADDRESS   VARCHAR2(2000)
)

2 定义结果集（Record）

存放xxuser的部分字段

DECLARE
  -- only 2 fileds
  TYPE t_record_user IS RECORD(
    user_id   xxuser.user_id%type,
    user_name xxuser.user_name%type);

  TYPE t_user IS TABLE OF t_record_user INDEX BY BINARY_INTEGER;

  v_arry_user t_user;
BEGIN
  SELECT user_id, user_name BULK COLLECT INTO v_arry_user FROM xxuser;
  FOR i IN 1 .. v_arry_user.COUNT LOOP
    dbms_output.put_line(v_arry_user(i).user_name);
  END LOOP;
END;

3 使用ROWTYPE

存放xxuser的全部字段，比Record简洁。

DECLARE
  -- ALL,XXUser(user_id, user_name, sex, age, address)
  TYPE t_user IS TABLE OF xxuser%ROWTYPE INDEX BY BINARY_INTEGER;

  v_arry_user t_user;
BEGIN
  SELECT * BULK COLLECT INTO v_arry_user FROM xxuser;
  FOR i IN 1 .. v_arry_user.COUNT LOOP
    dbms_output.put_line(v_arry_user(i).user_name || v_arry_user(i).sex);
  END LOOP;
END;

==================================================

对于集合类型，与单一的数据类型相比较而言，应该以一个整体的观念来考虑集合，即是一批类型相同的数据组合而非单一的数据。因此集
合类型集合的声明、赋值、初始化较之单一类型而言，有很大的不同。尤其是嵌套表与变长数组，在赋值之前必须先初始化。当嵌套表和变长数
组在声明时，它们都会自动地被设置成NULL值。也就是嵌套表和变长数组中集合不存在任何元素，并不是针对它所拥有的元素。可以使用系统定
义的与集合类型同名的函数来初始化集合。我们必须显式地调用构造函数为每一个变长数组和嵌套表变量进行初始化操作(对于关联数组来说，
是不需要使用构造函数进行初始化的)。

        有关集合类型的描述请参考：
        PL/SQL 联合数组与嵌套表 
        PL/SQL 变长数组
        PL/SQL --> PL/SQL记录 

一、联合数组的赋值

    联合数组的不需要初始化，直接赋值即可。(后续讲到的集合的初始化均指嵌套表与变长数组)

DECLARE
   TYPE idx_loc_type IS TABLE OF VARCHAR2( 13 )
                           INDEX BY BINARY_INTEGER;

   loc_tab   idx_loc_type;
BEGIN
   loc_tab( 1 ) := 'NEW YORK';                        -->联合数组不需要初始化，直接赋值即可
   loc_tab( 2 ) := 'DALLAS';
   DBMS_OUTPUT.put_line( ' loc_tab(1) value is ' || loc_tab( 1 ) );
   DBMS_OUTPUT.put_line( ' loc_tab(2) value is ' || loc_tab( 2 ) );
END;

--------------------------------------------------------------------------------------------------------

DECLARE
   TYPE idx_loc_type IS TABLE OF VARCHAR2( 13 )
                           INDEX BY BINARY_INTEGER;

   loc_tab     idx_loc_type;
   v_counter   INTEGER := 0;
BEGIN
   FOR x IN ( SELECT loc FROM dept )       -->这里通过for 循环得到loc的值
   LOOP
      v_counter   := v_counter + 1;        -->使用一个v_counter变量来控制联合数组的下标
      loc_tab( v_counter ) := x.loc;       -->将得到的loc的值赋值给联合数组中对应的一个下标位
      DBMS_OUTPUT.put_line( ' loc_tab(' || v_counter || ') value is ' || loc_tab( v_counter ) );
   END LOOP;
END;

二、集合的初始化与赋值

1、初始化的方法
     集合类型主要分为三步来完成，一是声明，二是初始化，三是赋值。初始化和赋值可以在声明块中完成，也可以在执行块中完成。
        collection_name collection_type:=collection_type();   -->初始化集合为空(empty)
        
     集合的初始化主要是通过构造函数(构造函数即是声明类型是的类型名)来进行初始化，下面常用的初始化方法包括：
        a、在声明块声明集合，且在声明块中使用构造函数初始化为空(empty)但非NULL，在执行块中使用extend方式后进行赋值
        b、在声明块声明集合，在执行块中使用构造函数初始化为空(empty)但非NULL，在执行块中使用extend方式后赋值
        c、在声明块声明集合，在执行块中使用构造函数初始化时一并赋值
        d、在声明块声明集合，同时使用构造函数初始化并赋值，即三步合为一步来完成
     对于初始化为空的集合(empty)，后续需要使用extend方式来扩展容量，除非使用bulk collect into方式
             
2、集合赋值的方法
        collection_name(subscript) := expression;
        
3、赋值时可能引起的异常 
     在下面几种给集合元素赋值的情况下，可能会引起多种异常。
        a、如果下标索引不存在或无法转换成正确的数据类型，PL/SQL就会抛出预定义异常VALUE_ERROR。
                通常，下标是一个整数。但关联数组的下标也可以是VARCHAR2类型。 
        b、如果所给下标索引指向了一个未初始化的元素时，PL/SQL就会抛出SUBSCRIPT_BEYOND_COUNT异常。 
        c、如果集合被自动初始化为空值并且程序引用了其中的一个元素，PL/SQL会抛出COLLECTION_IS_NULL异常。

4、元素的引用
        collection_name(subscript)

     可以把其中的表元素作为参数传递。如verify_loc(nest_loc_tab(i))，verify_loc为函数或过程。

   

三、集合的初始化与赋值引用示例    

1、未初始化集合的情形
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );

   loc_tab   nest_loc_type;
BEGIN
   loc_tab( 1 ) := 'NEW YORK';
   loc_tab( 2 ) := 'DALLAS';
   DBMS_OUTPUT.put_line( ' loc_tab(1) value is ' || loc_tab( 1 ) );
   DBMS_OUTPUT.put_line( ' loc_tab(2) value is ' || loc_tab( 2 ) );
END;

DECLARE
*
ERROR at line 1:
ora-06531: Reference to uninitialized collection  -->收到了ora-06531错误提示，变长数组未初始化时会收到同样的错误提示
ora-06512: at line 6
--------------------------------------------------------------------------------------------------------

2、集合为NULL的判断
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );

   loc_tab   nest_loc_type;
BEGIN
   IF loc_tab IS NULL THEN
      DBMS_OUTPUT.put_line( 'Before initialization, the loc_tab is null.' );
   -- While the collection  is null, we cannot check its COUNT attribute.
   --   DBMS_OUTPUT.PUT_LINE('It has ' || loc_tab.COUNT || ' elements.');
   ELSE
      DBMS_OUTPUT.put_line( 'Before initialization, the loc_tab is not null.' );
   END IF;

   loc_tab     := nest_loc_type( );         --> initialize empty nest table

   IF loc_tab IS NULL THEN
      DBMS_OUTPUT.put_line( 'After initialization, the loc_tab is null.' );
   ELSE
      DBMS_OUTPUT.put_line( 'After initialization, the loc_tab is not null.' );
      DBMS_OUTPUT.put_line( 'It has ' || loc_tab.COUNT || ' elements.' );
   END IF;
END;

Before initialization, the loc_tab is null.
After initialization, the loc_tab is not null.
It has 0 elements.

PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------------------------------

3、使用空构造函数在声明时进行初始化
-->使用该方法初始化之后，表明嵌套表或变成数组是空的，但是非NULL，在执行块再对其赋值
-->下面对变长数组进行初始化
DECLARE
   TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;

   varry_loc_tab   varry_loc_type := varry_loc_type( );                 -->仅仅是在集合变量之后使用空构造函数
BEGIN
   varry_loc_tab( 1 ) := 'NEW YORK';                                    -->尽管变长数组被初始化，但仍然不能直接赋值
   varry_loc_tab( 2 ) := 'DALLAS';                                      -->这是由变长数组和嵌套表特性决定需要先做extend
   DBMS_OUTPUT.put_line( ' varry_loc_tab(1) value is ' || varry_loc_tab( 1 ) );
   DBMS_OUTPUT.put_line( ' varry_loc_tab(2) value is ' || varry_loc_tab( 2 ) );
END;

DECLARE
*
ERROR at line 1:
ora-06533: subscript beyond count
ora-06512: at line 6
--------------------------------------------------------------------------------------------------------

4、使用空构造函数在声明时进行初始化，执行块使用extend方式扩展后赋值
DECLARE
   TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;

   varry_loc_tab   varry_loc_type := varry_loc_type( );              -->仅仅是在集合变量之后使用空构造函数
BEGIN
   varry_loc_tab.EXTEND;                                             -->需要使用extend方式扩展集合容量
   varry_loc_tab( 1 ) := 'NEW YORK';
   varry_loc_tab.EXTEND;                                             -->需要使用extend方式扩展集合容量
   varry_loc_tab( 2 ) := 'DALLAS';
   DBMS_OUTPUT.put_line( ' varry_loc_tab(1) value is ' || varry_loc_tab( 1 ) );
   DBMS_OUTPUT.put_line( ' varry_loc_tab(2) value is ' || varry_loc_tab( 2 ) );
END;
--------------------------------------------------------------------------------------------------------

5、嵌套表的初始化,使用构造函数在执行块直接初始化并赋值
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );

   loc_tab   nest_loc_type;
BEGIN
   loc_tab     :=
      nest_loc_type( 'NEW YORK'           -->使用声明时的类型nest_loc_type函数来进行初始化
                    ,'DALLAS'
                    ,'CHICAGO'
                    ,'BOSTON' );

   FOR i IN 1 .. loc_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line( 'loc_tab(' || i || ') value is ' || loc_tab( i ) );
   END LOOP;
END;
--------------------------------------------------------------------------------------------------------

6、含有NOT NULL嵌套表的初始化
DECLARE
   TYPE loc_type IS TABLE OF VARCHAR2( 13 ) NOT NULL;      -->定义了NOT NULL约束条件

   loc_tab   loc_type;
BEGIN
   loc_tab     :=
      loc_type( 'NEW york'
               ,NULL                                       -->构造时传递了NULL值
               ,NULL
               ,'boston' );

   FOR i IN 1 .. loc_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line( 'loc_tab(' || i || ') value is ' || loc_tab( i ) );
   END LOOP;
END;

-->由于存在not null约束，初始化传递null值则收到错误提示
ERROR at line 8:
ora-06550: line 8, column 17:
pls-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ora-06550: line 9, column 17:
pls-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ora-06550: line 6, column 4:
pl/SQL: Statement ignored
--------------------------------------------------------------------------------------------------------

7、变长数组的初始化,使用构造函数直接初始化并赋值
-->变长数组的初始化与嵌套表一样，可以使用构造函数直接初始化并赋值
DECLARE
   TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;

   varry_loc_tab   varry_loc_type;
BEGIN
   varry_loc_tab :=
      varry_loc_type( 'NEW YORK'
                     ,'DALLAS'
                     ,'CHICAGO'
                     ,'BOSTON' );

   FOR i IN varry_loc_tab.FIRST .. varry_loc_tab.LAST   -->注意此处使用了集合方法中的函数first和last来控制循环步长
   LOOP
      DBMS_OUTPUT.put_line( 'varry_loc_tab(' || i || ') value is ' || varry_loc_tab( i ) );
   END LOOP;
END;
--------------------------------------------------------------------------------------------------------

8、声明时初始化(构造)、并赋值
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 ) NOT NULL;

   nest_loc_tab   nest_loc_type := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' ); -->在声明时直接初始化并赋值
BEGIN
   FOR i IN 1 .. nest_loc_tab.COUNT    -->注意此处调用了集合操作方法中的count函数
   LOOP
      DBMS_OUTPUT.put_line( 'nest_loc_tab(' || i || ') value is ' || nest_loc_tab( i ) );
   END LOOP;
END;
--------------------------------------------------------------------------------------------------------

9、SQL语句中使用构造函数
CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 );   -->创建一个嵌套表类型

CREATE TABLE tb_tmp                   -->创建表tb_tmp
(
   empno   NUMBER( 4 )
  ,ename   VARCHAR2( 10 )
  ,mail    mail_type                  -->列mail的类型为mail_type
)
NESTED TABLE mail                     -->注意此处需要指定嵌套表的存储方式
   STORE AS mail_tab;

INSERT INTO tb_tmp
   SELECT 8888, 'Jack', mail_type( 'Jack@yahoo.com', 'Jack@163.com' ) FROM dual;  -->插入数据时需要使用构造函数

10、集合与集合之间的赋值
-->下面的例子声明了两个变长数组last_name_type和surname_type
DECLARE
   TYPE last_name_type IS VARRAY( 3 ) OF VARCHAR2( 64 );

   TYPE surname_type IS VARRAY( 3 ) OF VARCHAR2( 64 );

   -->下面声明了两个相同类型的变长数组并为其赋值，group1和group2使用了相同的构造函数
   group1   last_name_type := last_name_type( 'Jones', 'Wong', 'Marceau' );
   group2   last_name_type := last_name_type( 'Klein', 'Patsos', 'Singh' );
   -->下面的group3使用了surname_type作为类型
   group3   surname_type := surname_type( 'Trevisi', 'Macleod', 'Marquez' );
BEGIN
   group1      := group2;                           -- >group1 和group2之间可以相互赋值
--   group3 := group2;                             -->raises an error    PLS-00382: expression is of wrong type
END;
-- >group3和group2则不能赋值，因为两者为不同的数据类型
-->尽管last_name_type与surname_type类型定义是相同的，但其实例化后，其集合变量不能互相赋值
--------------------------------------------------------------------------------------------------------

11、使用NULL值集合为集合赋值
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 30 );

   nest_loc_tab         nest_loc_type := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' );
   empty_nest_loc_tab   nest_loc_type;                --> 嵌套表empty_nest_loc_tab没有初始化，此时被自动置为NULL
BEGIN
   IF nest_loc_tab IS NOT NULL THEN
      DBMS_OUTPUT.put_line( 'OK, at first nest_loc_tab is not null.' );
   END IF;

   nest_loc_tab := empty_nest_loc_tab;                -->将empty_nest_loc_tab的值(NULL)嵌套表赋值给nest_loc_tab

   IF nest_loc_tab IS NULL THEN                       --> 此时nest_loc_tab被置为NULL，相当于没有初始化
      DBMS_OUTPUT.put_line( 'OK, now nest_loc_tab has become null.' );
   END IF;

   nest_loc_tab := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' );  -->此时如果后续需要使用该嵌套表，应重新初始化它
END;

12、记录类型的变长数组的初始化、赋值与元素引用
DECLARE
   TYPE emp_name_rec IS RECORD                             -->声明一个基于用户定义的记录类型
   (
      firstname   employees.first_name%TYPE
     ,lastname    employees.last_name%TYPE
     ,hiredate    employees.hire_date%TYPE
   );

   TYPE emplist_arr IS VARRAY( 10 ) OF emp_name_rec;       -->声明一个基于记录的变长数组，且最大尺寸为10

   seniorsalespeople   emplist_arr;                        -->声明基于记录的变长数组变量

   CURSOR c1 IS                                            -->声明游标，其列前面的记录类型相对照
      SELECT first_name, last_name, hire_date FROM employees;

   TYPE nameset IS TABLE OF c1%ROWTYPE;                    -->声明基于游标的记录类型

   seniorten           nameset;                            -->声明基于游标记录类型的变量
   endcounter          NUMBER := 10;                       -->变量endcounter计数器
BEGIN
   seniorsalespeople := emplist_arr( );                    -->初始化集合

   SELECT first_name, last_name, hire_date                 -->从表中提取数据，且使用了BULK COLLECT INTO方式
   BULK   COLLECT INTO seniorten
   FROM   employees
   WHERE  job_id = 'SA_REP'
   ORDER BY hire_date;

   IF seniorten.LAST > 0 THEN
      IF seniorten.LAST < 10 THEN                          -->如果小于10，则缩小变长数组的最大尺寸
         endcounter  := seniorten.LAST;
      END IF;

      FOR i IN 1 .. endcounter     -->使用循环将游标类型变量中的元素逐条赋值给记录的变长数组变量seniorsalespeople并输出
      LOOP
         seniorsalespeople.EXTEND( 1 );
         seniorsalespeople( i ) := seniorten( i );
         DBMS_OUTPUT.
         put_line(seniorsalespeople(i).lastname||', '||seniorsalespeople(i).firstname||', '||seniorsalespeople(i).hiredate);
      END LOOP;
   END IF;
END;

    上面的这个例子是一复合的数据类型，比单一的集合类型更为复杂。我们知道集合通常是针对单列多行数据而言，而记录则是单行多列。两
者的综合，则此时就等同于数据库中的一张二维表。示例中首先声明用户定义的记录类型以及变长数组，接下来基于这两者声明变量。后面使用
基于游标的记录类型来申明变量seniorten与前面的变量seniorsalespeople相对应，seniorten变量用于存储后面的SQL语句批量提取的数据集。
后面使用了一个for循环来从seniorten变量取出数据并赋值为seniorsalespeople。注：在这个例子中变量seniorten存储的记录超出了变长数组
的最大尺寸，因此后续的被丢弃。

13、记录类型的嵌套表的初始化、赋值与元素引用
DECLARE
   TYPE emp_name_rec IS RECORD
   (
      firstname   employees.first_name%TYPE
     ,lastname    employees.last_name%TYPE
     ,hiredate    employees.hire_date%TYPE
   );

   TYPE emplist_tab IS TABLE OF emp_name_rec;

   seniorsalespeople   emplist_tab;
   endcounter          NUMBER := 10;

   TYPE empcurtyp IS REF CURSOR;    -->声明游标变量类型

   emp_cv              empcurtyp;   -->声明游标变量类型的变量emp_cv
BEGIN
   OPEN emp_cv FOR
      SELECT first_name, last_name, hire_date
      FROM   employees
      WHERE  job_id = 'SA_REP'
      ORDER BY hire_date;

   FETCH emp_cv
   BULK   COLLECT INTO seniorsalespeople;    -->使用BULK   COLLECT INTO 方式一次将数据加载到seniorsalespeople变量

   CLOSE emp_cv;

   IF seniorsalespeople.LAST > 0 THEN
      IF seniorsalespeople.LAST < 10 THEN
         endcounter  := seniorsalespeople.LAST;
      END IF;

      FOR i IN 1 .. endcounter
      LOOP
         DBMS_OUTPUT.
         put_line(seniorsalespeople(i).lastname||', '||seniorsalespeople(i).firstname||', '||seniorsalespeople(i).hiredate);
      END LOOP;
   END IF;
END;
-->Author : Robinson Cheng
-->Blog   : http://blog.csdn.net/robinson_0612

    上面的这个例子稍有不同于前面的例子，使用的基于用户定义记录的嵌套表方式，且使用了游标变量类型。在fetch时直接将数据fetch 到
集合变量seniorsalespeople中，此时不需要使用extend方式来扩展。

四、总结

1、对于集合类型在为其赋值之前，需要对集合进行初始化。而联合数组不需要初始化而直接进行赋值。
2、在声明嵌套表与变长数组时，这些集合类型会被自动置为NULL，即集合不存在任何元素。而不是集合中的元素为NULL。
3、集合类型的初始化方法是是直接使用声明时的同名类型构造器来对集合进行初始化。
4、集合类型的初始化方法有多种，可以在声明时初始化，也可以在执行块初始化。
5、集合类型的赋值可以在声明块声明时赋值，也可以在执行块执行时使用extend方式扩展后再赋值。
6、集合类型的初始化过程连同赋值可以在声明集合的同时使用构造函数直接进行初始化并赋值，从而一步完成。
7、SQL语句中也需要使用构造函数来操作集合数据。
8、注意本文描述中的集合初始化后为空的理解。初始化后为空表示的是一个空(empty)集合，而未初始化时是NULL(UNKNOWN)值。
9、集合与集合之间的赋值需要声明的为同一类型的变量之间才可以赋值，否则收到错误提示。

10、注意理解复合类型之间(嵌套表和变长数组中嵌有PL/SQL记录)的变量元素间的传递以及集合方法BULK COLLECT INTO，LAST，EXTEND等。

--==========================================================================================

 

Oracle集合（联合数组（索引表），嵌套表，变长数组，记录类型的嵌套表）的初始化与赋值，以及它们的区别

 

--其中嵌套表与变长数组在赋值之前必须初始化，可以使用与集合类型同名的函数来进行初始化，
联合数组无需初始化  www.2cto.com  

 

--1.联合数组：

DECLARE  

  TYPE ind_tab_type IS TABLE OF VARCHAR2(2000)  

                    INDEX BY BINARY_INTEGER;  

  ind_tab           ind_tab_type;  

BEGIN  

  ind_tab(1) := 'lubinsu';--这里的下标可以随意指定，可以通过循环来获取  

  ind_tab(2) := 'luzhou';  

  --dbms_output.put_line(ind_tab(0));  

  --dbms_output.put_line(ind_tab(1));  

  FOR i IN ind_tab.first..ind_tab.last LOOP  

    dbms_output.put_line('ind_tab(' || i || '):' || ind_tab(i));  

  END LOOP;  

END;  

/  

 

--2.嵌套表的初始化1

--嵌套表的下标默认为1开始，也可以自己指定任意值  www.2cto.com  

DECLARE  

  TYPE nest_tab_type IS TABLE OF VARCHAR2(2000) NOT NULL; --如果设置not null条件那么在初始化的时候不可以设置null  

  nest_tab nest_tab_type := nest_tab_type('lubinsu', 'luzhou'); --初始化的时候只要在集合变量之后使用空的构造函数或者直接赋值即可  

BEGIN  

  FOR i IN nest_tab.first .. nest_tab.last LOOP  

    dbms_output.put_line('nest_tab(' || i || ') value is ' || nest_tab(i));  

  END LOOP;  

END;  

/  

 

--3.嵌套表和的初始化2

DECLARE  

  TYPE nest_tab_type IS TABLE OF VARCHAR2(2000) NOT NULL; --如果设置not null条件那么在初始化的时候不可以设置null  

  nest_tab nest_tab_type := nest_tab_type(); --初始化的时候只要在集合变量之后使用空的构造函数或者直接赋值即可  

BEGIN  

  nest_tab.extend;  

  nest_tab(1) := 'lubinsu';  

  nest_tab.extend;  

  nest_tab(2) := 'luzhou';  

  FOR i IN nest_tab.first .. nest_tab.last LOOP  

    dbms_output.put_line('nest_tab(' || i || '):' || nest_tab(i));  

  END LOOP;  

END;  

/  

--如果设置not null条件那么在初始化的时候不可以设置null,如：nest_tab(1) := null;否则出错提示；  

ORA-06550: line 7, column 18:  

PLS-00382: expression is of wrong type  

ORA-06550: line 7, column 3:  

PL/SQL: Statement ignored  

--赋值的时候必须使用extend来扩展集合的容量否则会如下错误  

ERROR at line 1:    

ora-06533: subscript beyond count    

ora-06512: at line 6  

/  

 

--4.变长数组类似于PL/SQL表，每个元素都被分配了一个连续的下标，从1开始

--4.变长数组的初始化(与嵌套表的初始化方式一样)

DECLARE  

  TYPE varray_tab_type IS VARRAY(10) OF VARCHAR2(2000);  

  varray_tab varray_tab_type :=  varray_tab_type('lubinsu', 'luzhou'); --初始化的时候只要在集合变量之后使用空的构造函数或者直接赋值即可  

BEGIN  

  varray_tab.extend;  

  varray_tab(3) := 'zengq';  

  varray_tab.extend;  

  varray_tab(4) := 'buwei';  

  FOR i IN varray_tab.first .. varray_tab.last LOOP  

    dbms_output.put_line('varray_tab(' || i || '):' || varray_tab(i));  

  END LOOP;  

END;  

/  

 

--5.集合与集合之间的赋值必须是相同的TYPE

DECLARE  

  TYPE type1 IS TABLE OF NUMBER(2);  

  TYPE type2 IS TABLE OF NUMBER(2);  

  type1_tab  type1 := type1(1, 2, 3);  

  type1_tab2 type1 := type1(4, 5, 6);  

  type2_tab  type2 := type2(3, 2, 1);  

BEGIN  

  type1_tab2 := type1_tab;  

  --type1_tab2 := type2_tab; 不可用  

  FOR i IN type1_tab2.first .. type1_tab2.last LOOP  

    dbms_output.put_line('type1_tab2(' || i || '):' || type1_tab2(i));  

  END LOOP;  

END;  

/  

--type1_tab2 := type2_tab;报错  

ORA-06550: line 10, column 17:  

PLS-00382: expression is of wrong type  

ORA-06550: line 10, column 3:  

PL/SQL: Statement ignored  

  

RESULT:  

type1_tab2(1):1  

type1_tab2(2):2  

type1_tab2(3):3  

/  

 

--6.使用null值为集合赋值

DECLARE  

  TYPE type1 IS TABLE OF NUMBER(2);  

  type1_tab  type1 := type1();--已经初始化，不为空，虽然没有赋值  

  type1_tab2 type1;--未初始化，为空  

BEGIN  

  IF type1_tab IS NOT NULL THEN  

    dbms_output.put_line('type1_tab is not null');  

  END IF;  

    

  --type1_tab := NULL;  

  --或者  

  type1_tab := type1_tab2;  

    

  IF type1_tab IS NULL THEN  

    dbms_output.put_line('type1_tab is null');  

  END IF;  

END;  

/  

 

--7.超出变长数组长度的值将会被丢弃  www.2cto.com  

--8.记录类型的嵌套表的初始化，赋值以及元素的引用

DECLARE  

  TYPE object_rec IS RECORD(  

    object_id   all_objects_loc.object_id%TYPE,  

    object_name all_objects_loc.object_name%TYPE,  

    object_type all_objects_loc.object_type%TYPE);  

  

  TYPE object_tab_type IS TABLE OF object_rec;  

  

  object_tab object_tab_type;  

  

  TYPE obj_cur_type IS REF CURSOR; --声明游标变量类型  

  obj_cur obj_cur_type;  

BEGIN  

  OPEN obj_cur FOR  

    SELECT a.object_id, a.object_name, a.object_type  

    FROM   all_objects_loc a  

    WHERE  rownum <= 10;  

  

  FETCH obj_cur BULK COLLECT  

    INTO object_tab;  

  CLOSE obj_cur;  

  FOR i IN 1 .. object_tab.count LOOP  

    dbms_output.put_line('object_tab(' || i || '):' || object_tab(i)  

                         .object_id || ',' || object_tab(i).object_name || ',' || object_tab(i)  

                         .object_type);  

  END LOOP;  

END;  

/  

原文：http://blog.csdn.net/leshami/article/details/7525891

原文：http://www.2cto.com/database/201301/184529.html

 

 

 

=========================================================================================================

 

 

版权声明：本文为博主原创文章，未经博主允许不得转载。

个人理解，table函数是把数组类型的变量展开显示出来

三种用法:

 1 table()与返回值为数组的类型的普通函数一起使用

 2 table()与返回值为数组类型的管道函数一起使用

3, table()与系统包中的管道函数一起使用 实际也就是2

以下代码为例子：

--创建类型t_test
SQL> create or replace type t_test as object (
  2  id number,
  3  time date,
  4  data varchar2(60)
  5  );
  6  /

Type created

Executed in 0.063 seconds
 --创建以t_test类型的数组t_test_tb
SQL> create or replace type t_test_tb as table of t_test;
  2  /

Type created

Executed in 0.031 seconds

--1 创建普通函数 返回数组类型，用数组的实例存放结果集(t_array),结果集全部处理完成后一起返回
SQL> create or replace function f_test_array(n in number default null) return t_test_tb as
  2    t_array t_test_tb := t_test_tb();
  3  begin
  4    for i in 1..nvl(n,100) loop
  5        t_array.extend();
  6        t_array(t_array.count) := t_test(i,sysdate,'mi'||i);
  7    end loop;
  8    return t_array;
  9  end;
 10  /

Function created

Executed in 0.046 seconds
 --直接select是不能显示的
SQL> select f_test_array(10) from dual;

F_TEST_ARRAY(10)
----------------
<Object>

Executed in 0.062 seconds
 --对于返回类型是数组的那么用table()函数或者the
SQL> select * from table(f_test_array(10));

        ID TIME        DATA
---------- ----------- ------------------------------------------------------------
         1 2010-8-10 1 mi1
         2 2010-8-10 1 mi2
         3 2010-8-10 1 mi3
         4 2010-8-10 1 mi4
         5 2010-8-10 1 mi5
         6 2010-8-10 1 mi6
         7 2010-8-10 1 mi7
         8 2010-8-10 1 mi8
         9 2010-8-10 1 mi9
        10 2010-8-10 1 mi10

10 rows selected

Executed in 0.187 seconds

SQL> select * from the(select f_test_array(10) from dual);

        ID TIME        DATA
---------- ----------- ------------------------------------------------------------
         1 2010-8-10 1 mi1
         2 2010-8-10 1 mi2
         3 2010-8-10 1 mi3
         4 2010-8-10 1 mi4
         5 2010-8-10 1 mi5
         6 2010-8-10 1 mi6
         7 2010-8-10 1 mi7
         8 2010-8-10 1 mi8
         9 2010-8-10 1 mi9
        10 2010-8-10 1 mi10

10 rows selected

Executed in 0.172 seconds
 --2 用返回类型为数组的管道函数，这里没用用到存放中间结果的变量，每处理完一条记录那么立刻返回结果
SQL> create or replace function f_test_pipe(n in number default null) return t_test_tb pipelined
  2  as
  3  begin
  4         for i in 1..nvl(n,100) loop
  5             pipe row(t_test(i,sysdate,'mi'||i));
  6         end loop;
  7         return;
  8  end;
  9  /

Function created

Executed in 0.031 seconds

SQL> select f_test_pipe(10) from dual;

F_TEST_PIPE(10)
---------------
<Object>

Executed in 0.062 seconds

SQL> select * from table(f_test_pipe(10));

        ID TIME        DATA
---------- ----------- ------------------------------------------------------------
         1 2010-8-10 1 mi1
         2 2010-8-10 1 mi2
         3 2010-8-10 1 mi3
         4 2010-8-10 1 mi4
         5 2010-8-10 1 mi5
         6 2010-8-10 1 mi6
         7 2010-8-10 1 mi7
         8 2010-8-10 1 mi8
         9 2010-8-10 1 mi9
        10 2010-8-10 1 mi10

10 rows selected

Executed in 0.156 seconds

SQL> select * from the(select f_test_pipe(10) from dual);

        ID TIME        DATA
---------- ----------- ------------------------------------------------------------
         1 2010-8-10 1 mi1
         2 2010-8-10 1 mi2
         3 2010-8-10 1 mi3
         4 2010-8-10 1 mi4
         5 2010-8-10 1 mi5
         6 2010-8-10 1 mi6
         7 2010-8-10 1 mi7
         8 2010-8-10 1 mi8
         9 2010-8-10 1 mi9
        10 2010-8-10 1 mi10

10 rows selected

Executed in 0.172 seconds

SQL> drop table test;

Table dropped

Executed in 0.047 seconds
 --3 table调用包中返回为数组类型的函数 dbms_xplan.display 是返回类型为数组的管道函数
SQL> create table test(id number, name varchar2(30));

Table created

Executed in 0.047 seconds

SQL> insert into test values(1,'d');

1 row inserted

Executed in 0 seconds

SQL> commit;

Commit complete

Executed in 0.016 seconds

SQL> explain plan for select * from test;

Explained

Executed in 0 seconds

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    30 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 |    30 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

12 rows selected

Executed in 0.187 seconds