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