This is a short PLSQL tutorial (one of a series) providing an introduction to Oracle's PL/SQL. 

• This tutorial discusses the language elements of PL/SQL. 
• The introduction looks at what PL/SQL can do.
• Part 2 looks at why and when to use PL/SQL
• Part3 looks at integrating PL/SQL with SQL.

PL/SQL Language Elements

PL/SQL has virtually all the language elements that you'd expect to find in a high-level language. In a short tutorial like this there's only room to provide an overview of the language elements so here it is.

Control Statements

The control statements are:

• if ... then ... else ...
• case (Oracle 9i and above)
• go to
• exception handling
• 4 types of loop statements: WHILE loops; cursor FOR loops; numeric FOR loops; and simple LOOPs terminated by an EXIT statement. See here for a quick tutorial on using LOOPs.

Oracle Tips & Tricks to SKYROCKET Your Career!

Blocks

As PL/SQL is block structured you can decompose your programs into functions, procedures and anonymous blocks and group these blocks into packages. Also, each type of block may be nested in any other type of block.

PL/SQL Procedures

Procedures are defined like so:-

CREATE OR REPLACE PROCEDURE <my_procedure> IS

    < my type and data definitions
    (including any sub-procedures/functions)>

BEGIN

    <my procedure code>;

END <my_procedure>;

PL/SQL Functions

To define a function, just replace the keyword PROCEDURE with the keyword FUNCTION and add RETURN <return datatype>. For example a function called first_and_last returning a character string would be declared as follows

CREATE OR REPLACE FUNCTION first_and_last
(first_name VARCHAR2, last_name VARCHAR2)
RETURN VARCHAR2 IS
BEGIN

    RETURN first_name||' '||last_name;

END first_and_last;

There's not space in a short PLSQL tutorial like this to go into much detail, but one thing to note is that functions always have to return a value otherwise an error (called an exception) occurs and your program may be terminated (depending on whether or not the exception has been handled).

PL/SQL Packages

Packages are defined in two parts:

1. the package specification - which defines the objects (types, procedures, functions and variables) available to external code units (only the interface to procedures and functions is defined in the package specification).
   
   CREATE OR REPLACE PACKAGE <my_package> AS
      <public type/cursor/procedure/function/variable declarations>
   END <my_package>;
   
   
2. the package body which defines private objects (available only to routines in this package) and completes the definition of public procedures and functions declared in the package spec.
   
   CREATE OR REPLACE PACKAGE BODY <my_package> AS
     <private type/cursor/procedure/function/variable declarations>
     <completion of public procedures/functions     declared in the package specification>
   END <my_package>;
   

PL/SQL Data Types

Available data types include:

• fixed and variable length character strings (CHAR and VARCHAR2)
• BOOLEAN
• floating point (NUMBER)and integer (INTEGER and PLS_INTEGER)
• date and time (including DATE with a resolution to one hundredth of a second and, since Oracle9i, TIMESTAMP with a resolution to one billionth of a second )
• internet types (XML and URI (Universal Resource Identifier))
• large object types - hold up to 4 GBytes of data:
  • BLOB -binary large object stored in the database
  • CLOB - character large object data stored in the database
  • BFILE - pointer to a file up to 4 GBytes in size held outside the database, only read access is allowed
• user defined types - based on built-in types
• structured types:
  • records
  • collection types - one dimensional array-type structures
    • PL/SQL tables - also known as index-by tables, collections and associative arrays, these are unbounded (no upper or lower limit), sparse (not every cell has to be populated or defined)
    • Nested /object tables - these differ from associative arrays in that they are dense to start with (i.e. the data occupies consecutive cells), these are unbounded
    • VARRAYS - variable-sized arrays, these are bounded and dense

In an effort to keep this tutorial to a reasonable length there won't be any details on how to use these data types (this tutorial is only intended to be an overview after all!). Instead let's save that for a future PL/SQL tutorial.

PL/SQL Identifier Naming Rules

Let's take just a brief diversion into the rules for naming objects (variables, functions, procedures, packages etc), as these rules should be present even in an introductory PLSQL tutorial like this.

Many words (especially those that form part of PL/SQL itself such as BEGIN, OPEN, DATE etc.) are reserved - that is they can't be used for user-defined identifiers on they're own. Although you can add a suitable prefix or suffix to create a new word such as START_DATE etc.

The rules for naming identifiers are as follows:

• Must start with a letter
• Can contain $, _ (the underscore) and # and the digits 0 to 9
• Cannot contain spaces or any other special characters
• Cannot be more than 30 characters in length
• Case differences are ignored

This means that the following declarations are illegal:

• plsql tutorial blob;       -- contains a space
• 1 PL/SQL tutorial blob;  -- starts with a number
• PLSQL tutorial-1 blob;    -- contains dashes
• begin varchar2(30);         -- reserved word
• x123456789012345678901234567890 number; -- too long
  

Continue with the next plsql tutorial to learn why and when you should use pl/sql. 

----------------

Looking for instructor-led Oracle PLSQL training? See here for Oracle PLSQL training in the UK or here for Oracle PL/SQL training in New Zealand.

Return to Introductory PLSQL Tutorial for an overview of pl/sql.