A stored procedure is a set
of instructions for a database, like a function
in EGL. Stored procedures differ from prepared statements because
the stored
procedure is kept permanently in the database itself, while a prepared
statement
is local to your program or logic part and is cached by the database
only
temporarily. Also, a stored procedure can consist of many SQL statements,
while a prepared statement can consist of only one SQL statement.
Fundamentally,
however, you can execute the same instructions with a stored procedure
as
you can with a prepared statement.
Using execute to call a
stored procedure
The execute keyword
is appropriate for calling stored procedures that do not return a
result set.
You can also use execute to call stored
procedures
that return one or more result sets, but in that case any result sets
are
ignored.
To call a stored procedure with
execute,
use the
#sql directive and specify the name
of the
stored procedure in the explicit SQL:
execute #sql{
CALL MYSTOREDPROCEDURE
};
If the stored procedure accepts parameters, pass
EGL
variables as host variables (see
Host variables):
myParameter int = 5;
execute #sql{
CALL MYSTOREDPROCEDURE(:myParameter)
};
The following example uses a prepared statement
in combination
with the
execute statement:
prepare p1 from "CALL MYSTOREDPROCEDURE(?)";
execute p1 using myParameter;
Using open to
call a stored procedure
You
can use the open keyword to call only stored
procedures
that return exactly one result set. To call a stored procedure that
does not
return a result set or that returns more than one result set, use execute.
To
call a stored procedure with
open, use the
#sql directive
and specify the name of the stored procedure in the explicit SQL:
open myResultSet with #sql{
CALL GETCUSTOMERS
};
Then you can access the result set through the
myResultSet identifier,
as in the following example, which assumes a SQLRecord part named
myCustomers:
myCustomers myCustomers;
get next from myResultSet into myCustomers;
If the
stored
procedure accepts parameters, pass EGL variables as host variables:
myParameter int = 5;
open myResultSet with #sql{
CALL GETCUSTOMERS(:myParameter)
};
The following example uses a prepared statement
in combination
with the
open statement:
prepare p1 from "CALL GETCUSTOMERS(?)";
open myResultSet with p1 using myParameter;
Special considerations for
the Oracle DBMS
When
you work with an Oracle database, you can call either a stored procedure
or
a stored function.
There are specific rules that apply to using
the
open statement
to call a stored procedure when you use an Oracle database:
- The
procedure must have at least one parameter.
- The first parameter
must be have an out or inOut modifier,
and must be a REF CURSOR type (an example of how to define this type
is shown
later). A variable based on this type, called a cursor variable, can
pass
result sets between the parts of a program.
- The SQL code that
the open statement runs should
represent the first parameter of the procedure with a question mark.
- If the procedure call is in a prepared statement, do not include
anything
in the using clause for the first parameter.
If
there are no other parameters, omit the using clause.
In the first example, using the
#sql directive,
the procedure has no parameters except the cursor variable:
open rs1 with #sql { call p1( ? ) };
In
the next example, also using
#sql, the procedure
has two parameters in addition to the cursor variable:
x int = 10;
y int = 1000;
open rs2 with #sql { call p2( ?, x, y ) };
In the next example, using a prepared statement,
the procedure
has no parameters except the cursor variable:
prepare pstmt3 from "call p1( ? )";
open rs3 with pstmt3;
In the next example, using a prepared statement,
the procedure
has two parameters in addition to the cursor variable:
prepare pstmt4 from "call p2( ?, ?, ? )";
open rs4 with pstmt4 using x, y;
The following example shows one way to define a
REF CURSOR
type to Oracle. (For other ways, refer to your Oracle documentation.)
execute #sql{ CREATE OR REPLACE PACKAGE MYPKG
AS
TYPE RC12 IS REF CURSOR;
END; };
The preceding code creates a new type named MYPKG.RC12
that you can use for the type of a parameter that holds the results
of a query.
The following EGL code defines a stored procedure that you can call
using
an EGL
open statement:
execute #sql{ CREATE PROCEDURE ZPQPRM2( c IN OUT MYPKG.RC12, x IN CHAR )
AS
BEGIN
OPEN c FOR SELECT firstnme, empno FROM empx WHERE empno > x ORDER BY empno;
END; };
In addition, you can create stored functions in
Oracle. A
stored function is the same as a stored procedure, except that it
returns
a value (Oracle's stored procedures cannot return a value). You can
call an
Oracle stored function from EGL, using a slightly different SQL syntax
than
in the stored procedure call.
In this first example, the called
function
is passed a string and returns an integer:
x int;
y string = "hello";
execute #sql{ call :x := func1( :y ) };
writeStdout( "The function returned " :: x );
This next example calls the same function using
a prepared
statement:
prepare q from "call ? := func1( ? )";
execute q using x, y;
writeStdout( "The function returned " :: x );
EGL can call Oracle stored functions that return
the results
of a query. The following rules apply:
- Call the function with
an EGL open statement.
- The function
must return a REF CURSOR type.
- The SQL code that the open statement
runs must
include a question mark to represent the value that the function returns.
- If the function call is in a prepared statement, do not include
anything
in the using clause for the first question
mark.
If the function has no parameters, omit the using clause.
In the first example, using the
#sql directive,
the function has no parameters except the cursor variable:
open rs5 with #sql { call ? := f5() };
In
the next example, also using
#sql, the function
has two parameters in addition to the cursor variable:
x int = 10;
y int = 1000;
open rs6 with #sql { call ? := f6( :x, :y ) };
In the next example, using a prepared statement,
the function
has no parameters except the cursor variable:
prepare pstmt7 from "call ? := f5()";
open rs7 with pstmt7;
In the next example, using a prepared statement,
the function
has two parameters in addition to the cursor variable:
prepare pstmt8 from "call ? := f6( ?, ? )";
open rs8 with pstmt8 using x, y;