w3resource
PL/SQL Cursor Exercises

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:

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:

Flowchart: 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

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.

What is the difficulty level of this exercise?