WHAT IS PL/SQL

PL/SQL (Procedural Language for SQL) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 6 - stored PL/SQL procedures/functions/packages/triggers since version 7), Times Ten in-memory database (since version 11.2.1), and IBM Db2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

PL/SQL includes procedural language elements such as conditions and loops, and can handle exceptions (run-time errors). It allows declaration of constants and variables, procedures, functions, packages, types and variables of those types, and triggers. Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.

Historically, the first public version of PL/SQL definition[2] was in 1995, and the Oracle's inception year ~1992. It implements the ISO SQL/PSM standard.[3]

PL/SQL program unit[edit]

The main feature of SQL (non-procedural) is also its drawback: control statements (decision-making or iterative control) cannot be used if only SQL is to be used. PL/SQL provides the functionality of other procedural programming languages, such as decision making, iteration etc. A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is developed, compiled, and ultimately executed on the database.[4]

PL/SQL anonymous block[edit]

The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label.[5]

For example:

<<label>>   -- this is optional
DECLARE
-- this section is optional
  number1 NUMBER(2);
  number2 number1%TYPE := 17;             -- value default
  text1   VARCHAR2(12) := '      Hello world       ';
  text2   DATE         := SYSDATE;