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