Use of Constants

A constant is something whose value never changes. For ex, literal 5 is a constant, because the value 5 can’t be changed. Similarly, the character literal “BOOK” is also a constant.

A constant variable is a normal variable but this variable’s value can’t be changed.

Why to use a constant when the literal can be directly used in a program?

Well, lets take an example:

Suppose, there is a program, which calculates commission for the sales reps depending on the no of orders that they booked. If the total no of orders are greater than 10 then the sales rep will get 5 percent commission on the total order that he/she booked.

CREATE OR REPLACE PROCEDURE pr_calc_comm (p_sales_rep_id ORDER_MST.sales_rep_id%TYPE) IS v_order_count PLS_INTEGER; v_order_value NUMBER; v_comm_amt NUMBER; v_stmt VARCHAR2(32000); BEGIN v_stmt := 'Select order_mst WHERE sales_rep_id =' || p_sales_rep_id|| ' at 100'; --Get the total no of orders and the order value SELECT COUNT(orders_id) , SUM(order_value) INTO v_order_count , v_order_value FROM order_mst WHERE sales_rep_id = p_sales_rep_id; /* Check whether the order count is greater than 10. If yes, then calculate the commission as 5% of the order value */ v_stmt :='Calculating comm at 200'; IF v_order_count > 10 THEN v_comm_amt := v_order_value * 5 / 100; v_stmt :='Insert into comm_tab at 300'; INSERT INTO comm_tab(sales_rep_id,comm_amt) VALUES (p_sales_rep_id, v_comm_amt); COMMIT; END IF; EXCEPTION WHEN OTHERS THEN Raise_application_error('Error' ||SQLERRM || ' at '|| v_stmt); END;

Above program will work fine.

What if, if the commission percentage is changed to 7 percent? Well, you can open the code and change the percentage to 7. Similarly, if the order count is changed to 15 from 10, then also, the program can be modified. You can replace 10 by 15.

What if, if the percentage 5 is used in more than one place in a big program, say a program of 1000 line? How can you find the value in that big program?

Now lets re-write the same program by using constants

CREATE OR REPLACE PROCEDURE pr_calc_comm (p_sales_rep_id ORDER_MST.sales_rep_id%TYPE) IS v_order_count PLS_INTEGER; v_order_value NUMBER; v_comm_amt NUMBER; v_stmt VARCHAR2(32000); --Declare constant variables c_min_order_count CONSTANT PLS_INTEGER := 10; c_comm_pct CONSTANT NUMBER(5,2) := 5; BEGIN v_stmt :='Selet on order_mst WHERE sales_rep_id =' || p_sales_rep_id|| ' at 100'; --Get the total no of orders and the order value SELECT COUNT(orders_id) , SUM(order_value) INTO v_order_count , v_order_value FROM order_mst WHERE sales_rep_id = p_sales_rep_id; v_stmt :='Calculating comm at 200'; /* Check whether the order count is greater than 10. If yes, then calculate the commission as 5% of the order value */ IF v_order_count > c_min_order_count THEN v_comm_amt := v_order_value * c_comm_pct / 100; v_stmt :='Insert into comm_tab at 300'; INSERT INTO comm_tab(sales_rep_id,comm_amt) VALUES (p_sales_rep_id, v_comm_amt); COMMIT; END IF; EXCEPTION WHEN OTHERS THEN Raise_application_error('Error' ||SQLERRM || ' at '|| v_stmt); END;

We have added two constant variables and used them in the program. Now, if the values need to be changed, then simply we need to change the value in the declaration section.

Now, what if, if the same variable needs to be used in more than one procedure/function/package body? Individual constant variables can be declared in the respective procedure/function/package bodies. If the value of the constant variable needs to be changed then we have to change in all the places where ever the constant variables were declared.

So, if the same constant variables need to be used in more than one procedure, then create a package, which contains the constant variables, and use them in the individual programs.

Another use of Constants

Suppose, in a Banking application, there can be diff types of accounts. Savings account, Current Account, and Salary account etc. Lets take a piece of code from a program

IF v_account_type = ‘S’ THEN --Check for savings acc type
--DO something
ELSIF v_account_type =’C’ THEN --Check for current acc type
--DO something
ELSIF v_account_type =’L’ THEN -- Check for Salary acc type
-- DO something
ELSE --display some error informing the user that Invalid acc type
-- or do nothing
END IF;

Instead of using the special lateral values like S, C, L, constants can be declared as

c_acc_type_savings CONSTANT CHAR(1) := 'S';

c_acc_type_current CONSTANT CHAR(1) := 'C';

c_acc_type_salary CONSTANT CHAR(1) := 'L';

and the above IF statement can be re-written as

IF v_account_type = c_acc_type_savings THEN --Check for savings acc type
--DO something

ELSIF v_account_type = c_acc_type_current THEN --Check for current acc type
--DO something
ELSIF v_account_type = c_acc_type_salary THEN -- Check for Salary acc type
-- DO something
ELSE
--display some error informing the user that Invalid account type
-- or do nothing
END IF;

Also, if a variable needs to be compared with a value called ‘Savings’ like

IF v_acc_type =’Savings’ THEN
---

END IF;

and, the value ‘Savings’ needs to be checked in many places, then there is a chance for making typo errors. Instead if a constant is used then the chance making typo errors can be eliminated

.

The best practice is to use constant variable instead of constant values.

Always declare constants at the appropriate scope rather than use special literal values in code. Do not use ‘magic’ literals in code.

The benefits of this methodology include the following:

• Standardization: If a value has a significant meaning for multiple objects within a package or throughout a schema, or even at a broader scope, it can be defined and maintained easily and consistently across the entire spectrum.
• Compile-time checking of all sentinal values. Since the actual value of the constant is maintained in one place only, there is no danger of multiple uses of the same value being misspelled, miss-cased (upper/lower) or otherwise inconsistent with one a nother, resulting in data or logic errors.

Ease of maintenance. If a value needs to change, the change is made in one place and all uses of it are automatically synchronized.
Last, but not the least, keep all the constants in a common packages and use them wherever required.