w3resource
PL/SQL Cursor Exercises

PL/SQL Cursor Exercises: Show the uses of a variable in explicit cursor query, and no result set is affected despite that value of the variable is incremented after every fetch

PL/SQL Cursor: Exercise-27 with Solution

Write a PL/SQL block to show the uses of a variable in explicit cursor query, and no result set is affected despite that value of the variable is incremented after every fetch.

Sample Solution:

PL/SQL Code:

DECLARE
    emp_sal   employees.salary%TYPE;
    sal_twise employees.salary%TYPE;
    newvar    INTEGER := 2;
    CURSOR cur1 IS
      SELECT salary,
             salary * newvar
      FROM   employees
      WHERE  job_id LIKE 'PU_%';
BEGIN
    OPEN cur1;
    LOOP
        FETCH cur1 INTO emp_sal, sal_twise;
        EXIT WHEN cur1%NOTFOUND;
        dbms_output.Put_line('When value of the variable: '
                             || newvar);
        dbms_output.Put_line('Salary: '
                             || emp_sal);
        dbms_output.Put_line('Twise of Salary:  '
                             || sal_twise);
        newvar := newvar + 1;
    END LOOP;
    CLOSE cur1;
END;
/ 

Sample Output:

SQL> /
When value of the variable: 2
Salary: 3100
Twise of Salary:  6200
When value of the variable: 3
Salary: 2900
Twise of Salary:  5800
When value of the variable: 4
Salary: 2800
Twise of Salary:  5600
When value of the variable: 5
Salary: 2600
Twise of Salary:  5200
When value of the variable: 6
Salary: 2500
Twise of Salary:  5000
When value of the variable: 7
Salary: 11000
Twise of Salary:  22000

PL/SQL procedure successfully completed.

Flowchart:

Flowchart: PL/SQL Cursor Exercises - Show the uses of a variable in explicit cursor query, and no result set is affected despite that value of the variable is incremented after every fetch

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to fetch the first three rows of a result set into three records using Same explicit cursor into different variables.
Next: Write a PL/SQL block to display department name, head of the department,city, and employee with highest salary.

What is the difficulty level of this exercise?