Oracle兼容-语法-CREATE FORCE VIEW

1. 语法

1. CREATE [ OR REPLACE ] FORCE VIEW name [ ( column_name [, ...] ) ]
2. AS query

2. 定义和用法

正常情况下，如果基表不存在，创建视图就会失败。但是可以使用使用本功能完成强制创建视图（前提：创建视图的语句无语法错误）。

3. Oracle兼容说明

  1. 不支持 PREPARE stmt FROM 和 EXCUTE IMMEDIATE 两种场景。
  2. 强制创建视图后，不支持 ALTER VIEW new_view compile 的语法，这点与Oracle不同。
  3. 错误提示结果可能与Oracle存在不同。如果 query（查询语句）不合理，当基表创建时报错为 ER_VIEW_INVALID，也存在创建基表 referenc view 的其它错误。
  4. 如果基表不存在，强制创建视图时的约束检测只会进行简单的列同名、表名重复的约束检测，这点与Oracle不同。
  5. 在Oracle中的强制创建视图时，在语法解析过程中，会对SQL语句进行规则检查，有些语句会直接报错，而GreatSQL则需要到prepare解析取值时才报错。当基表不存在时，Oracle会有自己的规则检测报错信息。

4. 示例

4.1 正常创建视图

1. greatsql> CREATE TABLE t1 (a INT, b INT);
2. greatsql> INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
3. greatsql> CREATE FORCE VIEW v1 (c,d) AS SELECT a,b FROM t1 WHERE a<=2;
4. greatsql> SELECT * FROM t1;
5. +------+------+
6. | a    | b    |
7. +------+------+
8. |    1 |    1 |
9. |    2 |    2 |
10. |    3 |    3 |
11. +------+------+

13. greatsql> SELECT * FROM v1;
14. +------+------+
15. | c    | d    |
16. +------+------+
17. |    1 |    1 |
18. |    2 |    2 |
19. +------+------+

4.2 创建视图时基表不存在，在当基表创建后，视图可正常使用

1. -- 强制创建基表不存在的视图
2. greatsql> CREATE FORCE VIEW v1 (c,d) AS SELECT a,b FROM t1 WHERE a<=2;
3. Query OK, 0 rows affected, 2 warnings (0.00 sec)

5. greatsql> SHOW WARNINGS;
6. +---------+------+---------------------------------------+
7. | Level   | Code | Message                               |
8. +---------+------+---------------------------------------+
9. | Error   | 1146 | Table 'greatsql.t1' doesn't exist     |
10. | Warning | 7567 | View created with compilation errors. |
11. +---------+------+---------------------------------------+

13. greatsql> SELECT * FROM t1;
14. ERROR 1146 (42S02): Table 'greatsql.t1' doesn't exist

16. greatsql> SELECT * FROM v1;
17. ERROR 1356 (HY000): View 'greatsql.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

19. -- 即便基表不存在，也能查看视图定义
20. greatsql> SHOW CREATE VIEW v1\G
21. *************************** 1. row ***************************
22. View: v1
23. Create View: CREATE FORCE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` (`c`,`d`) AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` <= 2)
24. character_set_client: utf8mb4
25. collation_connection: utf8mb4_0900_ai_ci

27. -- 创建基表，写入数据
28. greatsql> CREATE TABLE t1 (a INT, b INT);
29. greatsql> INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
30. greatsql> CREATE FORCE VIEW v1 (c,d) AS SELECT a,b FROM t1 WHERE a<=2;
31. greatsql> SELECT * FROM t1;
32. +------+------+
33. | a    | b    |
34. +------+------+
35. |    1 |    1 |
36. |    2 |    2 |
37. |    3 |    3 |
38. +------+------+

40. greatsql> SELECT * FROM v1;
41. +------+------+
42. | c    | d    |
43. +------+------+
44. |    1 |    1 |
45. |    2 |    2 |
46. +------+------+

4.3 两种不支持的场景

1. greatsql> EXECUTE IMMEDIATE 'CREATE OR REPLACE FORCE VIEW v1 AS SELECT * FROM t1';
2. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

4. greatsql> PREPARE stmt FROM 'CREATE OR REPLACE FORCE VIEW v1 AS SELECT * FROM t1';
5. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

4.4 约束检测处理失败，不能强制创建视图

1. greatsql> CREATE FORCE VIEW v1 AS SELECT * FROM v1;
2. ERROR 1462 (HY000): `greatsql`.`v1` contains view recursion

4. greatsql> CREATE FORCE VIEW v1 AS SELECT a,a FROM t1;
5. ERROR 1060 (42S21): Duplicate column name 'a'

4.5 部分错误信息和Oracle不一致

1. -- 1. 列重名
2. -- GreatSQL
3. greatsql> CREATE OR REPLACE FORCE VIEW v1 AS SELECT a.a, b.a FROM t1 a JOIN t1 b;
4. ERROR 1060 (42S21): Duplicate column name 'a'

6. -- Oracle
7. SQL> CREATE OR REPLACE FORCE VIEW v1 AS SELECT a.a, b.a FROM t1 a JOIN t1 b;
8. CREATE OR REPLACE FORCE VIEW v1 AS SELECT a.a, b.a FROM t1 a JOIN t1 b
9. *
10. ERROR at line 1:
11. ORA-00905: missing keyword

13. -- 2. 创建视图时嵌套
14. -- GreatSQL
15. greatsql> CREATE OR REPLACE FORCE VIEW v2 AS SELECT (SELECT a FROM v2) FROM DUAL;
16. ERROR 1462 (HY000): `greatsql`.`v2` contains view recursion

18. greatsql> CREATE OR REPLACE FORCE VIEW v2 AS SELECT * FROM (SELECT a FROM v2) ;
19. ERROR 1462 (HY000): `greatsql`.`v2` contains view recursion

21. -- Oracle
22. SQL> CREATE OR REPLACE FORCE VIEW v2 AS SELECT * FROM (SELECT a FROM v2) ;

24. Warning: View created with compilation errors.

26. SQL> CREATE OR REPLACE FORCE VIEW v2 AS SELECT (SELECT a FROM v2) FROM DUAL;

28. Warning: View created with compilation errors.

30. SQL> CREATE OR REPLACE FORCE VIEW v2 AS SELECT * FROM (SELECT a FROM v2) ;

32. Warning: View created with compilation errors.

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx