Sage-Code Laboratory

PL/SQL Syntax

PL/SQL is a procedural language designed specifically to embed SQL statements in stored procedures. Program units are compiled by the Oracle Database Engine and stored inside the database. And at run-time, both PL/SQL and SQL are running within the same server process. This technology makes PL/SQL code reliable and efficient.

PL/SQL syntax is inspired from Ada language and is not a curly braced language like C++ or Java. That makes PL/SQL code very readable. Though it is not a perfect language, it worth learning. We consider this language succesful and inspired. I believe it is a masterpiece.

First Example

Next example demonstrate two parts:

Package Specification.

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;

Package Body

create or replace package body HelloWorld as

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

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

end HelloWorld;

Syntax Rules:

We learn a lot from the above example:

Syntax Rules:

Embedded 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.

Parsed 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:

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

  -- variable declaration
  v_name   varchar2(100);
  v_salary number;
  open c_persons;
    fetch c_persons into v_name, v_salary;
    exit when c_persons#notfound; 
  end loop;
  close c_persons;

In this example we have new syntax elements:

Operators & Symbols

It is tedious to explain and learn all the operators. So I have made two tables for a better understanding. PL/SQL has much in common with Ada for selecting the symbols. It uses one or two succesive characters to make an operator or punctuation symbol:

Single Character

Oracle use simple character 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.

Symbol Meaning
+ 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

Double Characters

PL/SQL uses two character characters together to represent next symbol.

Symbol Meaning
:= 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 (
<> 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


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

Keyword Meaning
package used to create a package specification and package body
procedure declare a procedure
function declare a function
return create exit point for procedures and function
declare start an anonymous block of code
end end a block of code
as used for declarations
is used for declarations
loop create an infinite loop
when create a loop exit point or used in exception region or case statement
exit break a loop and continue after the end (can be used with "when")
continue continue a loop from start
exception create an exception region into a block of code or declare an exception
raise create a user exception
if used to make a decision based on a condition
then used after condition to make a decision block
else used for decision block as alternative when condition is false or default case for case statement
while create a loop that execute as long as a condition is true
type declare a type
record declare a record type
table used to create in memory collection of values or records
cursor create or describe a cursor
fetch read current line into a cursor and progress to next step
case create a multi-condition decision in combination with "when", "then","else"
object used to create an object. Oracle is object oriented.
select used to create a query
into used to collect data into a variable from sql
insert used to create new records into a table
update used to update data into a table
delete used to remove data from a table
commit used to commit transactions
rollback used to undo modifications
savepoint used to create a point in time to rollback to
parallel used to create parallel query
pragma give additional hints to PL/SQL compiler for special purposes
deterministic give additional hints about a function to the compiler

Congratulation! You have learned a lot already. Take a short break an continue with next page later. We will not go anyware.

Read next: Structure