人大金仓金仓数据库KingbaseES多表join视图支持可更新介绍

数据猿 2023-08-15

806

金仓数据库KingbaseES多表join视图支持可更新介绍

关键字：

KingbaseES、键保留表、目标基表、可更新视图

1.什么是键保留表、目标基表、可更新视图？

键保留表（Key-Preserved Table）：如果一个表的每个键，同样是多表join后结果的键，我们称该表是键值保护的。（基表A的Join列在另一侧基表B中是主键、唯一键或被唯一索引，则认为基表A是键保留表）

键保留表的行在视图结果中最多出现一次，它的本质是当通过JOIN视图执行DML操作时，可以找到目标行在基表中的对应行，进而通过视图对基表执行DML操作。键保留表是理解多表JOIN视图是否支持DML的基础。主要用于判断join视图是否支持DML，当JOIN视图的基表中存在一个或多个键保留表时，我们称该JOIN视图支持DML。

目标基表：对于多表JOIN视图进行DML操作时，只能操作其中一个基表，所有当前Query层中的真正操作的目标表或视图仅有一个，我们称其为目标基表。

可更新视图，是指通过视图，来更新(Update)、插入(Insert)、删除(Delete)基础表中的数据。视图是一个虚拟表，对视图的更新，实质上是更新基础表。
用户、前台查询视图时，仅需要确定关联关系即可。但是在数据更新方面，多表信息同步编辑功能，一般用instead of触发器实现。所以实现多表join视图可更新，可以大大简化前台编程的工作量。

2.多表join可更新视图的分类

2.1 可更新视图

2.1.1 多表join视图支持Insert操作

1.创建多表Join视图，满足如下条件，支持通过视图进行Insert操作数据到基表。

1)至少存在一个基表是键保留表（为方面后面文档描述，标记该基表为表A）；

2)不存在如下字段

SET操作符(Union、Intersect、Except、Minus)
Distinct操作符
Aggregate(如count、avg、sum、max、min、first) 携带Group By、Connect By、Start
With、Having、LIMIT或OFFSET Window函数 set-returning函数

3)Where子句中不含有rownum

4)FROM子句后不能是SELECT CURRENT_USER这样的语句或者函数

5)视图定义无with check option

2.Insert操作的限制：

1)Insert操作只可一次修改一个基表；
2)Insert操作的列需是表A的列；
3)当创建join视图带了with check option, insert操作不被允许；
4)如果基表的列非空且没有默认值，insert时忽略该列，insert操作不被允许；

2.1.2 多表join视图支持Update操作

1.创建多表Join视图，满足如下条件，支持通过视图进行Update操作数据到基表。

1)至少存在一个基表是键保留表（为方面后面文档描述，标记该基表为表A）；
2)不存在如下字段

SET操作符(Union、Intersect、Except、Minus)
Distinct操作符
Aggregate(如count、avg、sum、max、min、first) 携带Group By、Connect By、Start
With、Having、LIMIT或OFFSET Window函数 set-returning函数

3)Where子句中不含有rownum
4)FROM子句后不能是SELECT CURRENT_USER这样的语句或者函数

2.Update操作的限制：
1)Update操作只可一次修改一个基表；
2)Update操作的列需是表A的列；
3)当创建join视图带了with check option, 不允许更新join的列和重复表的所有列；

2.1.3 多表join视图支持Delete操作

1. 创建多表Join视图，满足如下条件，支持通过视图进行Delete操作数据到基表。
1)至少存在一个基表是键保留表（为方面后面文档描述，标记该基表为表A）。
2)不存在如下字段

SET操作符(Union、Intersect、Except、Minus)
Distinct操作符
Aggregate(如count、avg、sum、max、min、first) 携带Group By、Connect By、Start
With、Having、LIMIT或OFFSET Window函数
set-returning函数

3)Where子句中不含有rownum
4)FROM子句后不能是SELECT CURRENT_USER这样的语句或者函数

2. Delete操作的限制：
1)Delete操作只可一次修改一个基表；
2)Delete操作只能从基表A中删除数据；
3)存在多个基表支持DML时，Delete只从第一个支持DML的基表中删除数据。

2.2 只读视图（with read only）

当创建视图with read only, 不允许通过视图进行DML操作。

2.3 不可更新视图

1）创建视图为只读视图
2）创建视图的基表不是键保留表
3）创建视图含有违规字段（具体参考可更新视图规则）

3.多表join可更新视图的用途

目前Kingbase只支持单表视图的DML操作，支持多表Join视图的DML后，可通过多表join视图的进行操作，来更新对应基表的数据，可以大大降低前台编程的工作量。

4.KES和oracle的可更新视图区别

基本完全兼容oracle已知多表join支持可更新视图所有功能。
Oracle中两张表使用natural join建立视图，表的顺序不一致会导致自然连接的列不可更新。
Kes的自然连接不存在此类问题。

5.多表join支持可更新视图示例

5.1 可更新视图

create table dep (                                         
       deptno INT not null,                                
       dname VARCHAR(100) not null,                        
       loc VARCHAR(100) not null,                          
       Primary key (deptno)                                
);                                                                                                     
insert into dep values(10, 'Accounting', 'New York');                                                  
insert into dep values(20, 'Research', 'Dallas');                                                        
insert into dep values(30, 'Sailing', 'Boston'); 

