Introducing PL/SQL

PL/SQL is the language of choice for data-centric application development in Oracle databases. But like any programming language, it can also be used to write inefficient and
overly resource intensive programs.

The aim of this book is to present necessary tools to increase the quality and efficiency of PL/SQL code.  This is not a “teach yourself to program PL/SQL” book, and as such requires some previous experience of the PL/SQL language.

Where possible, the examples in this book are totally self-contained, but some may require extra privileges not assigned to test users in a normal environment.  For this reason it may be useful to set up a test user specifically for the examples given throughout this book as shown below.

CONN sys/password AS SYSDBA 

CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
ALTER USER test QUOTA UNLIMITED ON users; 

GRANT CONNECT TO test;
GRANT CREATE PROCEDURE TO test;
GRANT CREATE TYPE TO test;
GRANT EXECUTE ON dbms_lock TO test;
GRANT SELECT_CATALOG_ROLE TO test;

Any additional privileges will be described on a case-by-case basis.

The rest of this chapter will focus on an overview of PL/SQL, which will help to put the rest of the book into context.  Hopefully, none of this information will be completely new.

What is PL/SQL and Why Should I use It?

In most programming languages, database work involves connecting to the server, mapping datatypes and manually preparing and processing result sets. PL/SQL is a procedural language that is so tightly integrated with the SQL language that most of these tasks are either eliminated completely or incredibly simple.

The datatypes available in PL/SQL are a superset of those available in SQL, so datatype conversions between SQL and PL/SQL are rarely needed.  As a result PL/SQL allows interaction with both the data and metadata of database objects with greater ease and efficiency than is possible with most other languages. In addition PL/SQL supports dynamic SQL allowing statements to be created at runtime for greater flexibility.

Running application logic as PL/SQL on the database server can increase efficiency by reducing network traffic.  When business logic is processed in a client application, it is often necessary to pass a succession of statements between the application and the database server.  Each request and response involves network traffic, which can greatly affect overall performance. 

Passing a PL/SQL block containing multiple statements to the server can reduce network round trips, thereby improving performance.  Storing application code in the database takes this a step further as application logic is removed from the client layer and precompiled in the database, allowing modification without the need for a redeployment of client software.

The PL/SQL language is available on all platforms supported by Oracle, making it significantly more portable than many programming languages.  When application logic is located within the database, changes in client programming models have a reduced impact, as only presentation of the data is controlled at that level.

Figure 1.1: Using PL/SQL to improve performance.

Centralizing application logic enables a higher degree of security and productivity.  The use of Application Program Interfaces (APIs) can abstract complex data structures and security implementations from client application developers, leaving them free to do what they do best.

Oracle has continued to improve support for object orientated programming in PL/SQL.  This is a great marketing feature, but in reality few client application tools cope natively with Oracle object types.  As a result, the usage of this feature is often limited by the client tools accessing the data.

PL/SQL Architecture

The PL/SQL language is made up of both procedural code and SQL statements.  When valid PL/SQL code is executed, the PL/SQL engine executes all procedural code and sends SQL statements to the SQL engine of the database server.  Figure 1.2 represents this process in action for a PL/SQL block.

Figure 1.2 – PL/SQL Architecture.

The Oracle database contains a PL/SQL engine, which is used to execute all stored procedures, functions, packages, objects and triggers.  This allows application logic to be processed entirely within the database layer. 

Some application development tools, such as Oracle Forms and Oracle Reports, have their own PL/SQL engine, allowing procedural logic to be processed with no reference to the database server.

Overview of PL/SQL Elements

Blocks in PL/SQL

Blocks are the organizational unit for all PL/SQL code, whether it is in the form of an anonymous block, procedure, function, trigger or type.  A PL/SQL block is made up of three sections: declaration, executable and exception. Only the executable section is mandatory.

[DECLARE
  -- delarations]
BEGIN
  -- statements
[EXCEPTION
  -- handlers
END;

Based on this definition, the simplest valid block is shown below, but it does not do anything.

BEGIN
  NULL;
END;

The optional declaration section allows variables, types, procedures and functions do be defined for use within the block.  The scope of these declarations is limited to the code within the block itself, or any nested blocks or procedure calls.  The limited scope of variable declarations is shown by the following two examples. In the first, a variable is declared in the outer block and is referenced successfully in a nested block.  In the second, a variable is declared in a nested block and referenced from the outer block, resulting in an error as the variable is out of scope.

DECLARE
  l_number  NUMBER;
BEGIN
  l_number := 1;

  BEGIN
    l_number := 2;
  END;
END;
/

PL/SQL procedure successfully completed.

BEGIN
  DECLARE
    l_number  NUMBER;
  BEGIN
    l_number := 1;
  END; 

  l_number := 2;
END;
/
  l_number := 2;
  *
ERROR at line 8:
ORA-06550: line 8, column 3:
PLS-00201: identifier 'L_NUMBER' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored 

SQL>

The main work is done in the mandatory executable section of the block, while the optional exception section is where all error processing is placed.  The following two examples demonstrate the usage of exception handlers for trapping error messages.  In the first, there is no exception handler so a query returning no rows results in an error. In the second, the same error is trapped by the exception handler, allowing the code to complete successfully.

DECLARE
  l_date  DATE;
BEGIN
  SELECT SYSDATE
  INTO   l_date
  FROM   dual
  WHERE  1=2; -- For zero rows
END;
/
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4 

DECLARE
  l_date  DATE;
BEGIN
  SELECT SYSDATE
  INTO   l_date
  FROM   dual
  WHERE  1=2; -- For zero rows
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

PL/SQL procedure successfully completed.