Efficient Oracle PLSQL stored procedure to return sys_refcursor for similar, but different, SQL statements

Question

Am writing a PL/SQL stored procedure which needs to return a sys_refcursor containing JSON derived from a single table.

The SQL will have a consistent SELECT clause, but 2 different WHERE clauses, depending on inputs, and optional offset and fetch next statements.

While I have a brute force implementation, it feels very inefficient with a lot of duplicated code split between 6 IF statements.

Here's a stylized version of this brute force approach:

procedure query_my_table
(
     in_param_1
     ...
     in_param_n
     ...
    in_limit           number,
    in_offset          number,
    ref            out sys_refcursor
) is
begin
    if in_param_n is null then
        if nvl(in_offset, 0) = 0 then
            open ref for
            select json_object( ... cols ...
                    format json) as json
               from my_table
              where col_1 = in_param_1 and col_2 = in_param_2 and col_3 = in_param_3
        elsif nvl(in_limit, 0) = 0 then
            open ref for
            select json_object( ... cols ...
                    format json) as json
               from my_table
              where col_1 = in_param_1 and col_2 = in_param_2 and col_3 = in_param_3
            offset in_offset rows;
        else
            open ref for
            select json_object( ... cols ...
                    format json) as json
               from my_table
              where col_1 = in_param_1 and col_2 = in_param_2 and col_3 = in_param_3
            offset in_offset rows
            fetch next in_limit rows only;
        end if;
    else
        if nvl(in_offset, 0) = 0 then
            open ref for
            select json_object( ... cols ...
                    format json) as json
               from my_table
              where col_n=in_param_n;
        elsif nvl(in_limit, 0) = 0 then
            open ref for
            select json_object( ... cols ...
                    format json) as json
               from my_table
              where col_n=in_param_n
             offset in_offset rows;
        else
            open ref for
            select json_object( ... cols ...
                    format json) as json
               from my_table
              where col_n=in_param_n
             offset in_offset rows;
            offset in_offset rows
            fetch next in_limit rows only;
        end if;
    end if;
exception
    when others then
        if ref%isopen then
            close ref;
        end if;
        -- handle exception
end query_my_table;

Since the only thing that varies is the WHERE clause (including the fetch/offset), I hope it can be written more efficiently.

I have tried building dynamic SQL, but it fails compilation - the pattern I used was:

the_sql := <string with SELECT statement>;
open ref for
   execute immediate the_sql;

Any suggestions would be appreciated!

Answer 1

You can rewrite it as:

PROCEDURE query_my_table
(
  in_param_1
  ...
  in_param_n
  in_limit  IN  number,
  in_offset IN  number,
  ref       OUT sys_refcursor
)
IS
BEGIN
  OPEN ref FOR
    SELECT json
    FROM   (
      SELECT json_object(
               ... cols ...
               format json
             ) as json,
             ROW_NUMBER() OVER (ORDER BY something) AS rn
      FROM   my_table
      WHERE  col_1 = in_param_1
      AND    col_2 = in_param_2
      AND    col_3 = in_param_3
      AND    (col_n = in_param_n OR in_param_n IS NULL)
    )
    WHERE (in_offset IS NULL OR in_offset < rn)
    AND   (in_limit  IS NULL OR rn <= in_limit + COALESCE(in_offset, 0));
END query_my_table;