Is there a way to dynamically set SELECT Query for a FOR LOOP

Question

What i am trying is to iterate through a SELECT based on a certain condition.

CREATE OR REPLACE PROCEDURE procedure
IS
selectForLoop varchar2(256);
v_birthdate date;
BEGIN
select X into v_birthdate from Y where C = Z;
IF true THEN
   selectForLoop := 'Select name from employees';
ELSE
   selectForLoop := 'Select name from employees where birthdate = v_birthdate';
END IF;

FOR val in (selectForLoop)
LOOP
   BusinessLogic
END LOOP;

What i could do is:

CREATE OR REPLACE PROCEDURE procedure
IS
v_birthdate date;
BEGIN 
select X into v_birthdate from Y where C = Z;
If true THEN
   FOR i IN (Select name from employees)
   LOOP
      BusinessLogic
   END LOOP;
ELSE
   FOR i IN (Select name from employees where birthdate = v_birthdate
   LOOP
      BusinessLogic
   END LOOP;
END IF;

That would be a solution but the businesslogic is the same in both ways and only the data that i want to iterate through is different. Id really like to prevent that dirty copypasta attempt. Any suggestions?

Answer 1

You have two objectives

1. keep the code DRY and

2. use bind variables

If you have only a very simple condition (as in your example, use filter or use no filter) you my use IF statement to open different cursors for both cases.

IF salary_from is null THEN
  OPEN v_cursor FOR SELECT LAST_NAME, SALARY FROM hr.employees;
ELSE
  OPEN v_cursor FOR SELECT LAST_NAME, SALARY FROM hr.employees WHERE SALARY >= salary_from;
END IF;

Note that you should be carefull with the OR solution using the predicate SALARY >= salary_from OR salary_from is NULL

Why? You use one query for two very different scenarios. The cursor can returns either all data or very limited data, that could require a different access method (e.g. index access vs full table scan). So you may and with suboptimal plan in one case.

The problem with the above approach is that it does not scale. If you have four optional criteria you will need 16-fold IF with a highly redundant code.

What is the solution to keep the above objectives valid?

Use dynamic SQL, but do not concatenate the criteria values such as

 SELECT LAST_NAME, SALARY FROM hr.employees WHERE salary >= 1000 and salary <= 10000

This will invalidate the bind variable objective!

To keep with the example with optional parameter salary_from and salary_to you want to use

open v_cursor for v_sql using salary_from, salary_to;

But this requires, that both bind variables must be defined in the query text - what should be done in case you have only salary_from?

Opening the cursor for this query

SELECT LAST_NAME, SALARY FROM hr.employees WHERE SALARY >= :salary_from 

will lead to error

ORA-01006: bind variable does not exist

The trick is to use dummy predicates returning always true, but containing a bind variable (that will be ignored).

So if you have only salary_from as a filter you will create following dynamic SQL

SELECT LAST_NAME, SALARY FROM hr.employees WHERE SALARY >= :salary_from AND  (1=1 or SALARY <= :salary_to)

It contains both bind variables and the optimizer (with the *shortcut evaluation) will simplify it to the required

SELECT LAST_NAME, SALARY FROM hr.employees WHERE SALARY >= :salary_from

So the relevant code for the salary filter example would be

IF salary_from is NOT null THEN
  v_sql := 'SELECT LAST_NAME, SALARY FROM hr.employees WHERE SALARY >= :salary_from';
ELSE
  v_sql := 'SELECT LAST_NAME, SALARY FROM hr.employees WHERE (1 = 1 or SALARY >= :salary_from)';  
END IF;
IF salary_to is NOT null THEN
  v_sql := v_sql ||' AND  SALARY <= :salary_to';
ELSE
  v_sql := v_sql ||' AND  (1=1 or SALARY <= :salary_to)';  
END IF;
open v_cursor for v_sql using salary_from, salary_to;

Below the overview of the generated SQL for the four cases

-- no filter
SELECT LAST_NAME, SALARY FROM hr.employees WHERE (1 = 1 or SALARY >= :salary_from) AND  (1=1 or SALARY <= :salary_to)
-- salary_from 
SELECT LAST_NAME, SALARY FROM hr.employees WHERE SALARY >= :salary_from AND  (1=1 or SALARY <= :salary_to)
-- salary from, to
SELECT LAST_NAME, SALARY FROM hr.employees WHERE SALARY >= :salary_from AND  SALARY <= :salary_to
-- salary_to
SELECT LAST_NAME, SALARY FROM hr.employees WHERE (1 = 1 or SALARY >= :salary_from) AND  SALARY <= :salary_to

Similar topics with credit to this idea: here, here and here

Answer 2

You can check for a argument being NULL in your query. Example (based on the sample data emp/dept)

CREATE OR REPLACE PROCEDURE list_employees (hired_after_i DATE DEFAUlT NULL)
AS
BEGIN
  FOR r IN 
    (SELECT * FROM emp 
      WHERE hiredate > hired_after_i OR 
            hired_after_i IS NULL)
  LOOP
    dbms_output.put_line(r.ename||' hired on '||r.hiredate);
  END LOOP;
END;
/
Procedure LIST_EMPLOYEES compiled

set serveroutput on size 999999
BEGIN
  list_employees;
END;
/

SMITH hired on 17-DEC-1980
ALLEN hired on 20-FEB-1981
WARD hired on 22-FEB-1981
JONES hired on 02-APR-1981
MARTIN hired on 28-SEP-1981
BLAKE hired on 01-MAY-1981
CLARK hired on 09-JUN-1981
SCOTT hired on 19-APR-1987
KING hired on 17-NOV-1981
TURNER hired on 08-SEP-1981
ADAMS hired on 23-MAY-1987
JAMES hired on 03-DEC-1981
FORD hired on 03-DEC-1981
MILLER hired on 23-JAN-1982

PL/SQL procedure successfully completed.

BEGIN
  list_employees(hired_after_i => DATE'1982-01-01');
END;
/
SCOTT hired on 19-APR-1987
ADAMS hired on 23-MAY-1987
MILLER hired on 23-JAN-1982

PL/SQL procedure successfully completed.

If you want to look over a collection but the way it is populated is different then this is also a possibility. For this example I'm populating a pl/sql collection in 2 different ways depending on the value of an argument and then looping through the collection once.

create or replace PROCEDURE list_employees2 (some_input_variable VARCHAR2)
AS
  TYPE emp_t  IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
  t_emp emp_t;
BEGIN
  IF some_input_variable = 'A' THEN
    SELECT ename 
      BULK COLLECT INTO t_emp 
      FROM emp WHERE hiredate > DATE'1982-01-01';
  ELSIF some_input_variable = 'B' THEN
    t_emp(1) := 'KOEN';
    t_emp(2) := 'MIKE';
  END IF;
  FOR i IN 1 .. t_emp.COUNT LOOP
    dbms_output.put_line(t_emp(i));
  END LOOP;
END;
/

Answer 3

You can use cursors and cursor variables in PL/SQL to dynamically switch between queries.

CREATE OR REPLACE PROCEDURE procedure
IS
  selectForLoop varchar2(256);
  v_name employees%name%TYPE;
  v_birthdate date;
  v_cursor SYS_REFCURSOR;
BEGIN
  IF condition THEN
    OPEN v_cursor FOR SELECT name FROM employees;
  ELSE
    OPEN v_cursor FOR SELECT name FROM employees WHERE birthdate = 
 v_birthdate;
  END IF;

  LOOP
    FETCH v_cursor INTO v_name;
    EXIT WHEN v_cursor%NOTFOUND;
    BusinessLogic;
  END LOOP;
  CLOSE v_cursor;
END;

You could also pass the cursor variable as a parameter to your business logic, and move the loop inside the business logic procedure:

OPEN v_cursor FOR selectForLoop;
BusinessLogic(v_cursor);
CLOSE v_cursor;


CREATE OR REPLACE
PROCEDURE BusinessLogic(p_cursor SYS_REFCURSOR IN) IS
  v_name employees%name%TYPE;
BEGIN
  LOOP
    FETCH p_cursor INTO v_name;
    EXIT WHEN p_cursor%NOTFOUND;
    -- Do business logic
  END LOOP;
END;