multiple rows iteration in oracle

Question

I have problem with oracle sql.

DECLARE r_cur select cma.desc, cma.model, cma.serial, smp.id
from model cma
join dictionary smp on smp.dictionary_id = cma.d_id;

The above will return 2 rows. And I also have variable, lets say v_var that can be either null or have some value.

Then i want to iterate over r_cur depending on the value of v_var. So I want something like this:

fetch r_cur into r_cur_single
if v_var is null then
select r_cur.desc, r_cur.model etc. into v_description, v_mode from r_cur where r_cur.serial is null
else 
select r_cur.desc, r_cur.model etc. into v_description, v_mode from r_cur where r_cur.serial like v_var

Oracle Developer returns an error that it cannot make a select from not table or view, but shouldn't it work with cursors in the same way. If no is there a way to iterate over such a cursor or maybe even use CASE in the select. I tried but the problem is that v_var can be null.

Personally I would create a new proc for the second part, have an IN OUT sys_refcursor and pass the values in and out — JohnHC
– JohnHC, Commented Jan 8, 2018 at 15:42

Alex Poole · Accepted Answer · 2018-01-08 16:07:04Z

2

A cursor is effectively a pointer into a result set. You can't query the cursor like a table.

You can refer to v_var in your cursor query; you just need to handle it being null:

DECLARE
  v_var model.serial%type;

  cursor r_cur is
    select cma.desc, cma.model, cma.serial, smp.id
    from model cma
    join dictionary smp on smp.dictionary_id = cma.d_id
    where (v_var is null and cma.serial is null)
    or (v_var is not null and cma.serial = v_var);
BEGIN
  -- populate v_var
  FOR r_row IN r_cur LOOP
    -- do whatever you need to with the row(s)
  END LOOP;
END;
/

The is not null is a bit redundant but makes the logic clearer. There's also no point in using like with a single scalar value, so I've switched to =.

You can also parameterise the cursor to make the relationship and flow a bit more obvious:

DECLARE
  v_var model.serial%type;

  cursor r_cur (c_var model.serial%type) is
    select cma.desc, cma.model, cma.serial, smp.id
    from model cma
    join dictionary smp on smp.dictionary_id = cma.d_id
    where (c_var is null and cma.serial is null)
    or (c_var is not null and cma.serial = c_var);
BEGIN
  -- populate v_var
  FOR r_row IN r_cur (v_var) LOOP
    -- do whatever you need to with the row(s)
  END LOOP;
END;
/

Or use an implicit cursor loop:

DECLARE
  v_var model.serial%type;
BEGIN
  -- populate v_var
  FOR r_rec IN (
    select cma.desc, cma.model, cma.serial, smp.id
    from model cma
    join dictionary smp on smp.dictionary_id = cma.d_id
    where (v_var is null and cma.serial is null)
    or (v_var is not null and cma.serial = v_var)
  ) LOOP
    -- do whatever you need to with the row(s)
  END LOOP;
END;
/

It would still be possible to keep your current cursor query and loop over all the results, using similar logic inside the loop to decide when you've found the row(s) you want to use, and then use PL/SQL assignment instead of a new query:

fetch r_cur into r_cur_single;
if (v_var is null and r_cur_single.serial is null)
  or (v_var is not null and r_cur_single.serial = v_var)
then
  v_description := r_cur_single.desc;
  v_mode := r_cur_single.model;
end if;

but it's probably going to be more efficient to put the filter into the query.

edited Jan 8, 2018 at 16:07

answered Jan 8, 2018 at 15:59

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lucaskos Over a year ago

BTW what if I want use select like this:

select cma.desc, cma.model, cma.serial, smp.id     from model cma     join dictionary smp on smp.dictionary_id = cma.d_id CASE  if v_var is null c.serial is null else c.serial = v_var

. Is it possible to make a select in that way ?

Alex Poole Over a year ago

Not in that form. You could use a case expression but it would be a bit non-intuitive and overly complicated.

Carlitos Way · Accepted Answer · 2018-01-08 16:14:12Z

0

There are many approachs to solve this... an easy one is to declare 2 cursors: one with the like expression and the other with IS NULL expression... then, you open the right cursor inside your IF ELSE v_var is not null logic...

answered Jan 8, 2018 at 16:14

Carlitos Way

3,44422 silver badges36 bronze badges

1 Comment

lucaskos Over a year ago

That is what I wanted to avoid. I'd like to have one select statement so there will be no repetition and I tried to achieve it with CASE but so far without success.

Collectives™ on Stack Overflow

multiple rows iteration in oracle

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related