PL/SQL block

PL/SQL Block Structure

PL/SQL block : PL/SQL is a block-structured language. A PL/SQL block defines a unit of processing, which can include its own local variables, SQL statements,cursors, and exception handlers. The blocks can be nested. The simplest block structure is given below:

DECLARE
Variable declarations
BEGIN
Program statements
EXCEPTION
WHEN exception THEN
Program Statements
END;

In the above PL/SQL block, block parts are logical. Blocks starts with DECLARATION section in which memory variable and other oracle objects can be declared. The next section contains SQL executable statements for manipulating table data by using the variables and constants declared in the DECLARE section. EXCEPTIONS are the last sections of the PL/SQL block which contains SQL and/ or EXCEPTION section is optional.
Each block can contain other blocks, i.e. blocks can be nested. Blocks of the code cannot be nested in the DECLARATION section.

PL/SQL Character Set

PL/SQL uses the standard ASCII set. The basic character set includes the following.
Uppercase alphabets-      A to Z
Lowercase alphabets-      a to z
Numbers –                          0 to 9
Symbols-                             ( ) + – * / < > =! ; : , . @ ‘ % “ # $ ^ & _ \ {  } ?  [ ]
Words used in a PL/SQL blocks are called lexical units. We can freely insert blank spaces between lexical units in PL/SQL blocks. The spaces have no effect on the PL/SQL block.

The ordinary symbols used in PL/SQL blocks are

( )   +   _ * / < > = ; % , “ [ ] :

Compound symbols used in PL/SQL block are

< >  !=  -=  ^=  <=  >=  :=  **  ||  <<  >>

PL/SQL Variables

Variables may be used to store the result of a query or calculations. Variables must be declared before being used. Variable in PL/SQL block are named variables. A variable name must begin with a character and can be followed by a maximum of 29 other characters (variable length is 30 characters).
Reserved word cannot be used as variable names unless enclosed within the double quotes. Variables must be separated from each other by at least one space or by a punctuation mark.

 The case (upper/lower) is insignificant when declaring variable names. Space cannot be used in a variable name.

Leave a Reply

Your email address will not be published.