CREATE TABLE emp (
	empno number(4) PRIMARY KEY,
	ename varchar2(10) not null,
	job varchar2(10),
	mgr number(4),
	sal number(7,2),
	comm number(7,2),
	deptno int,
	FOREIGN KEY (deptno) REFERENCES dep(deptno)
);
INSERT INTO emp (empno, ename, deptno) VALUES (7782, 'clark', 10);
INSERT INTO emp (empno, ename, deptno) VALUES (7783, 'king', 10);
INSERT INTO emp (empno, ename, deptno) VALUES (7784, 'miller', 20);
INSERT INTO emp (empno, ename, deptno) VALUES (7785, 'smith', 20);
INSERT INTO emp (empno, ename, deptno) VALUES (7786, 'adams', 20);
INSERT INTO emp (empno, ename, deptno) VALUES (7787, 'ford', 30);
INSERT INTO emp (empno, ename, deptno) VALUES (7788, 'scott', 30);
INSERT INTO emp (empno, ename, deptno) VALUES (7789, 'jones', 30);
INSERT INTO emp (empno, ename, deptno) VALUES (7790, 'hello', 30);

CREATE TABLE emp_bk (
	empno number(4) PRIMARY KEY,
	ename varchar2(10),
	job varchar2(10),
	mgr number(4),
	sal number(7,2),
	comm number(7,2),
	deptno int,
	FOREIGN KEY (deptno) REFERENCES dep(deptno)
);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7782, 'clark', 10);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7783, 'king', 10);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7784, 'miller', 20);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7785, 'smith', 20);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7786, 'adams', 20);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7787, 'ford', 30);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7788, 'scott', 30);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7789, 'jones', 30);
INSERT INTO emp_bk (empno, ename, deptno) VALUES (7790, 'hello', 30);

CREATE OR REPLACE VIEW emp_dept AS
	SELECT emp.empno, emp.ename, emp.deptno, emp.sal, emp_bk.empno AS empno_bk, emp_bk.mgr, emp_bk.job
	FROM emp JOIN emp_bk ON (emp.empno = emp_bk.empno);

多表join视图支持insert操作：
INSERT INTO emp_dept (empno, ename, deptno, sal) VALUES (7791, 'wade',20,20000);
验证：
select * from emp;
![在这里插入图片描述](https://img-blog.csdnimg.cn/7bb72f5d83f04890abbe738a3aad6f30.png)


多表join视图支持update操作
update emp_dept set ename='king' where empno=7782;
验证：
select * from emp;
![在这里插入图片描述](https://img-blog.csdnimg.cn/98516c62ab21415fba92b9f54d7a928a.png)


多表join视图支持delete操作
delete from emp_dept where empno=7790;
验证：
![在这里插入图片描述](https://img-blog.csdnimg.cn/48989f57efff473cae8eb770983ae605.png)

5.2 只读视图

CREATE OR REPLACE VIEW emp_dept AS
SELECT empno, ename, emp.deptno, sal, dname, loc
FROM emp JOIN dep ON (emp.deptno = dep.deptno AND dep.deptno IN (10, 20, 30))
WITH READ ONLY ;

只读视图的insert操作：
INSERT INTO emp_dept (empno, ename, deptno, sal) VALUES (7791, 'wade',20,20000);
![在这里插入图片描述](https://img-blog.csdnimg.cn/d03560fcc5a04139a629f1717113563c.png)


只读视图的update操作：
update emp_dept set ename='king' where empno=7782;
![在这里插入图片描述](https://img-blog.csdnimg.cn/1521b233e2134fc0aa75a26779cb1761.png)


只读视图的delete操作：
delete from emp_dept where empno=7789;
![在这里插入图片描述](https://img-blog.csdnimg.cn/cea107c389504905998d2a9dea2b38ff.png)

5.3 不可更新视图

create table kes_1(a int,b varchar(10));
create table kes_2(a int,b varchar(10));
insert into kes_1 values(1,'kes');
insert into kes_1 values(2,'kes');
insert into kes_2 values(1,'kes');
insert into kes_2 values(2,'kes');

create or replace view emp_dept as 
	select kes_1.a,kes_1.b,kes_2.a as aa,kes_2.b as bb from kes_1 join kes_2 on (kes_1.a=kes_2.a);

insert操作：

insert into emp_dept(a,b) values(3,'king');
![在这里插入图片描述](https://img-blog.csdnimg.cn/df2c59e708ba4791b8cdd38ca1abf6b3.png)

Update操作：

update emp_dept set a = 3 where b = 'kes';
![在这里插入图片描述](https://img-blog.csdnimg.cn/1ec0151b97ae4ef39ae4c1e14248671c.png)

Delete操作：

delete from emp_dept where a =1;
![在这里插入图片描述](https://img-blog.csdnimg.cn/4d814e364bc146769b7bbbf049ea4705.png)

人大金仓 kingbasees

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

人大金仓 金仓数据库KingbaseES多表join视图支持可更新介绍