PL/SQL变长数组

原创 2012年03月23日 15:47:29

  PL/SQL变长数组时PL/SQL集合数据类型中的一种,其使用方法与PL/SQL嵌套表大同小异,唯一的区别则是变长数组的元素的最大个数是有限
制的。也即是说变长数组的下标固定下限等于1,上限可以扩展。下面给出具体的描述及其使用方法。

 

一、变长数组语法
        TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF  -->type_name 用于指定varray类型名,size_limit 定义varray元素的最大个数
        element_type [NOT NULL];                                    -->element_type用于指定元素的数据类型 
        varray_name TYPE_NAME;                                      -->varray_name 用于定义varray变量

 

二、变长数组特性
          变长数组主要的特性即是元素的最大个数是有限制
          变长数组下标固定为1,上限可以扩展
          与嵌套表类似,在变长数组声明时自动设置为NULL值.所谓的空值指的是集合本身是空,不是针对它所拥有的元素
          故在元素引用前需要对其进行初始化

 

三、变长数组示例

--1、声明变长数组,并输出其结果
scott@CNMMBO> DECLARE
  2     CURSOR name_cur IS
  3        SELECT dname
  4        FROM   dept
  5        WHERE  deptno < 40;
  6  
  7     TYPE name_type IS VARRAY( 10 ) OF dept.dname%TYPE;  -->声明一个包含10个元素的变长数组,且且数据类型为dept.dname类型
  8  
  9     varray_dname_tab   name_type := name_type( );       -->初始化变长数组
 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        varray_dname_tab.EXTEND;                          -->使用extend进行扩展 
 18        varray_dname_tab( v_counter ) := name_rec.dname;  -->按下标输出变长数组的所有元素
 19        DBMS_OUTPUT.put_line(   'Dname ('
 20                             || v_counter
 21                             || ') is :'
 22                             || varray_dname_tab( v_counter ) );
 23     END LOOP;
 24  END;
 25  /
Dname (1) is :ACCOUNTING
Dname (2) is :RESEARCH
Dname (3) is :SALES		

--2、对变长数组直接赋初值,且使用count遍历并输出所有元素		
scott@CNMMBO> DECLARE
  2     TYPE name_type IS VARRAY( 2 ) OF VARCHAR2( 10 );
  3  
  4     varray_name_tab   name_type := name_type( 'Robinson', 'Jackson' ); -->此处对varray_name_tab初始化并赋初值
  5  BEGIN
  6     FOR i IN 1 .. varray_name_tab.COUNT                -->使用count来遍历并输出变长数组的所有元素
  7     LOOP
  8        DBMS_OUTPUT.put_line(   'Name varray_name_tab( '
  9                             || i
 10                             || ' ) is : '
 11                             || varray_name_tab( i ) );
 12     END LOOP;
 13  END;
 14  /
Name varray_name_tab( 1 ) is : Robinson
Name varray_name_tab( 2 ) is : Jackson

PL/SQL procedure successfully completed.

--3、超出变长数组大小的情形
scott@CNMMBO> DECLARE
  2     TYPE name_type IS VARRAY( 2 ) OF VARCHAR2( 10 );
  3  
  4     varray_name_tab   name_type := name_type( 'Robinson', 'Jackson' );
  5  BEGIN
  6     FOR i IN 1 .. varray_name_tab.COUNT
  7     LOOP
  8        DBMS_OUTPUT.put_line(   'Name varray_name_tab( '
  9                             || i
 10                             || ' ) is : '
 11                             || varray_name_tab( i ) );
 12     END LOOP;
 13  
 14     varray_name_tab.EXTEND;
 15     varray_name_tab( 3 ) := 'Johnson';
 16     DBMS_OUTPUT.put_line( 'Name varray_name_tab (3) is '
 17                          || varray_name_tab( 3 ) );
 18  END;
 19  /
Name varray_name_tab( 1 ) is : Robinson
Name varray_name_tab( 2 ) is : Jackson
DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 14

--4、存储变长数组到数据库及修改变长数组
scott@CNMMBO> CREATE OR REPLACE TYPE varray_phone IS VARRAY( 2 ) OF VARCHAR2( 40 ); -->创建变长数组类型
  2  /

Type created.

scott@CNMMBO> CREATE TABLE tb_emp        -->创建表tb_emp且其中一列使用到了变长数组
  2  (
  3     empno   NUMBER( 4 )
  4   , ename   VARCHAR2( 10 )
  5   , phone   varray_phone            -->列phone使用到了变长数组
  6  )
  7  ;

