A sample DB2 stored procedure

Introduction

I am not a database guy, but this one time I was cornered into writing a stored procedure on DB2 8.1. Below is the code which was written totally by googling around and painfully walking thru DB2 documentation. I am putting it here just a reference to stored proc syntax and DB2's basic control structs like if-else, while, using cursors etc.

Details

CREATE PROCEDURE SCHEMA_NAME.PROCEDURE1
        (IN VAR_DEPLOY_DESC VARCHAR(500),IN VAR_APP_DESC VARCHAR(100),IN VAR_SOA_LAYER VARCHAR(100),
        IN VAR_EVENT_DESC VARCHAR(500), IN VAR_SHORT_MESS VARCHAR(200), IN VAR_TIME_STP TIMESTAMP,
        IN VAR_STK_TRACE VARCHAR(10000), IN VAR_CONTEXT_THREAD VARCHAR(50),
        IN VAR_EXEC_DESC VARCHAR(500), IN VAR_METHOD_ID VARCHAR(10), IN VAR_PROG_LANG VARCHAR(20),
        IN VAR_REQ_IDENTITY VARCHAR(100), IN VAR_SESSION_ID VARCHAR(100),
        IN VAR_LINE_NO INTEGER)
language SQL

------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------

--begin atomic apparently start a transaction. (Not a 100% sure if that what it means)
p1: begin atomic

DECLARE LOOP_COUNTER INTEGER DEFAULT 0;
DECLARE NAME1  VARCHAR(200);
DECLARE VALUE1  VARCHAR(200);
DECLARE cur_deploy_id BIGINT;
DECLARE deploy_id_exists CHAR(1) DEFAULT 'y';
DECLARE cur_event_id BIGINT;

--Cursors are used to hold the results of a select statement
DECLARE cursor1 CURSOR FOR
        SELECT DEPLOYMENT_ID FROM SCHEMA_NAME.RT_DEPLOYMENT WHERE DEPLOYMENT_NAME = VAR_DEPLOY_DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET deploy_id_exists = 'n';
OPEN cursor1;
FETCH cursor1 INTO cur_deploy_id;

--If-else
IF  deploy_id_exists = 'n' THEN
        --Insert a new deploy ID
        insert into SCHEMA_NAME.RT_DEPLOYMENT (DEPLOYMENT_ID, DEPLOYMENT_NAME, APPLICATION_DESC, SOA_LAYER_DESC)
                values (default, VAR_DEPLOY_DESC,  VAR_APP_DESC, VAR_SOA_LAYER);
        set cur_deploy_id = IDENTITY_VAL_LOCAL();
END IF;


INSERT INTO SCHEMA_NAME.RT_EVENT
        (EVENT_ID, EVENT_DESC, SHORT_MESSAGE_DESC, EVENT_TSTP,
        STACK_TRACE_DESC, CONTEXT_THREAD_DESC, EXECUTABLE_DESC, METHOD_ID,
        PROGRAMMING_LANG_DESC, REQUESTING_IDENTITY_DESC, SESSION_ID, LINE_NO)
VALUES (DEFAULT, VAR_EVENT_DESC, VAR_SHORT_MESS, VAR_TIME_STP ,
        VAR_STK_TRACE, VAR_CONTEXT_THREAD, VAR_EXEC_DESC, VAR_EXEC_DESC,
       VAR_PROG_LANG, VAR_REQ_IDENTITY, VAR_SESSION_ID, VAR_LINE_NO );
set cur_event_id =  IDENTITY_VAL_LOCAL();

--While loop
WHILE (LOOP_COUNTER <= 3) DO
    SET NAME1 = 'name';
    SET VALUE1 = 'value';
    SET LOOP_COUNTER = LOOP_COUNTER + 1;
    INSERT INTO SCHEMA_NAME.RT_VALUE (VALUE_ID, EVENT_NAME, EVENT_VALUE) VALUES (DEFAULT, NAME1, VALUE1);
    INSERT INTO SCHEMA_NAME.RT_EVENT_RT_VALUE (EVENT_ID, VALUE_ID) VALUES (cur_event_id, IDENTITY_VAL_LOCAL());
END WHILE;


INSERT INTO SCHEMA_NAME.RT_DEPLOY_RT_EVENT (DEPLOYMENT_ID, EVENT_ID) VALUES ( cur_deploy_id, cur_event_id);

CLOSE cursor1;

END p1