Array in PL/SQL

普通的数组

语法：TYPE year_type IS TABLE OF number INDEX BY BINARY_INTEGER;

The above single statement containsa lot of meaning. First of all, we aresimply defining our own data type,“year_type.”Make sure that “year_type” is not a variable. It is a user defined data type. According to the above statement, “year_type” is a data type which can hold a set (or table) of values (typically of type “number”),organized with a BINARY_INTEGER index. The BINARY_INTEGER in this scenario simply acts as a location number or position of the memory location within the table (or simply called an index).

The word "table" here has nothing to do with database tables, confusingly. The methods create in-memory arrays

Sample 1:数组的游标可以是任何数字，数组的长度随时可以增加

declare
      type year_type is table of number index by binary_integer;
      year_sales year_type;
      tot_sales   number;
begin
      year_sales(1990) := 34000;
      year_sales(1991) := 45000;
      year_sales(1992) := 43000;

      tot_sales := year_sales(1990) + year_sales(1991) +
year_sales(1992);
      dbms_output.put_line('Total sales: ' || tot_sales);
end;

Sample 2:数组一般用在循环中

declare
      type year_type is table of number index by binary_integer;
      year_sales year_type;
      tot_sales   number := 0;
      i           number;
begin
      year_sales(1990) := 34000;
      year_sales(1991) := 45000;
      year_sales(1992) := 43000;
      year_sales(1993) := 13000;
      year_sales(1994) := 53000;

      for i in 1990..1994
      loop
            tot_sales := tot_sales + year_sales(i);
            dbms_output.put_line('Sales of ' || i || ': ' ||
year_sales(i));
      end loop;
      dbms_output.put_line('Total sales: ' || tot_sales);

end;

Sample 3: 判断下数组中是否存在这个游标值

declare
      type year_type is table of number index by binary_integer;
      year_sales year_type;
      tot_sales   number := 0;
      i           number;
begin
      year_sales(1990) := 34000;
      year_sales(1991) := 45000;
      year_sales(1992) := 43000;
      year_sales(1996) := 13000;
      year_sales(1998) := 53000;

      for i in 1990..2000
      loop
            if year_sales.exists(i) then
                  tot_sales := tot_sales + year_sales(i);
                  dbms_output.put_line('Sales of ' || i || ': '
|| year_sales(i));
            end if;
      end loop;
      dbms_output.put_line('Total sales: ' || tot_sales);

end;

VARRAY（动态数组）

If we know the data size of data that we are operate we can use VARRAYs that are lenght fixed, this is Oracle environment so the subscripts start from 1,Alternative is using VARRAY, where array subscript starts from 1 and the length of VARRAYs is fixed.
语法：TYPE VarrayType is VARRAY(size) of ElementType;

* A varray stores an ordered set of elements.
* Each element has an index associated with it.
* A varray has a maximum size that you can change dynamically.

You create a varray type using the SQL DDL CREATE TYPE statement.You specify the maximum size and the type of elements stored in the varray when creating the

The basic Oracle syntax for the CREATE TYPE statement for a VARRAY type definition would be:CREATE OR REPLACE TYPE name-of-type IS VARRAY(nn)of type

Where name-of-type is a valid attribute name, nn is the number of elements (maximum) in the array, and type is the data type of the elements of the array.
You can change the maximum size of a varray using the ALTER TYPE statement.

SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
  2  /
Type created.
SQL>
SQL> desc addressVarray;
 addressVarray VARRAY(2) OF VARCHAR2(50)

SQL>

Sample 1:

example

    declare
      type NumberVarray is varray(100) of NUMERIC(10);
      myArray NumberVarray;
    BEGIN
      myArray := NumberVarray(1,10,100,1000,10000);

      myArray(1) = 2;

      for i in myArray.first..myArray.last
      loop
        DBMS_OUTPUT.put_line('myArray('||i||') :'||myArray(i));
      end loop;  
    end;
END;

OUTPUT:

myArray(1) : 2
myArray(2) : 10
myArray(3) : 100
myArray(4) : 1000
myArray(5) : 10000

Sample 2:

declare
   type array_t is varray(3) of varchar2(10);
   array array_t := array_t('Matt', 'Joanne', 'Robert');
begin
   for i in 1..array.count loop
       dbms_output.put_line(array(i));
   end loop;
end;

More VARRAY ARRAY Example Refer:http://www.java2s.com/Tutorial/Oracle/0520__Collections/CreatingaVarrayType.htm