Improving readability

You can use local modules to dramatically improve the readability and maintainability of your code. In essence, local modules allow you to follow "top-down design" or "stepwise refinement" methodologies very closely. You can also use the same technique to "decompose" or "refactor" an existing program so that it is more readable.

The bottom-line result of using local modules in this way is that you can dramatically reduce the size of your execution sections (you are transferring many lines of logic from an "inline" location in the execution section to a local module callable in that section). By keeping your execution sections small, you will find that it is much easier to read and understand the logic.

I suggest that you adopt as a guideline in your coding standards that execution sections of PL/SQL blocks be no longer than 60 lines (the amount of text that can fit on a screen or page). This may sound crazy, but if you follow the techniques in this section, you will find it not only possible but highly advantageous.

Suppose that I have a series of WHILE loops (some of them nested) whose bodies contain a series of complex calculations and deep nestings of conditional logic. Even with extensive commenting, it can be difficult to follow the program flow over several pages, particularly when the END IF or END LOOP of a given construct is not even on the same page as the IF or LOOP statement that began it.

In contrast, if you pull out sequences of related statements, place them in one or more local modules, and then call those modules in the body of the program, the result is a program that can literally document itself. The assign_workload procedure offers a simplified version of this scenario that still makes clear the gains offered by local modules:

PROCEDURE assign_workload (department_in IN emp.deptno%TYPE) IS CURSOR emps_in_dept_cur (department_in IN emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno = department_in; PROCEDURE assign_next_open_case (emp_id_in IN NUMBER, case_out OUT NUMBER) IS BEGIN ... full implementation ... END; FUNCTION next_appointment (case_id_in IN NUMBER) RETURN DATE IS BEGIN ... full implementation ... END; PROCEDURE schedule_case (case_in IN NUMBER, date_in IN DATE) IS BEGIN ... full implementation ... END; BEGIN /* main */ FOR emp_rec IN emps_in_dept_cur (department_in) LOOP IF analysis.caseload (emp_rec.emp_id) < analysis.avg_cases (department_in); THEN assign_next_open_case (emp_rec.emp_id, case#); schedule_case (case#, next_appointment (case#)); END IF; END LOOPEND assign_workload;

The assign_workload procedure has three local modules:

assign_next_open_casenext_appointmentschedule_case

It also relies on two packaged programs that already exist and can be easily plugged into this program: analysis.caseload and analysis.avg_cases. For the purposes of understanding the logic behind assign_workload, it doesn't really matter what code is executed in each of them. I can rely simply on the names of those modules to read through the main body of this program. Even without any comments, a reader can still gain a clear understanding of what each module is doing. Of course, if you want to rely on named objects to self-document your code, you'd better come up with very good names for the functions and procedures.