An Introduction to PL/SQL: How It Works with SQL and Oracle Database

At a new company, PL/SQL is required to join the team. Although I’ve focused more on the front-end side, to become a full-stack developer, I plan to leverage my knowledge and skills in databases.

I’ll start by covering the fundamentals. As I gain more experience and learn from my mistakes, I’ll dive into more advanced topics and practical cases.

1. What is PL/SQL?
2. How It Works
3. Block-Structured Language
4. Nesting Blocks
5. How to Run Blocks
6. Naming Blocks
7. SQL Code in PL/SQL Blocks

What is PL/SQL?
PL/SQL is an extension of SQL, designed specifically for the Oracle Database. PL/SQL stands for Procedural Language/ Structured Query Language. While SQL is declarative, meaning SQL itself determines how to execute a command (e.g., when selecting data, we just say SELECT, and SQL returns the result), PL/SQL is imperative. This means we explicitly write the code that tells the system how to execute the command. PL/SQL is a more flexible and powerful data programming language. Note that PL/SQL can only be executed within the Oracle Database and is not designed for standalone languages like Java or C#.

How It Works
PL/SQL code is compiled by the Oracle Database server and stored within the database. At run-time, both PL/SQL and SQL run within the same server process.

Block-Structured Language
PL/SQL is a block-structured language, similar to JavaScript. A PL/SQL block consists of three sections: Declarative, Executable, and Exception Handling, which are separated by the keywords DECLARE, BEGIN, EXCEPTION, and END.

Example from the Oracle Documentation blog:

DECLARE
    l_message
    VARCHAR2 (100) := ‘Hello World!’;
BEGIN
    DBMS_OUTPUT.put_line(l_message);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(SQLERRM);
END;

Section Explanations:
Declarative: This section contains statements to declare variables, constants, data types, and other code elements.

Executable: This section contains statements that are executed when the block is run.

Exception handling: This section handles errors and catches exceptions when they occur.

The Declarative and exception-handling sections are optional. Only the Executable section is mandatory. Therefore, the following block is perfectly valid.

BEGIN
    DBMS_OUTPUT.put_line(’Hello World!’);
END;

Nesting Blocks
PL/SQL blocks can be nested within other blocks.

How to Run Blocks
There are various tools available for running PL/SQL blocks, and developers often have personal preferences.

SQL*Plus: This is a command-line interface that allows execution of both SQL and PL/SQL. It is often seen as a basic, no-frills tool provided by Oracle for executing SQL statements and PL/SQL blocks.

IDEs: These tools offer more features and enhance productivity. Popular IDEs for PL/SQL development include Oracle SQL Developer and PL/SQL Developer, which provide graphical interfaces that make writing, debugging, and managing PL/SQL code more efficient.

Naming Blocks
The blocks shared above don’t have names. They are called “anonymous blocks” and are often used as temporary PL/SQL units. On the other hand, named blocks, or subprograms, can be useful because they can be called multiple times without needing to declare or write the same repetitive code. They are similar to functions or methods in languages like JavaScript.

Example:

CREATE OR REPLACE FUNCTION
hello_message
    (place_in IN VARCHAR2)
    RETURN VARCHAR2
IS
BEGIN
    RETURN ‘Hello ‘ || place_in;
END hello_message;

The details will be explained later, but for now, it’s important to know that blocks can be named.

SQL Code in PL/SQL Blocks
As mentioned earlier, both PL/SQL and SQL run within the same server process at runtime. Programs written in PL/SQL interact with the Oracle Database using SQL to read from or write to the database. The Oracle Database facilitates this by allowing SQL and PL/SQL to be combined seamlessly.

Example from the Oracle documentation blog.

DECLARE
    l_name employees.last_name%TYPE;
BEGIN
    SELECT last_name
        INTO l_name
        FROM employees
        WHERE employee_id = 138;

    DBMS_OUTPUT.put_line(l_name);
END;

The SELECT, FROM, and WHERE classes are SQL statements but can be used within PL/SQL blocks.

In this blog post, I discussed what PL/SQL is, how it works, and its relationship with SQL and the Oracle Database. As I continue to learn, I may discover additional insights that I initially missed. I’ll revisit and update this post to ensure it provides the best possible understanding.