if-else, case or decode inside of a ref cursor

Question

Hi there,

I have this big code where I want 3 things in my search:
1- look for all the orders (delivered and not) that match the search:
2- look for all the pendent orders that match the search;
3- look for all the delivered orders that match the search;

create or replace
function search_order(search IN VARCHAR2, a_option NUMBER) RETURN types.ref_cursor
AS
    orders_cursor types.ref_cursor;

BEGIN
    if search is not null then
      if a_option = 0 then /*case 1*/
         OPEN orders_cursor FOR
          select value(f), value(p),i.qtd_if, i.prec_total_if , forn.nome_fornecedor
              from item_fornecimento i, produto p ,fornecimento f, fornecedor forn
              where f.id_fornecimento in (select f.id_fornecimento from fornecimento f where f.id_fornecedor in 
             (select f1.id_fornecedor from fornecedor  f1 where f1.nome_fornecedor LIKE '%'||search||'%')) 
              and f.id_fornecimento= i.id_fornecimento and i.id_prod= p.id_prod and
              f.id_fornecedor = forn.id_fornecedor
              order by forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod asc;  
        RETURN orders_cursor;


      ELSIF a_option = 1 then /*case 2*/
        OPEN orders_cursor FOR
        (...)
            where f.id_fornecimento in (select f.id_fornecimento from fornecimento f where f.id_fornecedor in 
            (select f1.id_fornecedor from fornecedor  f1 where f1.nome_fornecedor LIKE '%'||search||'%')and f.data_entrega is null) 
            (...) 
        RETURN orders_cursor;

        ELSE /* case 3*/
        OPEN orders_cursor FOR
          (...)
              where f.id_fornecimento in (select f.id_fornecimento from fornecimento f where f.id_fornecedor in 
              (select f1.id_fornecedor from fornecedor  f1 where f1.nome_fornecedor LIKE '%'||search||'%')and f.data_entrega is not null) 
              (...) 
          RETURN orders_cursor;    
      end if;
    end if;

END;

This works if my search is not null, but if it is I would like just to modify a little bit the inner select and turn it into something like this: (select f1.id_fornecedor from fornecedor f1 where f1.nome_fornecedor LIKE '%'||search||'%')and f.data_entrega is not null) to --> (select f1.id_fornecedor from fornecedor f1)and f.data_entrega is not null)

So I have 3 conditions for search, and I would like to know if it's possible to use something like case, decode or even another cursor with a parameter, to do this inner select with:
- LIKE if the search string is not null;
- without LIKE, if the string is null;

But I haven't seen any examples of this and things can really go quite messy. Could someone help a newbie with same code?

Answer 1

You can use dynamic SQL to customize the statement which gets executed.

The following example returns a set of DEPT records, depending on the two parameters passed to the function. The internal logic varies the where clause to use neither, either or both of the parameters.

create or replace function get_dept_details
    ( p_loc dept.location%type := null
      , p_name dept.dname%type := null )
    return sys_refcursor
is
    rc sys_refcursor;
    stmt varchar2(32767);
begin
    stmt := 'select * from dept';
    if p_loc is null and p_name is null 
    then
        open rc for stmt;
    elsif p_loc is not null and p_name is null
    then
        stmt := stmt||' where loc = :1';
        open rc for stmt using p_loc;
    elsif p_loc is null and p_name is not null
    then
        stmt := stmt||' where dname = :1';
        open rc for stmt using p_name;
    else
        stmt := stmt||' where loc = :1 and dname = :2';
        open rc for stmt using p_loc, p_name;
    end if;
    return rc;
end;
/

Answer 2

I too would use dynamic SQL for this, but you can use a PLSQL case to control execution flow:

BEGIN

  CASE
    WHEN search IS NOT NULL AND a_option = 0 THEN
      OPEN orders_cursor FOR
        SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
                              AND forn.nome_fornecedor LIKE '%'||search||'%'
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;

    WHEN search IS NULL AND a_option = 1 THEN
      OPEN orders_cursor FOR
        SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
                             AND f.data_entrega IS NULL
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;

    WHEN search IS NOT NULL AND a_option = 1 THEN
      OPEN orders_cursor FOR
        SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
                             AND f.data_entrega IS NULL
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
                              AND forn.nome_fornecedor LIKE '%'||search||'%'
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;
  END CASE;

END;

It's not complete, but you get the idea. I also converting your ANSI-89 JOIN syntax to ANSI-92, and got rid of the IN clauses in the process.

Answer 3

Thanks for your replies, I found them very insteresting and my code became cleaner and more readable I guess. Still, OMG Ponies seems to fit better than the other one. But I found that my code is still big, perhaps I'm being stubborn!

Here's the final result,to whom it may interest

create or replace
function search_order(search IN VARCHAR2, a_option NUMBER) RETURN types.ref_cursor
AS
    orders_cursor types.ref_cursor;

BEGIN
      CASE    
      /*all the orders that match, no matter if they're delivered or not*/
      WHEN search IS NOT NULL AND a_option = 0 THEN
      OPEN orders_cursor FOR
        SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
          AND forn.nome_fornecedor LIKE '%'||search||'%'
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;
      RETURN orders_cursor;

      /*all the orders, no matter if they're delivered or not*/
      WHEN search IS NULL AND a_option = 0 THEN
      OPEN orders_cursor FOR
      SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;
      RETURN orders_cursor;

      /*########################## OPTION 1 #################################*/

      /*all the matched and pendent orders*/
      WHEN search IS NOT NULL AND a_option = 1 THEN
      OPEN orders_cursor FOR
        SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
          AND forn.nome_fornecedor LIKE '%'||search||'%'AND f.data_entrega IS NULL
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;
      RETURN orders_cursor;

      /*all the pendent orders*/
      WHEN search IS NULL AND a_option = 1 THEN
      OPEN orders_cursor FOR
        SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
          AND f.data_entrega IS NULL
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;
      RETURN orders_cursor;

      /*########################## OPTION 2 #################################*/
      /*all the matched and delivered orders*/
      WHEN search IS NOT NULL AND a_option = 2 THEN
      OPEN orders_cursor FOR
        SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
          AND forn.nome_fornecedor LIKE '%'||search||'%'AND f.data_entrega IS NOT NULL
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;
      RETURN orders_cursor;

      /*all the delivered orders*/
      WHEN search IS NULL AND a_option = 2 THEN
      OPEN orders_cursor FOR
        SELECT VALUE(f), 
               VALUE(p),
               i.qtd_if, 
               i.prec_total_if, 
               forn.nome_fornecedor
          FROM ITEM_FORNECIMENTO i
          JOIN PRODUTO p ON p.id_prod = i.id_prod
          JOIN FORNECIMENTO f ON f.id_fornecimento = i.id_fornecimento
          JOIN FORNECEDOR forn ON forn.id_fornecedor = f.id_fornecedor
          AND f.data_entrega IS NOT NULL
      ORDER BY forn.nome_fornecedor,f.data_encomenda desc,p.nome_prod;
      RETURN orders_cursor;
      end case;
END;