我有一個程式loyalty_raise，旨在為每個符合條件的員工增加薪水（由函式確定eligible_for_raise，基于作業時間）。我宣告了一個游標來存盤符合條件的員工的所有 id，并嘗試更新表
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;
但這給了我一個錯誤：PL/SQL: ORA-00904: "C_ELIGIBLE_EMPS": invalid identifier
uj5u.com熱心網友回復：
如果我正確理解了您的任務，則可能是WHERE CURRENT OF您要查找的子句。
我正在使用 Scott 的示例模式，為當前工資低于 2000 的員工提高工資。
功能：
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.
程序：注意 cursor 的FOR UPDATE子句（第 6 行），以及UPDATEs WHERE CURRENT OF（第 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.
測驗：這是初始資料：
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.
運行程式：
SQL> exec loyalty_raise;
PL/SQL procedure successfully completed.
檢查結果：
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>
uj5u.com熱心網友回復：
這可以使用游標變數和BULK COLLECT. 這樣，游標結果會填充一個集合，然后可以TABLE在 where 子句中使用函式參考該集合。
EMP/DEPT 資料集的示例，帶有一個虛擬的 compatible_for_raise 函式和一個匿名 pl/sql 塊：
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>
uj5u.com熱心網友回復：
不，你不能。您必須預先將游標轉換為表型別變數。如果您假設有大量記錄（數百萬） - 您必須事先將游標轉換為全域臨時表。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/483089.html