MySQL存储过程语法错误

Question

我的mysql版本是5.5，当运行这个mysql存储过程时，它返回这个错误。

代码:

CREATE DEFINER = CURRENT_USER PROCEDURE `getReceiptNumber`(IN bankcode 
varchar,IN receipttype varchar,OUT seq int)
BEGIN
    update receipt_number 
    set seq_number = (seq_number + 1) 
    where bank_code = bankcode and receipt_type = receipttype;
    select seq_number from receipt_number where bank_code = bankcode and 
receipt_type = receipttype into seq; 
END;

表结构:

CREATE TABLE `receipt_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bank_code` varchar(255) DEFAULT NULL,
  `cur_date` varchar(255) DEFAULT NULL,
  `cur_year` int(11) DEFAULT NULL,
  `receipt_type` varchar(255) DEFAULT NULL,
  `seq_number` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

错误:

[Err] 1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'INreceipttype varchar,OUT seq int) 附近使用的正确语法
开始
更新收据号
在第 1 行设置 seq_nu'

Answer 1

您没有提到参数 varchar 的大小，

尝试下面的代码。

DELIMITER $$
CREATE DEFINER = CURRENT_USER PROCEDURE getReceiptNumber(IN bankcode VARCHAR(10),IN receipttype VARCHAR(10),OUT seq INT)
BEGIN
    UPDATE receipt_number 
    SET seq_number = (seq_number + 1) 
    WHERE bank_code = bankcode AND receipt_type = receipttype;
    SELECT seq_number FROM receipt_number WHERE bank_code = bankcode AND 
receipt_type = receipttype INTO seq; 
END$$

DELIMITER ;