There are two parts to any stored procedure:

• 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