Bringing It All Together Example

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:

 CALL add_new_employee('123456','ROBERT','K','ALEXANDER','PLANNING',1,?,?) 

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)