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
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.
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.
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer