You can call an SQL stored procedure with the execute, open, or get statement; in each case, you use the #sql directive.
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.
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.
execute #sql{
CALL MYSTOREDPROCEDURE
};
myParameter int = 5;
execute #sql{
CALL MYSTOREDPROCEDURE(:myParameter)
};
prepare p1 from "CALL MYSTOREDPROCEDURE(?)";
execute p1 using myParameter;
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.
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 Record part (stereotype
SQLRecord) named MyCustomerPart:myCustomers MyCustomerPart;
get next from myResultSet into myCustomers;
myParameter int = 5;
open myResultSet with #sql{
CALL GETCUSTOMERS(:myParameter)
};
prepare p1 from "CALL GETCUSTOMERS(?)";
open myResultSet with p1 using myParameter;
You can use the get keyword with a dynamic array 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.
myCustomers MyCustomerPart[]{};
get myArray with #sql{
CALL GETCUSTOMERS
};
myCustomers MyCustomerPart[]{};
myParameter int = 5;
get myArray with #sql{
CALL GETCUSTOMERS(:myParameter)
};
myCustomers MyCustomerPart[]{};
myParameter int = 5;
prepare p1 from "CALL GETCUSTOMERS(?)";
get myCustomers with p1 using myParameter;
When you work with an Oracle database, you can call either a stored procedure or a stored function.
open rs1 with #sql { call p1( ? ) };
x int = 10;
y int = 1000;
open rs2 with #sql { call p2( ?, :x, :y ) };
prepare pstmt3 from "call p1( ? )";
open rs3 with pstmt3;
prepare pstmt4 from "call p2( ?, ?, ? )";
open rs4 with pstmt4 using x, y;
execute #sql{ CREATE OR REPLACE PACKAGE MYPKG
AS
TYPE RC12 IS REF CURSOR;
END; };
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.
x int;
y string = "hello";
execute #sql{ call :x := func1( :y ) };
writeStdout( "The function returned " :: x );
prepare q from "call ? := func1( ? )";
execute q using x, y;
writeStdout( "The function returned " :: x );
open rs5 with #sql { call ? := f5() };
x int = 10;
y int = 1000;
open rs6 with #sql { call ? := f6( :x, :y ) };
prepare pstmt7 from "call ? := f5()";
open rs7 with pstmt7;
prepare pstmt8 from "call ? := f6( ?, ? )";
open rs8 with pstmt8 using x, y;
The following limitations apply to using stored procedures in EGL: