Stored procedures can be written in COBOL, PL/1, REXX and Java etc. Db2 stored procedures are handled by WLM -work Load Manager. Main uses are reusability and security. How to write stored procedure?
Syntax for DB2 Stored Procedure
CREATE PROCEDURE UPDATE_SALARY (IN EMPLOYEE_NUM CHAR(6), IN RATE1 DECIMAL(5,2)) LANGUAGE SQL WLM ENVIRONMENT SAMP1 COMMIT ON RETURN YES IF RATE1 <= 0.7 THEN UPDATE EMP SET SALARY = SALARY * RATE1 WHERE EMPNO= EMPLOYEE_NUM; ELSE UPDATE EMP SET SALARY = SALARY * 0.7 WHERE EMPNO = EMPLOYEE_NUM; END IF
There are three parameters passed to SP(stored procedure). IN- Input, OUT-Output INOUT-Input and output.