• 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记录 

    一、联合数组的赋值

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

    [sql] view plain copy 在CODE上查看代码片派生到我的代码片
    1. DECLARE    
    2.    TYPE idx_loc_type IS TABLE OF VARCHAR2( 13 )    
    3.                            INDEX BY BINARY_INTEGER;    
    4.     
    5.    loc_tab   idx_loc_type;    
    6. BEGIN    
    7.    loc_tab( 1 ) := 'NEW YORK';                        -->联合数组不需要初始化，直接赋值即可    
    8.    loc_tab( 2 ) := 'DALLAS';    
    9.    DBMS_OUTPUT.put_line( ' loc_tab(1) value is ' || loc_tab( 1 ) );    
    10.    DBMS_OUTPUT.put_line( ' loc_tab(2) value is ' || loc_tab( 2 ) );    
    11. END;    
    12.     
    13. --------------------------------------------------------------------------------------------------------     
    14.     
    15. DECLARE    
    16.    TYPE idx_loc_type IS TABLE OF VARCHAR2( 13 )    
    17.                            INDEX BY BINARY_INTEGER;    
    18.     
    19.    loc_tab     idx_loc_type;    
    20.    v_counter   INTEGER := 0;    
    21. BEGIN    
    22.    FOR x IN ( SELECT loc FROM dept )       -->这里通过for 循环得到loc的值    
    23.    LOOP    
    24.       v_counter   := v_counter + 1;        -->使用一个v_counter变量来控制联合数组的下标    
    25.       loc_tab( v_counter ) := x.loc;       -->将得到的loc的值赋值给联合数组中对应的一个下标位    
    26.       DBMS_OUTPUT.put_line( ' loc_tab(' || v_counter || ') value is ' || loc_tab( v_counter ) );    
    27.    END LOOP;    
    28. 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为函数或过程。

       

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

    [sql] view plain copy 在CODE上查看代码片派生到我的代码片
    1. 1、未初始化集合的情形    
    2. DECLARE    
    3.    TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );    
    4.     
    5.    loc_tab   nest_loc_type;    
    6. BEGIN    
    7.    loc_tab( 1 ) := 'NEW YORK';    
    8.    loc_tab( 2 ) := 'DALLAS';    
    9.    DBMS_OUTPUT.put_line( ' loc_tab(1) value is ' || loc_tab( 1 ) );    
    10.    DBMS_OUTPUT.put_line( ' loc_tab(2) value is ' || loc_tab( 2 ) );    
    11. END;    
    12.     
    13. DECLARE    
    14. *    
    15. ERROR at line 1:    
    16. ora-06531: Reference to uninitialized collection  -->收到了ora-06531错误提示，变长数组未初始化时会收到同样的错误提示    
    17. ora-06512: at line 6    
    18. --------------------------------------------------------------------------------------------------------    
    19.     
    20. 2、集合为NULL的判断    
    21. DECLARE    
    22.    TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );    
    23.     
    24.    loc_tab   nest_loc_type;    
    25. BEGIN    
    26.    IF loc_tab IS NULL THEN    
    27.       DBMS_OUTPUT.put_line( 'Before initialization, the loc_tab is null.' );    
    28.    -- While the collection  is null, we cannot check its COUNT attribute.    
    29.    --   DBMS_OUTPUT.PUT_LINE('It has ' || loc_tab.COUNT || ' elements.');    
    30.    ELSE    
    31.       DBMS_OUTPUT.put_line( 'Before initialization, the loc_tab is not null.' );    
    32.    END IF;    
    33.     
    34.    loc_tab     := nest_loc_type( );         --> initialize empty nest table    
    35.     
    36.    IF loc_tab IS NULL THEN    
    37.       DBMS_OUTPUT.put_line( 'After initialization, the loc_tab is null.' );    
    38.    ELSE    
    39.       DBMS_OUTPUT.put_line( 'After initialization, the loc_tab is not null.' );    
    40.       DBMS_OUTPUT.put_line( 'It has ' || loc_tab.COUNT || ' elements.' );    
    41.    END IF;    
    42. END;    
    43.     
    44. Before initialization, the loc_tab is null.    
    45. After initialization, the loc_tab is not null.    
    46. It has 0 elements.    
    47.     
    48. PL/SQL procedure successfully completed.    
    49. --------------------------------------------------------------------------------------------------------    
    50.     
    51. 3、使用空构造函数在声明时进行初始化    
    52. -->使用该方法初始化之后，表明嵌套表或变成数组是空的，但是非NULL，在执行块再对其赋值    
    53. -->下面对变长数组进行初始化    
    54. DECLARE    
    55.    TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;    
    56.     
    57.    varry_loc_tab   varry_loc_type := varry_loc_type( );                 -->仅仅是在集合变量之后使用空构造函数    
    58. BEGIN    
    59.    varry_loc_tab( 1 ) := 'NEW YORK';                                    -->尽管变长数组被初始化，但仍然不能直接赋值            
    60.    varry_loc_tab( 2 ) := 'DALLAS';                                      -->这是由变长数组和嵌套表特性决定需要先做extend       
    61.    DBMS_OUTPUT.put_line( ' varry_loc_tab(1) value is ' || varry_loc_tab( 1 ) );    
    62.    DBMS_OUTPUT.put_line( ' varry_loc_tab(2) value is ' || varry_loc_tab( 2 ) );    
    63. END;    
    64.     
    65. DECLARE    
    66. *    
    67. ERROR at line 1:    
    68. ora-06533: subscript beyond count    
    69. ora-06512: at line 6    
    70. --------------------------------------------------------------------------------------------------------    
    71.     
    72. 4、使用空构造函数在声明时进行初始化，执行块使用extend方式扩展后赋值    
    73. DECLARE    
    74.    TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;    
    75.     
    76.    varry_loc_tab   varry_loc_type := varry_loc_type( );              -->仅仅是在集合变量之后使用空构造函数    
    77. BEGIN    
    78.    varry_loc_tab.EXTEND;                                             -->需要使用extend方式扩展集合容量    
    79.    varry_loc_tab( 1 ) := 'NEW YORK';    
    80.    varry_loc_tab.EXTEND;                                             -->需要使用extend方式扩展集合容量    
    81.    varry_loc_tab( 2 ) := 'DALLAS';    
    82.    DBMS_OUTPUT.put_line( ' varry_loc_tab(1) value is ' || varry_loc_tab( 1 ) );    
    83.    DBMS_OUTPUT.put_line( ' varry_loc_tab(2) value is ' || varry_loc_tab( 2 ) );    
    84. END;    
    85. --------------------------------------------------------------------------------------------------------    
    86.     
    87. 5、嵌套表的初始化,使用构造函数在执行块直接初始化并赋值    
    88. DECLARE    
    89.    TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );    
    90.     
    91.    loc_tab   nest_loc_type;    
    92. BEGIN    
    93.    loc_tab     :=    
    94.       nest_loc_type( 'NEW YORK'           -->使用声明时的类型nest_loc_type函数来进行初始化    
    95.                     ,'DALLAS'    
    96.                     ,'CHICAGO'    
    97.                     ,'BOSTON' );    
    98.     
    99.    FOR i IN 1 .. loc_tab.COUNT    
    100.    LOOP    
    101.       DBMS_OUTPUT.put_line( 'loc_tab(' || i || ') value is ' || loc_tab( i ) );    
    102.    END LOOP;    
    103. END;    
    104. --------------------------------------------------------------------------------------------------------    
    105.     
    106. 6、含有NOT NULL嵌套表的初始化    
    107. DECLARE    
    108.    TYPE loc_type IS TABLE OF VARCHAR2( 13 ) NOT NULL;      -->定义了NOT NULL约束条件    
    109.     
    110.    loc_tab   loc_type;    
    111. BEGIN    
    112.    loc_tab     :=    
    113.       loc_type( 'NEW york'    
    114.                ,NULL                                       -->构造时传递了NULL值    
    115.                ,NULL    
    116.                ,'boston' );    
    117.     
    118.    FOR i IN 1 .. loc_tab.COUNT    
    119.    LOOP    
    120.       DBMS_OUTPUT.put_line( 'loc_tab(' || i || ') value is ' || loc_tab( i ) );    
    121.    END LOOP;    
    122. END;    
    123.     
    124. -->由于存在not null约束，初始化传递null值则收到错误提示    
    125. ERROR at line 8:    
    126. ora-06550: line 8, column 17:    
    127. pls-00567: cannot pass NULL to a NOT NULL constrained formal parameter    
    128. ora-06550: line 9, column 17:    
    129. pls-00567: cannot pass NULL to a NOT NULL constrained formal parameter    
    130. ora-06550: line 6, column 4:    
    131. pl/SQL: Statement ignored    
    132. --------------------------------------------------------------------------------------------------------    
    133.     
    134. 7、变长数组的初始化,使用构造函数直接初始化并赋值    
    135. -->变长数组的初始化与嵌套表一样，可以使用构造函数直接初始化并赋值    
    136. DECLARE    
    137.    TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;    
    138.     
    139.    varry_loc_tab   varry_loc_type;    
    140. BEGIN    
    141.    varry_loc_tab :=    
    142.       varry_loc_type( 'NEW YORK'    
    143.                      ,'DALLAS'    
    144.                      ,'CHICAGO'    
    145.                      ,'BOSTON' );    
    146.     
    147.    FOR i IN varry_loc_tab.FIRST .. varry_loc_tab.LAST   -->注意此处使用了集合方法中的函数first和last来控制循环步长    
    148.    LOOP    
    149.       DBMS_OUTPUT.put_line( 'varry_loc_tab(' || i || ') value is ' || varry_loc_tab( i ) );    
    150.    END LOOP;    
    151. END;    
    152. --------------------------------------------------------------------------------------------------------    
    153.     
    154. 8、声明时初始化(构造)、并赋值    
    155. DECLARE    
    156.    TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 ) NOT NULL;    
    157.     
    158.    nest_loc_tab   nest_loc_type := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' ); -->在声明时直接初始化并赋值    
    159. BEGIN    
    160.    FOR i IN 1 .. nest_loc_tab.COUNT    -->注意此处调用了集合操作方法中的count函数    
    161.    LOOP    
    162.       DBMS_OUTPUT.put_line( 'nest_loc_tab(' || i || ') value is ' || nest_loc_tab( i ) );    
    163.    END LOOP;    
    164. END;    
    165. --------------------------------------------------------------------------------------------------------    
    166.     
    167. 9、SQL语句中使用构造函数    
    168. CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 );   -->创建一个嵌套表类型    
    169.     
    170. CREATE TABLE tb_tmp                   -->创建表tb_tmp    
    171. (    
    172.    empno   NUMBER( 4 )    
    173.   ,ename   VARCHAR2( 10 )    
    174.   ,mail    mail_type                  -->列mail的类型为mail_type    
    175. )    
    176. NESTED TABLE mail                     -->注意此处需要指定嵌套表的存储方式    
    177.    STORE AS mail_tab;    
    178.     
    179. INSERT INTO tb_tmp    
    180.    SELECT 8888, 'Jack', mail_type( 'Jack@yahoo.com', 'Jack@163.com' ) FROM dual;  -->插入数据时需要使用构造函数    
    181.     
    182. 10、集合与集合之间的赋值    
    183. -->下面的例子声明了两个变长数组last_name_type和surname_type    
    184. DECLARE    
    185.    TYPE last_name_type IS VARRAY( 3 ) OF VARCHAR2( 64 );    
    186.     
    187.    TYPE surname_type IS VARRAY( 3 ) OF VARCHAR2( 64 );    
    188.     
    189.    -->下面声明了两个相同类型的变长数组并为其赋值，group1和group2使用了相同的构造函数    
    190.    group1   last_name_type := last_name_type( 'Jones', 'Wong', 'Marceau' );    
    191.    group2   last_name_type := last_name_type( 'Klein', 'Patsos', 'Singh' );    
    192.    -->下面的group3使用了surname_type作为类型    
    193.    group3   surname_type := surname_type( 'Trevisi', 'Macleod', 'Marquez' );    
    194. BEGIN    
    195.    group1      := group2;                           -- >group1 和group2之间可以相互赋值    
    196. --   group3 := group2;                             -->raises an error    PLS-00382: expression is of wrong type    
    197. END;    
    198. -- >group3和group2则不能赋值，因为两者为不同的数据类型    
    199. -->尽管last_name_type与surname_type类型定义是相同的，但其实例化后，其集合变量不能互相赋值    
    200. --------------------------------------------------------------------------------------------------------    
    201.     
    202. 11、使用NULL值集合为集合赋值    
    203. DECLARE    
    204.    TYPE nest_loc_type IS TABLE OF VARCHAR2( 30 );    
    205.     
    206.    nest_loc_tab         nest_loc_type := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' );    
    207.    empty_nest_loc_tab   nest_loc_type;                --> 嵌套表empty_nest_loc_tab没有初始化，此时被自动置为NULL    
    208. BEGIN    
    209.    IF nest_loc_tab IS NOT NULL THEN    
    210.       DBMS_OUTPUT.put_line( 'OK, at first nest_loc_tab is not null.' );    
    211.    END IF;    
    212.     
    213.    nest_loc_tab := empty_nest_loc_tab;                -->将empty_nest_loc_tab的值(NULL)嵌套表赋值给nest_loc_tab    
    214.     
    215.    IF nest_loc_tab IS NULL THEN                       --> 此时nest_loc_tab被置为NULL，相当于没有初始化    
    216.       DBMS_OUTPUT.put_line( 'OK, now nest_loc_tab has become null.' );    
    217.    END IF;    
    218.     
    219.    nest_loc_tab := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' );  -->此时如果后续需要使用该嵌套表，应重新初始化它    
    220. END;    
    221.     
    222. 12、记录类型的变长数组的初始化、赋值与元素引用    
    223. DECLARE    
    224.    TYPE emp_name_rec IS RECORD                             -->声明一个基于用户定义的记录类型    
    225.    (    
    226.       firstname   employees.first_name%TYPE    
    227.      ,lastname    employees.last_name%TYPE    
    228.      ,hiredate    employees.hire_date%TYPE    
    229.    );    
    230.     
    231.    TYPE emplist_arr IS VARRAY( 10 ) OF emp_name_rec;       -->声明一个基于记录的变长数组，且最大尺寸为10    
    232.     
    233.    seniorsalespeople   emplist_arr;                        -->声明基于记录的变长数组变量    
    234.     
    235.    CURSOR c1 IS                                            -->声明游标，其列前面的记录类型相对照    
    236.       SELECT first_name, last_name, hire_date FROM employees;    
    237.     
    238.    TYPE nameset IS TABLE OF c1%ROWTYPE;                    -->声明基于游标的记录类型    
    239.     
    240.    seniorten           nameset;                            -->声明基于游标记录类型的变量    
    241.    endcounter          NUMBER := 10;                       -->变量endcounter计数器    
    242. BEGIN    
    243.    seniorsalespeople := emplist_arr( );                    -->初始化集合    
    244.     
    245.    SELECT first_name, last_name, hire_date                 -->从表中提取数据，且使用了BULK COLLECT INTO方式    
    246.    BULK   COLLECT INTO seniorten    
    247.    FROM   employees    
    248.    WHERE  job_id = 'SA_REP'    
    249.    ORDER BY hire_date;    
    250.     
    251.    IF seniorten.LAST > 0 THEN    
    252.       IF seniorten.LAST < 10 THEN                          -->如果小于10，则缩小变长数组的最大尺寸    
    253.          endcounter  := seniorten.LAST;    
    254.       END IF;    
    255.     
    256.       FOR i IN 1 .. endcounter     -->使用循环将游标类型变量中的元素逐条赋值给记录的变长数组变量seniorsalespeople并输出    
    257.       LOOP    
    258.          seniorsalespeople.EXTEND( 1 );    
    259.          seniorsalespeople( i ) := seniorten( i );    
    260.          DBMS_OUTPUT.    
    261.          put_line(seniorsalespeople(i).lastname||', '||seniorsalespeople(i).firstname||', '||seniorsalespeople(i).hiredate);    
    262.       END LOOP;    
    263.    END IF;    
    264. END;    
    265.     
    266.     上面的这个例子是一复合的数据类型，比单一的集合类型更为复杂。我们知道集合通常是针对单列多行数据而言，而记录则是单行多列。两    
    267. 者的综合，则此时就等同于数据库中的一张二维表。示例中首先声明用户定义的记录类型以及变长数组，接下来基于这两者声明变量。后面使用    
    268. 基于游标的记录类型来申明变量seniorten与前面的变量seniorsalespeople相对应，seniorten变量用于存储后面的SQL语句批量提取的数据集。    
    269. 后面使用了一个for循环来从seniorten变量取出数据并赋值为seniorsalespeople。注：在这个例子中变量seniorten存储的记录超出了变长数组    
    270. 的最大尺寸，因此后续的被丢弃。    
    271.     
    272. 13、记录类型的嵌套表的初始化、赋值与元素引用    
    273. DECLARE    
    274.    TYPE emp_name_rec IS RECORD    
    275.    (    
    276.       firstname   employees.first_name%TYPE    
    277.      ,lastname    employees.last_name%TYPE    
    278.      ,hiredate    employees.hire_date%TYPE    
    279.    );    
    280.     
    281.    TYPE emplist_tab IS TABLE OF emp_name_rec;    
    282.     
    283.    seniorsalespeople   emplist_tab;    
    284.    endcounter          NUMBER := 10;    
    285.     
    286.    TYPE empcurtyp IS REF CURSOR;    -->声明游标变量类型     
    287.     
    288.    emp_cv              empcurtyp;   -->声明游标变量类型的变量emp_cv    
    289. BEGIN    
    290.    OPEN emp_cv FOR    
    291.       SELECT first_name, last_name, hire_date    
    292.       FROM   employees    
    293.       WHERE  job_id = 'SA_REP'    
    294.       ORDER BY hire_date;    
    295.     
    296.    FETCH emp_cv    
    297.    BULK   COLLECT INTO seniorsalespeople;    -->使用BULK   COLLECT INTO 方式一次将数据加载到seniorsalespeople变量    
    298.     
    299.    CLOSE emp_cv;    
    300.     
    301.    IF seniorsalespeople.LAST > 0 THEN    
    302.       IF seniorsalespeople.LAST < 10 THEN    
    303.          endcounter  := seniorsalespeople.LAST;    
    304.       END IF;    
    305.     
    306.       FOR i IN 1 .. endcounter    
    307.       LOOP    
    308.          DBMS_OUTPUT.    
    309.          put_line(seniorsalespeople(i).lastname||', '||seniorsalespeople(i).firstname||', '||seniorsalespeople(i).hiredate);    
    310.       END LOOP;    
    311.    END IF;    
    312. END;    
    313. -->Author : Robinson Cheng    
    314. -->Blog   : http://blog.csdn.net/robinson_0612    
    315.     
    316.     上面的这个例子稍有不同于前面的例子，使用的基于用户定义记录的嵌套表方式，且使用了游标变量类型。在fetch时直接将数据fetch 到    
    317. 集合变量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

    以下代码为例子：

    [c-sharp] view plain copy
    1. --创建类型t_test  
    2. SQL> create or replace type t_test as object (  
    3.   2  id number,  
    4.   3  time date,  
    5.   4  data varchar2(60)  
    6.   5  );  
    7.   6  /  
    8.    
    9. Type created  
    10.    
    11. Executed in 0.063 seconds  
    12.  --创建以t_test类型的数组t_test_tb  
    13. SQL> create or replace type t_test_tb as table of t_test;  
    14.   2  /  
    15.    
    16. Type created  
    17.    
    18. Executed in 0.031 seconds  
    19.    
    20. --1 创建普通函数 返回数组类型，用数组的实例存放结果集(t_array),结果集全部处理完成后一起返回  
    21. SQL> create or replace function f_test_array(n in number default null) return t_test_tb as  
    22.   2    t_array t_test_tb := t_test_tb();  
    23.   3  begin  
    24.   4    for i in 1..nvl(n,100) loop  
    25.   5        t_array.extend();  
    26.   6        t_array(t_array.count) := t_test(i,sysdate,'mi'||i);  
    27.   7    end loop;  
    28.   8    return t_array;  
    29.   9  end;  
    30.  10  /  
    31.    
    32. Function created  
    33.    
    34. Executed in 0.046 seconds  
    35.  --直接select是不能显示的  
    36. SQL> select f_test_array(10) from dual;  
    37.    
    38. F_TEST_ARRAY(10)  
    39. ----------------  
    40. <Object>  
    41.    
    42. Executed in 0.062 seconds  
    43.  --对于返回类型是数组的那么用table()函数或者the  
    44. SQL> select * from table(f_test_array(10));  
    45.    
    46.         ID TIME        DATA  
    47. ---------- ----------- ------------------------------------------------------------  
    48.          1 2010-8-10 1 mi1  
    49.          2 2010-8-10 1 mi2  
    50.          3 2010-8-10 1 mi3  
    51.          4 2010-8-10 1 mi4  
    52.          5 2010-8-10 1 mi5  
    53.          6 2010-8-10 1 mi6  
    54.          7 2010-8-10 1 mi7  
    55.          8 2010-8-10 1 mi8  
    56.          9 2010-8-10 1 mi9  
    57.         10 2010-8-10 1 mi10  
    58.    
    59. 10 rows selected  
    60.    
    61. Executed in 0.187 seconds  
    62.    
    63. SQL> select * from the(select f_test_array(10) from dual);  
    64.    
    65.         ID TIME        DATA  
    66. ---------- ----------- ------------------------------------------------------------  
    67.          1 2010-8-10 1 mi1  
    68.          2 2010-8-10 1 mi2  
    69.          3 2010-8-10 1 mi3  
    70.          4 2010-8-10 1 mi4  
    71.          5 2010-8-10 1 mi5  
    72.          6 2010-8-10 1 mi6  
    73.          7 2010-8-10 1 mi7  
    74.          8 2010-8-10 1 mi8  
    75.          9 2010-8-10 1 mi9  
    76.         10 2010-8-10 1 mi10  
    77.    
    78. 10 rows selected  
    79.    
    80. Executed in 0.172 seconds  
    81.  --2 用返回类型为数组的管道函数，这里没用用到存放中间结果的变量，每处理完一条记录那么立刻返回结果  
    82. SQL> create or replace function f_test_pipe(n in number default null) return t_test_tb pipelined  
    83.   2  as  
    84.   3  begin  
    85.   4         for i in 1..nvl(n,100) loop  
    86.   5             pipe row(t_test(i,sysdate,'mi'||i));  
    87.   6         end loop;  
    88.   7         return;  
    89.   8  end;  
    90.   9  /  
    91.    
    92. Function created  
    93.    
    94. Executed in 0.031 seconds  
    95.    
    96. SQL> select f_test_pipe(10) from dual;  
    97.    
    98. F_TEST_PIPE(10)  
    99. ---------------  
    100. <Object>  
    101.    
    102. Executed in 0.062 seconds  
    103.    
    104. SQL> select * from table(f_test_pipe(10));  
    105.    
    106.         ID TIME        DATA  
    107. ---------- ----------- ------------------------------------------------------------  
    108.          1 2010-8-10 1 mi1  
    109.          2 2010-8-10 1 mi2  
    110.          3 2010-8-10 1 mi3  
    111.          4 2010-8-10 1 mi4  
    112.          5 2010-8-10 1 mi5  
    113.          6 2010-8-10 1 mi6  
    114.          7 2010-8-10 1 mi7  
    115.          8 2010-8-10 1 mi8  
    116.          9 2010-8-10 1 mi9  
    117.         10 2010-8-10 1 mi10  
    118.    
    119. 10 rows selected  
    120.    
    121. Executed in 0.156 seconds  
    122.    
    123. SQL> select * from the(select f_test_pipe(10) from dual);  
    124.    
    125.         ID TIME        DATA  
    126. ---------- ----------- ------------------------------------------------------------  
    127.          1 2010-8-10 1 mi1  
    128.          2 2010-8-10 1 mi2  
    129.          3 2010-8-10 1 mi3  
    130.          4 2010-8-10 1 mi4  
    131.          5 2010-8-10 1 mi5  
    132.          6 2010-8-10 1 mi6  
    133.          7 2010-8-10 1 mi7  
    134.          8 2010-8-10 1 mi8  
    135.          9 2010-8-10 1 mi9  
    136.         10 2010-8-10 1 mi10  
    137.    
    138. 10 rows selected  
    139.    
    140. Executed in 0.172 seconds  
    141.    
    142. SQL> drop table test;  
    143.    
    144. Table dropped  
    145.    
    146. Executed in 0.047 seconds  
    147.  --3 table调用包中返回为数组类型的函数 dbms_xplan.display 是返回类型为数组的管道函数  
    148. SQL> create table test(id number, name varchar2(30));  
    149.    
    150. Table created  
    151.    
    152. Executed in 0.047 seconds  
    153.    
    154. SQL> insert into test values(1,'d');  
    155.    
    156. 1 row inserted  
    157.    
    158. Executed in 0 seconds  
    159.    
    160. SQL> commit;  
    161.    
    162. Commit complete  
    163.    
    164. Executed in 0.016 seconds  
    165.    
    166. SQL> explain plan for select * from test;  
    167.    
    168. Explained  
    169.    
    170. Executed in 0 seconds  
    171.    
    172. SQL> select * from table(dbms_xplan.display);  
    173.    
    174. PLAN_TABLE_OUTPUT  
    175. --------------------------------------------------------------------------------  
    176. Plan hash value: 1357081020  
    177. --------------------------------------------------------------------------  
    178. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    179. --------------------------------------------------------------------------  
    180. |   0 | SELECT STATEMENT  |      |     1 |    30 |     3   (0)| 00:00:01 |  
    181. |   1 |  TABLE ACCESS FULL| TEST |     1 |    30 |     3   (0)| 00:00:01 |  
    182. --------------------------------------------------------------------------  
    183. Note  
    184. -----  
    185.    - dynamic sampling used for this statement  
    186.    
    187. 12 rows selected  
    188.    
    189. Executed in 0.187 seconds  
    190.    
     
  • 相关阅读:
    python2 与python3 区别的总结 持续更新中......
    基础数据类型初识（三）字典
    基础数据类型初识（二）列表，元组
    基本数据类型初识（一）数字，字符串
    python基础知识（理论）
    进程池 和 管道 ， 进程之间的 信息共享
    并发编程
    进程 和 多进程
    计算机系统的发展史
    网络编程 黏包
  • 原文地址：https://www.cnblogs.com/aipan/p/6516163.html
Copyright © 2020-2023  润新知