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.
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:
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
DECLAREkeyword 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
BEGINandENDkeywords 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
EXCEPTIONkeyword 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.