Summary: in this tutorial, you will learn how to use the PL/SQL constants that hold values which do not change throughout the execution of the program.
Introduction to PL/SQL constants
Unlike a variable, a constant holds a value that does not change throughout the execution of the program.
Constants make your code more readable. Consider the following line of code that calculates the list price from price.
Code language: SQL (Structured Query Language) (sql)l_list_price := l_price + l_price * 0.1;
By looking at this, we don’t know what 0.1
means. It can be anything. Of course, you can use a comment to explain the meaning of 0.1
:
-- price with value-added tax (VAT) 10%
l_list_price := l_price + l_price * 0.1;
Code language: SQL (Structured Query Language) (sql)
However, it is even better if you use a constant like this:
Code language: SQL (Structured Query Language) (sql)l_list_price := l_price + l_price * co_vat;
In this code, co_vat
is constant that stores the VAT tax 10%.
To declare a constant, you specify the name, CONSTANT
keyword, data type, and the default value. The following illustrates the syntax of declaring a constant:
Code language: SQL (Structured Query Language) (sql)constant_name CONSTANT datatype [NOT NULL] := expression
In this syntax:
constant_name
is the name of the constant that you are declaring.
datatype
specify the type of value that the constant will hold.
NOT NULL
optionally impose a NOT NULL
constraint on the constant. This prevents the constant from storing NULL or an empty string.
expression
use an expression as the initial value for the constant. The type of the return value of the expression must be compatible with the data type of the constant.
PL/SQL constant examples
The following example declares two constants co_payment_term
and co_payment_status
:
DECLARE
co_payment_term CONSTANT NUMBER := 45; -- days
co_payment_status CONSTANT BOOLEAN := FALSE;
BEGIN
NULL;
END;
Code language: SQL (Structured Query Language) (sql)
If you attempt to change the co_payment_term
in the execution section, PL/SQL will issue an error, for example:
DECLARE
co_payment_term CONSTANT NUMBER := 45; -- days
co_payment_status CONSTANT BOOLEAN := FALSE;
BEGIN
co_payment_term := 30; -- error
END;
Code language: SQL (Structured Query Language) (sql)
Here is the error message:
Code language: SQL (Structured Query Language) (sql)PLS-00363: expression 'CO_PAYMENT_TERM' cannot be used as an assignment target
The following illustrates how to declare a constant whose value is derived from an expression:
DECLARE
co_pi CONSTANT REAL := 3.14159;
co_radius CONSTANT REAL := 10;
co_area CONSTANT REAL := (co_pi * co_radius**2);
BEGIN
DBMS_OUTPUT.PUT_LINE(co_area);
END;
Code language: SQL (Structured Query Language) (sql)
In this example, the co_area
constant receives the value from an expression involved two other constants.
In this tutorial, you have learned about the PL/SQL constants that hold values which remain unchanged throughout the execution of the program.