DB2_建立在臨時表上的檢視-過程-表函式-觸發器

目的：
測試DB2臨時表上的檢視-過程-表函式-觸發器，本指令碼摘錄自DB2安裝目錄admin_scripts/ctgg.db2。
版本：Windows DB2 Express-C V9.7

使用者要求：
作業系統使用者mh有DBADM,SECADM許可權,mh1234

操作步驟：
使用"db2cmd db2 -td@"進入互動模式,執行後續操作。

1,建立payroll表
CONNECT TO sample@

CREATE BUFFERPOOL BufForSample IMMEDIATE PAGESIZE 4k@
CREATE USER TEMPORARY TABLESPACE TbspaceCgtt PAGESIZE 4k MANAGED BY DATABASE
   USING (FILE 'cont_Cgtt' 3000) BUFFERPOOL BufForSample@
CREATE REGULAR TABLESPACE TbspacePayroll PAGESIZE 4k MANAGED BY DATABASE
   USING (FILE 'cont_Payroll' 3000) BUFFERPOOL BufForSample@

CREATE TABLE payroll (empid          CHARACTER(6) REFERENCES employee(empno),
                      salaryPA       DECFLOAT,
            tax_payable    DECFLOAT,
            tax_exempted   DECFLOAT,
            tax_proof      DECFLOAT,
            tax_to_be_paid DECFLOAT,
            deptno         CHARACTER(3),
            calculate_tax  INT) IN TbspacePayroll@

INSERT INTO payroll(empid, deptno, salaryPA)
(SELECT empno,workdept, (salary * 12) AS salary FROM employee)@

UPDATE payroll SET tax_exempted = 100000@

2,建立臨時表及檢視、索引
CREATE GLOBAL TEMPORARY TABLE cgtt.tax_cal AS
  (SELECT e.empno, e.firstnme, e.lastname, e.birthdate, e.bonus, e.comm,
          p.salarypa,
          p.tax_payable, p.tax_exempted,  p.tax_proof, p.tax_to_be_paid,p.deptno
     FROM employee AS e, payroll AS p
   WHERE e.empno = p.empid)
   DEFINITION ONLY ON COMMIT PRESERVE ROWS
   IN TbspaceCgtt@

-- Create INDEX 'IndexOnCgtt' based on 'tax_cal'.
CREATE INDEX cgtt.indexOnId ON cgtt.tax_cal(empno) ALLOW REVERSE SCANS@

-- Create View 'ViewOnCgtt' based on 'tax_cal', to print the IT sheet
CREATE VIEW cgtt.ViewOnCgtt AS
  SELECT empno, firstnme, lastname, birthdate, deptno,bonus, comm, salarypa,
         tax_to_be_paid
     FROM cgtt.tax_cal@

3,建立過程,函式
CREATE FUNCTION tax_compute(salarypa DECFLOAT,
                            exempted DECFLOAT,awarded_pay DECFLOAT)
SPECIFIC common_calculator
RETURNS DECFLOAT
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
  DECLARE payable_tax DECFLOAT;
  SET payable_tax = salarypa - exempted;

  IF payable_tax <= 500000 THEN
    SET payable_tax = payable_tax * 0.10;
  ELSEIF payable_tax > 500000 AND payable_tax <= 1000000 THEN
    SET payable_tax = payable_tax * 0.20;
  ELSEIF payable_tax > 1000000 THEN
    SET payable_tax = payable_tax * 0.30;
  END IF;

  SET payable_tax = payable_tax + (awarded_pay * 0.30);
  RETURN payable_tax;
END@

CREATE  PROCEDURE initial_tax_compute()
SPECIFIC initialTax
LANGUAGE SQL
BEGIN
  DECLARE id CHARACTER(6);
  DECLARE at_end SMALLINT DEFAULT 0;
  DECLARE salary DECFLOAT;
  DECLARE payable_tax DECFLOAT;
  DECLARE not_found CONDITION for SQLSTATE '02000';

  DECLARE IterateOverEmpRecord CURSOR WITH HOLD FOR SELECT empid,salaryPA FROM payroll;
  DECLARE CONTINUE HANDLER for not_found SET at_end = 1;

  OPEN IterateOverEmpRecord;

  ins_loop: LOOP
    FETCH IterateOverEmpRecord INTO id,salary;

    IF at_end = 1 THEN
      LEAVE ins_loop;
    ELSE

      UPDATE payroll SET tax_payable = tax_compute(salary,100000,0) WHERE empid = id;
      COMMIT;
      ITERATE ins_loop;
    END IF;
  END LOOP;

  CLOSE IterateOverEmpRecord;
END@

CREATE PROCEDURE update()
SPECIFIC updater
LANGUAGE SQL
BEGIN
  DECLARE at_end SMALLINT DEFAULT 0;
  DECLARE id CHARACTER(6);
  DECLARE tax_left DECFLOAT;
  DECLARE tax_p DECFLOAT;
  DECLARE not_found CONDITION FOR SQLSTATE '02000';

  DECLARE UpdateCGTT CURSOR WITH HOLD FOR
              SELECT empno,tax_to_be_paid,tax_payable
                 FROM cgtt.tax_cal;
  DECLARE UpdatePayroll CURSOR WITH HOLD FOR
              SELECT empno,tax_to_be_paid,tax_payable
                 FROM cgtt.tax_cal;
  DECLARE CONTINUE HANDLER for not_found SET at_end = 1;

  OPEN UpdateCGTT;

  up_loop: LOOP
    FETCH UpdateCGTT INTO id,tax_left,tax_p;

    IF at_end = 1 THEN
      LEAVE up_loop;
    ELSE
      UPDATE cgtt.tax_cal SET tax_payable = tax_left,
                              tax_to_be_paid = tax_left - tax_p
                          WHERE empno = id;
      ITERATE up_loop;
    END IF;
  END LOOP;

  CLOSE UpdateCGTT;

  SET at_end = 0;
  OPEN UpdatePayroll;

  update_payroll: LOOP
    FETCH UpdatePayroll INTO id,tax_left,tax_p;

    IF at_end = 1 THEN
      LEAVE update_payroll;
    ELSE
      UPDATE payroll SET tax_payable = tax_p,
                         tax_to_be_paid = tax_left
                     WHERE empid = id;
    ITERATE update_payroll;
    END IF;
  END LOOP;

  CLOSE UpdatePayroll;

