Re: Multi-dimensional Array
Date: Fri, 21 Mar 2003 18:56:10 GMT
Message-ID: <3E7B6418.9060100_at_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. >>> >>> >> >> >>
>
>
[Quoted] A google search for "two dimensional array" brings up an article I've posted [Quoted] before. But here we go again, you can simulate a two dimensional array with a [Quoted] simple mapping function. It's usually what your compiler does when you use a [Quoted] 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 [Quoted]
- 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; /
[Quoted] 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; [Quoted] 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.
[Quoted] 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
[Quoted] LYNEAR(2):='TWO'; --- IT REALLY IS A LINEAR ARRAY LYNEAR(INDEX2D(1,2)):='xxx'; --- but INDEX2D allows you to pretend it's 2D [Quoted] LYNEAR(INDEX2D(2,2)):='YYY'; LYNEAR(INDEX2D(3,5)):='ZZZ'; --- GREAT FOR SPARSE MATRICES TOO. [Quoted] DBMS_OUTPUT.PUT_LINE('_at_(2) = '|| LYNEAR(2) ); DBMS_OUTPUT.PUT_LINE('_at_(1,2)) = '||LYNEAR(INDEX2D(1,2))|| ' _at_'||INDEX2D(1,2) ); DBMS_OUTPUT.PUT_LINE('_at_(2,2)) = '||LYNEAR(INDEX2D(2,2))|| ' _at_'||INDEX2D(2,2) ); DBMS_OUTPUT.PUT_LINE('_at_(3,5)) = '||LYNEAR(INDEX2D(3,5))|| ' _at_'||INDEX2D(3,5) );END;
/
[Quoted] There is an obvious limit on using this function, ie you have to use it whenever you use the array.
[Quoted] Not a built in feature of PL/SQL, but very flexible none-the-less.
[Quoted] 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 -- [Quoted] "Two roads diverged in a wood and I I took the one less travelled by and that has made all the difference." robert frostReceived on Fri Mar 21 2003 - 19:56:10 CET