index<--

PL/SQL Syntax

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
begin
   return "Hello World";
end say_hello;

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

end HelloWorld;
/

Syntax Rules:

We learn a lot from the above example:

Syntax Rules:

• A PL/SQL program is created using packages, procedures and functions;
• Every package has two parts: Package Specification and Package Body;
• It uses English keywords: begin … end to establish a block of code;
• It is not case sensitive. This means keywords can be written with UPPERCASE or lowercase letters;
• In PL/SQL every program line end with semi-column ";"
• The comment line in PL/SQL start with "-"
• We can define a function with no parameters
• A function must return one result using return keyword
• We can define a procedure with no parameters

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:

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:

• we declare anonymous block of code using declare keyword;
• cursor keyword allow us to create an explicit cursor;
• we declare 2 variables using datatype varchar2 and number;
• we start executable region using begin keyword;
• we open the cursor using keyword "open"
• we iterate over the cursor items;
• we close the cursor using close keyword;
• we have used DBMS_OUTPUT Oracle package to display name and salary;
• we have used symbol || to concatenate strings.

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.

Double Characters

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

Keywords

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

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