PL/SQL Constants 🎯

Welcome to our deep dive into PL/SQL Constants! In this lesson, we'll explore the fundamental building blocks of PL/SQL programming - constants. By the end of this tutorial, you'll have a solid understanding of how to use constants, why they are crucial, and how to apply them in practical scenarios.

What are PL/SQL Constants? 📝

In programming, a constant is a value that cannot be changed during the execution of a program. PL/SQL provides several types of constants such as:

Number
Character
Boolean
Date
VARCHAR2 (string)
NUMBER(precision, scale)
LONG

Why use PL/SQL Constants? 💡

Constants make your code more readable and maintainable by defining clear, unchanging values.
They help reduce errors by ensuring that specific values are consistently used throughout your code.
Constants can make it easier to modify your program by allowing you to change a single value rather than searching through multiple lines of code for a specific variable.

PL/SQL Number Constants 📝

A Number constant is used to store integer or decimal values. Here's how to declare and use a number constant:

sql

-- Declare a number constant
CONSTANT_NAME CONSTANT number := value;

-- Example
CONST_PI CONSTANT number := 3.14159265358979323846;

-- Use the constant in a SQL statement
SELECT sqrt(CONST_PI) FROM dual;

💡 Pro Tip: Use meaningful names for your constants to make your code self-documenting.

PL/SQL Character Constants 📝

A Character constant is used to store a single character or string of characters enclosed in single quotes. Here's how to declare and use a character constant:

sql

-- Declare a character constant
CONSTANT_NAME CONSTANT char := 'value';

-- Example
CONST_GREETING CONSTANT char := 'Hello, World!';

-- Use the constant in a SQL statement
SELECT CONST_GREETING FROM dual;

💡 Pro Tip: Be careful with string concatenation, as Oracle uses two different methods: || and CONCAT.

PL/SQL Boolean Constants 📝

A Boolean constant is used to store a value of either TRUE or FALSE. Here's how to declare and use a boolean constant:

sql

-- Declare a boolean constant
CONSTANT_NAME CONSTANT boolean := value;

-- Example
CONST_IS_ADMIN CONSTANT boolean := TRUE;

-- Use the constant in a conditional statement
IF CONST_IS_ADMIN THEN
  DBMS_OUTPUT.PUT_LINE('Welcome, Admin!');
END IF;

💡 Pro Tip: Use boolean constants to simplify conditional logic and improve code readability.

PL/SQL Date Constants 📝

A Date constant is used to store a specific date and time. Here's how to declare and use a date constant:

sql

-- Declare a date constant
CONSTANT_NAME CONSTANT date := 'DD-MON-YYYY HH24:MI:SS';

-- Example
CONST_BIRTHDAY CONSTANT date := '01-JAN-1990 00:00:00';

-- Use the constant in a SQL statement
SELECT CONST_BIRTHDAY FROM dual;

💡 Pro Tip: Use the 'TO_DATE' function to convert strings to dates.

PL/SQL VARCHAR2 (String) Constants 📝

A VARCHAR2 constant is used to store a string of characters. Here's how to declare and use a VARCHAR2 constant:

sql

-- Declare a VARCHAR2 constant
CONSTANT_NAME CONSTANT varchar2(length) := 'value';

-- Example
CONST_ADDRESS CONSTANT varchar2(255) := '123 Main Street, Anytown, USA';

-- Use the constant in a SQL statement
SELECT CONST_ADDRESS FROM dual;

💡 Pro Tip: Be aware of the length limit when using VARCHAR2 constants.

Quiz 🎯

That concludes our introduction to PL/SQL Constants! In the next lesson, we'll dive into PL/SQL Variables, which allow you to store and manipulate dynamic values.

Until then, happy coding! 🤖💪