PL/SQL Constants

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

PL/SQL Constants

As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

For example: If you want to write a program which will increase the salary of the employees by 25%, you can declare a constant and use it throughout the program. Next time when you want to increase the salary again you can change the value of the constant which will be easier than changing the actual value throughout the program.

General Syntax to declare a constant is:

constant_name CONSTANT datatype := VALUE; 
  • constant_name is the name of the constant i.e. similar to a variable name.
  • The word CONSTANT is a reserved word and ensures that the value does not change.
  • VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.

For example, to declare salary_increase, you can write code as follows:

DECLARE 
salary_increase CONSTANT number (3) := 10; 

You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error.

DECLARE 
 salary_increase CONSTANT number(3); 
BEGIN 
 salary_increase := 100; 
 dbms_output.put_line (salary_increase); 
END;