PL/SQL Constants

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

Summary: In this tutorial, you will learn how to use the PL/SQL constants, which hold values that do not change throughout the program’s execution.

Introduction to PL/SQL constants #

Unlike a variable, a constant holds a value that does not change throughout the program’s execution.

Constants make your code more readable. Consider the following line of code that calculates the list price from the price.

l_list_price :=  l_price + l_price * 0.1;Copy

By looking at this, we don’t know what 0.1 means. It can be anything. One option is that 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;Copy

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

l_list_price := l_price + l_price * co_vat;Copy

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]  := expressionCopy

In this syntax:

• constant_name is the name of the constant that you are declaring.
• datatype specifies 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 uses 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;Copy

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;Copy

Here’s the error message:

PLS-00363: expression 'CO_PAYMENT_TERM' cannot be used as an assignment targetCopy

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;Copy

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

Summary #

• Use PL/SQL constants to hold values that remain unchanged throughout the program’s execution.