Standards – annoying warrant or useful tool?
Poorly prepared standards which don’t provide anything positive in the software development process can annoy. Programmers instead of focusing on creative programming – are concentrating on following The Standard, which is often written on several pages and can be very meticulous. You can see these poor people counting spaces, the number of which was specifically stated in the applicable convention. Too meticulous standards, even if they improve the clarity and readability of the code, can bring more harm than good. Therefore, when preparing the standards one must firmly restrict only to the things that may improve the quality of the code. Contrary to appearances, the advantages of using naming standards are not limited only to improving clarity and readability of the code. They can give us a lot more benefits.
Using proper prefixes / suffixes we can encode a huge amount of essential information in the variables’ names. Such information available immediately, without searching the code – simplifies understanding and analysis of procedures and what is more, it can prevent many bugs. Offhand knowledge of the main attributes of variable speeds up programming and helps the whole process.
Key attributes of variables that are relevant in the programming process are:
- purpose / function in the procedure
- scope
- direction: is the input, output, both
- is it a constant
- data type
The naming convention should allow simple encoding of the information in the variable name.
The purpose of the variable in the procedure
Proper naming of variables is an absolute necessity to make our code readable. It seems very simple – but in practice it turns out to be quite difficult. I’m sure you often came across code crammed with variables like these: x_data, data_tmp, y_data, cur_data. The names of those variables do not report a purpose and hinder the analysis procedure. The code should be clear and understandable. At higher decomposition levels – it should be even readable like a book.
|
1
2
3
4
5
|
begin
var1: = prc + prc_r * r;
end;
|
The above code says something to someone? Let’s try to convert it:
|
1
2
3
4
5
|
begin
new_employee_sal: = employee_sal + (company_income * sal_rise_percnt) s
end;
|
Proper variable names is the first and most important element necessary to improve the quality of our system.
Variable Scope
The ability to determine at a glance what scope has the variable is very important, especially in complex processes, which use many different types of variables: local, global, parameters.
Mistakes caused by invalid scoping are very difficult to detect! Once we have debugged such an error for a good few days before we found it. The ability to distinguish the scope of a variable eliminates this type of error.
The scope of variables can be distinguished using prefixes in variable name:
g <name> – global variables i.e. gCurrentDate
p <name> – input parameters i.e. pdCurrentDate
And what about local variables? Local variables are best left without prefixes. We use local variables most often so why write every time the extra letter? If the variable does not have the prefix g or p that means we are dealing with a local variable. Thanks to that we have one char more to use in the variable name to reflect its purpose.
Variable – IN or OUT?
The parameters can be input or output, they can also be constant. Their use and behavior considerably differs from each other. Knowing the type of the variable can facilitate the understanding of the code and the programming.
CONSTANT variables and input variables cannot be changed in the procedure. For output variables you need to prepare a variable to capture the output parameter. Ability to distinguish the direction of variables makes the work easier.
When calling a procedure from the parent procedure it is important to distinguish inputs from outputs. Of course, you can check the variable type in the specification. But wouldn’t it be faster to “decode” the information from the variable name? It will reduce the frustrating jumping from snippet of code we are working on to specification and back.
Because we already use prefixes, the for the purpose of direction specification we may apply suffixes:
p <name> io and – IN / OUT parameter
p <name> o – OUT parameter
<UPPERCASE_VARIABLE_NAME> or c <name> – a constant
What’s missing is the suffix for “IN parameter”. But on the other hand, if the variable starts with “p” there is no suffix _io or _o so it must be an IN type parameter, the most popular. Once again we saved a char :). As they say, DRY – Do not Repeat Yourself: P
Instead of:
|
1
2
3
4
5
|
begin
var1: = prc + prc_r * r;
end;
|
we can write this:
|
1
2
3
4
5
|
begin
new_employee_sal_o: = p_employee_sal + (company_income * g_SAL_RISE_PERCENT);
end;
|
We can see that:
- new_employee_sal_o is an output variable, calculated and returned by the procedure
- p_employee_sal variable is an input parameter
- company_income is a local variable
- g_SAL_RISE_PERCENT is a global constant variable.
When calling procedures sensible variable suffixes give even greater benefits.
Let’s take a look at the following snippet:
|
1
2
3
4
5
|
begin
calc_salary (Employee NewSalary);
end;
|
Is there anything we can know based on the code? Not really. To learn more we must either see calc_salary procedure code or / and check the variable types Employee and newSalary.
But as we add prefixes and suffixes:
|
1
2
3
4
5
|
begin
calc_salary (pnEmployee => nEmployee, pnNewSalary_o => nNewSalary);
end;
|
We can see that both parameters are numeric, pnEmployee parameter is an input parameter, the parameter is pnNewSalary_o → output parameter, so we cannot put there a literal, constant or input parameter. We have to define a numeric variable nNewSalary to capture the value from the procedure.
It should be pointed out that the benefits of this approach can only be observed when we call a procedure using named parameters, not the positional (parameters will be passed to the procedure by names).
Scope prefixes
| SCOPE | PREFIX | EXAMPLE |
|---|---|---|
| GLOBAL | g<name> | gdCurrentDate |
| LOCAL | without prefix | vUserName |
| CONSTANT | <type prefix><NAME> (uppercase) | gnUSER_ID |
| PARAMETR IN | p<type prefix><name> | pnUserId |
| PARAMETR OUT | p<type prefix><name>_o | pvUserName_o |
| PARAMETR IN OUT | p<type prefix><name>_io | pvFeeAmt_io |
Determining the type of data in the variable name is probably the least common convention. This convention is not difficult to apply. We just need to add the letter as a prefix in the variable name which reflects the variable type ex. v for varchar2, n for number. This information will help us to prevent from unpleasant but easy to avoid bugs. The data type of a variable.
Thanks to that we can distinguish variable names from the names of columns in SQL queries. Why do we need this? Consider the following example:
|
1
2
3
4
5
6
|
begin
select salary into rooms from employee
where emp_id = emp_id;
end;
|
Oracle will compile the procedure without warning. If we do not notice anything unusual – request that remain in out application. But the consequences can be significant. In the WHERE emp_id column is compared with … emp_id column of the table. Although we have variable with the same name in the code – Oracle will not use it. And instead of the record for the employee – we will get all the records in the table. Probably this error will not be detected on unit tests as testing just one row in the employee table is enough, isn’t it? …
Well, let’s try to improve:
|
1
2
3
4
5
6
|
begin
select salary into rooms from employee
where emp_id = l_emp_id;
end;
|
Well, now it should work. But are we sure?
It depends on what the type of variable l_emp_id! If it is the same type as emp_id, then we are lucky. If variable l_emp_id has different type than the emp_id column in the table, and there is an index – Oracle performs implicit data conversion in order to adapt it to variable parameter l_emp_id. As a result, instead of a fast query using index, we have a full table scan.
To detect this type of error we need experience, luck or time-consuming debugging. When we try to get the explain plan for query using bind variable:
|
1
2
3
|
select salary into rooms from employee
where emp_id = :emp_id ;
|
Oracle will calculate query cost assuming correct type of variable! The cost will be low but time execution still poor.
We can also set the proper value instead of l_emp_id in the query. But since we probably know the type of the column we will use the proper one. Oracle again will tell us that the query is fast and uses the index.
But performance still not good.
What can we do about it?
|
1
2
3
4
5
6
|
begin
select salary into rooms from employee
where emp_id = v_emp_id;
end;
|
At first glance we can see that the variable type is varchar2 making it much easier to notice a potential problem. Usually indexed fields are well-known and the picture of the number primary key compared with a variable of different type (v – varchar2) may raise a reasonable doubt. Thanks to information encoded in the variables’ names we can avoid common mistakes in data types which are usually harmless but can have significant performance implications.
Suffixes and prefixes have to be combined depending on the scope, direction and data type of the variable. If we have parameter IN / OUT , number -> we should encode all of information in the name ex. pnEmpSal_io.
Do we always use prefixes and suffixes?
No, there are always exceptions. For variables such as well known iterators we should use usual name like i, j, z, x, y. Every programmer seeing something like that:
|
1
|
for i in 1..100 loop
|
knows exactly what it is.
|
1
|
for nEmployeeId in 1..100 loop
|
this introduces a slight uneasiness. Iterators’ names are often used in arrays or collections. Writing long names can be frustrating.
|
1
2
3
4
5
|
for nEmployeeId in 1..100 loop
tabEmplyees (nEmployeeId).employee_id: = nEmployeeId;
end loop;
|
It’s probably better to stick with the good old i, j, x:
|
1
2
3
4
5
|
for i in 1..100 loop
tabEmplyees(i).employee_id: = i;
end loop;
|
That’s better 🙂
CamelCase or under_score
Yes, this topic has been specifically omitted, pushed to the end. Because it does not really matter if you use CamelCase or under_score . If only the variable names are well chosen and the convention allows you to quickly distinguish different types and scope of variables and parameters – this is what we want to achieve through standards. Arguing whether CamelCase or under_score is pointless – just agree to one of those and get it over with 😉
Preparing standards
Well prepared naming standards can greatly simplify the work and prevent code from bugs. You can apply the proposed standards but you can also modify them or prepare your own. It is important to meet the basic purposes that make developers’ work easier and more enjoyable.
- The variable name should define its task / function in the process
- The variable name should specify its scope
- The variable name should specify its direction
- The variable name should specify the data type of the variable
Examples of standards:
| SCOPE | PREFIX | EXAMPLE |
|---|---|---|
| GLOBAL | g<name> | gdCurrentDate |
| LOCAL | without prefix | vUserName |
| CONSTANT | <type prefix><NAME> (uppercase) | gnUSER_ID |
| IN PARAMETER | p<type prefix><name> | pnUserId |
| OUT PARAMETER | p<type prefix><name>_o | pvUserName_o |
| IN/OUT PARAMETER | p<type prefix><name>_io | pvFeeAmt_io |
| TYPE | PREFIX | EXAMPLE |
|---|---|---|
| VARCHAR | v<name> | vUserName |
| NUMBER | n<name> | nUserId |
| BOOLEAN | b<name> | bIfUserActive |
| ROWTYPE | rec<name> or row<name> | recUSerData |
| TABLE | t<name> or tab<name> | tUsers |
| CURSOR | cur<name> | curUserTxn |
| INTEGER | i<name> | iTxnCnt |
| DATE | d<name> | dCurrentDate |
This post was published originally at my blog, which you can find here how2ora.blogspot.com.