Oracle之数组

以前没有遇到过数组这种类型，最近遇到了需要数组才能解决问题，找了一下关于数组的用法，现整理下来。

 

Oracle中的数组分为固定数组和可变数组。

一、固定数组

   固定数组：在定义的时候预定义了数组的大小，在初始化数组时如果超出这个大小，会提示ORA-06532:超出小标超出限制！

  语法：

     TYPE arry_var IS VARRAY(2) OF VARCHAR2(10);--定义了一个存放2个char类型的数组

  例：

    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; 

二、可变数组

 可变数组：在数组中可以有任意数量的元素，元素的类型要预先定义，数组的下标可以设置为自增.

  1、一维数组

  1）.下标为自增长，table 表示可变长度，index by bybinary_integer子句代表以符号整数为索引

   语法：

   TYPE t_table IS TABLE OF VARCHAR2(30) INDEX BYBINARY_INTEGER; 

    例：

    DECLARE 
    TYPE t_table IS TABLE OF VARCHAR2(30) INDEX BYBINARY_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  3   9 ，此处的1 3 9是以字符型存放在数组中

注：Oracle中的数组下标是从1开始的，下标必须是连续的。

  2、多维数组

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

  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);--此处只用到了xxuser表中的两个字段

     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; --BULK COLLECT表示将批量查询数据直接插入collection中，而不是通过cursur一条条插入；在现在的程序中大部分实现是通过游标插入的。
     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;