PL/SQL Constants

You can declare PL/SQL constants along with the value and can not change them throughout the program block. The constants declaration functionality is available in almost all programming languages.

Constant Syntax

The general syntax for declaring a constant variable is:

Constant_name CONSTANT Datatype[Size] := Value; 

Explanation

• Constant_name is a predefined name of the constant (similar to a variable name).
• CONSTANT is a reserved keyword.
• Data type is a valid PL/SQL data type.
• Size is an optional specification of data type. It holds maximum capacity value for the particular variable.
• Value must be assigned to a constant when it is declared. You can not assign or change it later.
• Each constant declaration is terminated by a semicolon.

Constant Example

In this example, we will store the employee number which is NOT NULL (compulsory), employee Name and employee department which is constant,

Example

DECLARE
    eno number(5) NOT NULL := 2
    ename varchar2(15) := 'Branson Devs';
    edept CONSTANT varchar2(15) := 'Web Developer';
BEGIN
    dbms_output.put_line('Declared Value:');
    dbms_output.put_line(' Employee number: ' || eno || ' Employee Name: ' || ename);
    dbms_output.put_line('Constant Declared:');
    dbms_output.put_line(' Employee Department: ' || edept);    
END;
/

Backward slash '/' is indicated to execute the above PL/SQL Block Program.

Example Result

Variable/Constant Declarations Example

In this example, we will store the pi which is constant real number, radius and area which are real number,

Example

DECLARE
    pi CONSTANT REAL := 3.14159;
    radius REAL := 3;
    area REAL := (pi * radius**2);
BEGIN
    dbms_output.put_line(' PI: ' || pi || ' Radius: ' || radius);
    dbms_output.put_line(' Area: ' || area);    
END;
/

Example Result

• « Previous
• Next »