LOTUS CONNECTOR

例:SQL での DB2 ストアドプロシージャのサンプル
次に示すのは、簡単な SQL ストアドプロシージャのサンプルです。

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