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

 

 

   
 

PL/SQL Overview
Oracle Tips by Burleson
 

The Procedural Language extension to SQL (PL/SQL) was first introduced in late 1991 and dramatically reshaped the role of the Oracle developer. PL/SQL literally made the impossible possible for SQL developers; for the first time, they could develop complex applications without using a 3GL program from inside Oracle*Forms.

PL/SQL introduced several abilities to the Oracle developer, including:

  • Looping structures

  • The ability to embed SQL statements inside PL/SQL code

  • IF-THEN-ELSE logic

  • The ability to deal with multiple rows of data

  • A robust method of handling errors

In its first incarnation, PL/SQL was used only inside Oracle*Forms 3.0 (in special blocks called procedures). Triggers inside a form could execute both SQL DML statements and PL/SQL procedures (which in turn could execute SQL DML statements and other PL/SQL procedures). Today, PL/SQL has matured considerably, adding many new features and becoming more tightly integrated with the Oracle database.

PL/SQL code runs in any Oracle database on any hardware platform, making the code highly transportable. Applications developed for your Personal Oracle database can easily be moved up to larger machines and vice versa.

Blocks

PL/SQL is written in sections called blocks. Listing 2.10 shows the structure of a typical PL/SQL block.

Listing 2.10 A sample PL/SQL block.

DECLARE
   <variable declarations>
 
BEGIN
   <statements>
EXCEPTION
   <error condition>
       <code for handling error>
END;

Blocks of PL/SQL code may be nested within each other to form sub-blocks, as shown in Listing 2.11.

Listing 2.11 A sample PL/SQL block with a sub-block.

DECLARE
   <variable declarations>
BEGIN
   <statements>
   DECLARE
      <variable declarations>
   BEGIN
      <statements>
   EXCEPTION
      <error condition>
          <code for handling error condition>
   END;
   <statements>
EXCEPTION
   <error condition>
       <code for handling error>
END;

In practice, there is no limit to how far PL/SQL blocks may be nested within each other (although too many levels of indentation become confusing).

The structure of a PL/SQL block is simple. The DECLARE statement is followed by variable, constant, and other definitions. You can then manipulate variables and data following the BEGIN statement. The EXCEPTION statement (errors in PL/SQL are called exceptions; this term originated within Ada, PL/SQL’s root language) allows you to define code to handle specific error conditions. The END statement signifies the end of the PL/SQL block.

Not every PL/SQL block will have a DECLARE statement. This is especially true of sub-blocks, because variables inside a block cease to exist once the block is closed.

 

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