We've shown you a number of techniques available  to connect to an Oracle database. Now we shall provide a quick overview of Oracle's own procedural extensions to SQL.

This section doesn't aim to be a PL/SQL bible. Instead, we'll cover some of the main differences between PL/SQL and standard ANSI SQL .

The "PL" in PL/SQL is short for Procedural Language. It is an extension to SQL that allows you to create PL/SQL programs that contain standard programming features such as error handling, flow-of-control structures, and variables, all allowing you to manipulate Oracle data. By itself, SQL does not support these concepts .

Block Structure

A PL/SQL program consists of any number of blocks or sections of code. In our ASP scripts we can create any number of chunks of code to execute on the server using the <%...%> tags. This is
similar to PL/SQL, where a set of statements can be grouped logically together as part of a larger
set of instructions :

A PL/SQL block has three distinct sections:

Declarations

Statements

Handlers

They are defined in the following way:

The declarations section contains any variables or constants that are going to be used within the statements section. You can have any number of statements to execute, but if an error occurs in
any of them, processing will stop and execution will move to the exception section for trapping, if
any are defined.

In above example we declare TotalSal as a variable in the declarations section:

All of the remaining code up to the EXCEPTION line forms the statements section, followed by two exception handlers: NO_DATA_FOUND and OTHERS.

When you declare an exception handler you must tell Oracle which one of the in-built exceptions you want to trap , such as ZERO_DIVIDE. In our case we've trapped NO_DATA_FOUND, which is raised when an empty result set is retrieved, and OTHERS, which is a catch-all handler that will trap any other exceptions that you have not explicitly named. You can have any number of exception handlers and you can also set up your own exception types, but that is beyond the scope of this chapter.

Once an exception has been trapped you cannot issue the equivalent to a VBScript RESUME NEXT as the PL/SQL program will exit at the last line in the exception handler. This is somewhat different to the operation of SQL Server's T-SQL  in which you can check the value of @@Error after any statement, provided that the error was of a trappable nature.

The dbms_output.put_line('No records found.'); statement allows us to briefly mention PL/SQL debugging. dbms_output is a built-in Oracle Package (a package is a way to group together collections of stored procedures) that can be used to send messages to the console. In order to actually see these messages you must execute the SET SERVEROUTPUT ON; statement from within the SQL*Plus SQL editor. Each call to dbms_output.put_line will write out the string message passed to it.

Variable Declaration

At the start of a PL/SQL block you must define any variables that are to be used, after the DECLARE statement. You can use any of the standard Oracle data-types such as NUMBER, VARCHAR2 or any PL/SQL data-type, such as BOOLEAN. It is just a case  of defining the variable name followed by the data-type and using a semi-colon between multiple declarations:

For a full list of Oracle data-types check out http://technet.us.oracle.com/doc/server.804/a58227/ch6.htm#649

Assigning Values to Variables

In ASP we use the = statement to assign a value to one of our variables. In PL/SQL it is slightly different, in that we must use :=.

If we are returning a value from a database table or system function, then we use the INTO statement:

Conditional Flow of Control

We use the If...Then...Else construct to control  the execution flow of our ASP scripts. PL/SQL also supports this construct in a similar format:

Surprisingly, PL/SQL doesn't yet provide support for the CASE statement.

Looping Flow Control

To loop through a section of code, PL/SQL supports a number of LOOP statements . The first is similar to the VBScript For...Next statement:

Where countervar is the counter variable, start is the initial starting value and end is the final value. For example:

The equivalent loop in VBScript would be:

The WHILE...LOOP allows us to execute a section of code until a certain condition is true, just as we do with the Do...Loop structure in ASP:

Of course, there's a lot more to PL/SQL than that. PL/SQL is like any programming language with many constructs, statements and functions, but these are the typical building blocks that you will come across in any PL/SQL program.

Oracle Packages

We covered stored procedures a few chapters ago, so now we'll take a quick look at Oracle Packages . An Oracle package serves as a way to group procedures and functions into common groups typically based upon their functionality. A package has two sections: the specification that contains a definition of any objects that can be referenced outside of the package, and a body that contains the implementation of the objects. The specification section must be declared first:

For example:

This defines a package called Employee_pkg that contains a single stored procedure called GetEmployeeAge.

The package body contains the actual implementation of the procedures within the package.
This effectively allows us to hide procedures inside the package by not declaring  them in the
package specification:

The specification for our Employee_pkg could look like this:

To call the GetEmployeeName procedure within Employee_pkg from ASP we use must prefix the stored procedure name with the package name. We'll be covering the execution of stored procedures in much more detail in the next section :

Now that we've had a brief look at Oracle packages we can use some of their features in the final section in this chapter, when we come to retrieving ADO resultsets from an Oracle stored procedure. Before we do that, let's create a sample application that uses a number of stored procedures to perform common data-entry actions.