CIT325: Lab 4 Instructions
Lab #4: Oracle Assignment
Objectives
The lab is designed to teach you how to work with loops and nested loops in the scope of an anonymous PL/SQL programs. The anonymous block PL/SQL program should achieve these objectives:
- Learn how to iterate forward in a loop.
- Learn how to iterate backward in a loop.
- Learn how to couple the behavior of an outer and inner loop.
- Learn how to print a text file as a final outcome.
Business Scenario
Application development involves writing and testing software. After you master basic programming structures, you begin writing programs that do things over and over again – iteration; and you begin to use collections to manage variable sets.
The lab is designed to teach you how to write a small PL/SQL program that demonstrates how you write dependent loops that move forward and backward through collections.
Help Section
The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.
Instructional Material →
The following code shows you how to loop across a range of values.
- How to read forward through a range of values in a loop.
1 2 3 4 5 6 7 8 9 10 11 12
BEGIN /* Read forward through a range of values. */ FOR i IN 1..10 LOOP /* Print values right aligned. */ IF i < 10 THEN dbms_output.put_line('Value of "i" is: ['||i||']'); ELSE dbms_output.put_line('Value of "i" is: ['||i||']'); END IF; END LOOP; END; /
It prints:
VALUE OF "i" IS: [1] VALUE OF "i" IS: [2] VALUE OF "i" IS: [3] VALUE OF "i" IS: [4] VALUE OF "i" IS: [5] VALUE OF "i" IS: [6] VALUE OF "i" IS: [7] VALUE OF "i" IS: [8] VALUE OF "i" IS: [9] VALUE OF "i" IS: [10]
- How to read backward through a range of values in a loop.
1 2 3 4 5 6 7 8 9 10 11 12
BEGIN /* Read backward through a range of values. */ FOR i IN REVERSE 1..10 LOOP /* Print values right aligned. */ IF i < 10 THEN dbms_output.put_line('Value of "i" is: ['||i||']'); ELSE dbms_output.put_line('Value of "i" is: ['||i||']'); END IF; END LOOP; END; /
It prints:
VALUE OF "i" IS: [10] VALUE OF "i" IS: [9] VALUE OF "i" IS: [8] VALUE OF "i" IS: [7] VALUE OF "i" IS: [6] VALUE OF "i" IS: [5] VALUE OF "i" IS: [4] VALUE OF "i" IS: [3] VALUE OF "i" IS: [2] VALUE OF "i" IS: [1]
- How to read through an ordered list of values in a for loop.
This example shows you how to read through a collection of an object type. It’s very similar to how you read through a collection of a record type in Lab 3. The difference between the two approaches is simple. You declare an object type as a SQL data type, and you declare a record type inside a subsequent PL/SQL programming block.
You can declare a collection as a SQL collection type or as a PL/SQL collection type. As a rule, you declare the collection as a SQL type when you plan to use it in several programs. You declare the collection inside a PL/SQL type when you plan to use it in one or a few programs. The example declares the object type as a SQL type and the collection as a PL/SQL type.
The solution now has a SQL component and a PL/SQL component:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
-- You declare a days object type of a number and string in SQL with the following syntax. CREATE OR REPLACE TYPE weekday IS OBJECT ( xnumber NUMBER , xtext VARCHAR2(9)); / -- You would implement the anonymous PL/SQL block with the following code. DECLARE /* Declare an array of days and gifts. */ TYPE days IS TABLE OF weekday; /* Initialize the collection of days. */ lv_days DAYS := days( weekday(1,'Sunday') , weekday(2,'Monday') , weekday(3,'Tuesday') , weekday(4,'Wednesday') , weekday(5,'Thursday') , weekday(6,'Friday') , weekday(7,'Saturday')); BEGIN /* Read forward through the contents of the loop. */ FOR i IN 1..lv_days.COUNT LOOP dbms_output.put_line('Value of "day" is: ['||lv_days(i).xtext||']'); END LOOP; END; /
It prints:
VALUE OF "day" IS: [Sunday] VALUE OF "day" IS: [Monday] VALUE OF "day" IS: [Tuesday] VALUE OF "day" IS: [Wednesday] VALUE OF "day" IS: [Thursday] VALUE OF "day" IS: [Friday] VALUE OF "day" IS: [Saturday]
- How to read through a list of values that has a sparsely populated index value.
This example shows you how to read through a collection with missing index values. You can delete elements of any table collection after creating the collection. This unfortunately creates a problem. A for-loop expects a densely populated set of index values. A densely populated index is a sequential set of numeric values. That means they are a set of integers that start with a zero or one and increment by one to the maximum number of elements in the collection. All PL/SQL collections start with a one. When you have a set of index values with gaps, the index is sparsely populated.
The following anonymous block:
- Populates the collection with seven sequential values
- Deletes two elements inside a loop based on the collection element count
- Navigates the sparsely populated list by changing the index range
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
DECLARE /* Declare an array of days and gifts. */ TYPE days IS TABLE OF weekday; TYPE weekdays IS TABLE OF weekday; /* Initialize the collection of days. */ lv_days DAYS := days( weekday(1,'Sunday') , weekday(2,'Monday') , weekday(3,'Tuesday') , weekday(4,'Wednesday') , weekday(5,'Thursday') , weekday(6,'Friday') , weekday(7,'Saturday')); lv_weekdays WEEKDAYS := weekdays(); BEGIN /* Remove the weekend elements, which alters the collection. */ FOR i IN 1..lv_days.COUNT LOOP IF lv_days(i).xtext IN ('Saturday','Sunday') THEN lv_days.DELETE(i); END IF; END LOOP; /* Read forward through the contents of the loop. */ FOR i IN 1..lv_days.LAST LOOP IF lv_days.EXISTS(i) THEN dbms_output.put_line('Value of "day" is: ['||i||']['||lv_days(i).xnumber||']['||lv_days(i).xtext); END IF; END LOOP; END; /
It prints the values in their proper order of occurrence:
VALUE OF "day" IS: [2][2][Monday VALUE OF "day" IS: [3][3][Tuesday VALUE OF "day" IS: [4][4][Wednesday VALUE OF "day" IS: [5][5][Thursday VALUE OF "day" IS: [6][6][Friday
Students may want or need supplemental articles that let them review tips and techniques. The following is a function that checks for valid dates inside strings. You should run this type of check after you rule out a number and alphanumeric string.
The lab has one part. The test cases should print the lyrics of The Twelve Days of Christmas.
Lab Description
[25 points] Click the Lab Instructions link to open the instructions inside the current webpage.
Lab Instructions →
The lab is designed to have you enter the unique elements of the Twelve Days of Christmas into collections and then prints the lyrics of the song. This is what your final output should look like:
Twelve Days of Christmas →
On the first day of Christmas my true love sent to me: -A Partridge in a pear tree On the second day of Christmas my true love sent to me: -Two Turtle doves -and a Partridge in a pear tree On the third day of Christmas my true love sent to me: -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the fourth day of Christmas my true love sent to me: -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the fifth day of Christmas my true love sent to me: -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the sixth day of Christmas my true love sent to me: -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the seventh day of Christmas my true love sent to me: -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the eighth day of Christmas my true love sent to me: -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the nineth day of Christmas my true love sent to me: -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the tenth day of Christmas my true love sent to me: -Ten Lords a leaping -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the eleventh day of Christmas my true love sent to me: -Eleven Pipers piping -Ten Lords a leaping -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the twelfth day of Christmas my true love sent to me: -Twelve Drummers drumming -Eleven Pipers piping -Ten Lords a leaping -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree |
You need to write an anonymous block PL/SQL program that uses two collections. One collection implements a list of eight character variable length strings. The other collection implements a list of the following object type, which you need to create as part of this lab:
| Object Type: STRUCT | |||||
|---|---|---|---|---|---|
| Member Name | Member Type | ||||
| day_name | VARCHAR2(8) | ||||
| gift_name | VARCHAR2(24) | ||||
The lab requires you to implement two loops. One runs in ascending order and the other in descending order. You need to couple the behavior of the two loops to solve this problem.
The collection that implements a list of eight character variable length strings should contain:
- “first”
- “second”
- “third”
- “fourth”
- “fifth”
- “sixth”
- “seventh”
- “eighth”
- “ninth”
- “tenth”
- “eleventh”
- “twelfth”
The collection of the object type should include the unique elements of the lyrics, like:
- “and a” “Partridge in a pear tree”
- “Two” and “Turtle doves”
- “Three” and “French hens”
- “Four” and “Calling birds”
- “Five” and “Golden rings”
- “Six” and “Geese a laying”
- “Seven” and “Swans a swimming”
- “Eight” and “Maids a milking”
- “Nine” and “Ladies dancing”
- “Ten” and “Lords a leaping”
- “Eleven” and “Pipers piping”
- “Twelve” and “Drummers drumming”
All other values are repetitive strings. They should be embedded inside calls to the put_line function calls of the dbms_output package. You should also note that the first day of Christmas is a capital “A” as opposed to “and a” when it is the first day of Christmas. You can print line breaks between the verses by referring to a CHR(13), like:
DECLARE ... BEGIN ... dbms_output.put_line(CHR(13)); END; / |
Some documentation advises using a CHR(10), which works in a Windows environment. It works because Windows actually transmits a line break and carriage return. Using a CHR(13) works in Linux because it only transmits a carriage return.
Test Case
Click the Test Case Instructions link to open the test case instructions inside the current webpage.
Test Case Instructions →
You test the apply_plsql_lab4.sql script it’s log file for the lyrics of the The Twelve Days of Christmas. It should print the following in the log file:
On the first day of Christmas my true love sent to me: -A Partridge in a pear tree On the second day of Christmas my true love sent to me: -Two Turtle doves -and a Partridge in a pear tree On the third day of Christmas my true love sent to me: -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the fourth day of Christmas my true love sent to me: -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the fifth day of Christmas my true love sent to me: -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the sixth day of Christmas my true love sent to me: -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the seventh day of Christmas my true love sent to me: -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the eighth day of Christmas my true love sent to me: -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the nineth day of Christmas my true love sent to me: -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the tenth day of Christmas my true love sent to me: -Ten Lords a leaping -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the eleventh day of Christmas my true love sent to me: -Eleven Pipers piping -Ten Lords a leaping -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree On the twelfth day of Christmas my true love sent to me: -Twelve Drummers drumming -Eleven Pipers piping -Ten Lords a leaping -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree |
