Overview of PL/SQL
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 :
DECLARE TotalSal NUMBER(5);
BEGIN
SELECT SUM(Sal)
INTO TotalSal
FROM
emp
WHERE ename
LIKE 'S%';
dbms_output.put_line('totalSalary='
|| TotalSal );
IF TotalSal <
10000 THEN
UPDATE
emp SET
Sal = Sal * 1.1
WHERE
ename LIKE 'S%';
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
dbms_output.put_line('No
records found.');
WHEN OTHERS
THEN
dbms_output.put_line(SQLERRM);
END;
A PL/SQL block has three distinct sections:
Declarations
Statements
Handlers
They are defined in the following way:
[DECLARE declarations]
BEGIN
statements
[EXCEPTION handlers]
END;
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:
DECLARE TotalSal NUMBER(5);
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.
Oracle uses the / character to mark the end of a block of
SQL to execute within SQL*Plus .
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:
DECLARE TotalBonus NUMBER(6);
BonusPaid BOOLEAN;
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 :=.
SalePrice := (ProductPrice / 100)
* SalesTax;
If we are returning a value from a database
table or system function, then we use the INTO
statement:
SELECT SUM(Quantity)
INTO ItemsOrdered FROM OrderBasket;
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:
IF SaleCount > 10
AND SaleCount < 20 THEN
UPDATE emp SET
sal = sal * 0.3;
ELSIF SaleCount = 5
THEN
UPDATE emp SET
sal = sal * 0.2;
ELSE
UPDATE emp SET
sal = sal * 0.1;
END IF;
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:
FOR countervar IN start..end
LOOP
statements to
execute
END LOOP;
Where countervar
is the counter variable, start
is the initial starting value and end
is the final value. For example:
FOR
intCounter IN 1..5 LOOP
INSERT INTO OrderLine(ID)
VALUES(OrderLineID.NEXTVAL);
END
LOOP;
The equivalent loop in VBScript would
be:
FOR
intCounter = 1 To 5
Response.Write "Value=" & intCounter
NEXT
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:
WHILE
TotalBonus < 10000 LOOP
SELECT Bonus, EmpID INTO EmpBonus, MyEmpID
FROM emp
WHERE EmpID <> MyEmpID;
Totalbonus := TotalBonus + Bonus;
RecordCount := RecordCount + 1;
END
LOOP;
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:
PACKAGE
package_name
IS
{variable and type declarations }
{cursor declarations}
[module specifications]
END
{package_name};
For example:
CREATE
OR REPLACE PACKAGE Employee_pkg
AS
PROCEDURE GetEmployeeName(i_empno
IN NUMBER,
o_ename
OUT VARCHAR2);
END
Employee_Pkg;
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:
PACKAGE
BODY package_name
IS
{variable and type declarations}
{cursor specifications - SELECT statements}
[module specifications]
BEGIN
[procedure bodies]
END
{package_name};
The specification for our Employee_pkg
could look like this:
CREATE
OR REPLACE PACKAGE BODY Employee_pkg
AS
PROCEDURE GetEmployeeName(i_empno
IN NUMBER,
o_ename
OUT VARCHAR2)
IS
BEGIN
SELECT ename
INTO o_ename
FROM emp
WHERE empno = i_empno;
END GetEmployeeName;
END
Employee_pkg;
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 :
With objCommand
.CommandText = "{call Employee_pkg.GetEmployeeName(?,
?)}"
.CommandType = adCmdText
.Parameters(0).Direction = adParamInput
.Parameters(0).Value = varEmpNo
.Parameters(1).Direction = adParamOutput
.Execute
Response.Write "Name=" & .Parameters(1).Value
End With
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.