Stored Procedures
We'll finish off with something of a holy
grail. Unlike SQL Server, PL/SQL does not allow us to execute a SELECT
statement within a stored procedure without a corresponding INTO
statement . This means we cannot easily return a recordset
back to the calling client whether it is an ASP script or another PL/SQL program.
Consider the following SQL Server stored
procedure:
CREATE PROCEDURE sp_GetAuthors
AS
BEGIN
SELECT au_lname,
au_fname
FROM authors
ORDER BY au_lname,
au_fname
END
Try creating the following very similar
stored procedure in Oracle:
CREATE PROCEDURE sp_GetAuthors
AS
BEGIN
SELECT ename
FROM emp
ORDER BY ename;
END;
You'll receive the following error messages:
Errors for PROCEDURE
SP_GETAUTHORS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PLS-00428:
an INTO clause is expected in this SELECT statement
4/3 PL/SQL:
SQL Statement ignored
Once upon a time, I searched
Oracle's own PL/SQL documentation for an answer to this, and I got the impression
that this will never be implemented. I believe the reason was, that they feel
a calling program, X, should pass parameters into another program, Y, allowing
Y to populate the results so that X can then deal with them. This approach doesn't
really help us from an ADO point of view.
However, it can actually be achieved by
using PL/SQL
tables and the Microsoft
ODBC for Oracle , or reference
cursors with Oracle's
Oracle Provider for OLE DB. We'll start off with PL/SQL
tables and cover reference cursors in the next section.
PL/SQL Tables are somewhat of a misnomer
as it might be easier if they were called PL/SQL Arrays. The following
diagram shows three records from the emp
table and how they would be represented in three PL/SQL Table variables:
We have three columns, ENAME,
JOB
and SAL
in our source result set. For each column of data we have a corresponding PL/SQL
table variable, o_ENAME,
o_JOB
and o_SAL,
each mapping to a value of each column. The PL/SQL table variables are distinct
entities in their own right. In order to populate the PL/SQL tables we need
to scroll through the records in the source resultset, and add an entry for
each column to the corresponding element in each PL/SQL table.
PL/SQL tables have the following characteristics:
One-Dimensional:
each PL/SQL table can contain only one column of data.
Integer-Indexed:
Each element of the array is indexed by a single integer much like a VBScript
array.
Unbounded
Dimensions: There is no limit to the size of a PL/SQL
table, as the structure
will alter in size to accommodate new elements.
Uniform
Data Type: Only a single uniform data type can be
stored in a particular
PL/SQL table. So, if you start off with a NUMBER
data-type, then all other elements
must also be a NUMBER.
PL/SQL table types are defined using the
TYPE
statement , for example:
TYPE
tblFirstName IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
This would declare a PL/SQL table type
called tblFirstName
that could be used by a variable to store an array of strings up to 30 characters
in length. A variable of this type could be declared as the parameter to a stored
procedure, thus:
PROCEDURE GetEmployeeList(o_FirstName
OUT tblFirstName)
Each PL/SQL table type that you want to
use must be defined within the specification
section of an Oracle Package.
In the case of a stored procedure that
returns a list of employee names and numbers, we must create an individual parameter
for both the employee name and the employee number values, both being declared
using the PL/SQL table type as defined in our package specification.
In order to populate the employee number
and employee name PL/SQL tables with data, we can use a cursor
that loops through a selection of records and transfers each item of data into
the corresponding PL/SQL table element.
A cursor allows you to programmatically
step through a result set of data, performing operations based on the current
row until the end of the result set is reached.
The easiest way to implement an Oracle
cursor is by declaring it outside of a program block and
then opening it using a cursor FOR...LOOP.
The cursor FOR...LOOP
opens the cursor for you, repeatedly fetches rows of values from the result
set into fields and then closes the cursor once all
rows have been processed .
For example, the following cursor will
calculate the total salary for all employees in the emp
table:
DECLARE CURSOR emp_cur
IS SELECT sal FROM emp;
TotalSalary NUMBER;
BEGIN
FOR emp_rec
IN emp_cur
LOOP
TotalSalary
:= TotalSalary + emp_rec.sal;
END LOOP;
END;
We'll start our example off by creating
a simple package that contains one stored procedure called EmployeeSearch.
This will allow us to retrieve a list of employees from the emp
table within the scott
schema, based on their name.
Jump to your SQL editor and add the following
package specification to the scott
schema:
CREATE OR REPLACE PACKAGE
Employee_Pkg
AS
TYPE tblEmpNo IS
TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE tblEName IS
TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE tblJob
IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
PROCEDURE EmployeeSearch
(i_EName
IN VARCHAR2,
o_EmpNo
OUT tblEmpNo,
o_EName
OUT tblEName,
o_Job
OUT tblJob);
END Employee_Pkg;
Our package is called Employee_Pkg,
which we will need to use when referencing the EmployeeSearch
procedure. We will be returning three columns in our Recordset:
employee number, name and job, so we have created a separate PL/SQL table type
for each column.
Note that EmployeeSearch
doesn't actually include any code – that's the job of the package body.
If you try to define the implementation here you'll get an error from Oracle.
We've defined one input parameter, the
name to search for, and a separate output parameter for each of the columns
to return. Now we can create the package body – the bit that does the actual
work, so execute the following SQL script :
CREATE OR REPLACE PACKAGE
BODY Employee_Pkg
AS
PROCEDURE EmployeeSearch
(i_EName
IN VARCHAR2,
o_EmpNo
OUT tblEmpNo,
o_EName
OUT tblEName,
o_Job
OUT tblJob)
IS
We start off by adding the word BODY
before the package name, dropping the PL/SQL table definitions, and adding the
word IS
to start the implementation.
CURSOR cur_employee
(curName VARCHAR2) IS
SELECT empno,
ename,
job
FROM emp
WHERE UPPER(ename)
LIKE '%' || UPPER(curName) || '%'
ORDER BY ename;
RecordCount NUMBER
DEFAULT 0;
If you recall from our overview of PL/SQL
blocks, we need to declare any variables or cursors that
are going to be used by our procedure. We define a cursor called cur_employee
that has its own
input parameter called curName
and a number variable called RecordCount
to store a count of the records processed .
Our cursor isn't that sophisticated: it
uses || to add the wildcard character '%'
to the beginning and the end of the required search name. In SQL Server, we
would have used the + string concatenation operator. This enables the LIKE statement
to find any employee's names that contain the specified characters. As we populate
each of the PL/SQL table parameters we need to keep a track of the current element
being set, so we use RecordCount.
PL/SQL tables are 1-based so we must increment the RecordCount
first as it starts from 0 initially .
BEGIN
FOR
curRecEmployee IN cur_employee(i_EName)
LOOP
RecordCount:= RecordCount + 1;
o_EmpNo(RecordCount):=
curRecEmployee.empno;
o_EName(RecordCount):=
curRecEmployee.ename;
o_Job(RecordCount):=
curRecEmployee.job;
END
LOOP;
END EmployeeSearch;
END Employee_Pkg;
Here we have defined the actual implementation
of the EmployeeSearch
procedure. We simply
open the cursor and ask it to transfer each record into a cursor variable called
curRecEmployee.
Notice that we didn't actually define the variable curRecEmployee,
as this is simply a reference
name to the record structure for the cursor. We can still refer to it within
our cursor FOR...LOOP
as though it was declared.
Then it's just a case of moving through
each record, incrementing the record count, and transferring each individual
field into each output parameter in the identical element position using RecordCount.
Now we need to call the procedure
from an ASP script to populate the data. This is where you're
likely to have the most problems when writing your own procedures. The following
rules must be remembered, otherwise it simply won't work and you could
spend days and days trying to work out why – as I did!
Use the Microsoft ODBC Driver for Oracle.
If you try to use the OLE DB Provider for Oracle
you'll get an error message saying "Catastrophic
Error"! You should also try to ensure that you're
using at least version 2.573.4202.00
of the driver.
Argument Naming and Positioning.
When setting the Command
object's CommandText,
you must ensure that you use exactly the same name and same position
for each parameter as you did when you declared each parameter in your stored
procedure. If not, you'll get the rather misleading ODBC error message "Resultset
column must
be a formal argument".
Maximum Records Returned.
You must use the resultset
qualifier as part of your CommandText
string to tell the driver which parameters are recordsets, such as:
"{call
Employee_Pkg.EmployeeSearch("?,
{resultset100,
o_EmpNo,
o_EName,
o_Job})}"
The number after resultset
indicates the maximum number of records to be returned
in this call. The driver actually allocates a memory cache to store this amount
of data. (There appears to be no documentation that confirms what happens when
the number of records is a lot less than this number.) If you exceed this number,
by even one record, then you will receive Oracle error ORA-06512. It is suggested that
you limit the number of records within your cursor population by passing the
required value as an additional parameter to your stored procedure and limiting
the cursor FOR...LOOP.
We didn't do this in our example but it might be a nice exercise to try.
|
So we can now create a simple ASP
script to call our procedure. I'm going to use a single ASP script that
contains a form that submits to itself and writes out the search results.
|
|
<% Option Explicit
Response.Expires
= 0%>
<HTML>
<HEAD>
<TITLE>Stored
Procedure Recordset Demo</TITLE>
</HEAD>
<BODY>
<CENTER><H2>Stored
Procedure Recordset Demo</H2></CENTER>
<%
Dim strSearchName
Dim objConnection
Dim objCommand
Dim objRecordset
Dim varEmpNo
strSearchName = Request.Form("txtSearchName")
If strSearchName = ""
Then strSearchName = "%"
We transfer the txtSearchName
input field from the form into a variable. If it was empty, which it will be
the first time, we set it to %
so that we get all matching names.
Set objConnection =
Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString
= "driver={Microsoft ODBC for Oracle};" & _
"server=Oracle8_dev;UID=scott;PWD=tiger;"
.CursorLocation
= adUseClient
.Open
End With
Here we connect to the database using
the Microsoft
ODBC Driver for Oracle .
Now for the fun part:
Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
Set .ActiveConnection
= objConnection
.CommandText
= "{call Employee_Pkg.EmployeeSearch("
& _
"?, {resultset 100, o_EmpNo,
o_EName,
o_Job})}"
.CommandType
= adCmdText
.Parameters(0).Value
= strSearchName
Set objRecordset
= .Execute()
End With
%>
We are using the standard {call...}
and ?
syntax to define the first input parameter. Notice that we have included the
{resultset
100….}
string, as mentioned above, to define those parameters that are to be returned
in the Recordset
object and that we only want 100 records returned. We have simply pasted in
the names of the parameters exactly as we declared them. The only parameter
that we actually set is the first input parameter, the search name. Finally,
we call the Execute
statement to get our data.
What you do is navigate through the records
in the Recordset
and creating a nicely formatted
HTML table.
<FORM ACTION="StoredProcResultSetDemo.asp"
METHOD="POST">
<INPUT NAME="txtSearchName"
VALUE="<%=strSearchName%>">
<INPUT TYPE="SUBMIT"
VALUE="Search">
<P>
<TABLE BORDER=1>
<TR><TD>Employee</TD><TD>Job</TD></TR>
<%
Do While Not objRecordset.EOF
varEmpNo = objRecordset.Fields("o_EmpNo")
Response.Write
"<TR>" & _
" <TD><A HREF=EditEmp.ASP?EmpNo=" &
varEmpNo & ">" & _
objRecordset.Fields("o_EName")
& "</A></TD>" & _
" <TD>" & objRecordset.Fields("o_Job")
& "</TD>" & _
"</TR>"
objRecordset.MoveNext
Loop
Set objRecordset = Nothing
Set objCommand = Nothing
Set objConnection =
Nothing
%>
</TABLE>
</FORM>
</BODY>
</HTML>