Table created.

--插入新记录到变长数组
scott@CNMMBO> insert into tb_emp select 6666,'Robinson',varray_phone('13423456789','075520123650') from dual;

1 row created.

scott@CNMMBO> insert into tb_emp select 7777,'Jackson',varray_phone('13423456789','075520123650') from dual;

1 row created.

scott@CNMMBO> commit;

Commit complete.

scott@CNMMBO> col phone format a25
scott@CNMMBO> select * from tb_emp;   -->查看插入的记录

     EMPNO ENAME      PHONE
---------- ---------- -------------------------
      6666 Robinson   VARRAY_PHONE('13423456789
                      ', '075520123650')

      7777 Jackson    VARRAY_PHONE('13423456789
                      ', '075520123650')

-->插入变长数组包含3个元素的记录,此时提示超出了变长数组范围
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')        
  2  from dual;
insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')
                                         *
ERROR at line 1:
ORA-22909: exceeded maximum VARRAY limit

-->修改变长数组长度范围限制到3,且使用cascade,即及联修改到表tb_emp
scott@CNMMBO> alter type varray_phone modify limit 3 cascade;  

Type altered.

-->再次插入数据成功
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')
  2  from dual;

1 row created.

-->插入变长数组元素超出预定义字符串长度时收到错误提示
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')  
  2  from dual;
insert into tb_emp select 8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')
                                                      *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

-->修改变长数组元素的数据长度到varchar2(60),且使用cascade,即及联修改到表tb_emp
scott@CNMMBO> alter type varray_phone modify element type varchar2(60) cascade;

Type altered.

-->再次插入数据成功
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000') 
  2  from dual;

1 row created.

-->查看变长数组的定义
scott@CNMMBO> select parent_table_name,parent_table_column,type_name from user_varrays;

PARENT_TABLE_NA PARENT_TABLE_COLUMN            TYPE_NAME
--------------- ------------------------------ ------------------------------
TB_EMP          PHONE                          VARRAY_PHONE

四、更多参考

启用用户进程跟踪

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

PL/SQL 联合数组与嵌套表


 

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。

相关文章推荐

Oracle数组使用以及PLSQL操作记录

----------------------------固定长度数组  declare  type intarray is varray(30) of varchar2(100);  var_a...

oracle存储过程中如何使用数组(附范例)

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

PL/SQL 集合 -- 多维数组

-- Start 可变数组之多维数组。 DECLARE -- 定义一个可变数组类型 TYPE_TEST1,它的最大容量是2,元素类型是 INT TYPE TYPE_TEST1 IS VAR...

PLSQL学习(二) 数组专题

PLSQL学习(二) 数组专题       PLSQL中提供了三种数据的形式,功能还是比较强大的。但是总的来说,PLSQL中的数组知识掌握最简单的那种,其他大致了解就可以了。因为从实际应用的角...

PL/SQL变长数组

PL/SQL变长数组时PL/SQL集合数据类型中的一种,其使用方法与PL/SQL嵌套表大同小异,唯一的区别则是变长数组的元素的最大个数是有限 制的。也即是说变长数组的下标固定下限等于1,上限可以扩展...

只能在PL/SQL中访问联合数组。

  • 2012年02月29日 22:10
  • 13KB
  • 下载

ORACLE(PL/SQL) 根据字符分割(SPLIT)字符串返回数组

ORACLE没有提供现成的函数来进行字符串的分割,网上有很多中现实方法,本文介绍一种我自己的实现方式。主要是使用Oracle内部函数instr和substr函数的方式来实现。...

PL/SQL记录和表(oracle type(数组))(最简单!文章下面标黄标题是精华,必看)

PL/SQL记录和表 1.使用%TYPE  (用于基本数据类型) 例1(用户并不知道Course_No的数据类型,只知道他是基于Student_No数据类型的,随着Student_No的类型变化而...

PL/SQL 联合数组与嵌套表

通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。单列多行数据 则由联合数组或嵌套表来完成,其特点是类似于单列数据库表。在Or...

PL/SQL记录和表(oracle type(数组))(最简单!文章下面标黄标题是精华,必看)

PL/SQL记录和表 1.使用%TYPE  (用于基本数据类型) 例1(用户并不知道Course_No的数据类型,只知道他是基于Student_No数据类型的,随着Student_No的类型变化而...
  • cph18
  • cph18
  • 2014年09月03日 16:54
  • 227
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:PL/SQL变长数组
举报原因:
原因补充:

(最多只允许输入30个字)