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)
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:
- 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.
- LAST_NAME should be LAST_NAME with 4 digits appended to the end. These digits will be 0001 through 000n where n is the
- 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.
- All other columns will be copied exactly as they appear in the DROBERTS.BBT_USERS_TEMP table.
- Extra columns in DROBERTS.BBT_USERS_TEMP should be disregarded.
- 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
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:
select count(*) from phone_users;
select * from phone_users
where telephone_number =
(select max(telephone_number) from phone_users);
Note that myproc is whatever the name of your stored procedure.