DB2_自治事務

目的：
測試DB2自治事務，本指令碼摘錄自DB2安裝目錄admin_scripts/autonomous_transaction.db2。
版本：Windows DB2 Express-C V9.7

使用者要求：
作業系統使用者mh有DBADM,SECADM許可權,mh1234;作業系統使用者bob,密碼bob1234;作業系統使用者pat,密碼pat1234。

說明：
1,為了在CLP互動模式下支援多行輸入，可以使用"db2cmd db2 -td@"進入互動模式。
2,由於Express-C V9.7版本不支援DBMS_OUTPUT，呼叫時報錯，沒有測試report_generate()過程：
db2 => set serveroutput on;
SQL8004N  找不到所請求功能的有效許可證金鑰。

操作步驟：
1，設定測試環境
2，測試
3，清理測試環境

1，設定測試環境
CONNECT TO sample user mh using mh1234@

CREATE TABLE temp_employee(
empno CHAR(6),
empname VARCHAR(10),
lastname VARCHAR(10),
workdept CHAR(3),
bonus DECIMAL(9,2),
hiredate DATE)@

CREATE TABLE temp_payroll(
empno CHAR(6),
salary DECIMAL(9,2))@

CREATE TABLE event_log(
user_name VARCHAR(10),
event VARCHAR(65),
event_time TIME,
event_date DATE)@

-- Insert data into temp_employee table from employee table
INSERT INTO temp_employee VALUES
('000010', 'CHRISTINE', 'HAAS', 'A00', 1000.00, '01/01/1995')@
INSERT INTO temp_employee VALUES
('000020', 'MICHAEL', 'THOMPSON', 'B01', 800.00, '10/10/2003')@
INSERT INTO temp_employee VALUES
('000030', 'SALLY', 'KWAN', 'C01', 800.00, '04/05/2005')@
INSERT INTO temp_employee VALUES
('000050', 'JACK', 'GEYER', 'E01', 800.00, '08/17/1979')@
INSERT INTO temp_employee VALUES
('000060', 'IRVING', 'STERN', 'D11', 500.00, '09/14/2003')@
INSERT INTO temp_employee VALUES
('000070', 'EVA', 'PULASKI', 'D21', 700.00, '09/30/2005')@
INSERT INTO temp_employee VALUES
('000090', 'EILEEN', 'HENDERSON', 'E11', 600.00, '08/15/2000')@
INSERT INTO temp_employee VALUES
('000100', 'THEODORE', 'SPENSER', 'E21', 500.00, '06/19/2000')@
INSERT INTO temp_employee VALUES
('000110', 'VINCENZO', 'LUCCHESSI', 'A00', 900.00, '05/16/1988')@

-- Insert data into temp_payroll table
INSERT INTO temp_payroll VALUES
('000010', 10000.500)@
INSERT INTO temp_payroll VALUES
('000020', 12000.430)@
INSERT INTO temp_payroll VALUES
('000030', 11600.600)@
INSERT INTO temp_payroll VALUES
('000050', 10560.450)@
INSERT INTO temp_payroll VALUES
('000060', 13000.500)@
INSERT INTO temp_payroll VALUES
('000070', 11640.600)@
INSERT INTO temp_payroll VALUES
('000090', 12560.450)@
INSERT INTO temp_payroll VALUES
('000100', 13894.556)@
commit@

-- Fetch data from temp_employee
SELECT * FROM temp_employee@

-- Fetch data from temp_payroll
SELECT * FROM temp_payroll@

CREATE PROCEDURE event_log(IN event CHAR(1))
   AUTONOMOUS
   LANGUAGE SQL
   BEGIN
     CASE event
       WHEN 'U'
       THEN INSERT INTO event_log
           VALUES(SESSION_USER,
           'CALLING salary_update PROCEDURE TO UPDATE THE SALARY',
           CURRENT TIME,
           CURRENT DATE);

       WHEN 'S'
       THEN INSERT INTO event_log
           VALUES(SESSION_USER,
           'CALLING report_generate PROCEDURE TO VIEW EMPLOYEES SALARY',
           CURRENT TIME,
           CURRENT DATE);

     END CASE;
   END@

CREATE PROCEDURE update_salary
 (IN exp INTEGER,
  IN workdpt CHAR(3),
  IN new_salary INTEGER)
   LANGUAGE SQL
   BEGIN
    CALL event_log('U');
     UPDATE temp_payroll
        SET salary = salary + new_salary
      WHERE empno =
        (SELECT empno FROM temp_employee WHERE workdept = workdpt
         AND (CURRENT DATE - hiredate) > exp);
 
      IF (USER <> 'BOB')
          THEN
          ROLLBACK;
      END IF;
   END@

-- Grant execute privilege to user BOB on procedure update_salary
GRANT EXECUTE ON PROCEDURE update_salary TO USER bob@

-- Grant execute privilege to user PAT on procedure update_salary
GRANT EXECUTE ON PROCEDURE update_salary TO USER pat@

2，測試：
2.1初始資料
CONNECT RESET@
CONNECT TO sample USER mh USING mh1234@
 
SELECT salary FROM temp_payroll
 WHERE empno = (SELECT empno FROM temp_employee
   WHERE workdept = 'D11'
    AND (CURRENT DATE - hiredate) > 5)@
SALARY
-----------
   13000.50

  1 條記錄已選擇。

2.2 BOB呼叫更新操作
CONNECT RESET@
CONNECT TO sample user bob using bob1234@
CALL mh.update_salary(5, 'D11', 2000)@

CONNECT RESET@
CONNECT TO sample user mh using mh1234@

SELECT salary FROM temp_payroll
 WHERE empno = (SELECT empno FROM temp_employee
   WHERE workdept = 'D11'
    AND (CURRENT DATE - hiredate) > 5)@
SALARY
-----------
   15000.50

  1 條記錄已選擇。

2.3 PAT呼叫更新操作
CONNECT RESET@
CONNECT TO sample user pat using pat1234@

CALL mh.update_salary(5, 'D11', 2000)@

CONNECT RESET@
CONNECT TO sample user mh using mh1234@

SELECT salary FROM temp_payroll
 WHERE empno = (SELECT empno FROM temp_employee
   WHERE workdept = 'D11'
    AND (CURRENT DATE - hiredate) > 5)@
SALARY
-----------
   15000.50

  1 條記錄已選擇。

select * from event_log@
USER_NAME  EVENT                                                 EVENT_TIME EVENT_DATE
---------- ----------------------------------------------------- ---------- ----------
BOB        CALLING salary_update PROCEDURE TO UPDATE THE SALARY  09:48:05   2011-07-13
PAT        CALLING salary_update PROCEDURE TO UPDATE THE SALARY  09:52:41   2011-07-13

  2 條記錄已選擇。

3，清理測試環境
CONNECT RESET@
CONNECT TO sample USER mh USING mh1234@

DROP TABLE temp_employee@
DROP TABLE temp_payroll@
DROP TABLE event_log@
DROP PROCEDURE update_salary@
DROP PROCEDURE event_log@

CONNECT RESET@