PL/SQL Subprograms with Parameters - Exercise

Contact Us or call 1-877-932-8228
PL/SQL Subprograms with Parameters - Exercise

PL/SQL Subprograms with Parameters

Duration: 10 to 50 minutes.
  1. Recall the first format_phone function you created in the previous challenge exercise. Review it. NOTE: This is the format_phone function that does NOT use regular expressions. Or, if you did not complete the challenge exercise, review the provided function in the exercises folder.
  2. Modify the function to use an input parameter instead of a hard coded variable value. Compile and test the function using '123.456.7890' as the input parameter.
  3. This function will be used with phone numbers provided in the employees.phone_number field. Modify the input parameter and variables to reflect this.
  4. Some values in the employee phone number field are for international phone numbers. Modify the function so that it only processes U.S. phone numbers in the format reflected in the test value specified previously. If a phone number is not in U.S. format, simply return the original value.
  5. Write a new stored procedure called employee_report that takes a sys_refcursor as an out parameter.
  6. Modify the procedure so that it opens a cursor for a query that returns the employee first name, last name, and formatted phone number from the employees table (sort by the last name and first name).
  7. Write an anonymous block to test the stored procedure.

Code Sample:

Stored-Procedures-and-Functions/Exercises/phone_number_function3.sql
create or replace function format_phone
return varchar2
is 
  v_phone_number VARCHAR2(50) := '123.456.7890';
  v_format_phone_no VARCHAR2(50);
begin

  v_format_phone_no := '(' || v_phone_number;
  
  v_format_phone_no := replace(v_format_phone_no, '.', ') ');
  
  v_format_phone_no := substr(v_format_phone_no, 1, 9) ||
                              '-' ||
                              substr(v_format_phone_no, 12);
  
  return v_format_phone_no;

END;
/

Code Explanation

Solution:

Stored-Procedures-and-Functions/Solutions/phone_number_function3.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   the length of the string is 12
    and  the 4th character (one based) is a period
    and  the 8th character (one based) is a period,
    then it's a U.S. format phone number.
  */
  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;
/

-- Here's a quick way to test that the function works as
-- expected:
select phone_number, format_phone(phone_number)
from employees;

Code Explanation

Solution:

Stored-Procedures-and-Functions/Solutions/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) phone 
  from employees 
  order by last_name, first_name;
  
end;
/

Code Explanation

Solution:

Stored-Procedures-and-Functions/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
 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

Challenge

  1. Replace the function created above with one that uses regular expressions. If you wrote the additional challenge for the last challenge exercise, you can use the regular expression code from that. Otherwise you'll need to write the regular expression code now.
  2. Test the replaced function with the same anonymous block of code you wrote above.

Challenge Solution:

Stored-Procedures-and-Functions/Solutions/phone_number_function4.sql
create or replace function format_phone
(p_phone_number in employees.phone_number%type
)
return varchar2
is
begin

  IF regexp_instr(p_phone_number, '(\d{3})\.(\d{3})\.(\d{4})') = 1
  THEN
    return regexp_replace(p_phone_number, 
                          '(\d{3})\.(\d{3})\.(\d{4})', 
                          '(\1) \2-\3');
  ELSE
    return p_phone_number;
  END IF;

END;
/

Code Explanation

This solution is a bit more robust in that it also validates that specified characters are digits.

Next