MySQL StoredProcedure 从 select 中设置变量，以参数作为条件

Question

因此，我尝试使用在 select 语句中传入的 FName 和 LName 参数，从我之前在存储过程中添加到数据库的患者获取 PatientID(主键，非空，自动增量)变量，但我收到语法错误。因此，任何有关语法失败原因的帮助将不胜感激。

下面是相关代码，pFName和pLName是传入存储过程的参数。

DECLARE pPolicyHolder INT;
SET pPolicyHolder = (SELECT PatientID FROM Patient WHERE Fname = pFName AND LName = pLName);

这是整个过程。我意识到这可能不是最好/最干净的方法，但设置表的方式是需要在添加患者之前添加 MedicalHistory，并且必须在添加 PatientInsurance 之前添加患者，因为外键约束。

DELIMITER //
CREATE PROCEDURE AddNewPatient(
    IN pAllergies TEXT, IN pMedications TEXT, IN pExistingConditions TEXT, IN pMisc       
    TEXT, IN pFName VARCHAR(30), IN pLName VARCHAR(45), IN pGender CHAR(1), IN pDOB 
    DATE, IN pSSN DOUBLE, IN pMedicalHistory INT, IN pPrimaryPhysician INT, IN 
    pInsuranceCompany INT, IN pCoPay INT)
BEGIN
    START TRANSACTION;
         INSERT INTO MedicalHistory(Allergies, Medications, ExistingConditions, Misc) 
         VALUES(pAllergies, pMedications, pExistingConditions, pMisc);
    COMMIT;
        START TRANSACTION;
              INSERT INTO Patient(FName, LName, Gender, DOB, SSN, 
              MedicalHistory,PrimaryPhysician) VALUES(pFName, 
              LName,pGender,pDOB,pSSN,pMedicalHistory,pPrimaryPhysician);
        COMMIT;

       DECLARE pPolicyHolder INT;
       SET pPolicyHolder = (SELECT PatientID FROM Patient WHERE Fname = pFName AND 
                            LName = pLName);
    START TRANSACTION;
       INSERT INTO PatientInsurance(PolicyHolder, InsuranceCompany, CoPay) 
       VALUES(pPolicyHolder, pInsuranceCompany, pCoPay);
    COMMIT;
    END //
DELIMITER ;

错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE pPolicyHolder INT;
   SELECT PatientID into pPolicyHolder FROM Patient W' at line 15

Answer 1

您需要在过程的开头而不是中间声明变量。

CREATE PROCEDURE AddNewPatient(...)
BEGIN
   DECLARE pPolicyHolder INT;  <------declare here

   START TRANSACTION;
   ...
   COMMIT;

   SET pPolicyHolder = (SELECT PatientID FROM Patient WHERE Fname = pFName AND 
                        LName = pLName);
   ...