Oracle array Learning

From: http://blog.csdn.net/inthirties/archive/2009/08/03/4400905.aspx

 

A friend on csdn asks for a code segment for help.

The code snippet is as follows:

Declare
Type t_indexby is table of number
Index by binary_integer;
Type t_nesteed 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 simple sample code about arrays in Oracle. Looking at this section of the representative code, you can't help but think of the kind of passion that you used to explore the array type in Oracle alone.

This code is also good. The main array types are listed in simple examples. Good materials can't help but stimulate my desire to answer and write, so I don't have to worry about it, by the way, I forgot to fill the gaps in my previous summary.

This code shows how to use arrays in Oracle.

1. index by table

2. nested table

3. varray variable array

Here is an introduction to the three array types in the Oracle document.

An index-by table is the most flexible and generally best-known Ming collection type for use inside PL/SQL programs.

A nested table is appropriate for large collections that an application stores and retrieves in portions.

A varray is appropriate for small collections that the application stores and retrieves in their entirety.

Here is a generalization of the three of them through applicability. It seems that it has not directly affected us, or let us first understand it, what is embodied in the applicability here is a good understanding of NLP.

The sample code starts with 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 (10) of number; -- varray

The last and last sentences are obviously different. No length is defined, whereas varray defines the limit length. Varray has a length limit. If the length is exceeded, an out-of-bounds error is returned. Indexed by table and nested table obviously do not have this restriction, but they are also different for indexed by table and nested table.

The latter part of the sample above describes the differences between the two. For index by table, the index type has been specified here, each element is identified directly by using index-type variables without the need to expand the size. This function is somewhat like map in Java (the difference is that keys are ordered here), while nested table can be completely the same as list.

Let's take a look at the sample

V_indexby (1): = 1;
V_indexby (2): = 2;

Here, two elements are added to v_indexby: 1, 1, and 2. Note that (1), (2) are different from those in the nested table varray,

Here, I understand it as a key, not the sequence number of the element. So the index by subscript here is not necessarily continuous. It can be jumped, but the other two are different. The other two are actually array objects, and the subscript represents the sequence number of the element, different from Java, it starts from 1.

V_nested: = t_nested (1, 2, 3, 4, 5 );
V_varray: = t_varray (1, 2 );

Here, we define an array of five and two elements.

V_nested: = t_nested (1, 2, 3, 4, 5); 5 elements, values: 1, 2, 3, 4, 5

V_varray: = t_varray (); the values of the two elements are 1, 2.

It should be emphasized that for the nested table, extend should be used to extend the array. When adding elements, varrray does not need to (the length is known and is defined ).

V_nested.extend; v_nested (v_nested.count): = 6;

You can see their differences here. The structure of index by table is essentially different from that of nested table and varray, so it is bound to be different in use. The index by table subscript is not a serial number, so we can only access it through the key. This is the same as Java.

The above example is not provided, and I have found a lot of introductions on the Internet, but I have not provided a detailed method to traverse the index by table. Here I wrote a sample, for your 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'; -- it is a key, so it can be a negative number.

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;

/

Result

A-1

A1

A2

Note that the first method returns the first key, next (), and the next key.

For the nested table, the subscript is the serial number and cannot be a negative number.

Declare

Type t_array is table of varchar2 (1000 );

V_array t_array;

V_idx varchar2 (1000 );

Begin

V_array: = t_array (); --- you must first initialize it.

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;

/

The access to the nested tabled can also be the same as the above, of course, you can also use a simpler method to traverse

For I in 1 .. v_array.count Loop

Dbms_output.put (I | '');

Dbms_output.put_line (v_array (I ));

End loop;

In fact, we can see that the access to the List in Java is very similar.

This knowledge point has been written by many people. There are not a lot of content, but you need to accumulate little knowledge. In this article, we advise you to learn how to "do not accumulate steps, do not even thousands of miles, do not accumulate small streams, and do not build rivers and seas ", this is true for learning, and Oracle learning should be more true. I am still idle today. "You will pay back the game sooner or later ".

Oracle official introduction to index by ARRAY

Http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#insertedID8

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/inthirties/archive/2009/08/03/4400905.aspx