-- ----------------------------
-- Function structure for `currval`
-- ----------------------------
DROP FUNCTION IF EXISTS `currval`;
DELIMITER ;;
CREATE FUNCTION `currval`(seq_name varchar(50)) RETURNS bigint(20)
BEGIN
DECLARE value bigint;
SET value = 0;
SELECT
currentvalue INTO value
FROM quartz_sequence
WHERE name = seq_name;
RETURN value;
END
;;
DELIMITER ;
-- ----------------------------
-- Function structure for `nextval`
-- ----------------------------
DROP FUNCTION IF EXISTS `nextval`;
DELIMITER ;;
CREATE FUNCTION `nextval`(seq_name varchar(50)) RETURNS bigint(20)
BEGIN
UPDATE quartz_sequence
SET currentvalue = currentvalue + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
;;
DELIMITER ;
SHOW FUNCTION STATUS;
SHOW CREATE FUNCTION nextval;
SHOW CREATE FUNCTION currval;
DESC o_sequence;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| name | varchar(50) | NO | PRI | NULL | |
| currentvalue | bigint(20) | NO | | NULL | |
| increment | int(11) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
CREATE DEFINER=`root`@`localhost` FUNCTION `nextval`(seq_name varchar(50)) RETURNS bigint(20)
BEGIN
UPDATE o_sequence
SET currentvalue = currentvalue + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
CREATE DEFINER=`root`@`localhost` FUNCTION `currval`(seq_name varchar(50)) RETURNS bigint(20)
BEGIN
DECLARE value bigint;
SET value = 0;
SELECT
currentvalue INTO value
FROM o_sequence
WHERE name = seq_name;
RETURN value;
END
mybatis中调用存储过程
<select id="getOsid" resultClass="string">
select nextval('osid') as osid
</select>
存储过程:提供给QA调用修改数据库数据
<select id="updateData" parameterClass="string"
resultClass="int">
call freesql(#date#)
</select>
CREATE <a target=_blank href="mailto:DEFINER=`root`@`localhost">DEFINER=`root`@`localhost</a>` PROCEDURE `freesql`(sqltext varchar(4000))
BEGIN
SET @sql = sqltext;
PREPARE stmt FROM @sql;
EXECUTE stmt;
END
上面其实是实现了一个函数。