CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
just
just

Reputation: 41

if first query result is null go to second query in plsql

procedure sistem_kullanici_ara(ptckno in musteri.tck_no%type,
pcursor  in out sys_refcursor)  is   
 begin      
      open pcursor for
        select *
        from sistem_kullanici sk,
             musteri_rol      mr,
             musteri          m,
             calisan          c,
             rol r,
             departman        d,
             bolge            b
       where sk.calisan_id = c.calisan_id
       and mr.musteri_id = m.musteri_id
       and r.rol_id = mr.rol_id
       and r.rol_id = 2001
       and sk.bolge_kod = b.bolge_kod
       and sk.departman_kod=d.departman_kod
       and sk.bitis_tarih is null
         and m.tck_no = 11111
  end;

if this query result is null go to second query

like:

open pcursor for
        select *
        from musteri  m

       where m.tck_no = 11111
end;

I have two queries and if first query result is null I wan to go to second query.

How can I do this? UNION is not working for me, I need a different method.

Upvotes: 2

Views: 97

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

Firstly, create a view :

  create view v_sistem_kullanici as 
  select *
    from sistem_kullanici sk
    join musteri_rol      mr on <mr.related_id> = <sk.related_id>
    join musteri          m on mr.musteri_id = m.musteri_id
    join calisan          c on sk.calisan_id = c.calisan_id
    join rol              r on r.rol_id = mr.rol_id
    join departman        d on sk.departman_kod=d.departman_kod
    join bolge            b on sk.bolge_kod = b.bolge_kod
   where sk.bitis_tarih is null
     and r.rol_id = 2001;

and then create a procedure :

CREATE OR REPLACE PROCEDURE sistem_kullanici_ara(
                                                 ptckno in musteri.tck_no%type,
                                                 pcursor  in out sys_refcursor
                                                ) IS    
  r_sistem_kullanici  v_sistem_kullanici%rowtype;
BEGIN

  OPEN pcursor FOR 
    select *
      from v_sistem_kullanici
     where tck_no = ptckno;

 FETCH pcursor INTO r_sistem_kullanici; 

 IF pcursor%notfound THEN
     OPEN pcursor FOR 
     select *
       from musteri  m
      where m.tck_no = ptckno;
  END IF;

END;

The trick is to consider c1%notfound after fetch..into statement through the use of local rowtype variable generated by that newly created view.

Upvotes: 0

Popeye
Popeye

Reputation: 35910

I think first you can do count and then proceed with logic:

procedure sistem_kullanici_ara(ptckno in musteri.tck_no%type,
pcursor  in out sys_refcursor)  is   

lv_count number := 0;

 begin      
     select count(1) into lv_count
        from sistem_kullanici sk,
             musteri_rol      mr,
             musteri          m,
             calisan          c,
             rol r,
             departman        d,
             bolge            b
       where sk.calisan_id = c.calisan_id
       and mr.musteri_id = m.musteri_id
       and r.rol_id = mr.rol_id
       and r.rol_id = 2001
       and sk.bolge_kod = b.bolge_kod
       and sk.departman_kod=d.departman_kod
       and sk.bitis_tarih is null
         and m.tck_no = 11111;


    if lv_count > 0 then
      open pcursor for
        select *
        from sistem_kullanici sk,
             musteri_rol      mr,
             musteri          m,
             calisan          c,
             rol r,
             departman        d,
             bolge            b
       where sk.calisan_id = c.calisan_id
       and mr.musteri_id = m.musteri_id
       and r.rol_id = mr.rol_id
       and r.rol_id = 2001
       and sk.bolge_kod = b.bolge_kod
       and sk.departman_kod=d.departman_kod
       and sk.bitis_tarih is null
         and m.tck_no = 11111;

     else

      open pcursor for
        select *
        from musteri  m

       where m.tck_no = 11111;
    end if;
  end;

Cheers!!

Upvotes: 1

Related Questions