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