END@

CREATE  PROCEDURE final_tax_compute()
SPECIFIC finalTax
LANGUAGE SQL
BEGIN
  DECLARE id CHARACTER(6);
  DECLARE at_end SMALLINT DEFAULT 0;
  DECLARE salary DECFLOAT;
  DECLARE exempted DECFLOAT;
  DECLARE proof DECFLOAT;
  DECLARE awarded_pay DECFLOAT;
  DECLARE bonus DECFLOAT;
  DECLARE comm DECFLOAT;
  DECLARE not_found CONDITION for SQLSTATE '02000';

  DECLARE IterateOverEmpRecord CURSOR WITH HOLD FOR
          SELECT empno,bonus,comm,salaryPA,tax_exempted,tax_proof FROM cgtt.tax_cal;
  DECLARE CONTINUE HANDLER for not_found SET at_end = 1;

  OPEN IterateOverEmpRecord;

  ins_loop: LOOP
    FETCH IterateOverEmpRecord INTO id, bonus, comm, salary, exempted, proof;

    IF at_end = 1 THEN
      LEAVE ins_loop;
    ELSE       
      IF exempted > proof THEN
        SET exempted = proof;
      END IF;
      SET awarded_pay = bonus + comm;
 
      UPDATE cgtt.tax_cal SET tax_to_be_paid = tax_compute(salary,exempted,awarded_pay) WHERE empno = id;
      ITERATE ins_loop;
    END IF;
  END LOOP;

  CLOSE IterateOverEmpRecord;
 
END@

4，建立表函式
CREATE FUNCTION printITSheet()
SPECIFIC ITSheet
RETURNS TABLE (empno           CHARACTER(6),
               firstnme        VARCHAR(12),
               lastname        VARCHAR(15),
               birthdate       DATE,
               bonus           DECFLOAT,
               comm            DECFLOAT,
               salarypa        DECFLOAT,
               tax_to_be_paid  DECFLOAT)
LANGUAGE SQL
NO EXTERNAL ACTION
READS SQL DATA
RETURN
  SELECT empno, firstnme, lastname, birthdate, bonus, comm, salarypa, tax_to_be_paid
    FROM cgtt.ViewOnCgtt@

5，建立Payroll上的觸發器
CREATE TRIGGER tax_update AFTER UPDATE OF calculate_tax ON payroll
REFERENCING NEW TABLE AS new
FOR EACH STATEMENT
BEGIN ATOMIC

  INSERT INTO cgtt.tax_cal
   (empno, firstnme, lastname, birthdate, bonus, comm, salarypa, tax_payable,
    tax_exempted, tax_to_be_paid, tax_proof, deptno)
   (SELECT e.empno, e.firstnme, e.lastname, e.birthdate, e.bonus, e.comm,
          p.salarypa, p.tax_payable, p.tax_exempted, p.tax_to_be_paid,
   p.tax_proof, p.deptno
     FROM employee AS e, payroll AS p
   WHERE e.empno = p.empid AND p.tax_exempted > 0 AND p.deptno =
     (SELECT DISTINCT deptno FROM payroll WHERE calculate_tax = 1));
 
  CALL final_tax_compute();
END@

6，開始計算
-- Call the procedure 'tax_pay' to calculate the tax_payable
-- by an employee depending on his income and update the
-- 'payroll' table
CALL initial_tax_compute()@

-- Update the 'payroll' table with the tax proof submitted by the employees.
-- For ease of demonstration, set tax exemption for each employee as 100,000
-- and proof submitted by the employee as 50,000.
-- After all the employees of a department submit their tax proofs, update
-- the calculate_tax column of payroll table to 1.
-- This update invokes the trigger 'tax_update'.
  
UPDATE payroll SET tax_proof = 50000 WHERE deptno = 'D11'@
UPDATE payroll SET calculate_tax = 1 WHERE deptno = 'D11'@

-- Execute RUNSTATS command to update the created temporary table 'cgtt.tax_cal'
-- to update statistics
RUNSTATS ON TABLE cgtt.tax_cal FOR indexes cgtt.IndexOnId@

CALL update()@

-- CALL the table function to print the IT sheet for the employees.
SELECT * FROM TABLE(printITSheet()) as ITSheet@

7，清理測試環境
-- Remove contents from the created temporary table.
TRUNCATE TABLE cgtt.tax_cal IMMEDIATE@

-- DROP the trigger, procedure, and function created by the sample. .
DROP TRIGGER tax_update@
DROP FUNCTION tax_compute@
DROP SPECIFIC PROCEDURE updater@
DROP SPECIFIC PROCEDURE initialTax@
DROP SPECIFIC PROCEDURE finalTax@
DROP FUNCTION printITSheet@

-- DROP all the tables, indexes, and views created by the sample.
DROP INDEX cgtt.IndexOnId@
DROP TABLE cgtt.tax_cal@
DROP TABLE payroll@
DROP VIEW cgtt.ViewOnCgtt@

-- DROP tablespaces created by the sample
DROP TABLESPACE TbspaceCgtt@
DROP TABLESPACE TbspacePayroll@
DROP BUFFERPOOL BufForSample@