mysql实现oracle存储过程默认参数
2023-09-27 14:28:33 时间
我们都知道oracle存储过程支持为参数设置默认值,这样即使存储过程升级,原来的调用也可以不受影响。但是mysql不支持,mariadb也没有支持(截止10.4也是如此)。但是这一限制会导致升级麻烦重重。虽然如此,我们可以通过mysql 5.7/mariadb 10.2引入的json类型来变通实现。如下所示:
drop function number_stats; CREATE FUNCTION number_stats(in_numbers JSON) RETURNS INTEGER NOT DETERMINISTIC CONTAINS SQL COMMENT 'Accept an array of integers and their median' BEGIN DECLARE v_count INT UNSIGNED DEFAULT JSON_LENGTH(in_numbers); RETURN JSON_EXTRACT( in_numbers, CONCAT('$[', FLOOR(v_count / 2), ']') ); END;
mariadb> select number_stats('[1,2,3,4]'); +---------------------------+ | number_stats('[1,2,3,4]') | +---------------------------+ | 3 | +---------------------------+ 1 row in set mariadb> select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Monty" }','$.z'); +----------------------------------------------------------------+ | JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Monty" }','$.z') | +----------------------------------------------------------------+ | Monty | +----------------------------------------------------------------+ 1 row in set mariadb> select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Monty" }','$.x '); +----------------------------------------------------------------+ | JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Monty" }','$.x') | +----------------------------------------------------------------+ | NULL | +----------------------------------------------------------------+ 1 row in set mariadb> select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Monty" }','$.y '); +----------------------------------------------------------------+ | JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Monty" }','$.y') | +----------------------------------------------------------------+ | [0,1] | +----------------------------------------------------------------+ 1 row in set
https://federico-razzoli.com/variable-number-of-parameters-and-optional-parameters-in-mysql-mariadb-procedures
https://mariadb.com/kb/en/library/json_extract/
相关文章
- MySQL的四种变量类型
- datax同步oracle到mysql例子
- mysql和oracle建表语句以及数据类型的区别
- eclipse 连接mysql表生成实体类
- MySQL 中的反斜杠 ,真是太坑了!!
- 美团Java面试题,mysql使用教程5.5
- MySQL之实现Oracle中的rank()函数的功能
- 关于MySQL的tinyint(3)问题
- MySQL刷题篇(一)
- mysql之EXPLAIN优化分析
- 数据库笔试面试题库(Oracle、MySQL等)
- 专访搜狗DBA负责人王林平:为何从Oracle转向MySQL?
- 数据库迁移之从oracle 到 MySQL
- MySql类似Oracle的dual虚拟表
- mysql和oracle的区别(功能性能、选择、使用它们时的sql等对比)
- sqlserver、mysql、oracle各自的默认端口号
- Oracle SQL Developer 连接 MySQL
- 异构平台同步(mysql-->oracle)
- oracle转MySQL 踩过的坑 1.1(nvl()对应ifnull(),decode()对应if)