Friday, January 24, 2014

Hard Coded Variables in PL/SQL

Many applications need to use the same value in several different places. The examples below show a net value and a tax figure being added to create a gross amount. The tax figure is calculated by multiplying the net value by the tax rate. In the first example, the tax rate is hard-coded as 0.2. This is quite simple to implement but, if the tax rate changes, you will need to go through your whole application and amend every individual value: 

SQL> set serveroutput on
SQL> declare
  2    net number;
  3    tax number;
  4    gross number;
  5  begin
  6    net := 1;
  7    tax := 0.2;
  8    gross := net + (net * tax);
  9    dbms_output.put_line('Gross = '||to_char(gross,'$9.99'));
 10  end;
 11  /
Gross =  $1.20
 
PL/SQL procedure successfully completed.

SQL> 

The second example stores the tax rate as a constant in a package. When you need to know the tax rate, you fetch it from the package as shown below. This is slightly harder to implement but, if the tax rate changes, you only need to amend the value in one place: 

SQL> create or replace package special_values1
  2  is
  3    tax constant number := 0.2;
  4  end special_values1;
  5  /
 
Package created.
 
SQL> declare
  2    net number;
  3    tax number;
  4    gross number;
  5  begin
  6    net := 1;
  7    tax := special_values1.tax;
  8    gross := net + (net * tax);
  9    dbms_output.put_line('Gross = '||to_char(gross,'$9.99'));
 10  end;
 11  /
Gross =  $1.20
 
PL/SQL procedure successfully completed.

SQL>

The third example is similar to the second but, this time, the tax rate is returned by a function:
 
SQL> create or replace package special_values2
  2  is
  3    function tax
  4    return number;
  5  end special_values2;
  6  /
 
Package created.
 
SQL> create or replace package body special_values2
  2  is
  3    function tax
  4    return number
  5  is
  6  begin
  7    return 0.2;
  8  end;
  9  end special_values2;
 10  /
 
Package body created.
 
SQL> declare
  2    net number;
  3    tax number;
  4    gross number;
  5  begin
  6    net := 1;
  7    tax := special_values2.tax();
  8    gross := net + (net * tax);
  9    dbms_output.put_line('Gross = '||to_char(gross,'$9.99'));
 10  end;
 11  /
Gross =  $1.20
 
PL/SQL procedure successfully completed.
 
SQL>

The simplest method of all is possibly to store the tax rate as a value in a table. I won't insult your intelligence by showing an example using this method!

No comments: