PL/SQL—the procedural aspect

PL/SQL stands for Procedural Language-Structured Query Language. It is a significant member of the Oracle programming toolset and extensively used to code server-side programs. Some of its major accomplishments are that it:

• Supports programming constructs to design a program unit

• Implements business logic in an Oracle server using cursors and database objects such as packages, subprograms, and many more

• Makes the application portability easier

• Preserves execution privileges and transaction management

• Makes use of advanced PL/SQL features such as collections to process bulk data and enhance performance

• Allows external programs to be executed from PL/SQL

As a language, the different perceptions of PL/SQL are as follows:

• An interactive and structured language: The PL/SQL language comprises of a glossary of expressive and explanatory keywords. The self-indenting, structured feature, and ANSI compatibility ensures quick learning and adaptation for an individual.

• An embedded language : A PL/SQL program is not environment-dependent but can be easily invoked from any recognized Oracle development environment such as SQL* Plus, SQL Developer, TOAD, reporting tools, and so on.

• An integral language : A database manager can easily integrate a PL/SQL server-side program with other client-side programming interfaces such as Java, C++, or .NET. The PL/SQL procedures or subprograms can be invoked from client programs as executable statements.

My first PL/SQL program

A PL/SQL block is the elementary unit of a program which groups a set of executable procedural statements. A block has defined "start" and "end" stages and it has three forms:

• Anonymous: This block is an unnamed PL/SQL block which is persistent for single execution only

• Named: This block contains named PL/SQL programs which are stored physically in the database as schema objects

• Nested: A block within another PL/SQL block forms a nested block structure

The skeleton of a PL/SQL block has four sections:

• Header: This is an optional section which is required for the named blocks. It contains block name, block owner's name, parameter specifications, and return type specification (for functions).

• Declaration: This is an optional section which is used for declaration of local variables, cursors, and local subprograms. The DECLARE keyword indicates the beginning of the declaration section.

• Execution: This is the mandatory section of a PL/SQL block which contains the executable statements. These statements are parsed by the PL/SQL engine and executed on the block invocation. The BEGIN and END keywords indicate the beginning and end of an executable section.

• Exception: This is the optional section of the block which contains the exception handlers. The appropriate exception handler is activated upon any exception raised from the executable section to suggest alternate steps. The EXCEPTION keyword indicates the start of the exception section.

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

The following block diagram shows the structure of a PL/SQL block. The sections marked in red are the mandatory ones with the others being optional:

The PL/SQL following program illustrates the declaration and executable sections. The program declares a number variable, calculates its double value, and prints the result.

/*Enable the Serveroutput to display block messages*/
SET SERVEROUTPUT ON

Note

The SERVEROUTPUT parameter is a SQL*Plus variable which enables the printing of block debug messages. It is discussed in detail in the SQL*Plus section.

/*Start the PL/SQL block*/
DECLARE
/*Declare a local variable and initialize with a default value*/
   L_NUM NUMBER := 15;
   L_RES NUMBER;
BEGIN
/*Calculate the double of local variable*/
L_RES := L_NUM *2;
/*Print the result*/
   DBMS_OUTPUT.PUT_LINE('Double of '||TO_CHAR(L_NUM)||' is '||TO_CHAR(L_RES));
END;
/
Double of 15 is 30

PL/SQL procedure successfully completed.