Proper way of Declaring and Initializing Constants in PL/SQL.
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.
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;
v_pi CONSTANT NUMBER(7,6) := 3.141592;
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”.
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.
v_pi CONSTANT NUMBER(7,6)
Same code just this time I used keyword DEFAULT instead of assignment operator for initializing the constant.
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
v_pi CONSTANT NUMBER(7,6) NOT NULL DEFAULT 3.1415926;
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!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
NO SQL Script used in Video and in this article