Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL Constants

PL/SQL Constants

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.

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;

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

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:

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;

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;

Here is the error message:

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;

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.

  • Was this tutorial helpful?
  • YesNo
Previous PL/SQL Comments
Next PL/SQL IF Statement

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.