Section 17.5.  Local Modules

17.5. Local Modules A local module is a procedure or function that is defined in the declaration section of a PL/SQL block (anonymous or named). This module is considered local because it is defined only within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside that enclosing block. Figure 17-4 shows how blocks that are external to a procedure definition cannot "cross the line" into the procedure to directly invoke any local procedures or functions. Figure 17-4. Local modules are hidden and inaccessible outside the program The syntax for defining the procedure or function is exactly the same as that used for creating standalone modules. The following anonymous block, for example, declares a local procedure: DECLARE PROCEDURE show_date (date_in IN DATE) IS BEGIN DBMS_OUTPUT.PUT_LINE (TO_CHAR (date_in, 'Month DD, YYYY'); END; BEGIN ... END; Local modules must be located after all of the other declaration statements in the declaration section. You must declare your variables, cursors, exceptions, types, records, tables, and so on before you type in the first PROCEDURE or FUNCTION keyword. The following sections explore the benefits of local modules and offer a number of examples. 17.5.1. Benefits of Local Modularization There are two central reasons to create local modules: To reduce the size of the module by stripping it of repetitive code This is the most common motivation to create a local module; you can see its impact in the next example. The code reduction leads to higher code quality because you have fewer lines to test and fewer potential bugs. It takes less effort to maintain the code because there is less to maintain. And when you do have to make a change, you make it in one place in the local module, and the effects are felt immediately throughout the parent module. To improve the readability of your code Even if you do not repeat sections of code within a module, you still may want to pull out a set of related statements and package them into a local module. This can make it easier to follow the logic of the main body of the parent module. The following sections examine these benefits. 17.5.1.1 Reducing code volume Let's look at an example of reducing code volume . The calc_percentages procedure takes numeric values from the sales package (sales_pkg), calculates the percentage of each sales amount against the total sales provided as a parameter, and then formats the number for display in a report or form. The example you see here has only three calculations, but I extracted it from a production application that actually performed 23 of these computations! PROCEDURE calc_percentages (tot_sales_in IN NUMBER) IS l_profile sales_descriptors%ROWTYPE; BEGIN l_profile.food_sales_stg := TO_CHAR ((sales_pkg.food_sales / tot_sales_in ) * 100, '$999,999'); l_profile.service_sales_stg := TO_CHAR ((sales_pkg.service_sales / tot_sales_in ) * 100, '$999,999'); l_profile.toy_sales_stg := TO_CHAR ((sales_pkg.toy_sales / tot_sales_in ) * 100, '$999,999'); END; This code took a long time (relatively speaking) to write, is larger than necessary, and is maintenance-intensive. What if I need to change the format to which I convert the numbers? What if the calculation of the percentage changes? I will have to change each of the individual calculations. With local modules, I can concentrate all the common, repeated code into a single function, which is then called repeatedly in calc_percentages. The local module version of this procedure is shown here: PROCEDURE calc_percentages (tot_sales_in IN NUMBER) IS l_profile sales_descriptors%ROWTYPE; /* Define a function right inside the procedure! */ FUNCTION pct_stg (val_in IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN TO_CHAR ((val_in/tot_sales_in ) * 100, '$999,999'); END; BEGIN l_profile.food_sales_stg := pct_stg (sales_pkg.food_sales); l_profile.service_sales_stg := pct_stg (sales_pkg.service_sales); l_profile.toy_sales_stg := pct_stg (sales_pkg.toy_sales); END; All of the complexities of the calculation, from the division by tot_sales_in to the multiplication by 100 to the formatting with TO_CHAR, have been transferred to the function pct_stg. This function is defined in the declaration section of the procedure. By calling this function from within the body of calc_percentages, the executable statements of the procedure are much more readable and maintainable. Now, if the formula for the calculation changes in any way, I make the change just once in the function and it takes effect in all the assignments. 17.5.1.2 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 LOOP END assign_workload; The assign_workload procedure has three local modules : assign_next_open_case next_appointment schedule_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. 17.5.2. Scope of Local Modules The modularized declaration section looks a lot like the body of a package, as you will see in Chapter 18. A package body also contains definitions of modules. The big difference between local modules and package modules is their scope. Local modules can be called only from within the block in which they are defined; package modules canat a minimumbe called from anywhere in the package. If the package modules are also listed in the package specification, they can be called by any other program in your application. You should therefore use local modules only to encapsulate code that does not need to be called outside of the current program. Otherwise, go ahead, and create a package! 17.5.3. Sprucing Up Your Code with Local Modules These days it seems that whenever I write a program with more than 20 lines and any complexity whatsoever, I end up creating one or more local modules. Doing so helps me see my way through to a solution much more easily; I can conceptualize my code at a higher level of abstraction by assigning a name to a whole sequence of statements, and I can perform top-down design and stepwise refinement of my requirements. Finally, by modularizing my code even within a single program, I make it very easy to later extract a local module and make it a truly independent, reusable procedure or function. You could also, of course, move that logic out of the local scope and make it a package body-level program of its own (assuming you are writing this code in a package). Taking this approach will reduce the amount of nesting of local procedures, which can be helpful. It also, however, can lead to package bodies with a very long list of programs, many of which are only used within another program. My general principle is to keep the definition of an element as close as possible to its usage, which naturally leads to the use of local modules. I hope that as you read this, a program you have written comes to mind. Perhaps you can go back and consolidate some repetitive code, clean up the logic, and make the program actually understandable to another human being. Don't fight the urge. Go ahead, and modularize your code.