Oracle SQL/PLSQL Section 2.2: Definition of PL/SQL.
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL
and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory
database (since version 11.2.1), and IBM DB2 (since version 9.7).
The basic unit in PL/SQL is called a block, which is made up of three parts: a declarative part, an executable part,
and an exception-building part.
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<EXCEPTION handling>
END;
Declarations - This section starts with the keyword DECLARE. It is an optional section and defines all variables,
cursors, subprograms, and other elements to be used in the program.
Executable Commands - This section is enclosed between the keywords BEGIN and END and it is a mandatory
section. It consists of the executable PL/SQL statements of the program. It should have at least one executable line
of code, which may be just a NULL command to indicate that nothing should be executed.
Exception Handling - This section starts with the keyword EXCEPTION. This section is again optional and contains
exception(s) that handle errors in the program.
Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within other PL/SQL blocks using
BEGIN and END.
In an anonymous block, only the executable part of the block is required, other parts are not necessary. Below is an example of
simple anonymous code, which does not do anything but perform without error reporting.
BEGIN
NULL;
END;
/
Missing executable instruction leads to an error because PL/SQL does not support empty blocks. For example,
execution of the ode below leads to an error:
BEGIN
END;
/
The application will raise the error:
END;
*
ERROR AT line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "END" WHEN expecting one OF the following:
( BEGIN CASE DECLARE EXIT FOR GOTO IF LOOP MOD NULL PRAGMA
RAISE RETURN SELECT UPDATE WHILE WITH <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue CLOSE CURRENT DELETE FETCH LOCK INSERT OPEN ROLLBACK
SAVEPOINT SET SQL EXECUTE COMMIT FORALL MERGE pipe purge.
Symbol " * " in the line below the keyword "END;" means, that the block which ends with this block is empty or badly
constructed. Every execution block needs instructions to do, even if it does nothing, like in our example.
0 Comments