PL/SQL Cursor Exercises: Create a cursor displays the name and salary of each employee in the EMPLOYEES table whose salary is less than that specified by a passed-in parameter value cursor

PL/SQL Cursor: Exercise-24 with Solution

Write a program in PL/SQL to create a cursor displays the name and salary of each employee in the EMPLOYEES table whose salary is less than that specified by a passed-in parameter value.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
    var_record       employees%ROWTYPE;
    CURSOR cur_test (max_sal NUMBER) IS
        SELECT * FROM employees WHERE salary < max_sal;
BEGIN
    OPEN cur_test(5800);
    LOOP
        FETCH cur_test INTO var_record;
        EXIT WHEN cur_test%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Name: ' || var_record.first_name || chr(9)||' salary: '
            || var_record.salary);
    END LOOP;
    CLOSE cur_test;
END;
/

Sample Output:

SQL> /
Name: David      salary: 4800
Name: Valli      salary: 4800
Name: Diana      salary: 4200
Name: Alexander  salary: 3100
Name: Shelli     salary: 2900
Name: Sigal      salary: 2800
Name: Guy        salary: 2600
Name: Karen      salary: 2500
Name: Julia      salary: 3200
Name: Irene      salary: 2700
Name: James      salary: 2400
Name: Steven     salary: 2200
Name: Laura      salary: 3300
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to insert data into two tables from one table using cursor.
Next: Write a program in PL/SQL to show the uses of fetch one record at a time using fetch statement inside the loop.