PL/SQL Variables 🎯

Welcome to our deep dive into PL/SQL Variables! In this comprehensive guide, we'll explore everything you need to know about variables in PL/SQL, a powerful procedural language used for working with Oracle databases.

What are PL/SQL Variables? 📝

PL/SQL variables are containers used to store data during the execution of a PL/SQL block. They help us store and manipulate data dynamically, making our scripts more flexible and efficient.

Declaring PL/SQL Variables 💡

To declare a variable, we use the DECLARE keyword followed by the variable name, data type, and an optional initial value.

sql

DECLARE
  variable_name data_type [:= initial_value];

Data Types in PL/SQL

PL/SQL supports several data types, including:

Number: Stores numerical values (INTEGER, FLOAT, DECIMAL, etc.)
String: Stores character data (VARCHAR2)
Date: Stores date and time values (DATE)
Boolean: Stores true or false values (BOOLEAN, but Oracle does not support it)
Record and Array: Complex data structures (covered in advanced lessons)

Assigning Values to Variables 💡

After declaring our variables, we can assign values to them using the := operator.

sql

variable_name := value;

Example: Storing and Retrieving Data

Let's create a simple script that stores and retrieves data using variables.

sql

DECLARE
  name VARCHAR2(50) := 'John Doe';
  age NUMBER(3) := 30;
  dob DATE := TO_DATE('1992-01-01', 'YYYY-MM-DD');
BEGIN
  DBMS_OUTPUT.PUT_LINE('Name: ' || name);
  DBMS_OUTPUT.PUT_LINE('Age: ' || age);
  DBMS_OUTPUT.PUT_LINE('Date of Birth: ' || to_char(dob, 'DD-MON-YYYY'));
END;
/

📝 Note: The DBMS_OUTPUT package is used to display output during the execution of a PL/SQL block.

Using Variables in SQL Statements 💡

We can use variables in SQL statements by referencing the variable name directly.

sql

DECLARE
  name VARCHAR2(50) := 'John Doe';
BEGIN
  SELECT * FROM employees WHERE first_name = name;
END;

Quiz 💡

Stay tuned for our next lesson, where we'll delve deeper into PL/SQL, covering advanced topics like control structures, functions, and packages! 🚀