A detailed example is presented in this section to demonstrate all the basic features discussed thus far. The procedure in Figure 2.10 inserts a row into the employee table of the SAMPLE database. If you have not created the SAMPLE database, see Appendix A, "Getting Started with DB2." We recommend that you create this database because most examples in this book make use of it to demonstrate concepts.
Figure 2.10. add_new_employee procedure
CREATE PROCEDURE add_new_employee ( IN p_empno VARCHAR(6) -- (1) , IN p_firstnme CHAR(12) , IN p_midinit CHAR(1) , IN p_lastname VARCHAR(15) , IN p_deptname VARCHAR(30) , IN p_edlevel SMALLINT , OUT p_status VARCHAR(100) , OUT p_ts TIMESTAMP) LANGUAGE SQL SPECIFIC add_new_employee -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries BEGIN DECLARE v_deptno CHAR(3) DEFAULT ' '; -- (2) DECLARE v_create_ts TIMESTAMP; -- (3) SET v_create_ts = CURRENT TIMESTAMP; /* Get the corresponding department number */ SELECT deptno INTO v_deptno -- (4) FROM department WHERE deptname = p_deptname; /* Insert new employee into table */ -- (5) INSERT INTO employee ( empno , firstnme , midinit , lastname , workdept , hiredate , edlevel) VALUES ( p_empno , p_firstnme , p_midinit , p_lastname , v_deptno , DATE(v_create_ts) , p_edlevel ); SET p_status = 'Employee added'; -- (6) SET p_ts = v_create_ts; -- (7) END
The parameter list shown in Line (1) defines input and output variables to the procedures. The input parameters represent column values that will be inserted into the Employee table. Note that the p_ prefix is used for each variable to differentiate variables from column names. The output parameters are used to return a status and the TIMESTAMP of the execution of the procedure.
Line (2) declares the v_deptno variable to hold the department number retrieved from the Department table. v_deptno is initialized to ' '.
Line (3) declares the v_create_ts variable to hold the TIMESTAMP of execution of the procedure. To ensure that the same value of hiredate is used to insert into the Employee table, the CURRENT TIMESTAMP register is retrieved only once in the procedure.
To look up the department number in the Department table, a SELECT statement shown in Line (4) is used to retrieve deptno and saved in v_deptno.
Line (5) inserts into the Employee table using parameter values v_deptno and v_create_ts. The value of v_create_ts, which is of type TIMESTAMP, must be cast to DATE using the DATE casting function.
On Lines (6) and (7), the output parameters p_status and p_ts are set.
To execute the add_new_employee procedure on LUW, enter the following in CLP:
The output of this call is
P_STATUS: Employee added P_TS: 2002-09-29 17:19:10.927001 "ADD_NEW_EMPLOYEE" RETURN_STATUS: "0"
The results of many examples in the book are shown using the CLP on LUW. The output will vary based on the operating system. For example, if you are using the Run an SQL Script window of the iSeries Navigator, the output from the call above would be
Output Parameter #7 = Employee added Output Parameter #8 = 2002-09-29 17:19:10.927001 Statement ran successfully (591 ms)