Defer execution until needed
Just because you have a declaration section positioned "before" your execution section does not mean that you should declare all your program's variables there. It is quite possible that some actions taken in that section (the declarations themselves or the defaulting code) are not always needed and should not always be run on startup of the block.
Consider the following block of code:PROCEDURE always_do_everything (criteria_in IN BOOLEAN) IS big_string VARCHAR2(32767) := ten_minute_lookup (...); big_list list_types.big_strings_tt := two_minute_number_cruncher (...); BEGIN IF NOT criteria_in THEN use_big_string (big_string); process_big_list (big_list); ELSE /* Nothing big going on here */ ... END IF; END;
In this code, I declare a big string and call a function that takes ten minutes of elapsed time and lots of CPU time to assign the default value to that string. I also declare and populate a collection (via a package-declared table TYPE), again relying on a CPU-intensive function to populate that list. I take both of these steps because I know that I need to use the big_string and big_list data structures in my programs.
Then I write my execution section, run some initial tests, and everything seems OK—except that it runs too slowly. I decide to walk through my code to get a better understanding of its flow. I discover something very interesting: my program always declares and populates the big_string and big_list structures, but it doesn't use them unless criteria_in is FALSE, which is usually not the case!
Once I have this more thorough understanding of my program's logical flow, I can take advantage ofnested blocks (an anonymous block defined within another block of code) to defer the penalty of initializing my data structures until I am sure I need them. Here is a reworking of my inefficient program:PROCEDURE only_as_needed (criteria_in IN BOOLEAN)IS PROCEDURE heavy_duty_processing IS big_string VARCHAR2 (32767) := ten_minute_lookup (...); big_list list_types.big_strings_tt := two_minute_number_cruncher (...); BEGIN use_big_string (big_string); process_big_list (big_list); END;BEGIN IF NOT criteria_in THEN heavy_duty_processing; ELSE /* Nothing big going on here */ ... END IF;END;
One other advantage of this approach is that when the nested block terminates, the memory associated with its data structures is released. This behavior would come in handy in the above example if I needed to perform more operations in my program after I am done with my "big" variables. With the former approach, memory would not have been released until the entire program was done.