PL/SQL is a block-structured language and a PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END, which break up the block into three sections:
Declarative: statements that declare variables, constants, and other code elements, which can then be used within that block
Executable: statements that are run when the block is executed
Exception handling: a specially structured section you can use to catch, or trap, any exceptions that are raised when the executable section runs
Only the executable section is required. You do not have to declare anything in a block, and you do not have to trap exceptions raised in that block.
A block itself is an executable statement, so you can nest blocks within other blocks. Here are some examples:
The classic Hello World! block contains an executable section that calls the DBMS_OUTPUT.PUT_LINE procedure to display text on the screen:
DBMS_OUTPUT.put_line ('Hello World!');
Functions and procedures types of named blocks are discussed later in this article in more detail, as are packages.
A package is a container for multiple functions and procedures and Oracle extends PL/SQL with many supplied or built-in packages.
This next example block declares a variable of type VARCHAR2 (string) with a maximum length of 100 bytes to hold the string Hello World!.
DBMS_OUTPUT.PUT_LINE then accepts the variable, rather than the literal string, for display:
Note that I named the variable l_message. I generally use the l_ prefix for local variables variables defined within a block of code and the g_ prefix for global variables defined in a package.
This next example block adds an exception section that traps any exception (WHEN OTHERS) that might be raised and displays the error message, which is returned by the SQLERRM function (provided by Oracle).
VARCHAR2 (100) := 'Hello World!';
The following example block demonstrates the PL/SQL ability to nest blocks within blocks as well as the use of the concatenation operator (||) to join together multiple strings.
VARCHAR2 (100) := 'Hello';
l_message2 VARCHAR2 (100) :=
l_message || ' World!';