Oracle兼容-语法-WITH FUNCTION

1. 语法

1. WITH FUNCTION
2. <func_name> {<function...>}
3. SELECT(query_block)

2. 定义和用法

通过 WITH FUNCTION 子句，可以把很多原本需要存储过程来实现的复杂逻辑用一句SQL来进行表达，使用起来更方便。

相关使用说明如下：

  1. 定义的函数的作用域为所在的查询表达式内。
  2. 定义的存储函数对象不会存储到系统表中；
  3. 定义的存储函数比模式对象的存储函数拥有更高的优先级；
  4. 定义的存储函数比本地函数优先级低；
  5. 定义函数时不需要 CREATE FUNCTION 权限；
  6. 无需要求 sql_mode = ORACLE，只要创建函数时的语法不报错即可；
  7. 不支持 PREPARE STMT；
  8. 函数名不支持 schema_name.func_name 这种格式，只能在当前Schema中创建函数；
  9. 只支持SELECT子句，不支持其他子句，例如 DELET/UPDATE/MERGE 等。

备注：9点的功能是存储过程中，需清楚地看到函数定义，避免ddl操作开销的需求，暂时未支持，需收集具体使用场景后再进行适配开发

3. 示例

1. -- 先切换到ORACLE模式
2. greatsql> SET sql_mode = ORACLE;

4. greatsql> DELIMITER //

6. -- 1. 创建函数
7. greatsql> WITH
8. FUNCTION f1(c INT) RETURN INT
9. AS
10. BEGIN
11. RETURN c * 10;
12. END;
13. SELECT f1(3306) FROM DUAL; //
14. +----------+
15. | f1(3306) |
16. +----------+
17. |    33060 |
18. +----------+
19. 1 row in set (0.00 sec)

21. -- 2. 创建同名函数
22. greatsql> SELECT LENGTH('GreatSQL') FROM DUAL //
23. +--------------------+
24. | LENGTH('GreatSQL') |
25. +--------------------+
26. |                  8 |
27. +--------------------+
28. 1 row in set (0.00 sec)

30. -- 可以创建重名函数，但本地函数优先，可以看到结果并非返回6而是8
31. greatsql> WITH
32. FUNCTION LENGTH(c VARCHAR2(50)) RETURN INT
33. AS
34. BEGIN
35. RETURN 6;
36. END;
37. SELECT LENGTH('GreatSQL'), INSTR('Hello GreatSQL', 'G') FROM DUAL; //
38. +--------------------+------------------------------+
39. | LENGTH('GreatSQL') | INSTR('Hello GreatSQL', 'G') |
40. +--------------------+------------------------------+
41. |                  8 |                            7 |
42. +--------------------+------------------------------+
43. 1 row in set, 1 warning (0.00 sec)

45. -- 提示重名
46. greatsql> SHOW WARNINGS; //
47. +-------+------+---------------------------------------------------------------+
48. | Level | Code | Message                                                       |
49. +-------+------+---------------------------------------------------------------+
50. | Note  | 1585 | This function 'LENGTH' has the same name as a native function |
51. +-------+------+---------------------------------------------------------------+
52. 1 row in set (0.00 sec)

54. -- 3. 创建不同名函数
55. -- 可以返回函数中的固定值16
56. greatsql> WITH
57. FUNCTION LENGTH_tmp(c VARCHAR2(50)) RETURN INT
58. AS
59. BEGIN
60. RETURN 16;
61. END;
62. SELECT LENGTH_tmp('GreatSQL') FROM DUAL; //

64. -- 4. 切换到DEFAULT模式，在DEFAULT模式下创建函数
65. greatsql> SET sql_mode = DEFAULT; //

67. greatsql> WITH FUNCTION f2() RETURNS INT
68. BEGIN
69. DECLARE c, s INT;
70. DROP TEMPORARY TABLE IF EXISTS t1;
71. CREATE TEMPORARY TABLE t1(id INT);
72. INSERT INTO t1 VALUES (1), (2), (3);

74. SET c:= (SELECT COUNT(*) FROM t1);
75. SET s:= (SELECT SUM(id) FROM t1);

77. RETURN c * s;
78. END;
79. SELECT f2() FROM DUAL; //
80. +------+
81. | f2() |
82. +------+
83. |   18 |
84. +------+
85. 1 row in set (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx