Cursor_Anonymous_Procedure1.htm
Objective :
  • More On : anonymous procedure block
  • Cursor for loop:
    declare
    cursor emp_cur is
    = = = = = = = = = =
    for emp_rec in emp_cur loop
  • With SQL * Plus Tool and SQL Developer side by site
  • Totaling EMP Pay-roll
This example reemphasize the importance of PL/SQL as a reporting tool which embeds a SQL statement in a anonymous procedural block, execute special routine or function,  and then fetch the data row by row while displaying custom results. 
 
Scott: emp table:

Cursor PL/SQL Script:

declare
cursor emp_cur is
select ename, empno, sal
from emp
order by sal desc;
var_name VARCHAR2(10);
var_empno number(4):=0;
var_sal number (7,2) :=0;
var_tot number (9,2) :=0;
begin
for emp_rec in emp_cur
loop
var_name := emp_rec.ename;
var_empno := emp_rec.empno;
var_sal := emp_rec.sal;
var_tot := var_tot + emp_rec.sal;
dbms_output.put_line (var_name||','||var_empno||','|| var_sal);
end loop;
dbms_output.put_line('---------------');
dbms_output.put_line ('total salary : ' || var_tot);
end;
/

SQL Developer: Quotes "anonymous block completed", where a SQL* Plus quotes as a "Procedure Block"