• +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Live Chat
Follow Us:

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