LOTUS CONNECTORS

Example: DB2 Stored Procedure in SQL
The following is an example of a simple SQL stored procedure.

CREATE PROCEDURE DB2ADMIN.NEW_SALES_ORDER ( IN CUSTID int,

IN ITEMID int,

IN QTY int,

OUT SONUM bigint)

LANGUAGE SQL

P1: BEGIN

DECLARE CUSTVAR int;

DECLARE ITEMVAR int;

DECLARE QTYVAR int;

DECLARE ITEMCOSTVAR dec(7,2);

DECLARE AMTVAR dec(9,2);

DECLARE STAMP timestamp;

DECLARE cursor1 CURSOR FOR

SELECT ITEMCOST FROM DB2ADMIN.ITEMS

WHERE ITEMID = ITEMVAR;

DECLARE cursor2 CURSOR FOR

SELECT SONUM FROM DB2ADMIN.SALES_ORDERS

WHERE DTEORD = STAMP;

SET CUSTVAR = CUSTID;

SET ITEMVAR = ITEMID;

SET QTYVAR = QTY;

SET STAMP = CURRENT TIMESTAMP;

OPEN cursor1;

FETCH FROM cursor1 INTO ITEMCOSTVAR;

CLOSE cursor1;

SET AMTVAR = QTY * ITEMCOSTVAR;

INSERT INTO DB2ADMIN.SALES_ORDERS (CUSTID, ITEMID, QTY, AMT, DTEORD)

VALUES (CUSTVAR, ITEMVAR, QTYVAR, AMTVAR, STAMP);

OPEN cursor2;

FETCH FROM cursor2 INTO SONUM;

CLOSE cursor2;

END P1