Mega Code Archive
Use cursor variable in update statement
<![CDATA[
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, 'V', 'Ben', 'cv',to_date('03-MAR-90 8:30', 'dd-mon-yy hh24:mi'),NULL, NULL, 'PRESIDENT', 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, 'N', 'Haidy', 'ln', '08-MAR-90', NULL,1, 'VP, OPERATIONS', 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, 'N', 'Molly', 'mn', '17-JUN-91',NULL, 1, 'VP, SALES', 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, 'S', 'Mark', 'mq', '07-APR-90',NULL, 1, 'VP, FINANCE', 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, 'R', 'AUDRY', 'ar', '04-MAR-90',NULL, 1, 'VP, ADMINISTRATION', 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, 'U', 'MOLLY', 'mu', '18-JAN-91',NULL, 2, 'WAREHOUSE MANAGER', 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, 'M', 'ROBERTA', 'rm', '14-MAY-90',NULL, 2, 'WAREHOUSE MANAGER', 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, 'B', 'BEN', 'ry', '07-APR-90', NULL, 2,'WAREHOUSE MANAGER', 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, 'C', 'Jane', 'ac', '09-FEB-92',NULL, 2, 'WAREHOUSE MANAGER', 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, 'H', 'Mart', 'mh', '27-FEB-91', NULL, 2,'WAREHOUSE MANAGER', 41, 1307, NULL);
1 row created.
SQL>
SQL> DECLARE
2 CURSOR empCursor IS
3 SELECT employee_id, department_id, NVL(salary,0) salary, ROWID
4 FROM employee;
5 lv_record_num PLS_INTEGER DEFAULT 0;
6 BEGIN
7 FOR empRecord IN empCursor LOOP
8 lv_record_num := lv_record_num + 1;
9 IF empRecord.department_id = 10 OR
10 empRecord.department_id = 41 THEN
11 IF empRecord.salary > 1000 THEN
12 empRecord.salary := empRecord.salary * 1.05;
13 ELSE
14 empRecord.salary := empRecord.salary * 1.10;
15 END IF;
16 ELSIF empRecord.department_id = 31 THEN
17 IF empRecord.salary > 1400 THEN
18 empRecord.salary := empRecord.salary * 1.05;
19 ELSE
20 empRecord.salary := empRecord.salary * 1.10;
21 END IF;
22 ELSIF empRecord.department_id = 50 THEN
23 IF empRecord.salary > 2000 THEN
24 empRecord.salary := empRecord.salary * 1.05;
25 ELSE
26 empRecord.salary := empRecord.salary * 1.10;
27 END IF;
28 END IF;
29 UPDATE employee
30 SET salary = empRecord.salary
31 WHERE rowid = empRecord.ROWID;
32 DBMS_OUTPUT.PUT_LINE(' Employee: ' || empRecord.employee_id ||
33 ' Department: ' || empRecord.department_id ||' New Salary: ' ||
34 to_char(empRecord.salary, '$999,999.99'));
35 END LOOP;
36 COMMIT;
37 DBMS_OUTPUT.PUT_LINE('Update Process Complete. ' ||
38 lv_record_num || ' Records Processed.');
39 EXCEPTION
40 WHEN OTHERS THEN
41 DBMS_OUTPUT.PUT_LINE('Error on Record ' || lv_record_num ||
42 ': Update Process Aborted.');
43 ROLLBACK;
44 END;
45 /
Employee: 1 Department: 50 New Salary: $2,625.00
Employee: 2 Department: 41 New Salary: $1,522.50
Employee: 3 Department: 31 New Salary: $1,540.00
Employee: 4 Department: 10 New Salary: $1,522.50
Employee: 5 Department: 50 New Salary: $1,705.00
Employee: 6 Department: 41 New Salary: $1,260.00
Employee: 7 Department: 41 New Salary: $1,312.50
Employee: 8 Department: 41 New Salary: $1,155.00
Employee: 9 Department: 41 New Salary: $1,365.00
Employee: 10 Department: 41 New Salary: $1,372.35
Update Process Complete. 10 Records Processed.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
]]>