PL/SQL - Constants and Literals: A Beginner's Guide

Hello there, aspiring programmers! Today, we're going to embark on an exciting journey into the world of PL/SQL, specifically focusing on Constants and Literals. Don't worry if these terms sound like alien language right now - by the end of this tutorial, you'll be throwing these words around like a seasoned pro!

What are Constants and Literals?

Before we dive in, let's break down these terms:

• A Constant is a value that doesn't change throughout the execution of a program.
• A Literal is a value that appears directly in a program.

Think of constants as your favorite number that you always use, and literals as the numbers you jot down on a piece of paper. Let's explore these concepts in more detail!

Declaring a Constant

In PL/SQL, declaring a constant is like telling your program, "Hey, remember this value, it's important and it's not going to change!"

Here's the basic syntax:

constant_name CONSTANT datatype := value;

Let's break this down:

• constant_name: This is the name you give to your constant.
• CONSTANT: This keyword tells PL/SQL that we're declaring a constant.
• datatype: This specifies what kind of data the constant will hold (like NUMBER, VARCHAR2, etc.).
• :=: This is the assignment operator in PL/SQL.
• value: This is the actual value you're assigning to the constant.

Example 1: Declaring a Numeric Constant

DECLARE
    pi CONSTANT NUMBER(7,6) := 3.141592;
BEGIN
    DBMS_OUTPUT.PUT_LINE('The value of pi is: ' || pi);
END;

In this example, we're declaring a constant named pi with a value of 3.141592. The NUMBER(7,6) means it's a number with 7 total digits, 6 of which are after the decimal point.

When you run this code, it will output:

The value of pi is: 3.141592

Example 2: Declaring a String Constant

DECLARE
    greeting CONSTANT VARCHAR2(20) := 'Hello, PL/SQL World!';
BEGIN
    DBMS_OUTPUT.PUT_LINE(greeting);
END;

Here, we're declaring a constant named greeting that holds a string value. VARCHAR2(20) means it's a variable-length string that can hold up to 20 characters.

Output:

Hello, PL/SQL World!

The PL/SQL Literals

Now, let's talk about literals. These are the raw values you use in your code. PL/SQL supports several types of literals:

1. Numeric Literals

These are numbers. Simple, right? They can be integers or floating-point numbers.

DECLARE
    age NUMBER := 25;  -- Integer literal
    height NUMBER := 5.9;  -- Floating-point literal
BEGIN
    DBMS_OUTPUT.PUT_LINE('Age: ' || age);
    DBMS_OUTPUT.PUT_LINE('Height: ' || height || ' feet');
END;

Output:

Age: 25
Height: 5.9 feet

2. Character Literals

These are single characters enclosed in single quotes.

DECLARE
    grade CHAR := 'A';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Your grade is: ' || grade);
END;

Output:

Your grade is: A

3. String Literals

These are sequences of characters enclosed in single quotes.

DECLARE
    name VARCHAR2(20) := 'John Doe';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Welcome, ' || name || '!');
END;

Output:

Welcome, John Doe!

4. Boolean Literals

PL/SQL has three boolean literals: TRUE, FALSE, and NULL.

DECLARE
    is_student BOOLEAN := TRUE;
BEGIN
    IF is_student THEN
        DBMS_OUTPUT.PUT_LINE('This person is a student.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('This person is not a student.');
    END IF;
END;

Output:

This person is a student.

5. Date Literals

Date literals are enclosed in single quotes and must be in the format specified by your NLS_DATE_FORMAT parameter.

DECLARE
    hire_date DATE := '01-JAN-2023';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hire date: ' || hire_date);
END;

Output:

Hire date: 01-JAN-23

Putting It All Together

Now that we've covered constants and literals, let's see how we can use them together in a more complex example:

DECLARE
    -- Constants
    pi CONSTANT NUMBER(7,6) := 3.141592;
    greeting CONSTANT VARCHAR2(20) := 'Hello, ';

    -- Variables using literals
    radius NUMBER := 5;
    name VARCHAR2(20) := 'Alice';
    is_circle BOOLEAN := TRUE;

    -- Calculated value
    area NUMBER;
BEGIN
    IF is_circle THEN
        area := pi * radius * radius;
        DBMS_OUTPUT.PUT_LINE(greeting || name || '!');
        DBMS_OUTPUT.PUT_LINE('The area of your circle is: ' || ROUND(area, 2) || ' square units.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('This is not a circle calculation.');
    END IF;
END;

Output:

Hello, Alice!
The area of your circle is: 78.54 square units.

In this example, we've used constants (pi and greeting), numeric literals (5), string literals ('Alice'), and a boolean literal (TRUE). We've also performed a calculation using these values and displayed the result.

Conclusion

Congratulations! You've just taken your first steps into the world of PL/SQL constants and literals. Remember, constants are like faithful friends that stick with you throughout your program, while literals are the building blocks you use to create your PL/SQL masterpieces.

As you continue your programming journey, you'll find yourself using these concepts more and more. They're essential tools in any programmer's toolkit, helping to make your code more readable, maintainable, and efficient.

Keep practicing, stay curious, and before you know it, you'll be writing complex PL/SQL programs with ease. Happy coding!

Concept	Description	Example
Constant	A value that doesn't change throughout program execution	pi CONSTANT NUMBER(7,6) := 3.141592;
Numeric Literal	A number used directly in the code	25, 5.9
Character Literal	A single character enclosed in single quotes	'A'
String Literal	A sequence of characters enclosed in single quotes	'John Doe'
Boolean Literal	One of TRUE, FALSE, or NULL	TRUE, FALSE, NULL
Date Literal	A date value enclosed in single quotes	'01-JAN-2023'

Credits: Image by storyset