-----------------------------------------------------------------------------
-- (c) Copyright IBM Corp. 2007 All rights reserved.
-- 
-- The following sample of source code ("Sample") is owned by International 
-- Business Machines Corporation or one of its subsidiaries ("IBM") and is 
-- copyrighted and licensed, not sold. You may use, copy, modify, and 
-- distribute the Sample in any form without payment to IBM, for the purpose of 
-- assisting you in the development of your applications.
-- 
-- The Sample code is provided to you on an "AS IS" basis, without warranty of 
-- any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
-- IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
-- MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
-- not allow for the exclusion or limitation of implied warranties, so the above 
-- limitations or exclusions may not apply to you. IBM shall not be liable for 
-- any damages you suffer as a result of using, copying, modifying or 
-- distributing the Sample, even if IBM has been advised of the possibility of 
-- such damages.
-----------------------------------------------------------------------------
--
-- SOURCE FILE NAME: spserver.db2
--
-- SAMPLE: To create a set of SQL procedures
--
-- To create the SQL procedures:
-- 1. Connect to the database
-- 2. Enter the command "db2 -td@ -vf spserver.db2"
--
-- To call these SQL procedures, you can use the
-- C, CLI, or C++ spclient application, or the Spclient
-- application in Java, by compiling and running one of
-- the following source files:
-- C: samples/c/spclient.sqc (UNIX) or samples\c\spclient.sqc (Windows)
-- CLI: samples/cli/spclient.c (UNIX) or samples\c\spclient.c (Windows)
-- C++: samples/cpp/spclient.sqC (UNIX) or samples\cpp\spclient.sqx (Windows)
-- Java JDBC: samples/java/jdbc/Spclient.java (UNIX)
--            or samples\java\jdbc\Spclient.java (Windows)
-- Java SQLJ: samples/java/sqlj/Spclient.sqlj (UNIX)
--            or samples\java\sqlj\Spclient.sqlj (Windows)
-----------------------------------------------------------------------------
--
-- For more information on the sample scripts, see the README file.
--
-- For information on creating SQL procedures, see the Developing SQL and External Routines book.
--
-- For information on using SQL statements, see the SQL Reference.
--
-- For the latest information on programming, building, and running DB2
-- applications, visit the DB2 Information Center:
--     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- Stored Procedure: OUT_LANGUAGE
--  
-- Purpose: Returns the code implementation language of
--          routine 'OUT_LANGUAGE' (as it appears in the
--          database catalog) in an output parameter.
--
-- Parameters:
--
-- IN:      (none)
-- OUT:     procedureLanguage - the code language of this routine
-----------------------------------------------------------------------------
CREATE PROCEDURE OUT_LANGUAGE (OUT procedureLanguage CHAR(8))
SPECIFIC SQL_OUT_LANGUAGE
DYNAMIC RESULT SETS 0
LANGUAGE SQL
READS SQL DATA
BEGIN
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE errorLabel CHAR(32) DEFAULT '';
  
  -- in case of no data found  
  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND'; 

  -- in case of SQL error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

  SET errorLabel = 'SELECT STATEMENT';    
  SELECT language INTO procedureLanguage
    FROM sysibm.sysprocedures
    WHERE procname = 'OUT_LANGUAGE';
END @

-----------------------------------------------------------------------------
-- Stored Procedure: OUT_PARAM
-- 
-- Purpose: Sorts table STAFF by salary, locates and returns
--          the median salary
-- 
-- Parameters:
--
-- IN:      (none)
-- OUT:     medianSalary - median salary in table STAFF
-----------------------------------------------------------------------------
CREATE PROCEDURE OUT_PARAM (OUT medianSalary DOUBLE)
SPECIFIC SQL_OUT_PARAM
DYNAMIC RESULT SETS 0
LANGUAGE SQL
READS SQL DATA
BEGIN
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE errorLabel CHAR(32) DEFAULT '';
  DECLARE SQLCODE INTEGER DEFAULT 0;
  DECLARE v_numRecords INT DEFAULT 1;
  DECLARE v_counter INT DEFAULT 0;
  DECLARE v_mod INT DEFAULT 0;
  DECLARE v_salary1 DOUBLE DEFAULT 0;
  DECLARE v_salary2 DOUBLE DEFAULT 0;

  DECLARE c1 CURSOR FOR
    SELECT CAST(salary AS DOUBLE) FROM staff
    ORDER BY salary;

  -- in case of no data found
  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND'; 

  -- in case of SQL error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

  -- initialize OUT parameter
  SET medianSalary = 0;

  SET errorLabel = 'SELECT COUNT';
  SELECT COUNT(*) INTO v_numRecords FROM staff;

  SET errorLabel = 'OPEN CURSOR';
  OPEN c1;

  SET v_mod = MOD(v_numRecords, 2);

  CASE v_mod
   WHEN 0 THEN
     WHILE v_counter < (v_numRecords / 2 + 1) DO
       SET v_salary1 = v_salary2;
       FETCH c1 INTO v_salary2;
       SET v_counter = v_counter + 1;
     END WHILE;
     SET medianSalary = (v_salary1 + v_salary2)/2;
   WHEN 1 THEN
     WHILE v_counter < (v_numRecords / 2 + 1) DO
       FETCH c1 INTO medianSalary;
       SET v_counter = v_counter + 1;
     END WHILE;
  END CASE;

  SET errorLabel = 'CLOSE CURSOR';
  CLOSE c1;
