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 only defined within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside of that enclosing block.
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.
You can define both procedures and functions in the declaration section of any kind of PL/SQL block, be it anonymous, procedure, or function. You may not, however, define anonymous blocks (how would you call them?) or packages (maybe in a future release) in a declaration section.
The rest of this section explores the benefits of local modules and offers a number of examples.
2. What are the Benefits of Local Modularization?
There are two central reasons to create local modules:
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 the impact by 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. 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.
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 to make it easier to follow the logic of the main body of the parent module.
Reducing Code Volume
Let's take a look at an example. 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
BEGIN
:profile.food_sales_stg :=
TO_CHAR ((sales_pkg.food_sales / tot_sales_in ) * 100,
'$999,999');
:profile.service_sales_stg :=
TO_CHAR ((sales_pkg.service_sales / tot_sales_in ) * 100,
'$999,999');
: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 in code size 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 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 below:
PROCEDURE calc_percentages (tot_sales_in IN NUMBER)
IS
/* Define a function right inside the procedure! */
FUNCTION char_format (val_in IN NUMBER) RETURN VARCHAR2
IS
BEGIN
RETURN TO_CHAR ((val_in/tot_sales_in ) * 100, '$999,999');
END;
BEGIN
:profile.food_sales_stg := char_format (sales_pkg.food_sales);
:profile.service_sales_stg := char_format (sales_pkg.service_sales);
:profile.toy_sales_stg := char_format (sales_pkg.toy_sales);
END;
All 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.
No comments:
Post a Comment