Can you use a CURSOR in WHERE statement?-CodePudding

I have a procedure loyalty_raise that is suppoused to increase salary for every eligible employee (determined by a function eligible_for_raise, based on time worked). I declared a cursor to store all the id's of eligible employees, and tried to update the table

PROCEDURE loyalty_raise IS       
        CURSOR c_eligible_emps IS
            SELECT employeeId
            FROM Employees
            WHERE eligible_for_raise(employeeId) = 1;
    BEGIN
        OPEN c_eligible_emps;
        UPDATE Employees
        SET salary = salary   (salary * (salary_raise_multiplier / 100))
        WHERE employeeId IN c_eligible_emps;
        CLOSE c_eligible_emps;
    END loyalty_raise;

But it gives me an error: PL/SQL: ORA-00904: "C_ELIGIBLE_EMPS": invalid identifier

CodePudding user response：

If I understood your task correctly, it might be the WHERE CURRENT OF clause you're looking for.

I'm using Scott's sample schema, raising salaries for employees whose current salary is lower than 2000.

Function:

SQL> create or replace function eligible_for_raise (par_empno in number)
  2    return number
  3  is
  4    l_sal number;
  5  begin
  6    select sal into l_sal
  7    from emp
  8    where empno = par_empno;
  9    return case when l_sal < 2000 then 1 else 0 end;
 10  end;
 11  /

Function created.

Procedure: pay attention to cursor's FOR UPDATE clause (line #6), as well as UPDATEs WHERE CURRENT OF (line #16):

SQL> create or replace procedure loyalty_raise as
  2    cursor c_eligible_emps is
  3      select empno
  4      from emp
  5      where eligible_for_raise(empno) = 1
  6      for update;
  7    ceer c_eligible_emps%rowtype;
  8  begin
  9    open c_eligible_emps;
 10    loop
 11      fetch c_eligible_emps into ceer;
 12      exit when c_eligible_emps%notfound;
 13
 14      update emp set
 15        sal = sal   0.1
 16        where current of c_eligible_emps;
 17    end loop;
 18    close c_eligible_emps;
 19  end;
 20  /

Procedure created.

Testing: this is initial data:

SQL> select empno, ename, sal from emp order by sal desc;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000
      7902 FORD             3000
      7788 SCOTT            3000
      7566 JONES            2975
      7698 BLAKE            2850
      7782 CLARK            2450
      7499 ALLEN            1600      --> Allen, Turner, etc. should get a raise
      7844 TURNER           1500
      7934 MILLER           1300
      7521 WARD             1250
      7654 MARTIN           1250
      7876 ADAMS            1100
      7900 JAMES             950
      7369 SMITH             800

14 rows selected.

Run the procedure:

SQL> exec loyalty_raise;

PL/SQL procedure successfully completed.

Check the result:

SQL> select empno, ename, sal from emp order by sal desc;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000
      7902 FORD             3000
      7788 SCOTT            3000
      7566 JONES            2975
      7698 BLAKE            2850
      7782 CLARK            2450
      7499 ALLEN          1600.1    --> Right; Allen, Turner etc. really got a raise
      7844 TURNER         1500.1
      7934 MILLER         1300.1
      7521 WARD           1250.1
      7654 MARTIN         1250.1
      7876 ADAMS          1100.1
      7900 JAMES           950.1
      7369 SMITH           800.1

14 rows selected.

SQL>

CodePudding user response：

This is possible using a cursor variable and BULK COLLECT. That way the cursor result populates a collection that can then be referenced with the TABLE function in a where clause.

Example on the EMP/DEPT dataset with a dummy eligible_for_raise function and an anonymous pl/sql block:

koen>CREATE FUNCTION eligible_for_raise (empno emp.empno%TYPE) RETURN NUMBER
  2  IS
  3  BEGIN
  4    RETURN 1;
  5  END;
  6* /

Function ELIGIBLE_FOR_RAISE compiled

koen>CREATE TYPE emp_arr IS TABLE OF NUMBER;
  2* /

Type EMP_ARR compiled

koen>select sal from emp where ename = 'KING';

    SAL 
_______ 
   5001 

koen>DECLARE
  2     my_cursor SYS_REFCURSOR;
  3    
  4     l_emps emp_arr; 
  5  BEGIN
  6     OPEN my_cursor FOR 'SELECT empno FROM emp WHERE eligible_for_raise(empno) = 1';  
  7    
  8     FETCH my_cursor  
  9     BULK COLLECT INTO l_emps;  
 10     CLOSE my_cursor;     
 11     
 12     UPDATE emp SET sal = sal   1 WHERE empno IN (SELECT * FROM table(l_emps));
 13     
 14     
 15  END;
 16* /

PL/SQL procedure successfully completed.

koen>select sal from emp where ename = 'KING';

    SAL 
_______ 
   5002 

koen>

CodePudding user response：

No. You can't. You must previosly convert cursor to table type variable. If you suppose big amount of records (millions) - you must previously convert cursor to global temporary table.