CODEWITHSUNDEEP
oracle-databaseplsql
Asım Gündüz
Asım Gündüz

Reputation: 1297

Selecting conditions dynamically In oracle

Hi What I would like to do is take the search criterias as parameters to my function and search through the table depending on the input parameters.. for example: IF ALL the parameters are NULL then the function will return evey field in the table, if a parameter is not NULL that parameter will be a search criteria. I could do an If statement but it would be too long and assuming that I would have had a bigger table it would be very painful, Is there any way I could accomplish this Task? Thanks

My Code: 

create or replace type ordersinf as object(orderDate DATE, orderName VARCHAR2(20), orderPrice NUMBER)


create or replace type orders_tab IS TABLE OF ordersinf

and My Code is: 

create or replace function execImmFunc(orderDate DATE, orderName in
VARCHAR2, orderPrice in NUMBER) return orders_tab is
  Result orders_tab := orders_tab();
begin

  SELECT  ordersinf(orderDate => orders_Orderdate, orderName => orderName,orderPrice => orders_price)
  BULK COLLECT INTO RESULT
  FROM orders_table
  WHERE  orders_Orderdate >= orderDate OR orders_name = orderName OR orders_price = orderPrice ;

  return(Result);
end execImmFunc;

Upvotes: 1

Views: 103

Answers (4)

Mathias Magnusson
Mathias Magnusson

Reputation: 197

Are you trying to set up so a parameter is either returned in the resultset or used in the where-clause? It does not look like you do, but rather you want to return all columns and use parameters in where only when they are not null.

If so, why not just a classic nvl-setup?

SELECT  ordersinf(orderDate => orders_Orderdate
                 ,orderName => orderName
                 ,orderPrice => orders_price)
  BULK COLLECT INTO RESULT
  FROM orders_table
  WHERE orders_Orderdate >= nvl(orderDate,  orders_Orderdate)
     OR orders_name       = nvl(orderName,  orders_name) 
     OR orders_price      = nvl(orderPrice, orders_price);

I imagine those ORs ought to be AND as you probably want all conditions sent in to be true, but that is purely a desugn/requirement issue.

Upvotes: 0

Avrajit Roy
Avrajit Roy

Reputation: 3303

Hey I dont have workspace with me but below is a very simple snippet which can be used for dynamic sql formation as per the input provided. Hope this helps

SET SERVEROUTPUT ON;
DECLARE
  lv_col1 VARCHAR2(100):=123;
  lv_col2 VARCHAR2(100):=1;
  lv_col3 VARCHAR2(100):=NULL;
  lv_col4 VARCHAR2(100):=45;
  lv_col5 VARCHAR2(100):=8;
  lv_sql LONG;
  lv_where LONG;
BEGIN
  lv_sql:='SELECT * FROM TABLE WHERE 1 = 1 ';
  SELECT DECODE(lv_col1,NULL,'',' AND col1 = '''
    ||lv_col1
    ||'''')
    || DECODE(lv_col2,NULL,'',' AND col2 = '''
    ||lv_col2
    ||'''')
    || DECODE(lv_col3,NULL,'',' AND col3 = '''
    ||lv_col3
    ||'''')
    || DECODE(lv_col4,NULL,'',' AND col4 = '''
    ||lv_col4
    ||'''')
    || DECODE(lv_col5,NULL,'',' AND col5 = '''
    ||lv_col5
    ||'''')
  INTO lv_where
  FROM dual;
  dbms_output.put_line(lv_sql||lv_where);
END;

Upvotes: 1

Jon Spokes
Jon Spokes

Reputation: 2599

Is this what you mean

WHERE  (orderDate is null OR orders_Orderdate >= orderDate) AND (orderName is null OR orders_name = orderName) AND (orderPrice  is null OR orders_price = orderPrice );

If a parameter orderDate is null - the clause passes and is effectively ignored?

Upvotes: 1

MT0
MT0

Reputation: 167966

Something like this:

CREATE OR REPLACE FUNCTION execImmFunc(
  orderDate  IN DATE,
  orderName  IN VARCHAR2,
  orderPrice IN NUMBER
) RETURN orders_tab
IS
  Result orders_tab;
BEGIN
  SELECT  ordersinf(
            orderDate  => orders_OrderDate,
            orderName  => orders_Name,
            orderPrice => orders_price
          )
  BULK COLLECT INTO Result
  FROM   orders_table
  WHERE  ( orderDate  IS NULL OR orders_OrderDate >= orderDate )
  AND    ( orderName  IS NULL OR orders_name = orderName )
  AND    ( orderPrice IS NULL OR orders_price = orderPrice );

  RETURN Result;
END execImmFunc;

Upvotes: 1

Related Questions