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;