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

PL/SQL Cursor: Exercise-33 with Solution

Write a PL/SQL block to show the uses of a variable in explicit cursor query, and the result set is affected with the 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 'MK_%';
BEGIN
        dbms_output.Put_line('When value of the variable: '
                             || newvar);
    OPEN cur1;
    LOOP
        FETCH cur1 INTO emp_sal, sal_twise;
        EXIT WHEN cur1%NOTFOUND;

        dbms_output.Put_line('Salary: '
                             || emp_sal);
        dbms_output.Put_line('Twise of Salary:  '
                             || sal_twise);

    END LOOP;
    CLOSE cur1;
        newvar := newvar + 1;	
		
       dbms_output.Put_line('When value of the variable: '
                             || newvar);
    OPEN cur1;
    LOOP
        FETCH cur1 INTO emp_sal, sal_twise;
        EXIT WHEN cur1%NOTFOUND;

        dbms_output.Put_line('Salary: '
                             || emp_sal);
        dbms_output.Put_line('Twise of Salary:  '
                             || sal_twise);

    END LOOP;
    CLOSE cur1;		
END;
/ 

Sample Output:

SQL> /
When value of the variable: 2
Salary: 13000
Twise of Salary:  26000
Salary: 6000
Twise of Salary:  12000
When value of the variable: 3
Salary: 13000
Twise of Salary:  39000
Salary: 6000
Twise of Salary:  18000

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL program to display the name of the employee and increment percentage of salary according to their working experiences.
Next: Write a PL/SQL block to show the uses of a virtual column in an explicit cursor query.