Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Block Structure
Oracle Tips by Burleson
 

SPL is a block-structured language.  You begin a block with the keyword BEGIN and you end your block with the keyword END.  From BEGIN to END is considered a single executable statement.  An executable statement ends with a semi-colon (;). 

Between the BEGIN and END, are executable commands (i.e. your program).  Executable commands also end with a semi-colon.  This section, between the BEGIN and END, is called the "executable section".

The most basic executable command that exists is the NULL command.  This command does nothing; it's a no-op.  Its primary use is in making my life easier when I show a simple example of an SPL block.  Below is such a block:

BEGIN
  NULL;
END;

This is the simplest SPL program you will ever see.  It will run but it does absolutely nothing.  I take that back.  It does make a great illustration.

A program is not much use without variables.  A variable is where you will store values from one step in your program to another.  Variables are also what you use to return values to calling programs.

Variables are declared in the DECLARE section of a SPL block.  The DECLARE comes before the executable section but is part of the overall executable statement.

DECLARE
BEGIN
  NULL;
END;

In this example, I did not declare any variables but I did include the declaration section.  This is a valid piece of code.  If you launch the SQL Terminal and run it, you will see that it executes and when it finishes, SQL Terminal displays the text:  SPL Procedure Successfully complete.  It displays that text even if the code executing is a function or a packaged procedure.

edb=# DECLARE
edb-# BEGIN
edb$#   NULL;
edb$# END;

SPL Procedure successfully complete

edb=#

SPL blocks can be nested.  That means that a block can exist within another block.  This is very powerful and is something you will come to expect.  After a while, you won't even think about it.

Below is a block of code with nested blocks three levels deep.

DECLARE
BEGIN
  BEGIN
    DECLARE
    BEGIN
      NULL;
    END;
  END;
END;

In this piece of code, notice that the first nested block does not contain a DECLARE but the second one does.  Also, notice that a block is considered an executable piece of code.  Only the inner most block needs the NULL command and that is because every block MUST contain at least one executable command.

The next, optional, part of the block structure is the exception section.  The exception section is where you will catch errors.  I know you will always write perfect code but sometimes you might want to throw an error and catch it just to prove you can.

The structure of an EXCEPTION is the EXCEPTION keyword followed by a set of WHEN clauses.  The EXCEPTION section is optional but if you have an EXCEPTION section, you must have at least one WHEN clause.  The WHEN clause is where you will catch specific errors.

BEGIN

  NULL;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

The EXCEPTION keyword comes last in a block, just before the END.  In addition to the WHEN clause, the exception section, like the executable section, requires at least one executable command.  In the case above, I used NULL.

You can use exceptions in nested blocks and in fact, you will do that regularly.  I will cover exception handling in detail below but if you want to execute a block of code but still ensure that your program continues running, you would put that code in a nested block.

DECLARE
BEGIN
  <some commands>
  .
  .
  .
  BEGIN
    SELECT <data>
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END; 

  <some more commands>
  .
  .
  .
END;

In the case above, I want to execute a select statement and if I find no rows, I want to continue executing my program.  Without the nested block, my program would end when the NO_DATA_FOUND exception was raised by SPL.

Block Structure Review:

* The required keywords of a block are BEGIN and END

* The area between the BEGIN and END is called the executable section

* The optional DECLARE keyword identifies the declaration section of a block and is where you would define any variables to be used within that block

* The optional EXCEPTION keyword identifies the exception section of a block

* If the EXCEPTION keyword is used, it must contain at least one WHEN clause and each WHEN clause must contain at least one executable command

* Blocks can be nested within other blocks

* To continue processing after an exception, blocks should be nested and nested blocks should contain their own exception handlers.

 

             
This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter