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:

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

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.