Create a PL/SQL block to declare a cursor to select last name, first name, salary, and hire date from the EMPLOYEE table. Retrieve each row from the cursor and print the employee’s information if the employee’s salary is greater than $50,000 and the hire date is before 31-DEC-1997 (explicit cursor problem).

Question

What will be an ideal response?

Answer 1

```
SQL> DECLARE
2 CURSOR empcur IS
3 SELECT Lname, Fname, Salary, HireDate
4 FROM employee;
5 last employee.Lname%TYPE;
6 first employee.Fname%TYPE;
7 sal employee.Salary%TYPE;
8 hire employee.HireDate%TYPE;
9 BEGIN
10 OPEN empcur;
11 FETCH empcur INTO last, first, sal, hire;
12 WHILE empcur%FOUND LOOP
13 IF sal > 50000 AND hire < '31-DEC-1997' THEN
14 DBMS_OUTPUT.PUT(last||', '||first||' makes $'||sal);
15 DBMS_OUTPUT.PUT_LINE(' Hired: '||hire);
16 END IF;
17 FETCH empcur INTO last, first, sal, hire;
18 END LOOP;
19 CLOSE empcur;
20 END;
21 /
Smith, John makes $265000 Hired: 15-APR-60
Houston, Larry makes $150000 Hired: 19-MAY-67
Roberts, Sandi makes $75000 Hired: 02-DEC-91
McCall, Alex makes $66500 Hired: 10-MAY-97
Dev, Derek makes $80000 Hired: 15-MAR-95

PL/SQL procedure successfully completed.
```