oracle multiple criteria dynamic sql-CodePudding

I have a multiple criteria search function for user input/select different criteria to find results, and every criteria is optional, so the field value could be null; The PL/SQL backend processes each criteria value to construct a dynamic SQL. Currently, I use the below way to process, but it is hard for debugging and maintaining.

    jo        := json_object_t(p_payload);
    v_country := jo.get_String('IAINST_NATN_CODE');
    v_region  := jo.get_String('IAINST_REGN_CODE');
    v_rank_code  := jo.get_String('RANK_CODE');
    v_year  := jo.get_String('RANK_YEAR');
  
  
    v_sql := 'select * from IAVW_INST_JSON_TABLE i where 
         ((:1 is null) or (i.IAINST_NATN_CODE = :1))
        and ((:2 is null) or (i.IAINST_REGN_CODE = :2))
        and ((:3 is null) or (i.RANK_CODE = :3))
        and ((:4 is null) or (i.RANK_YEAR = :4))';
  
    OPEN c FOR v_sql
      USING v_country, v_country,  --1 
            v_region, v_region,  --2
            v_rank_code, v_rank_code, --3
            v_year, v_year; --4
            
    RETURN c;

Any good advice to improve?

CodePudding user response：

I would only change the structure of the clauses to be like :

 AND i.IAINST_REGN_CODE = NVL(:2, i.IAINST_REGN_CODE)

This way you will avoid OR and still won't interfer with indexing if there is any, but apart from that your code looks fine (and fine even without my suggestion either).

CodePudding user response：

After searching the related post. Here is the summary:

For my scenario, my table owns around 5K rows.

WHERE NVL(mycolumn,'NULL') = NVL(searchvalue,'NULL')

could simplify my dynamic SQL.

But if the table owns massive data, the above approach is not efficient (time cost to run the column conversion for NVL), please use the below query:

where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))

Details see this post: Determine Oracle null == null

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7806711400346248708