mysql - My SQL 存储过程中的 ELSEIF 异常
问题描述
我在 My SQL 中编写了存储过程,如下所示:
DELIMITER //
CREATE PROCEDURE SP_Add_Expense
(
p_Expense_ID int /* = null */,
p_Project_ID int,
p_Category_ID int,
p_Sub_Category_ID int,
p_SupplierID int,
p_ExpenseDate datetime,
p_Notes text/* =null */,
p_Quantity int,
p_Price decimal(18,2),
p_CreatedBy int
)
BEGIN
IF((select count(*) from Expense where Category_ID = p_Category_ID AND Sub_Category_ID = p_Sub_Category_ID
AND SupplierID = p_SupplierID AND p_Expense_ID IS NULL) > 0)
THEN
THROW 50005, N'Expense already exists!!!', 1
ELSEIF ((select count(*) from Expense where Expense_ID = p_Expense_ID) > 0)
THEN
Update Expense set Category_ID = p_Category_ID, Sub_Category_ID = p_Sub_Category_ID, SupplierID = p_SupplierID, Quantity = p_Quantity, ExpenseDate = p_ExpenseDate,
Notes = p_Notes, Price = p_Price, ModifiedBy = p_CreatedBy, ModifiedDate = NOW(), Project_ID= p_Project_ID where Expense_ID = p_Expense_ID;
ELSE
INSERT INTO Expense (Project_ID, Category_ID, Sub_Category_ID, SupplierID, Quantity, Price, CreatedBy, CreatedDate, ExpenseDate, Notes)
VALUES (p_Project_ID, p_Category_ID, p_Sub_Category_ID, p_SupplierID, p_Quantity, p_Price, p_CreatedBy, NOW(), p_ExpenseDate, p_Notes);
END IF;
END IF;
END;
//
DELIMITER ;
它在 ELSEIF 处抛出异常。异常逻辑就像我得到的费用计数大于 0 时抛出异常,就像它已经存在一样。不明白是什么错误。
解决方案
您应该使用 SIGNAL,而不是 THROW。
推荐阅读
- python - 为什么我在 pygame 中的拍摄代码不起作用?
- javascript - 从表格中选择一个单元格并为其赋予唯一的类
- zurb-foundation - 使用 flex 网格(不是 xy 网格)时,基础对齐列
- c# - 将 XML 内容从 TextBox 导出到 XML 文件
- electron - 忽略电子 8 中的拼写 API
- c - 我正在努力让柜台工作,有多少个月的收入比去年同月的收入高
- android - 配置不同的构建类型以使用不同的环境值
- java - 抛出和捕获异常的影响大吗?
- javascript - 将数组对象转换为所需格式的对象数组
- react-native - 我如何在堆栈导航中导航屏幕