PL/SQL Programming 1

Fall into Autumn 2, originally uploaded by Three Headed Dragon.

PL/SQL BLOCKS
PL/SQL program is consists of block of codes.

If the block has no name, it is called anonymous block and it is usually constructed dynamically and executed only once. The format is like this,

DECLARE
declare variables here;
BEGIN
statements;
END;

If you'd like to give the block a name, there are two ways to do it. The first way:
<<block_name>>
DECLARE

declare variables here;

BEGIN

statements;

END block_name;
This kind of named block is still usually executed only once.

The second way of giving it a name is to replace the "DECLARE" with "CREATE OR REPLACE PROCEDURE block_name AS" like this:

CREATE OR REPLACE PROCEDURE block_name AS
declare variables here;
BEGIN
statements;
END block_name;

This second way is usually called stored procedure, the code is stored in database and can be executed mutiple times.

BLOCK STRUCTURE
There are usually 3 sections in a block. The declarative section where you declare variables, the executable section where you write executable statements that actually performs some tasks and the exception section where you handle errors and exceptions in case any happen. Just like our daily work, we have resources stored in different places, folders; we perform tasks using those resources. In the middle, if we are thirsty and can't perform tasks anymore, we go out to get a cup of coffee (thirsty exception handled) and then we come back to continue our work. I can think of all kinds of exceptions, such as bathroom exception, hungray exception, etc. If exceptions are not handled, programs terminate unexpectedly or hang, because computer does not know what to do when these exceptions happen.