Lab 10: - PL/SQL subprograms.

For this exercise, you will create a table and populate it with hundreds of unique rows of test data. These rows of data will based upon 10 rows of data in a table in my schema. You will modify them slightly to make them unique by using Oracle functions in PL/SQL.

This is a common way for database software developers to generate large volumes of data for bulk testing. Perhaps you may also find some techniques here useful for your semester projects.

Step 1 - create table

Create a PHONE_USERS table to store the results of your procedure. The table is defined as follows: 
SQL> desc phone_users
 Name                            Null?    Type
 ------------------------------- -------- ----
 TELEPHONE_NUMBER                NOT NULL VARCHAR2(80)
 FIRST_NAME                               VARCHAR2(80)
 LAST_NAME                                VARCHAR2(80)
 KEYMAP_LASTNAME                          CHAR(4)
 PASSWORD                                 VARCHAR2(80)
The column TELEPHONE_NUMBER should be defined as the primary key. Also, create a unique constraint on LAST_NAME.

Step 2 - create function

Function name: KEYMAP

Input Parameter: VARCHAR, any string which will usually be a person's last name

Output value: VARCHAR, a 4 character string which represents their telephone extension. For purposes of this lab, the telephone extension will be based on the first four characters of their last name, 2 = ABC, 3 = DEF, 4 = GHI, 5=JKL, 6=MNO, 7=PQRS, 8=TUV, 9=WXYZ just like the letters on a telephone keypad. For names shorter than 4 characters, use zeros in those character positions. The Oracle function TRANSLATE will be helpful.

Step 3 - create procedure

You are to write a PL/SQL function that will iterate n times where n is the parameter you pass to your procedure. For each iteration of your procedure you will copy the rows of data from DROBERTS.BBT_USERS_TEMP into your PHONE_USERS table.

As you insert this data you should perform the following transformations:
  1. Each TELEPHONE_NUMBER should be unique. Make it is unique any way you want as long as it looks like a telephone number in the format (999) 999-9999.

  2. LAST_NAME should be LAST_NAME with 4 digits appended to the end. These digits will be 0001 through 000n where n is the iteration.

  3. KEYMAP_LASTNAME should be computed. It will be the telephone keymap for the 1st four letters of the last name. Use the function you wrote in step 2.

  4. All other columns will be copied exactly as they appear in the DROBERTS.BBT_USERS_TEMP table.

  5. Extra columns in DROBERTS.BBT_USERS_TEMP should be disregarded.

  6. Before your loop starts inserting data into PHONE_USERS, truncate this table table. Truncate is not DML so you can't use it like an insert/update/delete statement in PL/SQL. You must use Oracle's DBMS_SQL package or EXECUTE IMMEDIATE.

Step 4 - create package (Optional)

Create a package which contains both your function and your procedure.

Turn this in:

Print out your PL/SQL source for your function and procedure (or optionally just your package and package body code which would contain both). Also execute this in SQL*Plus and turn in the spool file: 
spool lab10.out
execute myproc(10);
select count(*) from phone_users;
select * from phone_users 
  where telephone_number = 
  (select max(telephone_number) from phone_users);
spool off
Note that myproc is whatever the name of your stored procedure.