w3resource

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:

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?



Follow us on Facebook and Twitter for latest update.