-----------------------------------------------------------------------------
-- (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: tbselcreate.db2
--
-- SAMPLE: How to create the tables and procedure used in tbsel.sqc client
--
-- To run this script from the CLP, perform the following steps:
-- 1. connect to the database
-- 2. issue the command "db2 -td@ -vf <script-name>"
-- where <script-name> represents the name of this script
-- OR
-- The script tbseldrop.db2 can be run as indicated above to drop the
-- tables and procedure that are created within this script.
-----------------------------------------------------------------------------
-- For more information on the sample programs, see the README file.
--
-- For information on developing C applications, 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
-----------------------------------------------------------------------------
-- This table represents a table belonging to a company, Company A
CREATE TABLE company_a
(ID SMALLINT NOT NULL UNIQUE,
NAME VARCHAR(9),
DEPARTMENT SMALLINT,
JOB CHAR(5),
YEARS SMALLINT,
SALARY DECIMAL(7,2))@
-- Populate table company_a with data.
INSERT INTO company_a VALUES(5275, 'Sanders', 20, 'Mgr', 15, 18357.50),
(5265, 'Pernal', 20, 'Sales', 1, 18171.25),
(5791, 'O''Brien', 38, 'Sales', 10, 18006.00)@
-- This table represents a table belonging to a company, Company B
-- One column in this table is a generated column (column ID) - the
-- value for this column is a generated unique value when a row is
-- inserted to this table.
CREATE TABLE company_b
(ID SMALLINT GENERATED BY DEFAULT AS IDENTITY(START WITH 2000, INCREMENT BY 1 NOCACHE) NOT NULL,
NAME VARCHAR(9),
DEPARTMENT SMALLINT,
JOB CHAR(5),
YEARS SMALLINT,
SALARY DECIMAL(7,2),
BENEFITS VARCHAR(50),
OLD_ID SMALLINT)@
-- Populate table company_b with data.
INSERT INTO company_b VALUES
(default, 'Naughton', 38, 'Clerk', 0, 12954.75, 'No Benefits', 0),
(default, 'Yamaguchi', 42, 'Clerk', 6, 10505.90, 'Basic Health Coverage', 0),
(default, 'Fraye', 51, 'Mgr', 6, 21150.00, 'Basic Health Coverage', 0),
(default, 'Williams', 51, 'Sales', 6, 19456.50, 'Basic Health Coverage', 0),
(default, 'Molinare', 10, 'Mgr', 7, 22959.20, 'Basic Health Coverage', 0)@
-- This table holds salary information about employees.
-- The old and new (after an update) salary values are stored with
-- the associated employee's ID.
CREATE TABLE salary_change
(ID SMALLINT NOT NULL UNIQUE,
OLD_SALARY DECIMAL(7,2),
SALARY DECIMAL(7,2))@
-- ----------------------------------------------------------------------------
-- SQL PROCEDURE: buy_company
--
-- No parameters.
--
-- PURPOSE: The buy_company procedure encapsulates the table updates required
-- after Company B takes over Company A. Each employees from table
-- company_a is allocated a new benefits package based on their years of
-- experience. The employee data is moved into table company_b.
-- Each employee's salary is increased by 5%. The old and new salaries
-- are recorded in table salary_change. Encapsulated in this procedure
-- are SQL statements that illustrate a SELECT from a data change
-- statement. Data change statements include: INSERT, UPDATE, DELETE,
-- MERGE statements.
--
-- SHOWS: How to use retrieve result sets from a data change statement.
-- - to retrieve generated column values after an INSERT to a table
-- without requiring a subsequent SELECT statement
-- - to retrieve the OLD and NEW values of a column after an UPDATE
-- stateent modifies the column value
-- - to use INCLUDE columns with a retrieved result set (SELECT FROM
-- a data change statement.
-- - to efficiently encapsulate a data change statement and subsequent
-- SELECT statement into one statement (useful for performance!)
-- ----------------------------------------------------------------------------
CREATE PROCEDURE buy_company
LANGUAGE SQL
BEGIN
DECLARE new_id SMALLINT; -- employee's new id when they switch companies
DECLARE old_salary DECIMAL(7,2); -- employee's old salary
DECLARE benefits VARCHAR(50); -- employee's benefits
-- The following SELECT statement has a DELETE statement as the
-- table-reference in its FROM clause. When executed this statement
-- deletes all the rows from company_a, while selecting all of the just
-- deleted rows into the cursor emp_cursor. The for loop is used to
-- iterate through each employee in the cursor.
FOR iterate_employees AS emp_cursor CURSOR FOR (SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY
FROM OLD TABLE (DELETE FROM company_a ))
DO
-- The following case statement sets the new employee's benefits based
-- on their years of experience.
CASE
WHEN (YEARS > 14)
THEN SET benefits = 'Advanced Health Coverage and Pension Plan';
WHEN (YEARS > 9)
THEN SET benefits = 'Advanced Health Coverage';
WHEN (YEARS > 4)
THEN SET benefits = 'Basic Health Coverage';
ELSE
SET benefits = 'No Benefits';
END CASE;
-- The following SELECT statement references an INSERT statement as the
-- table-reference in its FROM clause. It inserts an employee record
-- into table company_b using the values held in the cursor.
-- The current employee ID of the cursor is selected into the variable new_id.
-- The keywords FROM FINAL TABLE determine that the value in new_id is
-- the value of ID after the INSERT statement is complete.
-- Note: By using the SELECT from a data change statement statement only a
-- single query is required to both INSERT and retrieve the generated column
-- value from the target table. To retrieve the generated ID column value
-- otherwise would ahve required 2 statements (an INSERT statement and a
-- subsequent SELECT statement) Using the SELECT from a data change statement
-- is more efficient and is a particularly useful solution for this problem in
-- applications where performance is a priority (eg. OLTP applications).
SELECT ID INTO new_id
FROM FINAL TABLE (INSERT INTO company_b
VALUES(default, NAME, DEPARTMENT,
JOB, YEARS, SALARY, benefits, ID));
-- The following SELECT statement references an UPDATE statement
-- in its FROM clause. It updates an employee's salary by giving
-- them a 5% raise. The employee's id, old salary and current
-- salary are all read into varibles for later use in this
-- function.
-- The INCLUDE statement works by creating a temporary column to
-- keep track of the old salary. This temporary column is only
-- available for this statement and is gone once the statement
-- completes. The only way to keep this data after the statement
-- completes is to read it into a variable.
SELECT ID, OLD_SALARY, SALARY into id, old_salary, salary
FROM FINAL TABLE (UPDATE company_b INCLUDE (OLD_SALARY DECIMAL(7,2))
SET OLD_SALARY = SALARY,
SALARY = SALARY * 1.05
WHERE ID = new_id);
-- This INSERT statement inserts an employee's id, old salary and
-- current salary into the salary_change table.
INSERT INTO salary_change VALUES(id, old_salary, salary);
END FOR;
-- The following DELETE statement references a SELECT statement in its
-- FROM clause. It lays off the highest paid manager. This DELETE
-- statement removes the manager from the table company_b.
DELETE FROM (SELECT * FROM company_b
ORDER BY SALARY DESC FETCH FIRST ROW ONLY);
-- The following UPDATE statement references a SELECT statement in its
-- FROM clause. It gives the most senior employee a $10000 bonus.
-- This UPDATE statement raises the employee's salary in the table
-- company_b.
UPDATE (SELECT MAX(YEARS) OVER() AS max_years,
YEARS,
SALARY
FROM company_b)
SET SALARY = SALARY + 10000
WHERE max_years = YEARS;
END@