Create a PL/SQL block to increase salary of employees in department 10. The salary increase is 15% for the employees making less than $100,000 and 10% for the employees making $100,000 or more. Use a cursor with a FOR UPDATE clause. Update the salary with a WHERE CURRENT OF clause in a cursor FOR loop (cursor FOR loop problem).

Question

What will be an ideal response?

Answer 1

```
SQL> DECLARE
2 first employee.Fname%TYPE;
3 last employee.Lname%TYPE;
4 qual employee.QualId%TYPE;
5 Sal employee.Salary%TYPE;
6 v_qualId employee.QualId%TYPE := &Qualification_Id;
7 BEGIN
8 SELECT Lname, Fname, QualId, Salary
9 INTO last, first, qual, sal
10 FROM employee
11 WHERE QualId = v_qualId;
12 DBMS_OUTPUT.PUT_LINE(last || ', ' || first);
13 DBMS_OUTPUT.PUT_LINE('Qualification: ' || qual);
14 DBMS_OUTPUT.PUT_LINE('Salary: ' || sal);
15 EXCEPTION
16 WHEN NO_DATA_FOUND THEN
17 DBMS_OUTPUT.PUT_LINE
18 ('No employees with such qualification');
19 WHEN TOO_MANY_ROWS THEN
20 DBMS_OUTPUT.PUT_LINE
21 ('More than employee with qualification ' || v_qualId);
22 END;
23 /
Enter value for qualification_id: 7
No employees with such qualification

PL/SQL procedure successfully completed.

SQL> /
Enter value for qualification_id: 1
More than employee with qualification 1

PL/SQL procedure successfully completed.

SQL> /
Enter value for qualification_id: 5
Garner, Stanley
Qualification: 5
Salary: 45000

PL/SQL procedure successfully completed.
```