Arrays in pl/sql 2

[penzk001]

How can I declare an array of type number in pl/sql (oracle 8i). Also it is possible to size the array depending on the output of a query, to avoid allocating space that will remain used.

thanks

 

[sem]

Don't worry, the space is allocated dynamically even with VARRAY.

Regards, Dima

 

[penzk001]

Can you give me an example:
declare
arraysize NUMBER;
begin
select count(*) in arraysize from mytable;

end;

 

[penzk001]

This examples is clearer in what I want to do:

declare
arraysize NUMBER;

begin
select count(*) in arraysize from mytable;
-- gives from 8 to 20
for loop -- a cursor
-- performs a select that returns a number from 0 to NUMBER specified in the query above
here does like in java array[index from query]++;
end loop;
end;

 

[sem]

First of all you may use BULK COLLECT clause to improve performance if fetching a lot of records:

DECLARE
TYPE MytableRecs IS TABLE OF Mytable%ROWTYPE;
recs MytableRecs;
CURSOR c IS
SELECT * FROM Mytable;
BEGIN
OPEN c;
FETCH c BULK COLLECT INTO recs;
END;

You may also do it row-by-row with [index by] pl/sql table:

DECLARE
TYPE MytableRecs IS TABLE OF Mytable%ROWTYPE index by binary_integer;
recs MytableRecs;
CURSOR c IS
SELECT * FROM Mytable;
cnt binary_integer := 0;
BEGIN
for v in c loop
cnt := cnt + 1;
recs(cnt) := v;
end loop;
END;


Regards, Dima

 

[penzk001]

sem,

what I want to do is this:

I have a value with the number of combinations say 8. (value from query). Have at this point declare an array of 8 locations. Using a cursor in a table having 1000 rows, I get a value using a query from 1 to 8. Then the calculation is performed and the answer is added to the location indexed by the query (1 - 8).

Thus at the end of the for loop I have a array of 8 locations with the computed values.

 

[sem]

Unfortunately I can not understand your task. I can not imagine neither "value with 8 combinations" nor "query from 1 to 8" and especially "location indexed by the query".

Regards, Dima

 

[penzk001]

sem,

I have a cursor of a table which has 1500 rows, which has one column that its value ranges from 1 to 8 (integer) but this value can increase to 30. The maximum value is obtained from another query.

I am doing a calculation on each row in the cursor, the result is saved to variable tmp.

Now I need to add the variable tmp with the array[number of query that return 1 to 8 as above].

i.e
if the column has a value of 3, I want to save the do (as in Java) array[3] = array[3] + tmp;
likewise if the column has a value of 7, array[7] = array[7] + tmp;
I do not have idea how to create and used the array in PL/SQL.

After all rows in the cursor are processed I must display the contents of the array.

 

[sem]

DECLARE
TYPE Mytable IS TABLE OF number index by binary_integer; --type of your collection
m Mytable; --variable of that type
cntr binary_integer; --counter (index)
tmp number; -- some value
BEGIN
....
if m.exists(cntr) then --checks whether m(cntr) was initialized
m(cntr) := m(cntr) + tmp;
else
m(cntr) := tmp;
end if;
END;

Pl/sql table can be &quot;rarefied&quot;, i.e. you may write to ANY (<32K, may be negative, not null) index without need to define array size beforehand and this doesn't create intermediates:

m(0) := 1;
m(32000) := 2

Now you have 2-element collection.

I'd recommend you to read about Oracle COLLECTIONS in manualls.

Regards, Dima

 

[penzk001]

Thanks for your help sem