PL/SQL - Variables: A Beginner's Guide

Hello there, future PL/SQL wizards! Today, we're going to embark on an exciting journey into the world of PL/SQL variables. Don't worry if you're new to programming – I'll be your friendly guide, and we'll take this step by step. By the end of this tutorial, you'll be juggling variables like a pro!

What Are Variables?

Before we dive in, let's understand what variables are. Think of variables as little containers in your computer's memory that hold data. Just like how you might use different boxes to store different types of items in your room, we use variables to store different types of data in our programs.

Variable Declaration in PL/SQL

In PL/SQL, declaring a variable is like introducing yourself to someone new. You tell the program, "Hey, I'm here, and this is what I can hold!"

Basic Syntax

The basic syntax for declaring a variable in PL/SQL is:

variable_name datatype;

Let's break this down:

• variable_name is what you choose to call your variable
• datatype is the type of data the variable can hold

Examples

DECLARE
    my_name VARCHAR2(50);
    my_age NUMBER;
    is_student BOOLEAN;
BEGIN
    -- Your code here
END;

In this example:

• my_name can hold up to 50 characters
• my_age can hold a number
• is_student can be either TRUE or FALSE

Remember, in PL/SQL, we declare variables in the DECLARE section, before the BEGIN statement.

Common Data Types

Here's a table of common data types you'll encounter:

Data Type	Description	Example
VARCHAR2	Variable-length character string	'Hello, World!'
NUMBER	Numeric values	42, 3.14
DATE	Date and time values	'01-JAN-2023'
BOOLEAN	Logical values (TRUE/FALSE)	TRUE

Initializing Variables in PL/SQL

Now that we've declared our variables, let's give them some values! This process is called initialization.

Basic Initialization

You can initialize a variable right when you declare it:

DECLARE
    greeting VARCHAR2(20) := 'Hello, PL/SQL!';
    lucky_number NUMBER := 7;
BEGIN
    DBMS_OUTPUT.PUT_LINE(greeting);
    DBMS_OUTPUT.PUT_LINE('My lucky number is: ' || lucky_number);
END;

In this example, we're saying "Hello" to PL/SQL and sharing our lucky number. The := is the assignment operator in PL/SQL.

Initializing with NULL

Sometimes, you might want to declare a variable but not give it a value yet. In that case, you can use NULL:

DECLARE
    user_input VARCHAR2(100) := NULL;
BEGIN
    -- We'll get the user input later
END;

Variable Scope in PL/SQL

Variable scope is like a variable's neighborhood – it defines where the variable can be seen and used.

Local Variables

Variables declared in a block are local to that block. They can't be seen outside their block.

DECLARE
    outer_variable VARCHAR2(20) := 'I''m outside!';
BEGIN
    DBMS_OUTPUT.PUT_LINE(outer_variable);

    DECLARE
        inner_variable VARCHAR2(20) := 'I''m inside!';
    BEGIN
        DBMS_OUTPUT.PUT_LINE(outer_variable);  -- This works
        DBMS_OUTPUT.PUT_LINE(inner_variable);  -- This also works
    END;

    DBMS_OUTPUT.PUT_LINE(outer_variable);  -- This works
    -- DBMS_OUTPUT.PUT_LINE(inner_variable);  -- This would cause an error
END;

In this example, inner_variable is like a shy friend who only comes out in the inner block party. The outer block can't see it!

Global Variables

If you want a variable to be visible everywhere in your program, you can declare it in the outermost block.

DECLARE
    global_var NUMBER := 100;

    PROCEDURE print_global IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Global variable value: ' || global_var);
    END;

BEGIN
    print_global;
    global_var := global_var + 50;
    print_global;
END;

Here, global_var is like a popular kid – everyone knows them and can interact with them!

Assigning SQL Query Results to PL/SQL Variables

One of the coolest things about PL/SQL is how seamlessly it integrates with SQL. You can use SQL queries to assign values to your PL/SQL variables!

Single-Row Query

DECLARE
    employee_name VARCHAR2(100);
    employee_salary NUMBER;
BEGIN
    SELECT first_name || ' ' || last_name, salary
    INTO employee_name, employee_salary
    FROM employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: $' || employee_salary);
END;

In this example, we're fetching an employee's name and salary from the employees table and storing them in our PL/SQL variables.

Multiple-Row Query

For multiple rows, we need to use a cursor or a bulk collect. Here's an example with bulk collect:

DECLARE
    TYPE name_table_type IS TABLE OF VARCHAR2(100);
    employee_names name_table_type;
BEGIN
    SELECT first_name || ' ' || last_name
    BULK COLLECT INTO employee_names
    FROM employees
    WHERE department_id = 60;

    FOR i IN 1..employee_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ' || i || ': ' || employee_names(i));
    END LOOP;
END;

This script fetches all employee names from department 60 and stores them in a PL/SQL table. We then loop through this table to print each name.

And there you have it, folks! We've covered the basics of PL/SQL variables, from declaration to initialization, scope, and even how to populate them with SQL query results. Remember, practice makes perfect, so don't be afraid to experiment with these concepts.

Before I sign off, here's a little programmer joke for you: Why did the programmer quit his job? Because he didn't get arrays (a raise)! ?

Happy coding, and may your variables always be well-behaved!

Credits: Image by storyset