END @

-----------------------------------------------------------------------------
-- Stored Procedure: IN_PARAMS
--  
-- Purpose: Updates salaries of employees in department 'department'
--          using inputs lowsal, medsal, highsal as
--          salary raise/adjustment values.
--  
-- Parameters:
--
-- IN:      lowsal      - new salary for low salary employees
--          medsal      - new salary for mid salary employees
--          highsal     - new salary for high salary employees
--          department  - department to use in SELECT predicate
-- OUT:     (none)
--
-----------------------------------------------------------------------------
CREATE PROCEDURE IN_PARAMS (IN lowsal DOUBLE, IN medsal DOUBLE, IN highsal DOUBLE, IN department CHAR(3))
SPECIFIC SQL_IN_PARAMS
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE SQL 
MODIFIES SQL DATA
BEGIN
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE SQLCODE INTEGER DEFAULT 0;
  DECLARE errorLabel CHAR(32) DEFAULT '';
  DECLARE v_firstnme VARCHAR(12);
  DECLARE v_midinit CHAR(1);
  DECLARE v_lastname VARCHAR(15);
  DECLARE v_salary DOUBLE;
  DECLARE at_end SMALLINT DEFAULT 0;
 
  DECLARE c1 CURSOR FOR
    SELECT firstnme, midinit, lastname, CAST(salary AS DOUBLE)
    FROM employee
    WHERE workdept = department 
    FOR UPDATE OF salary;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET at_end = 1;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

  -- initialize OUT parameter
  SET errorLabel = 'OPEN CURSOR';
  OPEN c1;
  SET errorLabel = 'FIRST FETCH';
  FETCH c1 INTO v_firstnme, v_midinit, v_lastname, v_salary;
  WHILE (at_end = 0) DO
    IF (lowsal > v_salary) THEN
      UPDATE employee
      SET salary = lowsal
      WHERE CURRENT OF c1;
    ELSEIF (medsal > v_salary) THEN
      UPDATE employee
      SET salary = medsal
      WHERE CURRENT OF c1;
    ELSEIF (highsal > v_salary) THEN
      UPDATE employee
      SET salary = highsal
      WHERE CURRENT OF c1;
    ELSE UPDATE employee
      SET salary = salary * 1.10
      WHERE CURRENT OF c1;
    END IF;
    SET errorLabel = 'FETCH IN WHILE LOOP';
    FETCH c1 INTO v_firstnme, v_midinit, v_lastname, v_salary;
  END WHILE;
  SET errorLabel = 'CLOSE CURSOR';
  CLOSE c1;
END @

