w3resource
PL/SQL Cursor Exercises

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:

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:

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

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.

What is the difficulty level of this exercise?