Loading
 
What's Next: View All Tutorial →

PL SQL Block Structure and Types

PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks. A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).   Declara...

Published Date: 25/08/2014  Last Modified Date: 25/08/2014    New Post



PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks.

A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
 
  1. Declarative : Contains all the variables, constants, cursors and user-defined exceptions that are referenced in the executable and declarative section. This is an OPTIONAL section in a PL/SQL Block.
  2. Executable : Contains SQL statements to manipulate data in the database and PL?SQL statements to manipulate data in the block. This is a MANDATORY section.
  3. Exception Handling : Specifies the actions to perform when errors and abnormal conditions arise in the executable section. This is an OPTIONAL section in a PL/SQL Block.
 

Syntax:

DECLARE (Optional)
		Variables, 
     cursors, 
     user-defined exceptions
BEGIN (Mandatory)
- SQL statements
- PL/SQL statements
EXCEPTION (Optional)
		Actions to perform when errors occur
END; (Mandatory)

 

Block Types

A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested one within another. The basic units (procedures and functions, also known as subprograms, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested subblocks. Therefore, one block can represent a small part of another block, which in turn can be part of the whole unit of code.
Anonymous Blocks

Subprograms
Subprograms are named PL/SQL blocks that can accept parameters and can be invoked. You can declare them either as procedures or as functions. Generally use a procedure to perform an action and a function to compute a value.
You can store subprograms at the server or application level. Using Oracle Developer components (Forms, Reports, and Graphics), you can declare procedures and functions as part of the application (a form or report) and call them from other procedures, functions, and triggers (see next page) within the same application whenever necessary.
Note: A function is similar to a procedure, except that a function must return a value. 
 

Anonymous Block:

Anonymous blocks are unnamed blocks. They are declared at the point in an application where they are to be executed and are passed to the PL/SQL engine for execution at run time. You can embed an anonymous block within a precompiler program and within iSQL*Plus or Server Manager. Triggers in Oracle Developer components consist of such blocks.
[DECLARE]


BEGIN
  --statements

[EXCEPTION]

END;

Procedure:

Procedure/Subprograms are named PL/SQL blocks that can accept parameters and can be invoked. You can declare them either as procedures or as functions. Generally use a procedure to perform an action and a function to compute a value.
 
PROCEDURE name
IS

BEGIN
  --statements

[EXCEPTION]

END;

Function:

A function is similar to a procedure, except that a function must return a value. 
FUNCTION name
RETURN datatype
IS
BEGIN
  --statements
  RETURN value;
[EXCEPTION]

END;

 
Awaiting for Administrator approval




← Back to list


Related Post




×