PL/SQL Block Syntax

Following is syntax for general block,

declare

          variable, constant, cursor etc. declaration

begin

           (SQL, PLSQL) executable statements

exception

           error handling code

end;

·       The different parts/sections of a PL/SQL block are discussed as below.

·       The declare section contains the definitions of variables and other objects such as constants and cursors etc.

·       This section is an optional part of a PL/SQL block.

·       The procedure section contains conditional commands and SQL statements and is where the block is controlled.

·       This section is the only mandatory part of a PL/SQL block.

·       The exception section tells the PL/SQL block how to handle specified errors and user-defined exceptions.

·       This section is an optional part of a PL/SQL block.

·       Following is example.

SQL> set serveroutput on;

SQL> declare

                pi constant number(9,7) := 3.1415926;

r number(5);

                area number(14,2);

    begin

                R:= &r;

                area := pi * power(r,2);

                dbms_output.put_line('area='||area);

    end;

            .

SQL> /

Enter value for r: 2

old   6: R:= &r;

new  6: R:= 2;

area=12.57

PL/SQL procedure successfully completed.