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

 

 

   
 

A PL/SQL Wish List
Oracle Tips by Burleson
 

PL/SQL is not a perfect programming language (someone from Oracle told me in 1992 that it wasn’t really a programming language at all). If you have any experience with other programming languages, you’ll find that some really useful things are missing from PL/SQL. While these aren’t serious deficiencies, their incorporation would make PL/SQL a bit less tedious to use at times.

The continue Statement

The continue statement in C is a marvelous tool, allowing you to immediately skip to the next iteration of a loop without executing any steps that occur inside the loop after the continue statement. Unfortunately, PL/SQL has only a GOTO statement to allow you to accomplish this simple task. The GOTO statement is illustrated by this example:

LOOP
   <<START_OF_LOOP>>
  ...PL/SQL statements..
   GOTO <<START_OF_LOOP>>
END LOOP;

You can also mirror this functionality by putting the contents of your loop inside a stored procedure and using the RETURN statement to exit out of the stored procedure if you determine that your data can’t or shouldn’t be processed any further. This example calls a procedure that holds the internal logic of the loop.

LOOP
   MyLoopContents (parameter1 => value);
END LOOP;

Listing 2.21 illustrates a loop that simulates the functionality of a continue statement by using a stored procedure.

Listing 2.21 Using a stored procedure to simulate a C continue statement.

PROCEDURE MyLoopContents (parameter1    <datatype>)
 
IS
   <variable declarations>
 
BEGIN
    ...PL/SQL statements...
   IF some_condition THEN
       RETURN;
   END IF;
END;

The case/switch Statement

While not an essential part of any language, the case statement is more elegant than:

IF (x < 100) THEN
   <statements>
 
ELSIF (X <200) THEN
   <statements>
 
ELSIF (x < 300) THEN
   <statements>
 
ELSE
   <statements>
END IF;

Unfortunately, PL/SQL allows only the clumsy IF-THEN-ELSE method of testing multiple conditions.

Incrementing Variables

The ability to increment or decrement a variable in C using the ++, --, +=, and -= operators is something that I’ve often wished for while coding a procedure or function. Instead, you’ll have to settle for the less elegant method shown here:

nSum := nSum + 1;

While functionally equivalent, the previous example simply seems less graceful than a similar C statement:

nSum++;

You can create a stored function that accepts an integer value and returns the value incremented by one, although it hardly seems worth the effort.

 

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

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