Using PL/SQL Packages - Exercise

Contact Us or call 1-877-932-8228
Using PL/SQL Packages - Exercise

Using PL/SQL Packages

Duration: 10 to 15 minutes.
  1. Recall the stored procedure, function, and anonymous block from the previous lesson.
       phone_number_function1.sql
       employee_report_procedure.sql
       anonymous_block.sql
    
    The content of these files is listed under exercise code below. Review these files.
  2. Create a package and package body called reports.
  3. Add the procedure and function to the package and package body.
  4. Drop the procedure and the function that are outside the package (if needed).
       DROP FUNCTION format_phone2;
       DROP PROCEDURE employee_report;
    
  5. Modify the anonymous block to call the procedure within the package.

Code Sample:

Packages/Exercises/phone_number_function1.sql
create or replace 
function format_phone
(p_phone_number in employees.phone_number%type
)
return varchar2
is 
  v_phone employees.phone_number%type;
begin

  IF length(p_phone_number)     = 12
  and instr(p_phone_number,'.') = 4
  and instr(p_phone_number,'.', 5) = 8
  THEN
    v_phone := '(' || p_phone_number;
    v_phone := replace(v_phone, '.' ,') ');
    v_phone := substr(v_phone, 1,9) || '-' || substr(v_phone, 12);
    return v_phone;
  ELSE
    return p_phone_number;
  END IF;

END;
/

Code Explanation

Code Sample:

Packages/Exercises/employee_report_procedure.sql
create or replace procedure employee_report
(c in out sys_refcursor
)
is
begin  
  open c for 
  select 
    last_name, 
    first_name, 
    format_phone(phone_number) as phone 
  from employees 
  order by last_name, first_name;
  
end;
/

Code Explanation

Code Sample:

Packages/Exercises/anonymous_block.sql
DECLARE
  c1 sys_refcursor;
  fname employees.first_name%type;
  lname employees.last_name%type;
  phone employees.phone_number%type;
  tab CHAR(1) := CHR(9);
BEGIN
 employee_report(c1);
 loop
    fetch c1 into fname, lname, phone;
    exit when c1%notfound;
    dbms_output.put_line(fname || ' ' || fname || tab || phone);
  end loop;
  close c1;
END;
/

Code Explanation

Solution:

Packages/Solutions/package_specification.sql
create or replace package reports is

  procedure employee_report
  (c in out sys_refcursor);

  function format_phone
  (p_phone_number in employees.phone_number%type)
  return varchar2;

end reports;
/

Code Explanation

Solution:

Packages/Solutions/package_body.sql
create or replace package body reports is

procedure employee_report
(c in out sys_refcursor)
is
begin
  
  open c for 
  select 
    last_name, 
    first_name, 
    reports.format_phone(phone_number) phone 
  from employees 
  order by last_name, first_name;
  
end employee_report;


function format_phone
(p_phone_number in employees.phone_number%type)
return varchar2
is 
  v_phone employees.phone_number%type;
begin

  IF length(p_phone_number)     = 12
  and instr(p_phone_number,'.') = 4
  and instr(p_phone_number,'.', 5) = 8
  THEN
    v_phone := '(' || p_phone_number;
    v_phone := replace(v_phone, '.', ') ');
    v_phone := substr(v_phone, 1, 9) || '-' || substr(v_phone, 12);
    return v_phone;
  ELSE
    return p_phone_number;
  END IF;

END format_phone;

end reports;
/

Code Explanation

Solution:

Packages/Solutions/anonymous_block.sql
DECLARE
  c1 sys_refcursor;
  fname employees.first_name%type;
  lname employees.last_name%type;
  phone employees.phone_number%type;
  tab CHAR(1) := CHR(9);
BEGIN
 reports.employee_report(c1);
 loop
    fetch c1 into fname, lname, phone;
    exit when c1%notfound;
    dbms_output.put_line(fname || ' ' || lname || tab || phone);
  end loop;
  close c1;
END;
/

Code Explanation

Next