PL/SQL Constants

In Oracle's PL/SQL (Procedural Language/SQL), constants play an important role in maintaining the integrity and predictability of the values used throughout the program. Declaring the constants enhances the program readability and reduces the errors, it also provides the performance benefits in certain scenarios where the fixed value is repeatedly used.

In this article, we will explain PL/SQL constants in detail, including their characteristics and examples.

PL/SQL Constants

In PL/SQL, constants are declared with the help of the CONSTANT keyword, followed by data type and initialization of value. They are especially useful in scenarios where the fixed value is required multiple times but most remain unchanged to avoid unintended modifications or errors.

Characteristics of PL/SQL Constants:

• Immutable: Once initialized, their value cannot be changed further.
  
• Declaration: Constants must be assigned a value at the time of the declaration.
  
• Readability: Enhances the code clarity and makes it easier to understand and maintain.
  
• Performance: Reduces the overload of the repeated calculations or lookups by the storing the fixed value.

Syntax:

DECLARE
   constant_name CONSTANT data_type := value;
BEGIN
   -- Code block where the constant can be used
END;

Explanation:

• constant_name: The name of the constant should be a variable. The name must be meaningful and should follow the PL/SQL naming conventions.
  
• CONSTANT: The keyword is indicating that the value is assigned to constant cannot changed after the initialization.
  
• data_type: The data type of constant like NUMBER, VARCHAR2 or DATE.
  
• := : This is assignment operator which is used to initialize constant with the value.
  
• value: The value that is assigned to constant, which cannot be modified the later.

Example 1: Using a Constant in a Simple Arithmetic Operation

• By Using a Constant in a Simple Arithmetic Operation, pi is declared as the constant with value 3.14159 .
  
• The radius is assigned value 5, and the area of circle is calculated with the help of formula pi * radius^2 .
  
• The result of calculation is displayed withe the DBMS_OUTPUT.PUT_LINE .

Query:

DECLARE
   pi CONSTANT NUMBER := 3.14159;
   radius NUMBER := 5;
   area NUMBER;
BEGIN
   area := pi * radius * radius;
   DBMS_OUTPUT.PUT_LINE('Area of the circle: ' || area);
END;
/

Output:

Area of the circle: 78.53975

Explanation:

• In the above output, the constant pi is used in formula and since it cannot be changed, it is ensure that consistent results for all the calculations involving it.

Example 2: Using Constants in Conditional Logic

In PL/SQL, constants are fixed values that cannot be changed once defined. In the example, min_age is declared as a constant with a value of 18, representing the minimum voting age.

The conditional IF statement checks if the user_age (set to 20) is greater than or equal to min_age, determining the user's eligibility to vote. Since user_age is 20, the output is "User is eligible to vote."

Query:

DECLARE
   min_age CONSTANT NUMBER := 18;
   user_age NUMBER := 20;
BEGIN
   IF user_age >= min_age THEN
      DBMS_OUTPUT.PUT_LINE('User is eligible to vote.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('User is not eligible to vote.');
   END IF;
END;
/

Output:

User is eligible to vote.

Explanation:

• In the above code, min_age is the constant with value 18, representing minimum voting age.
  
• user_age is assigned the value 20.
  
• A conditional statement is checks whether the user_age is greater than or equal to the min_age to determine the eligibility to the vote.

Example 3: Constant with String Data Type

• In given below example company_name is the constant with value Tech Innovators Inc. where Constant with String Data type is used.
  
• The employee_name is initialized with value John Doe .
  
• The program outputs the message that concatenates employee name and the name of the company.

Query:

DECLARE
   company_name CONSTANT VARCHAR2(50) := 'Tech Innovators Inc.';
   employee_name VARCHAR2(30) := 'John Doe';
BEGIN
   DBMS_OUTPUT.PUT_LINE('Employee ' || employee_name || ' works at ' || company_name);
   -- Trying to change company_name will cause an error
   -- company_name := 'New Company';  -- This would generate a compilation error
END;
/

Output:

Employee John Doe works at Tech Innovators Inc.

Explanation:

• Here, the constant company_name is ensure that company name is remain fixed throughout the program. Attempting to change the company_name would give the compilation error highlighting immutability of the constants.

Conclusion:

In conclusion, the PL/SQL constants are the essential feature that enhance the reliability, maintainability and readability of our code. By using the constants, developers can safeguard key values like configuration settings, thresholds and fixed parameters, make sure that the remain unchanged throughout the program.

This immutability prevents the accidental modifications which can lead to errors and unpredictable outcomes. Furthermore, constants can improve the performance by reducing the need to re-evaluate or repeatedly reference same fixed value during the execution.

jagan716

Follow

Improve

Article Tags :

• Databases
• PL/SQL