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

From: Steinar Orset <steinar_at_quasar.no>
Date: Fri, 08 Jan 1999 09:41:59 +0100
Message-ID: <3695C4D6.813FB00F@quasar.no>

Hello all and thanks Thomas and Ed,
The solution presented below by Ed continues to impress me.

Regards Steinar.

prochak_at_my-dejanews.com wrote:

> 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 Fri Jan 08 1999 - 02:41:59 CST