PL/SQL 联合数组与嵌套表_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2513 | 回复: 0   主题: PL/SQL 联合数组与嵌套表        下一篇 
ab19890824
注册用户
等级:少尉
经验:351
发帖:92
精华:0
注册:2011-11-21
状态:离线
发送短消息息给ab19890824 加好友    发送短消息息给ab19890824 发消息
发表于: IP:您无权察看 2015-7-21 15:54:25 | [全部帖] [楼主帖] 楼主

      通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。单列多行数据
则由联合数组或嵌套表来完成,其特点是类似于单列数据库表。在Oracle 9i 之前称为PL/SQL索引表,9i 之后称之为联合数组。嵌套表也是集合
类型中的一种,下面分别介绍这两种集合数据类型的使用方法。

一、联合数组

    1、联合数组的特性  
        类似于一张简单的SQL表,按照主键进行检索数据  
        其数据行并不是按照预定义的顺序存储。当使用变量来检索其数据时,每行数据会分配一个连续的下标且从1开始。  
        下标可以为负值,且下标的数据类型支持binary_integer,pls_integer,varchar2  
        其数据元素个数无限制  
        不能作为表列的数据类型使用,即只能作为PL/SQL复合数据类型使用  
        存放的数据类型是临时数据,故不支持insert,selectinto 等SQL语句,等同于SQL server中的表变量  

    2、语法          

     TYPE type_name ISTABLEOF element_type [NOTNULL]  -->element_type为联合数组元数据指定数据类型(先使用TYPE声明表结构)
    INDEXBY key_type;                                  -->元素下标的数据类型
    table_name TYPE_NAME;                               -->再使用声明的TYPE类型声明实际数组名


    3、示例  

    --使用PLS_INTEGE类型联合数组
    scott@CNMMBO> DECLARE

    -->定义游标       
    2     CURSOR name_cur IS
    3        SELECT dname
    4        FROM   dept
    5        WHERE  deptno < 40;
    6
    7     TYPE name_type ISTABLEOF dept.dname%TYPE      -->定义一个联合数组类型,且其下标的数据类型为PLS_INTEGER
    8                          INDEXBY PLS_INTEGER;
    9
    10     dname_tab   name_type;                          -->声明类型为name_type的联合数组dname_tab


    11     v_counter   INTEGER := 0;
    12  BEGIN
    13     FOR name_rec IN name_cur
    14     LOOP
    15        v_counter   :=
    16           v_counter
    17           + 1;
    18        dname_tab( v_counter ) := name_rec.dname;      -->对联合数据进行循环赋值


    19        DBMS_OUTPUT.put_line(   'Dname is('
    20                               v_counter
    21                               '):'
    22                               dname_tab( v_counter ) );   -->输出联合数组中的所有元素


    23     END LOOP;
    24  END;
    25  /
    Dname is(1):ACCOUNTING
    Dname is(2):RESEARCH
    Dname is(3):SALES
    PL/SQL procedure successfully completed.
    --使用VARCHAR2类型联合数组


    scott@CNMMBO> DECLARE
    2     TYPE score_type ISTABLEOF NUMBER
    3                           INDEXBY VARCHAR2( 10 );    -->注意此处声明的联合数组的下标数据类型为varchar2
    4
    5     score_tab   score_type;
    6  BEGIN
    7     score_tab( 'SCOTT' ) := 95;                       -->对不同下标分别进行赋值
    8     score_tab( 'JOHN' ) := 98;
    9     score_tab( 'ROBINSON' ) := 96;
    10     DBMS_OUTPUT.put_line( 'First element is '
    11                            score_tab.FIRST );        -->输出联合数组score_tab中的第一个元素的下标
    12     DBMS_OUTPUT.put_line( 'last element is '
    13                            score_tab.LAST );         -->输出联合数组score_tab中的最后一个元素的下标
    14     DBMS_OUTPUT.put_line( 'The score of Scott is '
    15                            score_tab( 'SCOTT' ) );   -->输出下标为SCOTT的元素的值
    16  END;
    17  /
    First element is JOHN                                     -->注意此处输出的是JOHN(第一个和最后一个是按字母排序得到的)
    last element is SCOTT
    The score of Scott is 95
    PL/SQL procedure successfully completed.
    --下标超范围的情况


    scott@CNMMBO> DECLARE
    2     TYPE score_type ISTABLEOF NUMBER
    3                           INDEXBY VARCHAR2( 10 );
    4
    5     score_tab   score_type;
    6  BEGIN
    7     score_tab( 'SCOTT' ) := 95;
    8     score_tab( 'JOHN' ) := 98;
    9     score_tab( 'ROBINSON' ) := 96;
    10     DBMS_OUTPUT.put_line( 'The score of Jason is  '
    11                            score_tab( 'JASON' ) );
    12  END;
    13  /
    DECLARE
    *
    ERROR at line 1:
    ORA-01403: no data found                              -->当下标超范围时,将收到ORA-01403错误
    ORA-06512: at line 10


    二、嵌套表

      1、嵌套表的特点:  
          元素下表从1开始,个数没有限制.即元素个数可以动态增长  
          嵌套表的数组元素值可以是稀疏的,即可以使得中间的某个元素没有赋值  
          嵌套表的语法与联合数组类似,不同的是仅仅是少了indexby子句     
          嵌套表必需先初始化,然后才能引用其中的元素。如果初始化为空值,则后续需要使用extend来扩展其大小    
          嵌套表初始化时为密集的,但允许有间隙,即允许使用内置过程delete从嵌套表中删除元素  
          嵌套表类型可以作为表列的数据类型来使用  

      2、语法  

       TYPE type_name ISTABLEOF element_type [NOTNULL];   -->element_type为索引表元数据指定数据类型(先使用TYPE声明表结构) 
      table_name TYPE_NAME;                                 -->再使用声明的TYPE类型声明实际嵌套表       


      3、示例  

      --声明嵌套表并输出嵌套表的实际内容          


      scott@CNMMBO> DECLARE
      2     CURSOR name_cur IS
      3        SELECT dname
      4        FROM   dept
      5        WHERE  deptno < 40;
      6
      7     TYPE name_type ISTABLEOF dept.dname%TYPE;
      8
      9     dname_tab   name_type := name_type( );   -->需要初始化,否则将收到 ORA-06531:Reference to uninitialized collection.


      10     v_counter   INTEGER := 0;
      11  BEGIN
      12     FOR name_rec IN name_cur
      13     LOOP
      14        v_counter   :=
      15           v_counter
      16           + 1;
      17        dname_tab.EXTEND;         -->需要扩展,否则将收到 ORA-06533: Subscript beyond count


      18        dname_tab( v_counter ) := name_rec.dname;
      19        DBMS_OUTPUT.put_line(   'Dname ('
      20                               v_counter
      21                               ') is :'
      22                               dname_tab( v_counter ) );
      23     END LOOP;
      24  END;
      25  /
      Dname (1) is :ACCOUNTING
      Dname (2) is :RESEARCH
      Dname (3) is :SALES
      PL/SQL procedure successfully completed.
      --将嵌套表作为表列的数据类型来使用


      scott@CNMMBO> CREATEORREPLACE TYPE mail_type ISTABLEOF VARCHAR2( 100 );
      2  /
      Type created.
      scott@CNMMBO> CREATETABLE tb_tmp       

      -->创建表tb_tmp
      2  (
      3     empno   NUMBER( 4 )
      4   , ename   VARCHAR2( 10 )
      5   , mail    mail_type               -->列mail的类型为mail_type


      6  )
      7  NESTED TABLE mail                 -->注意此处需要指定嵌套表的存储方式
      8     STORE AS mail_tab;
      Table created.
      --为嵌套表插入数据
      scott@CNMMBO> insertinto tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com'from dual;  

      -->传统方式插入失败
      insertinto tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com'from dual
      *
      ERROR at line 1:
      ORA-00932: inconsistent datatypes: expected - got CHAR
      scott@CNMMBO> 

      insertinto tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']'from dual;  

      -->分割字符串方式插入失败
      insertinto tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']'from dual
      *
      ERROR at line 1:
      ORA-00932: inconsistent datatypes: expected - got CHAR
      scott@CNMMBO> 

      insertinto tb_tmp select 8888,'Jack',mail_type('Jack@yahoo.com','Jack@163.com'from dual; 

      -->插入时指定嵌套表类型 
      1 row created.
      scott@CNMMBO> commit;
      Commit complete.
      --查看存在嵌套表数据类型的表中的记录


      scott@CNMMBO> col mail format a30
      scott@CNMMBO> select * from tb_tmp;                    

      -->查询数据时带有嵌套表的类型
      EMPNO ENAME      MAIL
      ---------- ---------- ------------------------------
      8888 Jack       MAIL_TYPE('Jack@yahoo.com', 'J
      ack@163.com')
      scott@CNMMBO> DECLARE
      2     mail_tab   mail_type;    -->声明一个mail_type数据类型


      3  BEGIN
      4     SELECT mail
      5     INTO   mail_tab          -->将数据保存到 mail_tab 变量中
      6     FROM   tb_tmp
      7     WHERE  empno = 8888;
      8
      9     FOR i IN 1 .. mail_tab.COUNT-->轮循输出嵌套表类型中的值
      10     LOOP
      11        DBMS_OUTPUT.put_line( 'Jack mail address is '
      12                               mail_tab( i ) );
      13     END LOOP;
      14  END;
      15  /
      Jack mail address is Jack@yahoo.com
      Jack mail address is Jack@163.com
      PL/SQL procedure successfully completed.
      --更新表中含有嵌套表类型中的值


      scott@CNMMBO> DECLARE
      2     mail_tab   mail_type := mail_type( 'Jackson@yahoo.com', 'Jackson@163.com' );
      3  BEGIN
      4     UPDATE tb_tmp
      5     SET    mail         = mail_tab;
      6
      7     COMMIT;
      8     DBMS_OUTPUT.put_line( 'Jack mail address was updated' );
      9  END;
      10  /
      Jack mail address was updated
      PL/SQL procedure successfully completed.
      scott@CNMMBO> select * from tb_tmp;
      EMPNO ENAME      MAIL
      ---------- ---------- ------------------------------
      8888 Jack       MAIL_TYPE('Jackson@yahoo.com',
      'Jackson@163.com')
      --删除表中存在嵌套表类型的记录


      scott@CNMMBO> deletefrom tb_tmp where empno=8888;
      1 row deleted.
      scott@CNMMBO> commit;
      Commit complete.
      scott@CNMMBO> select * from tb_tmp;
      norows selected


      三、总结
      1、不论是联合数组还是嵌套表,应当以集合的方式来看待与处理
      2、联合数组在声明其类型时需要指定index by子句,而嵌套表则不需要
      3、联合数组和嵌套表两者元素个数无限制    
      4、联合数组不需要初始化,而嵌套表则需要对其进行初始化。其次是嵌套表初始化赋空值的情形则后续需要使用extend方式来扩展集合尺寸的大小
      5、联合数组不能作为表上列的数据类型,而嵌套表无此限制

      --转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论