I can’t remember the last time I wrote a PL/SQL procedure or function or package and did not declare a variable, constant, or type. We all write declarations, but do we all do it in ways that lead to maximum readability/maintainability and top-quality code? Probably not. So here are some tips.
Don’t skimp on the names
Skimp: expend or use less time, money, or material on something than is necessary in an attempt to economize. (Oxford Languages)
Less material as in fewer characters. I say this for two reasons:
- We no longer have the 30-character limit for identifiers, so (a) don’t go crazy with 100-character names, but also (b) don’t be afraid to be fully descriptive and step right over the old 30-character (now psychological) limit. I find this very liberating.
- Don’t be lazy. Don’t use “i” for the loop index. Don’t use var1, var2, var3 for variable names. don’t write code as though it is for university homework. 🙂
Instead of skimping….
- Standardize naming conventions: here at Insum, we use a variation of the wonderful Trivadis guidelines.
- Take the time to come up with names that accurately and clearly reflect what those variables or constants contain.
Use subtypes to give names to application “types”
What’s an application type? It’s an application-specific variant on a pre-defined type. Suppose, for example, I work a lot with prices in my sales application. A price of a product always has the form 99999.99. So I can do this, over and over again:
declare l_price number (7,2);
But I’d rather avoid doing that since, at some point, someone might decide that we will, say, track prices down to the 1000th of a dollar.
Wouldn’t it be nice if I could instead declare the variable as follows:
declare l_price price_t;
declare l_price sales_config.price_t;
Here’s a more elaborate example. I need to write a package body to keep track of multiple lists of strings that have already been “used” in some way in my session. No persistent data, so I will rely on collections. Here’s a version of part of the package body that does not take advantage of subtypes:
create or replace package body string_tracker is type booleans_t is table of boolean index by varchar2(32767); type two_fields_t is record ( case_sensitive boolean, list_of_values booleans_t ); type lots_of_two_fields is table of two_fields_t index by varchar2(32767); g_list_of_lists lots_of_two_fields;
And here’s a version of that which does use subtypes:
create or replace package body string_tracker is subtype list_name_t is varchar2(100); subtype used_name_t is varchar2(1000); subtype only_the_index_matters_t is boolean; type used_aat is table of only_the_index_matters_t index by used_name_t; type list_rt is record ( case_sensitive boolean, list_of_values used_aat ); type list_of_lists_aat is table of list_rt index by list_name_t; g_list_of_lists list_of_lists_aat;
Avoid hard-coding limits (like varchar2(N))
Suppose I need to declare a string variable to hold the largest possible value. It might look like this:
declare l_big_string varchar2(32767);
But this declaration then hard-codes the current maximum length. What if Oracle makes it bigger in the future? You could take this approach instead:
create or replace package app_types authid definer is subtype maxvarchar2_t is varchar2(32767); end; / declare l_big_string app_types.maxvarchar2_t;
Another use for subtypes! 🙂
Use %rowtype to consolidate declarations
Ever see a procedure with a declaration section like this?
procedure review_account ( id_in in integer ) is l_id sg_account.id%type; l_acc_name sg_account.name%type; l_description sg_account.description%type; l_driver_type_id sg_account.driver_type_id%type; l_engine_type_id sg_account.engine_type_id%type; l_output_type_id sg_account.output_type_id%type; l_is_static sg_account.is_static%type; l_sa_object_type_id sg_account.sa_object_type_id%type; l_author sg_account.author%type; l_column_position sg_account.column_position%type; l_output_prefix sg_account.output_prefix%type; l_output_suffix sg_account.output_suffix%type; l_file_extension sg_account.file_extension%type; l_source_file_name sg_account.source_file_name%type; l_created_on sg_account.created_on%type; l_created_by sg_account.created_by%type; l_changed_on sg_account.changed_on%type; l_changed_by sg_account.changed_by%type; l_in_context_name sg_account.in_context_name%type; l_is_locked sg_account.is_locked%type; l_locked_by sg_account.locked_by%type; l_locked_password sg_account.locked_password%type; l_universal_id sg_account.universal_id%type; l_is_top_level sg_account.is_top_level%type; l_app_id sa_application.id%type; l_app_name sa_application.name%type; l_app_description sa_application.description%type; l_refresh_frequency sa_application.refresh_frequency%type; l_def_roadmap_dir sa_application.def_roadmap_dir%type; l_def_script_dir sa_application.def_script_dir%type; l_def_code_dir sa_application.def_code_dir%type; l_def_sequence_prefix sa_application.def_sequence_prefix%type; l_def_sequence_suffix sa_application.def_sequence_suffix%type; l_def_pky_column_name sa_application.def_pky_column_name%type; l_rae_error_code sa_application.rae_error_code%type; l_deploy_dir sa_application.deploy_dir%type; l_use_qda sa_application.use_qda%type;
You see the pattern, right? Let’s work some magic:
procedure review_account ( id_in in integer ) is l_account sg_account%rowtype; l_application sa_application%rowtype;
Ah….so much better. No need to declare each variable individually – and if a column is added to the table, it is automatically available as a field in the record after recompilation.
You might ask: “I only need 10 of the columns, and the table has 500 columns. Won’t that use a bunch of extra memory?”
And I would answer:
- Maybe, but it probably won’t matter.
- You can avoid this problem by declaring an explicit cursor with your 10 columns and then %rowtype against that.
create or replace procedure review_account ( id_in in integer ) is l_account sg_account%rowtype; cursor sa_application_cols_cur is select id,name,description from sa_application; l_application sa_application_cols_cur%rowtype;
You might not even fetch a row from this cursor; it’s just to provide a structure for %rowtype.
All righty, I hope that gives you some ideas to apply to your next new program – or refactoring of an existing one.