PL/SQL Coupled Loops
A standard approach using fall through in languages that support it, like C, C++, and Java leverages a case structure. Unfortunately, in language that follow the Pascal pattern case statements act like if-else-if-else statements. In the Pascal model, you need to use coupled loops.
PL/SQL follows that Pascal, via Ada, structure. So, you must couple the performance of the inner descending loop to the maximum value of the outer ascending loop. Here’s an example of how you would code that in PL/SQL with the 12 Days of Christmas song, which has a chorus that grows with each verse.
It requires the definition of two ADTs (Attribute Data Types), a UDT (User-Defined Data Type) like a struct(ure), and a UDT table of the UDT struct. They’re all done in this code snippet.
/* Create a list of strings. */ CREATE OR REPLACE TYPE days IS TABLE OF VARCHAR2(8); / /* Create a list of strings. */ CREATE OR REPLACE TYPE song IS TABLE OF VARCHAR2(36); / /* Create a record structure. */ CREATE OR REPLACE TYPE lyric IS OBJECT ( DAY VARCHAR2(8) , gift VARCHAR2(24)); / /* Create a list of the record structure. */ CREATE OR REPLACE TYPE lyrics IS TABLE OF LYRIC; / |
The twelve_days function that will display the lyrics of the 12-Days of Christmas. It reads forward through the 12 days of Christmas and backwards through the chorus in the inner loop beginning with the current day of the outer loop.
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 31 32 33 34 35 36 37 38 39 40 | /* Create a function. */ CREATE OR REPLACE FUNCTION twelve_days ( pv_days DAYS , pv_gifts LYRICS ) RETURN song IS /* Initialize the collection of lyrics. */ lv_retval SONG := song(); /* Local procedure to add to the song. */ PROCEDURE ADD ( pv_input VARCHAR2 ) IS BEGIN lv_retval.EXTEND; lv_retval(lv_retval.COUNT) := pv_input; END ADD; BEGIN /* Read forward through the days. */ FOR i IN 1..pv_days.COUNT LOOP ADD('On the ' || pv_days(i) || ' day of Christmas'); ADD('my true love sent to me:'); /* Read backward through the lyrics based on ascending value of the day. */ FOR j IN REVERSE 1..i LOOP IF i = 1 THEN ADD('- '||'A'||' '||pv_gifts(j).gift); ELSE ADD('- '||pv_gifts(j).DAY||' '||pv_gifts(j).gift); END IF; END LOOP; /* A line break by verse. */ ADD(CHR(13)); END LOOP; /* Return the song's lyrics. */ RETURN lv_retval; END; / |
The typical test case for the function in PL/SQL is:
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | /* Test the function in PL/SQL. */ SET SERVEROUTPUT ON SIZE UNLIMITED DECLARE /* * Declare an lv_days array of an 8 character variable length string * and initialize it with values. */ lv_days days := days('first','second','third','fourth' ,'fifth','sixth','seventh','eighth' ,'nineth','tenth','eleventh','twelfth'); /* * Declare an lv_gifts array of the user-defined LYRIC data type and * initialize it with values. */ lv_gifts lyrics := lyrics(lyric(DAY => 'and a', gift => 'Partridge in a pear tree') ,lyric(DAY => 'Two', gift => 'Turtle doves') ,lyric(DAY => 'Three', gift => 'French hens') ,lyric(DAY => 'Four', gift => 'Calling birds') ,lyric(DAY => 'Five', gift => 'Golden rings' ) ,lyric(DAY => 'Six', gift => 'Geese a laying') ,lyric(DAY => 'Seven', gift => 'Swans a swimming') ,lyric(DAY => 'Eight', gift => 'Maids a milking') ,lyric(DAY => 'Nine', gift => 'Ladies dancing') ,lyric(DAY => 'Ten', gift => 'Lords a leaping') ,lyric(DAY => 'Eleven',gift => 'Pipers piping') ,lyric(DAY => 'Twelve',gift => 'Drummers drumming')); /* * Declare an lv_days array of an 36 character variable length string * and initialize it with values. */ lv_song song := song(); BEGIN /* Call the twelve_days function and assign the results to the local * lv_song variable. */ lv_song := twelve_days(lv_days,lv_gifts); /* * Read the lines from the local lv_song variable and print them. */ FOR i IN 1..lv_song.LAST LOOP dbms_output.put_line(lv_song(i)); END LOOP; END; / |
It displays:
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 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 |
As always, I hope this helps those learning how to write PL/SQL and/or code.
Subscribe via RSS