Oracle Programming in PL/SQL Oracle Tips by Burleson Consulting

Programming in PL/SQL

Databases have been in use long before the personal computer arrived on the scene.  IBM developed the Structured Query Language standard (dubbed SQL, and pronounced ?See-Quel?) over 30 years ago as a way to retrieve data from their new ?relational? database.  A decade later, Oracle released the first commercial relational database that used SQL, and SQL has become the de-facto query language for the vast majority of popular database products. 

For the complete story, we recommend the book ?Easy Oracle PL/SQL Programming?.  Once you have mastered basic SQL you are ready for the advanced book ?Oracle PL/SQL Tuning? by Dr. Timothy Hall.

Even though SQL is the standard language for interacting with any modern database, it does not mean that SQL is without limitations.  If we want to retrieve a set of records from the database and modify them according to a set of rules, (updating some and returning to the calling program others), we can?t do this with a single SQL call to the database.  Complex processing requires the ability to compare values (often called Boolean logic) and implement programmatic flow control.   In other words, some type of programming language was required to process the returned rows and implement the program rules.  To achieve this capability, Oracle introduced the Procedural Language extensions to the Structured Query Language or PL/SQL. 

Oracle PL/SQL was based on the ADA programming language which was developed by the Department of Defense to be used on mission critical systems.  Although not a ?sexy? language like Java or C, ADA is still being develop and used for applications such as aircraft control systems.  ADA is a highly structured, strongly typed programming language that uses natural language constructs to make it easy to understand.  The PL/SQL language inherited these attributes making PL/SQL easier to read and maintain than more cryptic languages such as C.  For example, below are two loops, one in PL/SQL and the other in a programming language called C.

As opposed to:

for (x = str; x< fin; x++) {i++}  --C

As we see, the PL/SQL statement is more verbose but also easier to understand.

PL/SQL is also portable within the Oracle database family and runs on all supported Oracle platforms including Oracle10g grid database. Even more important is platform independence, where programs developed in PL/SQL on a Windows Oracle database will load and run in a UNIX Oracle database.  With each release of the Oracle database, Oracle Corporation enhances the capabilities and performance of PL/SQL.  Remember, PL/SQL is an Oracle only product, and no other database management system will run PL/SQL.

Unlike other languages that execute externally, PL/SQL executes inside the database.  This means that you can take advantage of PL/SQL?s exceptional ability to manipulate data in the database without paying the network penalty of retrieving the data out of the database and them updating it back to the database.  Because PL/SQL runs inside the database it takes advantage of the capabilities and capacity of the database server.

Traditionally, PL/SQL has been a compiled/interpreted language similar to Java.   When PL/SQL code is loaded into the database it is compiled into an intermediate form, similar to the way Java is compiled into byte-code.  This intermediate code is portable across Oracle databases.  Later versions of Oracle (Oracle9i and 10g) will compile PL/SQL into native code for over 60 hardware platforms.  This natively compiled code runs more efficiently, but it loses the ability to move to other Oracle databases without recompiling.

Lastly, placing the code that interacts with the database in PL/SQL makes better use of the database resources.  PL/SQL packages are loaded as a package so as your program calls for data, the procedures and functions are already in cached memory.  Using PL/SQL will also result in your application using bind variables.  The only way not to use bind variables in PL/SQL is to implement dynamic SQL (discussed in Chapter 5).  The Database Administrator (DBA) also benefits when developers place their SQL inside PL/SQL because they have access to the statements for tuning.  For example the DBA can tune the SQL (adding hints, reordering the WHERE clause) without impacting the existing application.  Placing SQL inside PL/SQL also allows the code to be protected by the recovery capabilities of the Oracle database.

PL/SQL is the most common language for Oracle the world, and developers are realizing the benefits in both application performance and database performance by implementing the database interaction in PL/SQL.  There are even websites built entirely using PL/SQL.  For example, Oracle?s HTML-DB product is installed in the database and consists primarily of PL/SQL packages and Java scripts. 

PL/SQL Basic Structure

Like the ADA programming language, PL/SQL is based on blocks, and   PL/SQL provides a number of different blocks for different uses.  The characteristics of a block include:

* A block begins with a declarative section where variables are defined. 

* This is followed by a section containing the procedural statements surrounded by the BEGIN and END key words.  Each block must have a BEGIN and END statement, and may optionally include an exception section to handle errors.  The exception section is covered later in the book.

Here is an example of a simple block:

In the example above, the variable v_line is defined in the declarative section on line 2.  Like SQL statements, each line ends with a semicolon.  Once v_line is defined, it can be used in the procedural section.  First, v_line is assigned the literal string ?Hello World? on line 4.  Strings are surrounded by single quotes in SQL and PL/SQL.  The v_line variable is then placed in the output buffer using the procedure dbms_output.put_line. 

In PL/SQL, the semicolon defines the end of a line of code.  To execute the PL/SQL block, use the forward slash ?/? on a line by itself as shown on line 7.    If you forget the forward slash, SQL*Plus will simply wait for the next line to be entered.

Note:  If you execute a PL/SQL script and SQL*Plus returns a number, it probably is an indication that you forgot to place the?/? at the end of your script.  SQL*Plus is actually waiting for the next line.  Entering a ?/? will execute the script.

A PL/SQL block with no name is called an anonymous block.  It starts with the declare key word to define the declarative section. 

A named block is a procedure or a function.  The name portion defines the declarative section so the DECLARE key word is not used.

A procedure can be passed and change variables.  A function can be passed variables and must return a variable.

When variables are passed to a procedure or function they can be IN, OUT or INOUT.  An IN variable is passed into the procedure or function and is used, but can not be changed.  An OUT variable is passed to the procedure but it can be changed and left in the changed state when the procedure ends. 

An INOUT variable is passed to a procedure or function, and it can be used by the block, changed by the block, and left in a ?changed? state when the block ends.  A function can only be passed an IN variable and must return a variable.  If this is confusing, don?t worry.  When we get to name blocks and provide some examples it will be much clearer.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.