Product Documentation

FairCom Java & .NET Stored Procedures

Previous Topic

Next Topic

Structure of Stored Procedures

There are two parts to any stored procedure:

  • The procedure specification must provide the name of the procedure and may include other optional clauses.
  • The procedure body contains the Java code that executes when an application invokes the procedure.

A simple stored procedure requires only the procedure name in the specification and a statement that requires no parameters in the body, as shown in the following example. The procedure in the following example assumes a table called HelloWorldTBL exists, and inserts a string into that table.

A Simple Stored Procedure Example

CREATE PROCEDURE HelloWorld ()

Procedure Specification

BEGIN

SQLIStatement Insert_HelloWorld

= new SQLIStatement ("INSERT INTO

HelloWorldTBL(fld1) values

('Hello World!')");

Insert_HelloWorld.execute();

END

Procedure Body

From the Interactive SQL utility, isql, you could execute the procedure as follows:

ISQL> CREATE TABLE helloworldTBL (fld1 CHAR(100));

ISQL> CALL HelloWorld();

0 records returned

ISQL> SELECT * FROM helloworldTBL;

FLD1

----

Hello World!

1 record selected

The procedure specification can also contain other optional clauses:

  • Parameter declarations specify the name and type of parameters that the calling application will pass and receive from the procedure. Parameters can be of type 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.

    Note: names specified in the result-set declaration are not used within the stored procedure body. Instead, methods of the FairCom DB 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.

The following example shows a more complex procedure specification that contains these elements.

Complete Stored Procedure Example

Procedure Specification:

CREATE PROCEDURE new_sal(IN deptnum INTEGER, IN pct_incr INTEGER)

Parameter declarations

RESULT(empname CHAR(20), oldsal NUMERIC, newsal NUMERIC)

Procedure result set declaration

IMPORT import java.dbutils.SequenceType;

Import clause

Procedure Body:

BEGIN
.
.
.
END

Body

TOCIndex