joining collections in oracle

Question

can any one help me or provide me sample code where i have to loop through a collection based on condition . just like where clause in sql. your help is highly appreciated....

Here is my code:

create table MODEL1
(
  model_id  NUMBER ,
  model_seq NUMBER,
  p_ind     VARCHAR2(1)
);

insert into model1 (MODEL_ID, MODEL_SEQ, P_IND)
values (4, 103, 'U');

insert into model1 (MODEL_ID, MODEL_SEQ, P_IND)
values (3, 102, 'P');

insert into model1 (MODEL_ID, MODEL_SEQ, P_IND)
values (2, 101, 'U');

insert into model1 (MODEL_ID, MODEL_SEQ, P_IND)
values (1, 100, 'P');

MODEL PROCEDURE......

procedure (  param1,param2)        ( assume this procedure is being called from other procedure and collection has been populated already)

TYPE  l_tab is table of  MODEL1%rowtype;

begin

loop through l_tab records where  ltab.model_id=param1  and  p_ind =p

Join based on if else condition.

if   param2 is not null then

   l_tab.model_seq=param2 and ltab.p_ind='P'

if param2 is null then
         l_tab.p_ind='P'          etc...........

PL/SQL tables are just collections. You can't query them. I would think you could do something like this in straight SQL. — OldProgrammer, Oct 12 '13 at 15:02

Nick · Answer 1 · 2013-10-14T12:58:20.480

0

If you can guarantee your collection is densely populated, then you can simply (in Oracle 10 or 11g):

for idx in 1..my_collection.last LOOP
     if my_collection(idx).model_id = param1 and my_collection(idx).p_ind = 'p' then
              my_collection(idx).some_col = 'some new value';  -- you change the collection
     end if;
end loop;

edited Oct 14 '13 at 12:58

answered Oct 12 '13 at 15:11

Nick

2,524
17
25

1

I think you missed `.COUNT` (or `.LAST`) between `my_collection` and `LOOP`. – Przemyslaw Kruglej Oct 12 '13 at 20:40

score 0 · Answer 2 · answered Oct 12 '13 at 22:57

The answer to "just like a where clause" is yes and no.

No: You loop through the collection and you use the IF statement to check the condition.

Yes: But if you really want to use a where clause you can do this with oracle pipelined functions. Although I think for your purposes this is a bit overkill.

You would end with something like

select * from table(myfunction) where model_seq = param1

How this works is a bit more complicated. When I used it the last time I was quite happy with this link http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

joining collections in oracle

2 Answers2