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