Oracle兼容-存储过程-存储过程/存储函数默认参数值

1. 语法

1. [ IN | OUT | INOUT ]          #可选输入输出标记
2. var_name                      #参数名
3. var_type                      #参数类型
4. [ { := | DEFAULT } optval ]     #默认值

2. 定义和用法

在 ORACLE 模式下，GreatSQL存储过程/存储函数的参数定义时，允许添加类似 n INT := 1 这样的的标记用法。如果调用时该参数被指定了其他值，则这个表达式会被忽略，否则该参数将使用该表达式进行初始化。

该功能使得参数在调用的时候允许被省略，提升了易用性，更方便实用。

2.1 参数默认值使用要点

• 参数默认值仅针对输入参数有效，即标记为 IN 的参数。
• 参数值 optval 与参数类型 var_type 需要能兼容。
• 参数值 optval 所占的空间要小于参数长度定义。
• 参数值 optval 仅允许包含时间函数, 如：NOW()/SYSDATE()/CURRENT_TIMESTAMP() 等，不支持其他类型的函数。
• 参数值 optval 不允许包含对数据列的引用、存储过程调用、伪列等。
• 参数值 optval 必须为字面常量(LITERAL)，不支持表达式。例如，支持参数值 -1，但不支持 0-1 这种表达式，不支持逻辑运算和括号 ( ) 等。
• 允许任意位置参数有默认值。

2.2 支持的数据类型列表

参数默认值特性支持的数据类型详见下表：

几个注意事项：

• 布置及 SET、ENUM、JSON 等几个数据类型。
• BOOL 类型取值范围与 TINYINT 相同。
• 字符串相关类型如果使用默认值，则要求 客户端(连接)、数据库、数据列的字符集要一致，否则可能无法得到预期值。
• 字符串相关类型不允许使用长度超过定义的默认值（会报错，而不是自动截断处理）。
• 一些特殊默认值的处理与建表DDL中声明的数据里默认值保持一致。
• 参数为 VARBINARY 类型时，不会检查参数的字符集。
• 建表DDL声明中的数据列类型为 BLOB 和 TEXT 时，其默认值只支持设置为 NULL，不能设置为 ‘’ 或其他字符串。

2.3 支持的函数列表

参数默认值特性支持的函数详见下表：

上表中没有列出的函数都不支持，如 ABS()、SUM()、DECODE()` 等。

3.4 错误说明

当参数取值发生错误时，主要有以下两种错误提示：

• Parameter '%s' has invalid value

• 错误原因：在存储过程/存储函数的执行中，发现默认参数值非法。

• 建议动作：修改参数值。

• Parameter '%s' is invalid for %s

• 错误原因：参数非法。

• 建议动作：修改非法内容，如：参数名称、重复参数、不支持的类型、不支持的字符等。

3. Oracle兼容说明

GreatSQL存储过程/存储函数参数默认值用法和Oracle区别主要有：

• Oracle中支持对字符参数进行转码, GreatSQL不支持该动作。
• Oralce支持常量表达式类型默认参数，GreatSQL不支持该用法。

4. 示例

• 1. 示例1

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE p1(a BIT DEFAULT NULL) AS
5. BEGIN
6. SELECT a;
7. END; //

9. greatsql> CREATE FUNCTION f1(a BIT DEFAULT NULL) RETURN BIT DETERMINISTIC AS
10. BEGIN
11. RETURN a;
12. END; //

14. greatsql> CALL p1(NULL) //
15. +------+
16. | a    |
17. +------+
18. | NULL |
19. +------+
20. 1 row in set (0.00 sec)

22. Query OK, 0 rows affected (0.00 sec)

24. greatsql> CALL p1() //
25. +------+
26. | a    |
27. +------+
28. | NULL |
29. +------+
30. 1 row in set (0.00 sec)

32. Query OK, 0 rows affected (0.00 sec)

34. greatsql> SELECT f1(NULL) //
35. +----------+
36. | f1(NULL) |
37. +----------+
38. | NULL     |
39. +----------+
40. 1 row in set (0.00 sec)

42. greatsql> SELECT f1() //
43. +------+
44. | f1() |
45. +------+
46. | NULL |
47. +------+
48. 1 row in set (0.00 sec)

• 1. 示例2

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE p1(a DATE DEFAULT NOW()) AS
5. BEGIN
6. SELECT a;
7. END; //

9. greatsql> CREATE OR REPLACE FUNCTION f1(a DATE DEFAULT NOW()) RETURN DATE DETERMINISTIC AS
10. BEGIN
11. RETURN a;
12. END; //

14. greatsql> CALL p1(NOW()) //
15. +------------+
16. | a          |
17. +------------+
18. | 2023-12-04 |
19. +------------+
20. 1 row in set (0.00 sec)

22. Query OK, 0 rows affected, 1 warning (0.00 sec)

24. greatsql> CALL p1() //
25. +------------+
26. | a          |
27. +------------+
28. | 2023-12-04 |
29. +------------+
30. 1 row in set (0.00 sec)

32. Query OK, 0 rows affected, 1 warning (0.00 sec)

34. greatsql> SELECT f1(NOW()) //
35. +------------+
36. | f1(NOW())  |
37. +------------+
38. | 2023-12-04 |
39. +------------+
40. 1 row in set, 1 warning (0.00 sec)

42. greatsql> SELECT f1() //
43. +------------+
44. | f1()       |
45. +------------+
46. | 2023-12-04 |
47. +------------+
48. 1 row in set, 1 warning (0.00 sec)

• 1. 示例3

1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE p2(a DATE DEFAULT NOW(), b DATE DEFAULT NOW()) AS
5. BEGIN
6. SELECT a;
7. SELECT b;
8. END; //

10. greatsql> CALL p1(a=>'2023-10-23') //
11. +------------+
12. | a          |
13. +------------+
14. | 2023-10-23 |
15. +------------+
16. 1 row in set (0.00 sec)

18. Query OK, 0 rows affected (0.00 sec)

问题反馈

• 问题反馈 gitee (opens new window)

联系我们

扫码关注微信公众号