-----------------------------------------------------------------------------
-- Stored Procedure: INOUT_PARAM
--  
-- Purpose: Calculates the median salary of all salaries in the STAFF
--          above table the input median salary.
--
-- Parameters:
--
-- IN/OUT: medianSalary - median salary
--                        The input value is used in a SELECT predicate. 
--                        Its output value is set to the median salary. 
--
-----------------------------------------------------------------------------
CREATE PROCEDURE INOUT_PARAM (INOUT medianSalary DOUBLE)
SPECIFIC SQL_INOUT_PARAM
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE SQL 
READS SQL DATA
BEGIN 
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE SQLCODE INTEGER DEFAULT 0;
  DECLARE errorLabel CHAR(32) DEFAULT '';
  DECLARE v_mod INT DEFAULT 0;
  DECLARE v_medianSalary DOUBLE DEFAULT 0;
  DECLARE v_numRecords INT DEFAULT 1;
  DECLARE v_counter INT DEFAULT 0;
  DECLARE v_salary1 DOUBLE DEFAULT 0;
  DECLARE v_salary2 DOUBLE DEFAULT 0;

  DECLARE c1 CURSOR FOR 
    SELECT CAST(salary AS DOUBLE) FROM staff 
    WHERE salary > medianSalary
    ORDER BY salary;

  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND'; 

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

  SET errorLabel = 'SELECT COUNT';
  SELECT COUNT(*) INTO v_numRecords FROM staff WHERE salary > medianSalary;

  SET v_mod = MOD(v_numRecords, 2);

  SET errorLabel = 'OPEN CURSOR';
  OPEN c1;

  CASE v_mod
   WHEN 0 THEN
     WHILE v_counter < (v_numRecords / 2 + 1) DO
       SET v_salary1 = v_salary2;
       FETCH c1 INTO v_salary2;
       SET v_counter = v_counter + 1;
     END WHILE;
     SET medianSalary = (v_salary1 + v_salary2)/2;
   WHEN 1 THEN
     WHILE v_counter < (v_numRecords / 2 + 1) DO
       FETCH c1 INTO medianSalary;
       SET v_counter = v_counter + 1;
     END WHILE;
  END CASE;

  SET errorLabel = 'CLOSE CURSOR';
  CLOSE c1;
END @

-----------------------------------------------------------------------------
--  Stored Procedure: DECIMAL_TYPE
--
--  Purpose:  Takes in a decimal number as input, divides it by 2 
--            and returns the resulting decimal rounded off to 2 
--            decimal places.
--
--  Parameters:
--  
--   INOUT:   inOutDecimal - DECIMAL(10,2)
--                            
-----------------------------------------------------------------------------
CREATE PROCEDURE DECIMAL_TYPE (INOUT inOutDecimal DECIMAL(10,2))
SPECIFIC SQL_DEC_TYPE  
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE SQL 
READS SQL DATA
BEGIN

  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE errorLabel CHAR(32) DEFAULT '';
  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

  SET errorLabel = 'IF DECIMAL';
  IF (inOutDecimal = 0) THEN SET inOutDecimal = 1;
  ELSE SET inOutDecimal = inOutDecimal / 2;
  END IF;

END @

-----------------------------------------------------------------------------
--  Stored Procedure: ALL_DATA_TYPES  
--
--  Purpose: Take each parameter and set it to a new output value.
--           This sample shows only a subset of DB2 supported data types.
--           For a full listing of DB2 data types, please see the SQL 
--           Reference. 
--
--  Parameters:
--  
--   INOUT:   inOutSmallint, inOutInteger, inOutBigint, inOutReal,
--            inoutDouble
--   OUT:     charOut, charsOut, varcharOut, charsOut, timeOut
--
-----------------------------------------------------------------------------
CREATE PROCEDURE ALL_DATA_TYPES (INOUT inOutSmallint SMALLINT, 
  INOUT inOutInteger INTEGER, INOUT inOutBigint BIGINT,
  INOUT inOutReal REAL, INOUT inoutDouble DOUBLE,
  OUT charOut CHAR(1), OUT charsOut CHAR(15),
  OUT varcharOut VARCHAR(12), OUT dateOut DATE,
  OUT timeOut TIME)
SPECIFIC SQL_ALL_DAT_TYPES
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE SQL 
READS SQL DATA
BEGIN
  
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE errorLabel CHAR(32) DEFAULT '';
  
  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND'; 

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

  SET errorLabel = 'IF SMALLINT';
  IF (inOutSmallint = 0) THEN SET inOutSmallint = 1;
  ELSE SET inOutSmallint = inOutSmallint / 2;
  END IF;

  SET errorLabel = 'IF INTEGER';
  IF (inOutInteger = 0) THEN SET inOutInteger = 1;
  ELSE SET inOutInteger = inOutInteger / 2;
  END IF;

  SET errorLabel = 'IF BIGINT';
  IF (inOutBigint = 0) THEN SET inOutBigint = 1;
  ELSE SET inOutBigint = inOutBigint / 2;
  END IF;

  SET errorLabel = 'IF REAL';
  IF (inOutReal = 0) THEN SET inOutReal = 1;
  ELSE SET inOutReal = inOutReal / 2;
  END IF;

  SET errorLabel = 'IF DOUBLE';
  IF (inoutDouble = 0) THEN SET inoutDouble = 1;
  ELSE SET inoutDouble = inoutDouble / 2;
  END IF;

  SET errorLabel = 'SELECT midinit';
  SELECT midinit INTO charOut FROM employee WHERE empno = '000180';

  SET errorLabel = 'SELECT lastname';
  SELECT lastname INTO charsOut FROM employee WHERE empno = '000180';

  SET errorLabel = 'SELECT firstnme';
  SELECT firstnme INTO varcharOut FROM employee WHERE empno = '000180';

  SET errorLabel = 'VALUES CURRENT DATE';
  VALUES CURRENT DATE INTO dateOut;

  SET errorLabel = 'VALUES CURRENT TIME';
  VALUES CURRENT TIME INTO timeOut;

