PL/SQL Basics

PL/SQL stands for "Procedural Language for SQL". The Oracle Corporation created PL/SQL in the 1980's for its own Oracle database to supplement basic SQL with procedures, functions, packages, and triggers. Below are some of the major differences between SQL and PL/SQL:

SQL	PL/SQL
Executes one operation at a time	Executes multiple operation at a time
Declarative language	Procedural language
Data-oriented language	Application-oriented language
No variables	Variables
Interacts with database server	Does not interact with database server
No for loop or if statement	for loop, while loop, and if statements

As the above table shows, PL/SQL fills in many of the functionalities of typical high level programming languages. Now to take a look at some examples, we'll start with the anonymous block. The anonymous block is comprised of three parts, separated by four keywords, as such:
DECLARE
Declaration Section
BEGIN
Execution Section
EXCEPTION
Exception Section
END;

The declaration section is for the declaration of variables/constants. Note the use of :=, which is PL/SQL's assignment operator. The execution section is where the behavior is coded, and the exception section is where errors are handled. While the declaration and exception sections are optional, the execution section is required (the label is also optional).

<<label>>
DECLARE
  number1 NUMBER(2);
  number2 number1%TYPE := 17;             -- value default
  text1   VARCHAR2(12) := 'Hello world';
  text2   DATE         := SYSDATE;        -- current date and time
BEGIN
  SELECT street_number
    INTO number1
    FROM address
    WHERE name = 'INU';
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode));
     DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm);
END;

Functions are formatted similarly, with declarations at the top that include input and return type, a declaration (the "business", and an exception block:

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] <IS|AS>   -- heading part
amount number;                              -- declaration block
BEGIN                                       -- executable part
    <PL/SQL block with return statement>
        RETURN <return_value>;
[Exception
    none]
        RETURN <return_value>;
END;

Pipeline functions return collection but are performance-optimized so that each row that is created in the return dataset is returned as it's created—as opposed to waiting for the entire collection to be finished. Notice the use of keyword PIPELINED and the call of PIPE ROW that delineates the structure for each individual row:

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] [<AGGREGATE | PIPELINED>] <IS|USING>
    [declaration block]
BEGIN
    <PL/SQL block with return statement>
        PIPE ROW(<return type>);
        RETURN;
[Exception
    exception block]
        PIPE ROW <return type>;
        RETURN;
END;

Procedures are like functions but vary is two key ways: functions should only return a single value/collection, whereas procedures can return multiple, and while procedures can't be used in SQL statements, functions actually can.

CREATE PROCEDURE create_email_address (   -- Procedure heading
    name1 VARCHAR2,
    name2 VARCHAR2,
    company VARCHAR2,
    email OUT VARCHAR2
)                                         -- Procedure
AS
                                          -- Declarative (optional)
error_message VARCHAR2(30) := 'Email address is too long.';
BEGIN                                     -- Executable (required)
    email := name1 || '.' || name2 || '@' || company;
EXCEPTION                                 -- Exception (optional)
WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE(error_message);
END create_email_address;

Conditionals

Conditionals are fairly straight-forward as they read well in English, but note the need for an END IF statement. END statements are a common theme.

IF x = 1 THEN
   sequence_of_statements_1;
ELSIF x = 2 THEN
   sequence_of_statements_2;
ELSIF x = 3 THEN
   sequence_of_statements_3;
ELSE
   sequence_of_statements_N;
END IF;

CASE statements are much like if statements but potentially simpler to write:

CASE
   WHEN x = 1 THEN sequence_of_statements_1;
   WHEN x = 2 THEN sequence_of_statements_2;
   WHEN x = 3 THEN sequence_of_statements_3;
   ELSE sequence_of_statements_N;
END CASE;

Loops are a huge benefit of PL/SQL. Here's a very basic outline of a FOR loop, which will log the numbers between 0 and 10, as indicated by the range operator ("..") between the two numbers, and the "DBMS_OUTPUT.PUT_LINE()" method.

DECLARE
    var NUMBER;
BEGIN
    FOR var IN 0 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(var);
    END LOOP;
END;

I hope you've enjoyed this brief intro to PL/SQL as much as I enjoyed looking into it! I'm definitely excited to continue my journey into database topics by diving further into Oracle Database, the most popular database system in the world!

source