--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;
