Re: Does two dimensional arrays exist in PL/SQL?

From: <prochak_at_my-dejanews.com>
Date: Mon, 04 Jan 1999 22:55:40 GMT
Message-ID: <76rgtc$nnc$1@nnrp1.dejanews.com>

In article <3692cab6.2408523_at_192.86.155.100>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to Steinar Orset <steinar_at_quasar.no>
> (if that email address didn't require changing)
> On Mon, 04 Jan 1999 10:43:09 +0100, you wrote:
>
> >Hello all procedure experts,
> >I would like to work on two-dimesional arrays in PL/SQL. Is there a
> >possibility for that in the
> >following (or any other Oracle ) system: Personal Oracle7 Release
> >7.3.3.0.0 and PL/SQL Release 2.3.3.0.0 ?
> >
>
> Sort of. Its not really a 2 dimensional array but you can have tables of
> records. For example:
>
> SQL> declare
> 2 type myArray is table of emp%rowtype index by binary_integer;
> 3
> 3 empArray myArray;
> 4 begin
> 5 empArray(1).ename := 'Smith';
> 6 empArray(1).empno := 1345;
> 7 end;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> So, you don't access empArray(1,1) := 'Smith' but rather empArray(1).ENAME :=
> 'Smith'....
>
> >I work on one-dimesional arrays fine like:
> >
> >TYPE myarray IS table OF integer INDEX BY BINARY_INTEGER;
> >
> >k BINARY_INTEGER :=0;
> >
> >person_id myarray;
> >
> > person_id(k):= some_number;
> >
> >
> >Thanks for any help.
> >
> >Steinar.
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>

you can simulate a two dimensional array with a simple mapping function.

• 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 befor 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.

I hope this helps

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

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Jan 04 1999 - 16:55:40 CST