Steven Feuerstein
OTJ, Winter 1996
In this first article for his new bi-monthly column, Steven and his students search for the "perfect" loop.
Whew. It is Friday afternoon and I have just finished a three-day training session with a group of novice PL/SQL developers. It can be a real challenge to work with newcomers to PL/SQL because it is so difficult not to make assumptions about what a person knows and can recognize and appreciate at a glance. I am very happy, on the other hand, to have the opportunity to interact with less-experienced programmers. It makes me appreciate PL/SQL even more - and reinforces to me the importance of the best practices in PL/SQL development.
About halfway through this training class, I discovered that half of the group was not following what I believed to be the easy stuff. The other half was keeping up, but just barely. So I stopped presenting new material and instead wrote up an extra set of exercises. We then spent a day together writing code and working through silly syntax mistakes and the trials and tribulations of developing PL/SQL in Notepad for SQL*Plus execution. We explored different techniques, identifying risky pathways in PL/SQL and discovering the leanest, cleanest solutions. It was a fantastic experience, and it has led me to rethink my entire approach to teaching PL/SQL.
It also made me realize how even the simplest-sounding exercise can be very tricky and intimidating for a beginning PL/SQL developer. I thought you might be interested in one of these exercises and the path we followed to the "best" solution. I hope you find it as entertaining and enlightening as we did.
The second question on my exercise sheet was: "Write a procedure which displays the first Tuesday in each month of the current year." I thought that this task was pretty much a piece of cake - an easy way for the students to practice writing a loop. What I completely underestimated was the level of knowledge of PL/SQL date built-ins required to answer the problem and the range of possible implementations.
Does the exercise sound like a cinch to you? We came up with solutions ranging from 10 lines in length to a single program call inside a loop. Give it a try before reading further, then compare your answer to what follows.
The most important built-in to know about to answer this question is NEXT_DAY, which accepts a date and a day name, then returns the date on which that day next falls. For example, as I write this article, SYSDATE is Friday, September 20, 1996. So in SQL*Plus, the use of NEXT_DAY produces the following result:
SQL> exec p.l (NEXT_DAY (SYSDATE,
'MON'));
September 23, 1996 17:26:12
Which happens to be my birthday! (The p.l procedure is, by the way, PL/Vision's substitute for DBMS_OUTPUT.PUT_LINE. Check out www.revealnet.com and www.ora.com for information on how to obtain a copy of PL/Vision.)
Well, let's take a stab at solving the exercise with a top-down approach - and refining that solution. Then we'll look at one of the answers reached by a particularly creative student.
The numeric FOR loop executes once for each month in the year and displays the first Tuesday of the month. Of course, all of the interesting aspects of this exercise are buried in the display-first-Tuesday pseudocode, but it can be very useful to take your implementation a step at a time. Now we'll explore the construction of the display-first-Tuesday date.
The first impulse of many of my students was to (a) use the month number to create a date equal to the first day of each month and then (b) use NEXT_DAY to move to the next Tuesday. Following is the type of code I saw that day:
a) Create the first day of the month from the month number:
v_date := TO_DATE ('01' || TO_CHAR (mth)
|| TO_CHAR (SYSDATE, 'YY'), 'DDMMYY');
b) Use next_day to move to the next Tuesday:
v_date := NEXT_DAY (v_date, 'TUE');
Put it together and what have you got? An anonymous block that looks like this:
DECLARE
v_date DATE;
BEGIN
FOR mth IN 1 .. 12
LOOP
v_date := TO_DATE ('01' || TO_CHAR
(mth) || TO_CHAR (SYSDATE, 'YY'),
'DDMMYY');
v_date := NEXT_DAY (v_date, 'TUE');
p.l (v_date);
END LOOP;
END;
/
Now, I don't know about you, but although I had my reservations about whether this implementation was optimal, it looked all right to me. Construct a first-day-of-month string from the month number and SYSDATE and use TO_DATE to convert it, supplying a matching format mask, then move to the next Tuesday.
When I ran the tues1st.sql script, however, I received the following error:
SQL> @tues1st
DECLARE
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 6
It took me a little while to find the cause of the problem. I tested my hypothesis by changing the fourth line to:
FOR mth IN 10 .. 12
I then re-executed tues1st.sql and got more reasonable results:
SQL> @tues1st
October 8, 1996 00:00:00
November 5, 1996 00:00:00
December 3, 1996 00:00:00
In other words, the program works fine when the month number contains two digits. The problem is that the concatenation used to construct the first-day string does not take into account single-digit months. When the month number is 3, for example, tues1st.sql tries to convert the following string:
01396
using the mask "MMDDYY." Because 39 is not a valid month number, the conversion fails. To correct this flaw, you would have to use LPAD to pad the left of the month number to a length of two with zeroes. That process sounds ugly and cumbersome. Fortunately, one of my students made a timely discovery: You can get the first day of the month from the month number using the following date mask:
TO_DATE (TO_CHAR (mth), 'MM')
With this change, my tues1st.sql script boils down to the following:
DECLARE
v_date DATE;
BEGIN
FOR mth IN 1 .. 12
LOOP
v_date := NEXT_DAY (TO_DATE (TO_CHAR
(mth), 'MM'), 'TUE');
p.l (v_date);
END LOOP;
END;
/
and the results are very promising indeed:
SQL> @tues1st
January 2, 1996 00:00:00
February 6, 1996 00:00:00
March 5, 1996 00:00:00
April 2, 1996 00:00:00
May 7, 1996 00:00:00
June 4, 1996 00:00:00
July 2, 1996 00:00:00
August 6, 1996 00:00:00
September 3, 1996 00:00:00
October 8, 1996 00:00:00
November 5, 1996 00:00:00
December 3, 1996 00:00:00
It was very tempting at this point to celebrate success and move on to the next exercise. Fortunately, yet another sharp-eyed student pointed out a slight problem: "How can October 8 be the first Tuesday in the month?" she asked. "Seems to me that the day number of the first Tuesday should never be greater than seven."
Whoops. We definitely had a problem on our hands. Our algorithm was badly flawed. We started our search for the first Tuesday in a month from the first day in that month. If that day was, in fact, a Tuesday, we ended up displaying the second Tuesday in the month. We should have been looking for the next Tuesday from the last day of the previous month, not the first day of the current month. Fortunately, the solution was clear to any of us who worked extensively with PL/SQL: Use the LAST_DAY function to get the last day of the previous month (which can be achieved by calling ADD_MONTHS and shift back one month in the past):
Now when I execute tues1st.sql, I receive the following output: SQL> @tues1st January 2, 1996 00:00:00 February 6, 1996 00:00:00 March 5, 1996 00:00:00 April 2, 1996 00:00:00 May 7, 1996 00:00:00 June 4, 1996 00:00:00 July 2, 1996 00:00:00 August 6, 1996 00:00:00 September 3, 1996 00:00:00 October 1, 1996 00:00:00 November 5, 1996 00:00:00 December 3, 1996 00:00:00 So my program now works properly. But is it the best solution? Does it use the least amount of code to get the job done in a readable, maintainable way? To my mind, the script uses an awful lot of built-ins and a complex sequence of conversions to get the answer. It all makes sense, but does it really have to be that complicated?
With all of the talk lately about thin-client architectures, I thought it might be appropriate to offer a thin-code solution. A thin-code implementation fully leverages the elements of the PL/SQL language without sacrificing readability. And there are opportunities to put the tues1st.sql script on a diet. Consider, for example, the steps I took to get to the last day of the previous month: Go the first day of the current month, go back one month, and then go to the last day of that month.
This solution sounded so reasonable at the time. Now it sounds like the directions for getting through a maze. Could there be a simpler algorithm? Consider: If we are on the first day of month N, can't we just go back one day to get to the last day of month N-1? But you probably already figured that one out, right? Well, believe me, when you are standing in front of a dozen students, it can be a lot harder to see the obvious.
Other possible improvements? If the only point of the script is to display the first Tuesday, do we really need a local variable, v_date, to store that date? We do if our formula is so complex that the use of v_date comes in handy for showing the steps in the formula. Otherwise v_date becomes unnecessary.
Taking these considerations into account, I can reduce the tues1st.sql script to nothing more than the following code:
BEGIN
FOR mth IN 1 .. 12
LOOP
p.l (NEXT_DAY (TO_DATE (TO_CHAR
(mth), 'MM')-1, 'TUESDAY'));
END LOOP;
END;
/
Steven Feuerstein is the author of Oracle PL/SQL Programming and Advanced Oracle PL/SQL: Programming with Packages (O'Reilly and Associates, 1995 and 1996, respectively) and director of the Oracle Practice for SSC, a systems management consulting firm based in Chicago (www.saraswati.com). Steven is a senior technology officer for RevealNet and coauthor of the Reveal for PL/SQL knowledge server (www.revealnet.com). He is also the developer of PL/Vision, an extensive library of PL/SQL packages available through RevealNet. He writes regular columns on PL/SQL for several Oracle-related newsletters and magazines. You can email Steven at [email protected].