DB2 Old-New-Final-Table中间结果表

DB2底层通过维护事物表，来对表进行添加，更新，和删除操作，这些事物表有：

NEW TABLE，OLD TABLE，FINAL TABLE；他们也叫中间结果表。

当进行insert或update的时候，new table包含着将要添加的数据行或进行更新的数据值；

当进行update或delete的时候，old table包含着将要被更新的值或将要被删除的值。

 

1、Old Table

“存储”更新或者将要删除的数据，类似于触发器中refferencing old as olddata存储的数据，

Old Table仅限于应用在update和delete操作中。

create table empk(

empno varchar(6),

ename varchar(15),

salary decimal(9,2))

insert into empk

select empno,lastname,salary

from employee

order by salary desc

fetch first 5 rows only

db2 => select * from empk

 

EMPNO ENAME SALARY

------ --------------- -----------

000010 HAAS 152750.00

000030 KWAN 98250.00

000070 PULASKI 96170.00

000020 THOMPSON 94250.00

000090 HENDERSON 89750.00

 

下面更新empno为000090的员工薪水，更新的同时，我们想看下他的旧工资。

db2 => select * from old table(update empk set salary=50000 where empno='000090')

 

EMPNO ENAME SALARY

------ --------------- -----------

000090 HENDERSON 89750.00

 

下面是更新之后的数据：

db2 => select * from empk where empno='000090'

 

EMPNO ENAME SALARY

------ --------------- -----------

000090 HENDERSON 50000.00

 

当我们删除数据的时候，查看下被删除的数据：

db2 => select * from old table(delete from empk where salary<80000)

 

EMPNO ENAME SALARY

------ --------------- -----------

000090 HENDERSON 50000.00

 

2、New Table

new table存储新的数据，类似于触发器中referrencing new as newdata存储的新数据值，

仅限于应用在update和insert语句中。

在插入数据的同时我们想看下，新增加的值，但这只能查看新数据，不能像trigger那样对数据进行加工。

db2 => select * from new table(insert into empk values('000050','yeeXun',80000))

 

EMPNO ENAME SALARY

------ --------------- -----------

000050 yeeXun 80000.00

下面这个例子从employee表中取工资对低的3位员工信息，添加到empk表中：

select * from

new table(insert into empk

select empno,lastname,salary

from employee

order by salary asc

fetch first 3 rows only)

 

EMPNO ENAME SALARY

------ --------------- -----------

200340 ALONZO 31840.00

000290 PARKER 35340.00

200330 WONG 35370.00

此时的表中数据为：

db2 => select * from empk

 

EMPNO ENAME SALARY

------ --------------- -----------

000010 HAAS 152750.00

000030 KWAN 98250.00

000070 PULASKI 96170.00

000020 THOMPSON 94250.00

000050 yeeXun 80000.00

200340 ALONZO 31840.00

000290 PARKER 35340.00

200330 WONG 35370.00

下面给工资低于800000的员工涨工资，并查看涨工资后的工资：

db2 => select * from new table(update empk set salary = salary * 1.2 where salary < 80000)

 

EMPNO ENAME SALARY

------ --------------- -----------

200340 ALONZO 38208.00

000290 PARKER 42408.00

200330 WONG 42444.00

 

3、Inlcude

如果我们在更新一条数据的同时，想同时查看旧数据(更新之前)和新数据(更新之后)，

根据上面讲到的new table和old table，我们可以使用两个语句查看，如：

select salary from old table(update empk set salary=salary*1.1 where empno='200330')

union all

select salary from new table(update empk set salary=salary*1.1 where empno='200330')

然而，当执行此语句的时候，我们会得到如下的错误信息：

SQL20165N 在指定 SQL 数据更改语句的上下文中，不允许 FROM 子句中的 SQL数据更改语句。SQLSTATE=428FL

 

include关键词可以解决此问题，在更新数据的时候，可以同时把新旧数据查询出来，下面是实例：

 

select empno,salary as new_salary,old_salary

from new table(

update empk include(old_salary decimal(9,2))

set salary=salary*1.1,

old_salary=salary

where empno='200330')

 

EMPNO NEW_SALARY OLD_SALARY

------ ----------- -----------

200330 46688.40 42444.00

1 条记录已选择。

 

表中的数据也是如此：

db2 => select * from empk where empno='200330'

EMPNO ENAME SALARY

------ --------------- -----------

200330 WONG 46688.40

1 条记录已选择。

 

4、Final Table

final table“存储”数据修改操作、引用完整性操作和触发器操作之后的数据，

它可以用来检查在执行insert，update或者delete的时候，

被操作的数据是否存在触发器或者引用约束，此时final table关键词会导致这些操作停止。

如下面这个例子中，我们创建一个触发器，给新增员工增加10%的工资。

create trigger trig_empk

after insert on empk

referencing new as n

for each row

mode db2sql

update empk set salary=n.salary*1.1

where empno=n.empno

 

在执行下面语句的时候，我们就知道了，final table可以有效的避免这种隐藏的数据更改的操作。

select * from final table(insert into empk(empno,ename,salary) values('120821','ChenLinBo',50000))

★SQL0989N AFTER 触发器 "TRIG_EMPK" 尝试了修改表 "EMPK" 中由 FROM 子句内的 SQL数据更改语句修改的行。

SQLSTATE=560C3

 

db2 => select count(*) from empk where empno='120821'

1

-----------

0

1 条记录已选择。

 

添加数据时候，使用new table查看新的值，这个值是未经过任何触发器或者约束修改过的。

db2 => select * from new table(insert into empk values('120821','ChenLinBo',50000))

EMPNO ENAME SALARY

------ --------------- -----------

120821 ChenLinBo 50000.00 --★

1 条记录已选择。

 

db2 => select * from empk where empno='120821'

EMPNO ENAME SALARY

------ --------------- -----------

120821 ChenLinBo 55000.00 --★

1 条记录已选择。

 

5、Final Table和New Table的区别★

他们都同样的返回update或insert的中间结果数据，但是Final Table能确保对目标表的update和insert操作，

没有触发器或者应用完整性约束的后续数据修改，如上面这个例子。

 

来源 http://blog.csdn.net/bobo12082119/article/details/8775600