An array of Oracle

declare
type t_indexby is table of number
index by binary_integer;
type t_nested is table of number;
type t_varray is varray(10) of number;
v_indexby t_indexby;
v_nested t_nested;
v_varray t_varray;
begin
v_indexby(1):=1;
v_indexby(2):=2;
v_nested:=t_nested(1,2,3,4,5);
v_varray:=t_varray(1,2);
end; 

A very simple sample code for an array in Oracle. Looking at this piece of representative code, it is not the kind of passion that reminds me of the type of array in Oracle that I used to be alone in trying to figure out.

This code is also good, through a simple example of the main array types are listed out of the, good material is not inspired by and I answered the desire to write, so also not hesitate to refer to the force, summed up a few, also by the way to fill the vacancy I have previously forgotten summary
This code, acquisition. How arrays are used in Oracle
1. Index by Table
2. Nested table
3. Varray variable Array
Here is an introduction to these three types of arrays in the Oracle documentation
An index-by table was the most flexible and generally best-performing collection type for use inside PL/SQL programs.
A nested table is appropriate for large collections, A application stores and retrieves in portions.
A Varray is appropriate for small collections, the application stores and retrieves in their entirety.
Here is the application of the three of them summed up, as if not to give us too direct impact, or let us first understand it, here the application of the things embodied in the good understanding.
Sample code starts with a definition of three different types.
Type T_indexby is table of number index by Binary_integer; --Indexed by table
Type T_nesteed is table of number; --Nested table
Type T_varray is Varray (ten) of number; --Varray
The last two sentences are distinctly different from the latter, without defining the length, while the Varray defines the length of the LAT. Varray has a length limit and the access is more than the length of the words will prompt out of bounds error. Indexed by table and nested table obviously do not have this limitation, but they are also different for indexed by table and nested table.
The latter part of the above sample describes the difference between the two, for index by table, where the type of index has been specified, and the index type of the variable is indexed directly to identify each element, without the need for an extended size. This function is somewhat like the map in Java (there is a difference between the key is in order), and nested table can be exactly the same as the list
Let's look through sample.
V_indexby (1): = 1;
V_indexby (2): = 2;
Here in V_indexby Riga two elements, for 1, 1 and 2, 2, note here (1), (2) and back nested table has been varray,
Here, I understand it as key, not the ordinal of the element. So index by the subscript here, not necessarily continuous, can jump, and the other two are different, the other is a veritable array of objects, the subscript is the element is the ordinal, and Java, starting from 1.
V_nested:=t_nested (1,2,3,4,5);
(V_varray:=t_varray);
Here is an array of 5 and 2 elements defined respectively.
V_nested:=t_nested (1,2,3,4,5); 5 Elements with a value of 1,2,3,4,5
(V_varray:=t_varray); 2 elements with a value of
Emphasize that, for nested table, you need to use extend to extend the array, add elements, and varrray do not need to (already know the length, define the time).
V_nested.extend; V_nested (v_nested.count): = 6;
Everyone here basically can see their differences, index by table in the structure and nested table and Varray are essentially different, then bound to use the time must be different. Because the index by table subscript is not an ordinal, we can only access it via key, which is the same as Java.
The above example, did not provide, and I found on the Internet a lot of introduction did not give a detailed index by table traversal method, here I wrote a sample, for everyone to learn reference

declare
type t_array is table of varchar2(1000) index by binary_integer;
v_array t_array;
v_idx number;
begin
v_array(1) := ‘a1‘;
v_array(2) := ‘a2‘;
v_array(-1) := ‘a-1‘; -- Is a key so it can be negative
v_idx := v_array.first;
loop
exit when v_idx is null;
dbms_output.put_line(v_array(v_idx));
v_idx := v_array.next(v_idx);
end loop;
end;
/ 

Results A-1 A1 A2 Note here the method, first, returns the Key,next () returns the next key for nested table, the subscript is the ordinal, is not negative

Declare
type T_array is Table of VARCHAR2,
v_array t_array;
v_idx varchar2 (n); 
Begin
v_array: = T_array (); ---Be sure to start the conversation first.
V_array.extend; ----Extended array 
V_array (1): = ' A1 '; 
V_array.extend;
v_array (2): = ' A2 '; 
V_array.extend;
v_array (3): = ' A-1 '; ----V_array ( -1): = ' A-1 '; Error 
V_IDX: = v_array.first;
loop
dbms_output.put (v_idx| | ') 
Exit when V_idx is Null;
dbms_output.put_line (V_array (V_IDX)); 
V_idx: = V_array.next (V_IDX); 
end loop;
end;
/

See nested tabled's access can also be the same as above, of course, you can also use an easier way to traverse

You can actually see that the access to the list in Java is very similar.
This knowledge point, many people have written sample, the content is not many, but the small knowledge also to accumulate, an exhortation "not accumulate kuibu, no even thousands of miles, do not accumulate small stream, not to become Jianghai", learning is certainly so, and Oracle's learning should be so. Before the lazy, I still today is still in Dalat. "Go to the lake, sooner or later to also".

--------------------------------------------------------------------------------------------------------------- --------

Method describes usage restrictions
Count returns the number of elements in the collection
Delete Deletes all elements in the collection
Delete () deletes the element labeled X under the element, and if x is null, the collection remains unchanged to varray illegal
Delete (,) removes the element subscript from X to Y if the X>y collection remains unchanged to varray illegal
EXIST () returns True if the collection element x is already initialized, otherwise false
EXTEND add an element to index_by illegal at the end of the collection
EXTEND () Adding X elements to index_by illegal at the end of a collection
EXTEND (,) adds an x copy of element n to the end of the collection index_by illegal
First returns the lower label of the second element in the collection, and always returns 1 for the Varray collection.
Last returns the lower label of the final element in the collection, which is always equal to count for the Varray return value.
LIMIT returns the maximum number of elements for the Varry collection, which is useless for nested tables and NULL index_by collections for nested tables and index_by
NEXT () Returns the value of the element immediately after the element x and next to it, or null if the element is the last element.
PRIOR () Returns the value of the element in the collection that precedes its element x, and returns null if the element is the first element.
TRI M Deleting an element from the end of the collection is illegal for index_by
TRIM () deleting x elements from the end of the collection is illegal for index_by

Array of Oracle