PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.

PL/SQL Syntax

PL/SQL syntax is inspired from Ada language and is not a curly braced language like C++ or Java.

Here are some rules:

  1. A PL/SQL program is created using packages, procedures and functions;
  2. Every package has two parts: Package Specification and Package Body;
  3. We use English keywords: begin … end to establish a block of code;
  4. The language is not case sensitive. This means keywords can be written with UPPERCASE or lowercase letters;
  5. Every program line end with semi-column “;”

Let’s create an example:

create or replace package HelloWorld as

------------------------------------
-- a function can return one result
------------------------------------
function say_hello return varchar2;

------------------------------------
-- a procedure perform an action
-- a procedure do not have results
------------------------------------
procedure hello;

end HelloWorld;
/

create or replace package body HelloWorld as

function say_hello return varchar2 is
begin
   return "Hello World");
end say_hello;

procedure hello is
begin
   DBMS_OUTPUT.put_line("Hello World");
end;

end HelloWorld;
/

More syntax rules:

We learn a lot from the above example:

  1. The comment line in PL/SQL start with “–“
  2. We can define a function with no parameters
  3. A function must return one result using return keyword
  4. We can define a procedure with no parameters

SQL in PL/SQL

The most interesting feature of PL/SQL is capability to compile embedded SQL  in the package. This allow the package to be 100% compatible and related to database structure. If the database structure change the PL/SQL packages that are stored in the database become invalid and must be compiled again by a developer to become functional. An invalid package can’t be executed. This is of enormous value avoiding any run-time errors that could be catastrophic.

Embedded SQL

The SQL in other computer languages is usually a string. This string must be send to a database for execution to know if is a correct SQL. To avoid any errors developers must create unit testing just to check if the SQL has a valid syntax. In PL/SQL we can use SQL Keywords to create statements.

Here are some ways to create embedded SQL statements:

  1. Create an explicit cursor;
  2. Create an implicit cursor;
  3. Use select … into to capture SQL result;
  4. Use PL/SQL automatic variable binding;
  5. Use bulk DML operations;
  6. Create dynamic SQL;

Let’s analyze an example:

declare
  -- cursor declaration
  cursor c_persons is
    select name, salary 
      from employees
     where salary > 10000;

  -- variable declaration
  v_name   varchar2(100);
  v_salary number;
begin
  open c_persons;
  loop
    fetch c_persons into v_name, v_salary;
    exit when c_persons#notfound; 
    dbms_output.put_line(v_name||","||to_char(salary));
  end loop;
  close c_persons;
end;

In this example we have new syntax elements:

  1. we declare anonymous block of code using declare keyword;
  2. cursor keyword allow us to create an explicit cursor;
  3. we declare 2 variables using datatype varchar2 and number;
  4. we start executable region using begin keyword;
  5. we open the cursor using keyword “open”
  6. we iterate over the cursor items;
  7. we close the cursor using close keyword;
  8. we have used DBMS_OUTPUT Oracle package to display name and salary;
  9. we have used symbol || to concatenate strings.

Simple Symbols

Oracle use simple symbols almost like any other language. Some symbols are particular for PL/SQL. For example % is most of the time used as modulo operator but in PL/SQL is used to get attribute from some objects. The symbol “:” also has a special use to prefix a variable in dynamic SQL.

SymbolMeaning
+addition operator
%attribute indicator
character string delimiter
.component selector
/division operator
(expression or list delimiter
)expression or list delimiter
:host variable indicator
,item separator
*multiplication operator
quoted identifier delimiter
=relational operator
<relational operator
>relational operator
@remote access indicator
;statement terminator
subtraction/negation operator

Two symbols

Two symbols used side by side can be used to represent one operator.

SymbolMeaning
:=assignment operator
=>association operator: used for named parameters
||concatenation operator for strings
**exponentiation operator
<<label delimiter (begin) <<label>>
>>label delimiter (end)  <<label>>
/*multi-line comment delimiter (begin)
*/multi-line comment delimiter (end)
..range operator (from..to)
<>relational operator (divergence)
!=relational operator (divergence)
~=relational operator (divergence)
^=relational operator
<=relational operator: less then or equal to
>=relational operator: greater then or equal to
single-line comment indicator

Keywords

Next we enumerate some of the most significant PL/SQL keywords

KeywordMeaning
packageused to create a package specification and package body
proceduredeclare a procedure
functiondeclare a function
returncreate exit point for procedures and function
declarestart an anonymous block of code
endend a block of code
asused for declarations
isused for declarations
loopcreate an infinite loop
whencreate a loop exit point or used in exception region or case statement
exitbreak a loop and continue after the end (can be used with “when”)
continuecontinue a loop from start
exceptioncreate an exception region into a block of code or declare an exception
raisecreate a user exception
ifused to make a decision based on a condition
thenused after condition to make a decision block
elseused for decision block as alternative when condition is false or default case for case statement
whilecreate a loop that execute as long as a condition is true
typedeclare a type
recorddeclare a record type
tableused to create in memory collection of values or records
cursorcreate or describe a cursor
fetchread current line into a cursor and progress to next step
casecreate a multi-condition decision in combination with “when”, “then”,”else”
objectused to create an object. Oracle is object oriented.
selectused to create a query
intoused to collect data into a variable from sql
insertused to create new records into a table
updateused to update data into a table
deleteused to remove data from a table
commitused to commit transactions
rollbackused to undo modifications
savepointused to create a point in time to rollback to
parallelused to create parallel query
pragmagive additional hints to PL/SQL compiler for special purposes
deterministicgive additional hints about a function to the compiler