Sage-Code Laboratory
index<--

PL/SQL Structure

PL/SQL use procedural programming. It is similar to Ada language. It has procedures and functions. In this article I will explain the blocks of code used to construct a comprehensive program structure.

Anonymous block

The most simple code block in PL/SQL is the anonymous block. This is a section of code that have several statements but no name. We use this structure to create a sequence of statements that are executed one after another from top to bottom in order. We can declare local variables to be visible in the scope of this simple section.

Syntax:

<<label>>
declare    
  -- Declaration region for local types, variables & subprograms
begin
  -- Executable region
  ...
exception
  -- Exception handlers
  when <exception> then
    -- do something 
  when <exception> then
    -- do something else
  when OTHERS then
    -- do something
end;

As you can see this is very similar to Ada. We use a block to create a local scope for variables, types and subprograms. Also we create this kind of block when we have a program that may fail, to catch the exceptions, similar to Java: try … catch block. The block can have a label using notation <<label>> but this is optional and nobody uses it. Well almost nobody 🙂

Nested Structure

Blocks can be nested. In the declaration region of the block we can define types, variables and other blocks or subprograms. That is a big deal if you consider each subprogram can have it's own local scope. The big invention is that inner blocks can have access to outer blocks variables. So the inner scope can see variables from outer scope. If one variable is declared twice in outer block and inner block the outer block variable is hidden.

Named blocks

Using same structure PL/SQL define several other blocks: Packages, Functions, Procedures and Triggers. These structures do not start with declare but instead use a keyword that is the type of the block. The syntax is similar but the first line is different. The most large structure is the package. This can contain several other subprograms like procedures and functions.

Procedure

The procedure can be independent or can be stored in a package. The difference between a procedure an a block is that a procedure can be executed one or many times while a block is executed a single time. Except if the block is inside a procedure or function. A procedure is ended after the last statement before the exception region or can be ended using return keyword.

Example:

procedure get_price (quantity NUMBER, value NUMBER) is
  error_message VARCHAR2(30) := 'Quantity cant be zero.';
  price number;
begin
  price:= value/quantity;
  DBMS_OUTPUT.PUT_LINE(price);
exception
  when ZERO_DIVIDE then
    DBMS_OUTPUT.PUT_LINE(error_message);
end get_price;

In this example we demonstrate PROCEDURE keyword can be uppercase in PL/SQL. The procedure can receive parameters: quantity and value. The exception region can analyze one kind of error that is ZERO_DIVIDE predefined in Oracle. A procedure do not return a result but can have side-effects.

Function

The function in PL/SQL is just like procedure except we use FUNCTION keyword. A function can return a result. That is the only difference. PL/SQL is not a functional language. Therefore we do not have higher order functions. In PL/SQL a function can also have side-effects.

function get_price (quantity NUMBER, value NUMBER) return number is
  error_message VARCHAR2(30) := 'Quantity cant be zero.';
  price number;
begin
  price:= value/quantity;
  return price;
exception
  when ZERO_DIVIDE then
    DBMS_OUTPUT.PUT_LINE(error_message);
end get_price;

External SQL Script

The function and procedure can exist as independent database objects. For this purpose we incorporate all the functions and procedure into a script. This script can be run using sqlplus. To run the script we use notation @script_name.sql and press enter. The script use statement: CRATE OR REPLACE for every function or procedure. The script file end with symbol / (run), otherwise the script will not create the objects defined inside.

Script Syntax:

create or replace
function get_price (quantity NUMBER, value NUMBER) return number is
  error_message VARCHAR2(30) := 'Quantity cant be zero.';
  price number;
begin
  price:= value/quantity;
  return price;
exception
  when ZERO_DIVIDE then
    DBMS_OUTPUT.PUT_LINE(error_message);
end get_price;
/

Note: In Oracle we can break a statement into many lines. Every statement must end with ";" so the compiler can absorb the line breaks;

The Package

One of the most powerful feature of Oracle is the package. PL/SQL is a modular language like Ada. We can create multiple modules or components that can communicate to each other into a "Session". That is all happening in memory so it is very fast. Once a user is connected to a database he has created a session that is the user session. Each session can call user defined programs that are stored in packages. The sessions are isolated so each package can have multiple instances that can work safe in memory for each user.

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. You can think of a package as an application.

Packages have dependence to database structure, objects and other packages. Two packages can even depend on each other. That is also known as circular dependency that should be avoided. Packages can be encrypted so that code can be hidden from unauthorized access even from database administrator.

Packages have 2 parts that can be stored in two separated file scripts or one single file script. The best is to have 2 scripts. The file extension can be *.pks and *.pkb. If we use one script the extension can be *.sql or *.pkc. This is just a naming convention. To install a package into Database you must run this script.

Example:

In the next example we create a simple package that has one member: function "price": The function is indented two spaces but this is optional indentation. Unlike python the indentation level has no significance in PL/SQL. Just that developers align the code to be easy to read.

create package test as
  function price (a number, b number) return number;
end test;
/

create package body test as
  function price (quantity NUMBER, value NUMBER) return number is
     error_message VARCHAR2(30) := 'Quantity cant be zero.';
     result number;
  begin
     price:= value/quantity;
     return result;
  exception
     when ZERO_DIVIDE then
        DBMS_OUTPUT.put_line(error_message);
  end price;

end test;
/

Package Syntax:

The package syntax is a little bit more complicated then the above example. A package is organized using implicit regions. These regions are expected by the compiler and have importance in the package functionality. Many developers of PL/SQL work for years and have no clear understanding of the package regions.

Specification:

package package_name
is
 -- declarations of variables and types

 -- specifications of cursors

 -- specifications of modules

end package_name;

Implementation:

package body package_name
is
 -- declarations of variables and types

 -- specification and SELECT statement of cursors

 -- specification and body of modules

begin
   -- executable statements
   ...
exception
   -- exception handler
   ...
end [ package_name ];

In the above syntax I have used [ … ] notation to indicate this is optional. In the package implementation the BEGIN keyword starts the executable region of a package. This is executed one single time when the package is first used. Then the package is in memory loaded and ready to be used multiple times. The initialization region can't be executed again until the user disconnect.

Package Usability

To use a package we must prefix the member of the package with the package name. So we use syntax: package_name.member_name; The catch is we can use only the members that are published in the specification. The members defined in the package body are private to the package and can't be used outside of the package.

So the package has a public scope and a local scope. The "scope" is a region of memory that has a specific visibility relative to different section of the program. So the local members are visible only inside local procedures and functions while the public members can be used in other packages and also local inside the current package.

Order of declarations

The blocks of code or sections of code can be nested. Once a member is defined it can be used in the next member. PL/SQL do not use Hoisting technique. That means a member can't be used before is defined. Therefore the structure of PL/SQL packages often is upside-down. The main procedure or function is always at the bottom of the package before the executable region. This is if the package has a main procedure.

Conclusion: PL/SQL structure is very similar to Ada. Once you have learned PL/SQL you can easy understand Ada language.

Read next: Flow Control