Writing DB2 stored procedure

Writing a db2 stored procedure.

1. Creating a empty body procedure

    CREATE [OR REPLACE] PROCEDURE schemaname.proc_name(
        [IN|OUT] variable1 [datatype(char|varchar|integer|decimal|date|timestamp..)] ,
        ....
    )

    [DYNAMIC RESULT SETS 1] if you dont specify then DYNAMIC RESULT SETS is set to 0 meaning returns all resultset's ... however giving some no. will restrict it to that upper limit throwing SQL0464W
    LANGAUGE SQL
    BEGIN
           
             -- Procedure Body goes here --
      
    END;


    Sample DB2 stored procedure:
    CREATE OR REPLACE PROCEDURE SCHEMA1.EMPLOYEE_SUMMARY(
                                    IN EMP_ID VARCHAR(40)
                                )
    DYNAMIC RESULT SETS 1
    LANGAUGE SQL
    BEGIN

    END;


    Calling Stored procedure :
    call SCHEMA1.EMPLOYEE_SUMMARY('E_123_0001');


2. Inserting From stored procedure:
    CREATE OR REPLACE PROCEDURE SCHEMA1.CREATE_EMPLOYEE(  IN i_emp_id VARCHAR(40),
                                  IN i_emp_dob DATE,
                                  IN i_emp_name VARCHAR(100))
    LANGAUGE SQL
    BEGIN

        /* All the variable declration should be done at start */
        declare rcount integer DEFAULT 0;

        /* Insert the employee record */
        INSERT INTO EMPLOYEE(EMP_ID, EMP_DOB, EMP_NAME) VALUES(i_emp_id, i_emp_dob, i_emp_name);

        /* Get number of records inserted in to database */
        GET DIAGNOSTICS rcount = ROW_COUNT;
      
        /* Check last insert was successfull */
        if ( rcount <> 0) then
            return 1
        else
            return 0
        end if;
    END;


3. Getting result set from stored procedure
    CREATE OR REPLACE PROCEDURE SCHEMA1.SEARCH_EMPLOYEE(IN i_emp_name varchar(200))
    LANGAUGE SQL
    BEGIN

    /* DECLARE Cursor for the employee records */
    DECLARE result_set_1 CURSOR WITH RETURN TO CLIENT FOR
     SELECT
        *
    FROM
        EMPLOYEE
    WHERE
        EMP_NAME LIKE '%' || i_emp_name || '%';
    ORDER BY
        EMP_NAME ASC;
  
    /* Check if the search key is empty search all employee */
    if (i_emp_name is NULL || i_emp_name == '') then
    set i_emp_name = '*';
    end if;

    /* open the result set to be get fetched by the calling programm */
    open result_set_1;

    END;


4. Getting average percentage of the total salary for the employee.
    How much percetnage employee is costing to company.
    CREATE OR REPLACE PROCEDURE SCHEMA1.SALARY_SUMMARY()
    LANGAUGE SQL
    BEGIN

    /* DECLARE Cursor for the employee records */
    DECLARE result_set_1 CURSOR WITH RETURN TO CLIENT FOR
     SELECT
        EMP_NAME,
        SALARY / SUM(EMP_SALARY)  * 100 AS PERCENTAGE_SALARY
    FROM
        EMPLOYEE
  
    /* open the result set to be get fetched by the calling programm */
    open result_set_1;

    END;