PL/SQL Constants

Summary: in this tutorial, you will learn how to use the PL/SQL constants that hold values that 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.

l_list_price :=  l_price + l_price * 0.1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

However, it is even better if you use a constant like this:

l_list_price := l_price + l_price * co_vat;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this code, co_vat is a constant that stores the VAT tax of 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:

constant_name CONSTANT datatype [NOT NULL]  := expressionCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 imposes 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the error message:

PLS-00363: expression 'CO_PAYMENT_TERM' cannot be used as an assignment target
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the co_area constant receives the value from an expression involving two other constants.

In this tutorial, you have learned about the PL/SQL constants that hold values that remain unchanged throughout the execution of the program.