The procedure specification provides the name of the procedure and can include other optional clauses, such as:
Parameter declarations
Procedure result set declarations
Import clauses
The procedure body contains the Java code that executes when an application invokes the procedure.
A simple stored procedure requires the procedure name in the specification and a statement requiring no parameters in the body. The following example assumes the existence of a table named HellowWorld, and inserts a quoted string into that table.
Example: Creating a stored procedure
CREATE PROCEDURE HelloWorld ()
BEGIN
SQLIStatement Insert_HelloWorld = new SQLIStatement (
"INSERT INTO HelloWorld(fld1) values ('Hello World!')");
Insert_HelloWorld.execute();
END;
Example:Executing a stored procedure
SQLExplorer> CREATE TABLE helloworld (fld1 CHAR(100));
SQLExplorer> CALL HelloWorld();
0 records returned
SQLExplorer> SELECT * FROM helloworld;
FLD1
----
Hello World!
1 record selected
Subsequently, you can execute the procedure shown in the following example.
The procedure specification can also contain other clauses.
Parameter declarations specify the names and types of parameters that the calling application will pass and receive from the procedure. Parameters can be input, output, or both.
The procedure result set declaration details the names and types of fields in a result set the procedure generates. The result set is a set of rows that contain data generated by the procedure. If a procedure retrieves rows from a database table, for instance, it can store the rows in a result set for access by applications and other procedures. The names specified in the result‑set declaration are not used within the stored procedure body. Instead, methods of the OpenEdge SQL Java classes refer to fields in the result set by ordinal number, not by name.
The import clause specifies which packages the procedure needs from the Java core API. By default, the Java compiler imports the java.lang package. The IMPORT clause must list any other packages the procedure uses. OpenEdge SQL automatically imports the packages it requires.
The following example shows a more complex procedure specification that contains these elements.
CREATE PROCEDURE new_sal (
IN deptnum INTEGER,
IN pct_incr INTEGER
)RESULT (
empname CHAR (20),
oldsal NUMERIC,
newsal NUMERIC
)IMPORT
import java.dbutils.SequenceType;
BEGIN
.
.
.
END
The procedure specification provides the name of the procedure and can include other optional clauses, such as: