將在 deptno 10 作業的員工的薪水提高 15%，Deptno 20 提高 15% 和其他員工 5% 還顯示在該部門作業的相應員工。使用引數 Cursor 和 Cursor with Update 子句。
我嘗試這樣做但沒有得到解決方案。這是我的代碼。
DECLARE
CURSOR V_C1 (V_DEPTNO EMP.DEPTNO%TYPE)
IS
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = V_DEPTNO
FOR UPDATE OF SAL NOWAIT;
V_REC V_C1%ROWTYPE;
BEGIN
FOR V_REC IN V_C1 ('V_DEPTNO')
LOOP
IF V_REC.DEPTNO = 10
THEN
UPDATE EMP
SET SAL = V_REC.SAL V_REC.SAL * 15 / 100
WHERE DEPTNO = 'V_DEPTNO';
DBMS_OUTPUT.PUT_LINE (
V_REC.ENAME || ' ' || V_REC.SAL || ' ' || V_REC.DEPTNO);
ELSIF V.REC.DEPTNO = 20
THEN
UPDATE EMP
SET SAL = V_REC.SAL V_REC.SAL * 20 / 100
WHERE DEPTNO = 'V_DEPTNO';
DBMS_OUTPUT.PUT_LINE (
V_REC.ENAME || ' ' || V_REC.SAL || ' ' || V_REC.DEPTNO);
ELSE
UPDATE EMP
SET SAL = V_REC.SAL V_REC.SAL * 5 / 100
WHERE DEPTNO = 'V_DEPTNO';
DBMS_OUTPUT.PUT_LINE (
V_REC.ENAME || ' ' || V_REC.SAL || ' ' || V_REC.DEPTNO);
END IF;
END LOOP;
END;
注意-我需要增加在每個部門作業的所有員工的薪水，而無需明確給出部門編號，即程式應采用所有部門編號并自動增加在該部門作業的員工的薪水
必須在 pl/sql
uj5u.com熱心網友回復：
好的，這是出于教育目的，因此您需要使用游標和回圈等等。否則，你只會
update emp set
sal = sal * case when e.deptno in (10, 20) then 1.15
else 1.05
end;
無論如何：如果您必須使用引數化游標（用于員工），那么您首先必須獲取部門- 這是另一個游標。不要硬編碼部門（in IF-THEN-ELSE），CASE而是使用運算式。然后你可以這樣做：
SQL> DECLARE
2 CURSOR c_dept IS SELECT deptno FROM dept;
3
4 cr_dept dept.deptno%TYPE;
5
6 CURSOR c_emp (par_deptno IN dept.deptno%TYPE)
7 IS
8 SELECT empno, ename, sal old_sal
9 FROM emp
10 WHERE deptno = par_deptno;
11
12 cr_emp c_emp%ROWTYPE;
13 new_sal emp.sal%TYPE;
14 BEGIN
15 OPEN c_dept;
16
17 LOOP
18 FETCH c_dept INTO cr_dept;
19
20 EXIT WHEN c_dept%NOTFOUND;
21
22 DBMS_OUTPUT.put_line ('Department ' || cr_dept);
23
24 OPEN c_emp (cr_dept);
25
26 LOOP
27 FETCH c_emp INTO cr_emp;
28
29 EXIT WHEN c_emp%NOTFOUND;
30
31 UPDATE emp e
32 SET e.sal =
33 ROUND (
34 e.sal
35 * CASE
36 WHEN e.deptno IN (10, 20) THEN 1.15
37 ELSE 1.05
38 END)
39 WHERE e.empno = cr_emp.empno
40 RETURNING ROUND (sal)
41 INTO new_sal;
42
43 DBMS_OUTPUT.put_line (
44 RPAD (cr_emp.ename, 15, ' ')
45 || ': old salary = '
46 || TO_CHAR (cr_emp.old_sal, '9990')
47 || ', new salary = '
48 || TO_CHAR (new_sal, '9990'));
49 END LOOP;
50
51 CLOSE c_emp;
52 END LOOP;
53
54 CLOSE c_dept;
55 END;
56 /
這導致
Department 10
CLARK : old salary = 2450, new salary = 2818
KING : old salary = 5000, new salary = 5750
MILLER : old salary = 1300, new salary = 1495
Department 20
SMITH : old salary = 800, new salary = 920
JONES : old salary = 2975, new salary = 3421
SCOTT : old salary = 3000, new salary = 3450
ADAMS : old salary = 1100, new salary = 1265
FORD : old salary = 3000, new salary = 3450
Department 30
ALLEN : old salary = 1600, new salary = 1680
WARD : old salary = 1250, new salary = 1313
MARTIN : old salary = 1250, new salary = 1313
BLAKE : old salary = 2850, new salary = 2993
TURNER : old salary = 1500, new salary = 1575
JAMES : old salary = 950, new salary = 998
Department 40
PL/SQL procedure successfully completed.
SQL>
uj5u.com熱心網友回復：
您可以在單個 SQL 更新中完成。如果您想使用 PL/SQL 來顯示更改，那么：
DECLARE
TYPE name_list IS TABLE OF EMP.ENAME%TYPE;
TYPE sal_list IS TABLE OF EMP.SAL%TYPE;
TYPE dept_list IS TABLE OF EMP.DEPTNO%TYPE;
names name_list;
sals sal_list;
depts dept_list;
BEGIN
UPDATE EMP
SET sal = sal * CASE WHEN deptno IN (10, 20) THEN 1.15 ELSE 1.05 END
RETURNING ename, sal, deptno BULK COLLECT INTO names, sals, depts;
FOR i IN 1 .. names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (
names(i) || ' ' || sals(i) || ' ' || depts(i)
);
END LOOP;
END;
/
如果你真的需要游標，那么：
DECLARE
CURSOR v_depts IS
SELECT DEPTNO
FROM dept;
CURSOR V_C1 (V_DEPTNO EMP.DEPTNO%TYPE) IS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = V_DEPTNO
FOR UPDATE OF SAL NOWAIT;
V_DEPT V_depts%ROWTYPE;
V_REC V_C1%ROWTYPE;
v_sal EMP.SAL%TYPE;
BEGIN
FOR v_dept IN v_depts
LOOP
FOR V_REC IN V_C1 (v_dept.deptno)
LOOP
UPDATE EMP
SET SAL = SAL * CASE WHEN deptno IN (10, 20) THEN 1.15 ELSE 1.05 END
WHERE EMPNO = V_REC.EMPNO
RETURNING sal INTO v_sal;
DBMS_OUTPUT.PUT_LINE (
V_REC.ENAME || ' ' || v_sal || ' ' || V_REC.DEPTNO
);
END LOOP;
END LOOP;
END;
/
db<>在這里擺弄
uj5u.com熱心網友回復：
從 WHERE 子句中洗掉 V_DEPTNO 中的單引號，以便 SQL 搜索 V_DEPNO 的內容。否則，SLQ 會根據文字 V_DEPNO 搜索 DEPTNO 欄位。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/448759.html