1. Worksheets
The purpose of this weeks worksheet is to provide additional practice. Please attempt the worksheet without using provided answers.
2. Questions and Completion
To receive credit for completing the worksheet, you must say ‘Done’ on the worksheet folder.
If you have questions as you go through this worksheet, please feel free to email the instructor.
3. Simple PL/SQL Function
Create a function called format_phone. It will format the following string 123.456.7890 so that it looks like a U.S. phone number (123) 456-7890.
Note 1: Add two variables of type VARCHAR2 and length 50. The first variable will hold an unformatted phone number and the second a formatted phone number. Initialize the first variable to 123.456.7890.
Note 2: Look up PL/SQL functions replace and substr.
4. Parametric Function
Create a table Employee with last_name varchar2(50), first_name varchar2(50), phone_number varchar(13). Next insert some sample data of form (‘Joe’, ‘Smith’,’123.456.7890’) and (‘Chris’, ‘White’, ‘324.675.2344’)
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. In this function map the variables created in Exercise #3 to variable of type Employee phone number. Finally use the function to format all phone numbers in Employee to a U.S. phone number format.
5. Regex Function
Replace the function created above with one that uses regular expressions.
Look into regexp-instr and regexp_replace.
6. Check answers
Answers are here, but look at these answers after you have tried the questions.