Oracle PL/SQL Practical

Write a macro containing an anonymous PL/SQL block which uses cursor processing to retrieve rows from the SCOTT.EMP table, one by one, to perform the task set out below:

 each time a ‘CLERK’ is found in a row call a procedure which outputs to the screen the name and office location of that clerk’s manager and indicate whether the manager is on-site or in a different location to the clerk.

Note: The location information can be found in SCOTT.DEPT

The output to SQL*Plus should be ensured in the macro.

Code

--ensure output
set serveroutput on size 1000000
--anonymous block
-- declare
varJob scott.emp.job%type := 'CLERK';
-- Main Cursor
--
cursor curScott is     select * from scott.emp;   -- Variable of cursor rowtype
--
varScott curScott%rowtype;
--
-- Subprogram declaration
-- Subprogram takes an Empno to find the employee's manager and
compares the manager's location to the employee’s place of work.
--
procedure procEmp (parEmpno number) is        varmgr_name scott.emp.ename%type;
varmgr_loc scott.dept.loc%type;       varemp_loc scott.dept.loc%type;     begin
-- Get Mgr and Loc of employee in question, and that manager's name and loc
--
select  managers.ename,   locmgr.loc, locemp.loc
into  varmgr_name,  varmgr_loc, varemp_loc from
scott.emp employee,  scott.emp managers, scott.dept locmgr,
scott.dept locemp where employee.empno = parEmpno and
employee.mgr = managers.empno and managers.deptno = locmgr.deptno
and employee.deptno = locemp.deptno;
-- Compare locations and output information
--
if varmgr_loc <> varemp_loc then
dbms_output.put_line(varmgr_name||' manages '||parEmpno||' off-site in
'||varmgr_loc); else  dbms_output.put_line(varmgr_name||' manages '||parEmpno||'
on-site in  '||varmgr_loc); --test  end if;  end procEmp; begin
-- Start main cursor processing   open curScott;   loop
fetch curScott into varScott;
exit when curScott%notfound;
if varScott.job = varJob        then
-- Call to subprogram
--
procEmp(varScott.empno);        end if;    end loop;   close curScott; end; /