我正在尝试在MySQL中创建一个存储过程,该过程将从列中获取最大数字,添加一个并使用它来创建下一个条目。
DROP PROCEDURE IF EXISTS ops_software.create_invoice;
DELIMITER //
CREATE PROCEDURE ops_software.create_invoice(IN company VARCHAR(50))
BEGIN
SELECT @old_invoice_number := MAX(invoice_number)
FROM invoices
WHERE invoices.company = company;
SET @new_invoice_number := @old_invoice_number + 1
INSERT INTO invoices (company, invoice_number)
VALUES (company, @new_invoice_number)
END//
DELIMITER ;
CALL ops_software.create_invoice('Super Company')
我不想使用自动增量功能,因为有几个不同的公司名称,每个名称都有自己的发票编号
获取值有效,但我无法向其添加一个值或插入它以创建新条目
谢谢
CREATE PROCEDURE ops_software.create_invoice(IN in_company VARCHAR(50))
INSERT INTO invoices (company, invoice_number)
SELECT in_company, MAX(invoices.invoice_number) + 1
FROM invoices
WHERE invoices.company = in_company;
DELIMITER
和BEGIN-END
不需要。
PS. 可能会在并发环境中产生重复项。