Oracle PL/SQL cursor example

--A Procedure ProcCur is created
create or replace procedure ProcCur is

--A cursor is declared selecting 2 fields from employee_master emp_code and emp_name
cursor rec is select emp_code,emp_name from employee_master
              where emp_grade='S'
              order by emp_code;
-- 2 variables are declared of filed type emp_code and emp_name
ecode employee_master.emp_code%type;
ename employee_master.emp_name%type;
begin

-- add this  statement otherwise  ORU-10027: buffer overflow will rise
  dbms_output.enable(1000000);

-- cursor opened
  open rec;

-- column header printed
  dbms_output.put_line(lpad('Code',10,' ')||lpad('Name',60,' '));

-- loop started
  loop

--   current record fetched into ecode and ename. remember that number of fields in the cursor should
--   be equal to number of variables on which records are fetched.
    fetch rec into ecode,ename;

--  exit the loop if when no more records are fetched
    exit when rec%NOTFOUND;

-- fetched values printed
    dbms_output.put_line(lpad(ecode,10,' ')||lpad(ename,60,' '));

-- end of loop
  end loop;

--end of procedure
end TestProc;

-------------------------------------------------------------------------------------------------------------------------
-- Alternate method using for loop, this is the easiest and shortest wa.
-------------------------------------------------------------------------------------------------------------------------

--A Procedure ProcCur is created
create or replace procedure ProcCur is

begin

-- add this  statement otherwise  ORU-10027: buffer overflow will rise
  dbms_output.enable(1000000);

-- column header printed
  dbms_output.put_line(lpad('Code',10,' ')||lpad('Name',60,' '));

-- loop started
  for rec in (select emp_code,emp_name from employee_master
              where emp_grade='S'
              order by emp_code)

  loop

-- fetched values printed
    dbms_output.put_line(lpad(rec.emp_code,10,' ')||lpad(rec.emp_name,60,' '));

-- end of loop
  end loop;

--end of procedure
end TestProc;