Description
SECTION 1: PL/SQL Fundamentals
Q1 (10 Points) For each declaration below, indicate whether it is legal or not. Explain if it is not legal.
v_empno NUMBER(4);
v_a, v_b, v_c VARCHAR2(10);
v_address VARCHAR2(30) NOT NULL;
v_in_stock BOOLEAN := 1;
Save your answers in the file Section1Q1.
Q2 (10 Points) Your first PL/SQL program – Create an anonymous PL/SQL block to output the phrase “My PL/SQL Program
Works” to the screen. Save your code in the file Section1Q2.sql.
Q3 (15 Points) Create an anonymous PL/SQL block that accepts the current salary and raise percentage through SQL*Plus
substitution variables and calculate the new salary based on the input:
new salary := old salary * ( 1 + raise percentage / 100 )
The result should be stored in a PL/SQL variable and printed on the screen.
Your program should handle NULL values. A NULL value entered for either one or both of the input values is equivalent to
a numerical 0 (Note: To associate NULL values for your SQL*Plus substitution variables, just enter NULL.)
Save your code in the file Section1Q3.sql.
Q4 (15 Points) Create an anonymous PL/SQL block that accepts an integer number N through SQL*Plus substitution variable and
then determines for each of the numbers in the range 1 through N inclusive whether it is odd or even. Use the MOD function to
determine whether a number is odd or even. For example, MOD(10,2) = 0 and MOD(11,2) = 1. Print the results on the screen.
Your program should handle NULL values. N should be set to 0 if a NULL value is entered.
Save your code in the file Section1Q4.sql.
SECTION 2: Exceptions and Sub-programs
Q1 (50 Points) Write a PL/SQL procedure, parse_name, which accepts a string representing names and returns the first name, the last
name, and the title. The first name and last name returned from the procedure should be in upper cases while the title returned should
presever the orginal case. The input name string is in one of the following two formats:
FIRST_NAME LAST_NAME TITLE
LAST_NAME, FIRST_NAME TITLE
The first format depicts that the name string starts with a person’s first name and last name with a space in between, followed by
a space, and then the title of the person. On the other hand, the second format states that the name string starts with a person’s
first name, followed by a comma and a space, and then the last name suffixed with the title with a space before it.
The following are examples of valid name strings conforming to the formats:
1) Jane Doe Ms.
2) Doe, Jane Ms.
2
Your procedure should return JANE for first name, DOE for last name, and Ms. for title for the above two input strings.
Also, the following are examples of valid name strings conforming to the formats:
1) Jane Doe MS.
2) Doe, Jane MS.
Your procedure should return JANE for first name, DOE for last name, and MS. for title for the above two input strings.
Your procedure should generate exceptions if any one of the three components of the input name string is missing. Further, a
message should be printed indicating that the input string is invalid. For instance, the following input strings should cause an
exception to be generated and the appropriate messages printed:
1) Doe
2) Jane Doe
3) Doe, Jane
You should wrap parse_name within an anonymous PL/SQL block that accepts the name string through SQL*Plus substitution
variables, calls parse_name with the input string, and then prints the first name, last name, and title returned from the
parse_name procedure. You should print the title first, followed by the first name and then the last name, separated by spaces.
Save your work in the file named Section2Q1.sql.
General Notes: There are two sections to this assignment. Section1, there are four parts to this
section; each part may requires you to submit a file. Section2, there is one part to this section;
this part may requires you to submit a file.
So please create a folder for this assignment and submit an electronic copy of your solution files
of every section/question, all in one folder zipped and named “LastName HW2” and must be
submitted to your D2L/Assignment 2 Submission page. I will give you one submission locations
on the course web site.
Again: For example, for assignment #2, you need to create a folder named your LastName HW2 under
your c: home directory and save the Section1Q1.doc and your script files Section1Q2.sql,
Section1Q3.sql, Section1Q4.sql under the subfolder Section1. Also save your script file Section2Q1.sql,
under the subfolder Section2.
Then zip the folder “LastName HW2” and then submit the zipped file to your D2L/Assignment 2
Submission link
SUBMIT YOUR HW2 FOLDER AS ZIP FILE TO YOUR D2L ASSIGNMENT 2 SUBMISSION LINK
FOR GRADING. Make sure only one copy submitted.
