oracle 存储数组,oracle存储过程中的数组

之前遇到过一个老兄，问我是否会写存储过程，我自问也写过数百个存储过程，也对不少过程进行优化，尽管没有什么系统的理论知识，就回答说，比较熟，这老兄就问我有没有用过数组，说实话，用过，但是不太记得了，用的比较少，不是很熟，这个老兄说：写存储过程如果没有用过数组，就和没写过存储过程差不多。

虽然不赞同这老兄的说法，不过还是觉得应该研究一下的。这个老兄的意思是说，你可以把结果集放在一个结构数组中，这样子在内存里面访问起来比较快。我觉得应该是一样子的，因为都是在内存里面，即使是游标，因为存储过程都是在oracle的SGA里面的。

下面是一段网上的说明，觉得虽然例子看起来复杂了点但是还是很清晰的说明了数组的应用。[@more@]

type index_table is table of varchar2(2000) index by binary_integer;

type nested_table is table of varchar2(2000);

type v_arrary is varray(1000) of varchar2(2000);

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

A PL/SQL table is a single column array. The PL/SQL table is indexed by a binary integer counter so you can reference any value stored in a PL/SQL table in an indexed fashion. The number of rows in a PL/SQL table is dynamic, it can be increased as desired.

PL/SQL tables are declared in two steps. First, the table type is declared, then the PL/SQL table variable is assigned to that table type.

The PL/SQL declarations can occur in any block of a pl/sql construct. A PL/SQL table cannot be initialized in its declaration. The first reference to a PL/SQL table must be an assignment statement or a NO_DATA_FOUND will result. PL/SQL tables follow the same scope rules as other variables and cease to exist when the package, function or procedure is exited.

Example:

In this example, we declare a table type of NUMBER and then assign two tables, results and n10. Notice how the indexes are used in each to specify a specific value.

CREATE OR REPLACE FUNCTION max_value RETURN NUMBER IS

TYPE numtab IS TABLE OF NUMBER

INDEX BY BINARY_INTEGER;

results numtab;

n10 numtab;

max_loop NUMBER;

temp_store NUMBER;

CURSOR get_results IS

SELECT result FROM result_table;

i integer;

x integer:=1;

j integer:=1;

BEGIN

OPEN get_results;

FOR i IN 1..1500 LOOP

FETCH get_results INTO temp_store;

IF i = MOD(x,15) THEN

results(j):=temp_store;

END IF;

END LOOP;

j:=1;

FOR i IN 1..10 LOOP

n10(i):=GREATEST(results(j),results(j+1),results(j+2),

results(j+3),results(j+4),results(j+6),

results(j+7),results(j+7),results(j+8),

results(j+9));

j:=j+10;

END LOOP;

j:=1

max_loop:=GREATEST(n10(j),n10(j+1),n10(j+2),

n10(j+3),n10(j+4),n10(j+5),n10(j+6),n10(j+7),

n10(j+8),n10(j+9);

RETURN max_loop;

END;