END @

-----------------------------------------------------------------------------
-- Stored Procedure: ONE_RESULT_SET
--
-- Purpose: Returns a result set to the caller that identifies employees
--          with salaries greater than the value of input parameter
--          salValue.
--
-- Parameters:
-- 
-- IN:      salValue - salary
--
-----------------------------------------------------------------------------
CREATE PROCEDURE ONE_RESULT_SET (IN salValue DOUBLE)
SPECIFIC SQL_ONE_RES_SET
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE SQL 
READS SQL DATA
BEGIN 
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE SQLCODE INTEGER DEFAULT 0;
  DECLARE errorLabel CHAR(32) DEFAULT '';

  -- use WITH RETURN TO CLIENT in DECLARE CURSOR to always 
  -- return a result set to the client application
  DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR 
    SELECT name, job, CAST(salary AS DOUBLE) 
    FROM staff 
    WHERE salary > salValue
    ORDER BY salary;

  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND'; 

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;
 
  -- to return result set, do not CLOSE cursor
  SET errorLabel = 'OPEN CURSOR';
  OPEN c1;
 
END @

-----------------------------------------------------------------------------
-- Stored Procedure: RESULT_SET_CALLER
-- 
-- Purpose:  Returns a result set to the caller that identifies employees
--           with salaries greater than the value of input parameter
--           salValue.
-- 
-- Parameters:
--
-- IN:      salValue
-- OUT:     ResultSet
-----------------------------------------------------------------------------
CREATE PROCEDURE RESULT_SET_CALLER (IN salValue DOUBLE)
SPECIFIC SQL_RES_SET_CALLER
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
BEGIN
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE errorLabel CHAR(32) DEFAULT '';
  
  -- use WITH RETURN TO CALLER in DECLARE CURSOR to always
  -- return a result set to the calling application
  DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
    SELECT name, job, CAST(salary AS DOUBLE)
    FROM staff
    WHERE salary > salValue
    ORDER BY salary;

  -- in case of no data found
  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND'; 

  -- in case of SQL error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

  -- to return result set, do not CLOSE cursor
  OPEN c1;
END @

-----------------------------------------------------------------------------
--  Stored Procedure: TWO_RESULT_SETS
--
--  Purpose:  Return two result sets to the caller. One result set
--            consists of employee data of all employees with
--            salaries greater than medianSalary.  The other
--            result set contains employee data for employees with salaries
--            less than medianSalary.
--
--  Parameters:
-- 
--   IN:      medianSalary - salary
--
-----------------------------------------------------------------------------
CREATE PROCEDURE TWO_RESULT_SETS (IN medianSalary DOUBLE)
SPECIFIC SQL_TWO_RES_SETS
DYNAMIC RESULT SETS 2
NOT DETERMINISTIC
LANGUAGE SQL 
READS SQL DATA
BEGIN
  
  DECLARE nestCode INTEGER;
  DECLARE nestLabel CHAR(32);
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE errorLabel CHAR(32) DEFAULT '';
  
  DECLARE r1 CURSOR WITH RETURN FOR
    SELECT name, job, CAST(salary AS DOUBLE)
    FROM staff
    WHERE salary > medianSalary
    ORDER BY salary; 
  
  DECLARE r2 CURSOR WITH RETURN FOR
    SELECT name, job, CAST(salary AS DOUBLE)
    FROM staff
    WHERE salary < medianSalary
    ORDER BY salary DESC; 

  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND'; 

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;
    
  SET errorLabel = 'OPEN CURSOR r1';
  OPEN r1;

  SET errorLabel = 'OPEN CURSOR r2';
  OPEN r2;

  -- the EXIT handler ensures that we will not reach this point unless the 
  -- result set has results

END @