Like several other programming languages, the constant in PL/SQL is also a user defined identifier whose value remains unchanged throughout the program. Like variables in PL/SQL constants also need to be declared prior to their use. Furthermore you can only declare them in the declaration section of your PL/SQL block.
Syntax
PL/SQL has its own way of declaring a constant. To learn how to declare a constant in PL/SQL let’s quickly take a look at the syntax. This is our syntax:
constant_name CONSTANT datatype (data-width) := value;
First you need to give a valid name to your constant followed by keyword CONSTANT that indicates the declaration of a constant in your program. Then you have to specify the data type and data width for your constant followed by the assignment operator and the value which you want to assign to your constant.
Note here:
You must initialize a constant at its declaration. You have to initialize your constant at the time of its creation in declaration section of your PL/SQL block. You cannot initialize it anywhere else.
First example will demonstrate how to declare and initialize a constant.
SET SERVEROUTPUT ON;
DECLARE
v_pi CONSTANT NUMBER(7,6) := 3.141592;
BEGIN
DBMS_OUTPUT.PUT_LINE (v_pi);
END;
This is a simple example of Constant declaration and initialization. Here in declaration section I have declared a constant v_pi and initialized it with the approximate value of pi. In the execution section we have our DBMS output statement which is displaying the value stored into our constant.
This is the proper way of declaring and initializing a constant in PL/SQL. We have two more attributes of PL/SQL constants to discuss which are “DEFAULT” and “NOT NULL”.
DEFAULT
You can use default keyword instead of assignment operator to initialize the constant in PL/SQL. Let’s do an example and see how to initialize a constant using DEFAULT keyword.
DECLARE
v_pi CONSTANT NUMBER(7,6) DEFAULT 3.1415926;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_pi);
END;
/
Same code just this time I used keyword DEFAULT instead of assignment operator for initializing the constant.
NOT NULL
Next attribute is NOT NULL. Using this attribute you can impose NOT NULL constraint while declaring constants as well as variables. This will prevent you from assigning NULL values to your constants or variables.
To impose not null constraint simply write NOT NULL keyword before the Keyword default or before assignment operator in case you have used it. Let me show you how
DECLARE
v_pi CONSTANT NUMBER(7,6) NOT NULL DEFAULT 3.1415926;
BEGIN
DBMS_OUTPUT.PUT_LINE (v_pi);
END;/
That’s all you have to do.
That’s it guys. Hope you found this article useful. Kindly share it on your social networking and help me reach out to more people. Thanks & have a wonderful day!
