Oracle in a Nutshell

Types of Blocks

There are two basic types of blocks:

Anonymous block

Cannot be called from outside of the block that contains it. Optional declaration section begins with the keyword DECLARE. This type of block has the following form:

DECLARE
   today DATE DEFAULT SYSDATE;
BEGIN
   -- Display the date. 
   DBMS_OUTPUT.PUT_LINE ('Today is ' || today);
END;

Named block

Requires a header. Can be called outside of the block that contains it. Does not use the keyword DECLARE for the declaration section. This type of block is shown in the following example:

CREATE OR REPLACE PROCEDURE show_the_date
IS
   today DATE DEFAULT SYSDATE;
BEGIN
   -- Display the date. 
   DBMS_OUTPUT.PUT_LINE ('Today is ' || today);
END show_the_date;

You can declare parameters for a block in the header section. Parameters are optional, enclosed by parentheses, and comma-separated. Each parameter name must be followed by a datatype.