Re: Multi-dimensional Array

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Fri, 21 Mar 2003 18:56:10 GMT
Message-ID: <3E7B6418.9060100@adelphia.net>

M wrote:
> pl/sql table is only single-dimension. for example,
>
> declare
> type score_type is table of number(3) index by binary_integer;
> my_score score_type;
> begin
> for a in 1 .. 100 loop
> my_score(a) := a;
> end loop;
> end;
>
> Then I can assign values to my_score(a), a single-dimension array. Just
> want to know if there is something like, e.g. 2-dimensional arrays like
> my_score(a, b) ... or even multi-dimensional.
>
> M.
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.com> wrote in message
> news:U1lda.141918$S_4.82850_at_rwcrnsc53...
>

>>How about using pl/sql tables?
>>Jim
>>"M" <someone_at_microsoft.com> wrote in message
>>news:b53ej3$2144$1_at_news.hgc.com.hk...
>>
>>>Hi all,
>>>
>>>Just want to know in Oracle 8i, can I define some multi-dimensional
>>

> array
>

>>to
>>
>>>store data for analysis purpose.  Can this kind of array be define on
>>

> the
>

>>>fly, instead of some objects in the database?
>>>
>>>Thx.
>>>M.
>>>
>>>
>>
>>
>>

>
>

A google search for "two dimensional array" brings up an article I've posted before. But here we go again, you can simulate a two dimensional array with a simple mapping function. It's usually what your compiler does when you use a multidimensional array in languages that support it. So no need for nested types and tables.

• INDEX2D
• GIVEN THE INDICES IN A 2-DIMENSIONAL ARRAY.
• THIS RETURNS A UNIQUE INDEX IN A LINEAR ARRAY.

  ---

• Ed Prochak
• Magic Interface, Ltd.
• 440-498-3702

  ---

  CREATE OR REPLACE FUNCTION INDEX2D ( A INTEGER, B INTEGER ) RETURN INTEGER AS K INTEGER; N INTEGER; NDX INTEGER; BEGIN K := A+B-1; N := K*(K+1); N := N/2; NDX := N+1-B; RETURN NDX; END; /

The above routine assumes the first entry in the table is location (1,1) in the 2-D array and location 1 in the linear array. It also assumes use of only positive integers. If you prefer your 2-D array to start at location (0,0), then simply add 1 to both parameters before calling this routine OR adjust the body as follows:
  BEGIN

    K := A+B+1;
    N := K*(K+1);
    N := N/2;

    NDX := N-B;
    RETURN NDX;
  END; Feature: This is an unlimited 2-D array, well suited for use with PL/SQL tables. So don't forget to check that the entry exists at a given location before retrieving it.

So you use this within you PL/SQL code for example:

DECLARE
    TYPE EJP_TABLE IS TABLE OF VARCHAR2(30)                INDEX BY BINARY_INTEGER;
    LYNEAR EJP_TABLE;
BEGIN

    LYNEAR(2):='TWO';  --- IT REALLY IS A LINEAR ARRAY
    LYNEAR(INDEX2D(1,2)):='xxx';  --- but INDEX2D allows you to pretend it's 2D
    LYNEAR(INDEX2D(2,2)):='YYY';
    LYNEAR(INDEX2D(3,5)):='ZZZ';  --- GREAT FOR SPARSE MATRICES TOO.

    DBMS_OUTPUT.PUT_LINE('@(2) = '|| LYNEAR(2) );     DBMS_OUTPUT.PUT_LINE('@(1,2)) = '||LYNEAR(INDEX2D(1,2))||

                   ' @'||INDEX2D(1,2) );
    DBMS_OUTPUT.PUT_LINE('@(2,2)) = '||LYNEAR(INDEX2D(2,2))||
                   ' @'||INDEX2D(2,2) );
    DBMS_OUTPUT.PUT_LINE('@(3,5)) = '||LYNEAR(INDEX2D(3,5))||
                   ' @'||INDEX2D(3,5) );

END;
/

There is an obvious limit on using this function, ie you have to use it whenever you use the array.

Not a built in feature of PL/SQL, but very flexible none-the-less.

It can be extended to multiple demensions. You just need to do the math.

HTH
  Ed

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Fri Mar 21 2003 - 12:56:10 CST