mysql - 如何让MySQL程序检测错误

Question

我正在尝试编写一个输入发票号的存储过程。和日期，并尝试将日期更新到表行。如果成功它应该返回 1，如果失败则返回 0。我的代码如下。我得到的是它总是返回 1，但仅在日期有效时更新表。如果更新不成功，我希望它返回 0。感谢任何帮助，谢谢。

CREATE DEFINER=`root`@`localhost` FUNCTION `invoice_paid_date`(`inv_no` INT,    
     `date_paid` DATE)
  RETURNS SMALLINT
  LANGUAGE SQL
  DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT 'attempt to update paid_date in table invoice'
BEGIN
   declare exit handler for sqlwarning, sqlexception  return 0;
   update invoice set paid_date = date_paid where invoice_no = inv_no;
return 1;
END

Answer 1

试试这个:

CREATE DEFINER=`root`@`localhost` FUNCTION `invoice_paid_date`(`inv_no` INT,    
         `date_paid` DATE)
      RETURNS SMALLINT
      LANGUAGE SQL
      DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT 'attempt to update paid_date in table invoice'
    BEGIN
       DECLARE id INT DEFAULT 1;
       DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING
       BEGIN
           SET id = 0;
           RETURN 0;
       END;

       IF id = 1 THEN
           update invoice set paid_date = date_paid where invoice_no = inv_no;
           return 1;
       END IF;
    END