Be a Good Listener

Are you a good listener? When people speak, do you expend more effort figuring out how you will respond than attempting to truly understand what they mean? Being a good listener is, I believe, a sign of respect for others and a skill we should all cultivate. (I know that I need to make more of an effort in this area myself.)

Being a good listener is also a critical skill when a programmer uncovers requirements from users and translates them into code. All too often, we hear what our users say but we do not really listen. The consequence is that we often end up writing code that does not meet their requirements or does so in an inefficient manner. Consider the following example:

CREATE OR REPLACE PROCEDURE remove_dept ( deptno_in IN emp.deptno%TYPE, new_deptno_in IN emp.deptno%TYPE)IS emp_count NUMBER;BEGIN SELECT COUNT(*) INTO emp_count FROM emp WHERE deptno = deptno_in; IF emp_count > 0 THEN UPDATE emp SET deptno = new_deptno_in WHERE deptno = deptno_in; END IF; DELETE FROM dept WHERE deptno = deptno_in;END drop_dept;

This procedure drops a department from the department table, but first reassigns any employees in that department to another. The logic of the program is as follows: If I have any employees in that department, perform the update effecting the transfer. Then delete that row from the department table.

Can you see what is wrong here? Actually, this program is objectionable at two different levels. Most fundamentally, a good part of the code is unnecessary. If an UPDATE statement does not identify any rows to change, it does not raise an error; it simply doesn't do anything. So the remove_dept procedure could be reduced to nothing more than:

CREATE OR REPLACE PROCEDURE remove_dept ( deptno_in IN emp.deptno%TYPE, new_deptno_in IN emp.deptno%TYPE)IS emp_count NUMBER;BEGIN UPDATE emp SET deptno = new_deptno_in WHERE deptno = deptno_in; DELETE FROM dept WHERE deptno = deptno_in;END drop_dept;

Suppose, however, that it really is necessary to perform the check for existing employees. Let's take a closer look at what really is going on here. The question I need to answer is "Is there at least one employee?", yet if you look closely at my code, the question I really answer is "How many employees do I have?" I can transform the answer to that question into the answer to my first question with a Boolean expression (emp_count > 0), but in the process I may have gone overboard in my processing.

There are, in fact, a number of ways to answer the question "Do I have at least one of X?" The path you take may have a serious impact on performance. For a comparison of these different approaches, try out the atleastone.sql script available from the O'Reilly site.

The beginning of the atleastone.sql script creates a rather large copy of the employee table; this code is commented out to avoid the overhead of this step when the table is already in place. You will want to uncomment this section the first time you try the script.

Here's my conclusion from running this script: using a straightforward explicit cursor to fetch a single time and determine that there is at least one item is a very efficient (though not quite the most efficient) and very readable approach. Most importantly, it is also responsive to the question being asked—that is, the user requirements.