MySQL存储过程从选择语句为多个变量赋值 [英] MySQL store procedure Assign value to multiple variable from select statement

问题描述

这是我的存储过程.我在将值分配给Declared variable时遇到问题.当我执行它时，插入和更新命令可以正常工作，但声明变量的VALUE保持为0；但是我在数据库中有一些价值.我该如何正确地做到这一点.

This is my Stored procedure . I have problem to assign value to Declared variable . When I Execute it, Insert and Update Command work fine but VALUE of Declared Variable remain 0; But I have some value in Database. How can i Do this Corectly.

BEGIN
DECLARE PaidFee INT DEFAULT 0; 
DECLARE DueFee INT DEFAULT 0; 
DECLARE CourseFee INT DEFAULT 0; 
INSERT INTO `creditdirectory`(`TypeID`, `PersonName`, `CreditBy`, `PersonID`, `ModeOfPayment`,`Details`,`Amount`,`CompanyID`) 
VALUES(1,PersonName,CreditBy, AddmissionID, ModeOfPayment, 'Installment', PaidAmount, 
 CompanyID); 
SELECT `CourseFee`,`PaidFee`,`DueFee` INTO CourseFee,PaidFee,DueFee FROM `studentcoursedetails` WHERE `ID`= CourseID; 
SET PaidFee = PaidFee + PaidAmount; 
SET DueFee = CourseFee - PaidFee; 
IF (NextDueDate !='') THEN 
UPDATE `studentcoursedetails` SET `PaidFee` = PaidFee, `DueFee` = DueFee, `DueDate` = NextDueDate WHERE `ID`= CourseID; 
ELSE 
UPDATE `studentcoursedetails` SET `PaidFee` = PaidFee, `DueFee` = DueFee, `DueDate` = NULL WHERE `ID` = CourseID; 
END IF; 
END

推荐答案

不要使用具有相同列名的变量，查询将优先使用表列名.

Don't use variables with same name of columns, the query will take preference on table column names.

一个好主意是使用带前缀的变量:

A good idea is use variables with prefix:

BEGIN
    DECLARE p_PaidFee INT DEFAULT 0; 
    DECLARE p_DueFee INT DEFAULT 0; 
    DECLARE p_CourseFee INT DEFAULT 0; 
    INSERT INTO `creditdirectory`(`TypeID`, `PersonName`, `CreditBy`, `PersonID`, `ModeOfPayment`,`Details`,`Amount`,`CompanyID`) 
    VALUES(1,PersonName,CreditBy, AddmissionID, ModeOfPayment, 'Installment', PaidAmount, 
     CompanyID); 
    SELECT `CourseFee`,`PaidFee`,`DueFee` INTO p_CourseFee,p_PaidFee,p_DueFee FROM `studentcoursedetails` WHERE `ID`= CourseID; 
    SET p_PaidFee = p_PaidFee + PaidAmount; 
    SET p_DueFee = p_CourseFee - p_PaidFee; 
    IF (NextDueDate !='') THEN 
        UPDATE `studentcoursedetails` SET `PaidFee` = p_PaidFee, `DueFee` = p_DueFee, `DueDate` = NextDueDate WHERE `ID`= CourseID; 
    ELSE 
        UPDATE `studentcoursedetails` SET `PaidFee` = p_PaidFee, `DueFee` = p_DueFee, `DueDate` = NULL WHERE `ID` = CourseID; 
    END IF; 
END

这篇关于MySQL存储过程从选择语句为多个变量赋值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！