MySql存储过程else if和multi查询

Question

在下面的存储过程中，我遇到了一个错误:

DROP PROCEDURE IF EXISTS `SP_DeleteFileInfo`;
CREATE  PROCEDURE `SP_DeleteFileInfo`(pFileID int)
Set @FileRef= null;
select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
DELETE From filesinfo
WHERE filesinfo.FileID= pFileID;
IF EXISTS(SELECT * from filesrefrences where filesrefrences.ReferenceID= @FileRef and filesrefrences.RefrenceCount>1) then 
    update filesrefrences
    set 
        filesrefrences.RefrenceCount= filesrefrences.RefrenceCount-1
    where 
        filesrefrences.ReferenceID= @FileRef;
    SELECT @FileRef;
ELSE IF(@FileRef is Not NULL) THEN
    DELETE from filesrefrences WHERE filesrefrences.ReferenceID = @FileRef;
    SELECT -77;
ELSE
    SELECT -99;
end if;

遇到的错误是:

[SQL]
 select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
[Err] 1054 - **Unknown column 'pFileID' in 'where clause'**

解决这个错误的最佳方案是什么?

Answer 1

定义pFileID为IN参数，如

CREATE PROCEDURE SP_DeleteFileInfo(IN pFileID int)

完整的查询,

DROP PROCEDURE IF EXISTS `SP_DeleteFileInfo`;
DELIMITER //
CREATE  PROCEDURE `SP_DeleteFileInfo`(IN pFileID int)
BEGIN
    Set @FileRef= null;
    select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
    DELETE From filesinfo WHERE filesinfo.FileID= pFileID;
    IF EXISTS(SELECT * from filesrefrences where filesrefrences.ReferenceID= @FileRef and filesrefrences.RefrenceCount>1) then 
        update filesrefrences
        set  filesrefrences.RefrenceCount= filesrefrences.RefrenceCount-1
        where  filesrefrences.ReferenceID= @FileRef;
        SELECT @FileRef;
    ELSE 
        IF(@FileRef is Not NULL) THEN
            DELETE from filesrefrences WHERE filesrefrences.ReferenceID = @FileRef;
            SELECT -77;
        ELSE
            SELECT -99;
        END IF;
    END IF;
END //
DELIMITER ;

Answer 2

我必须添加一个额外的"End IF"来覆盖"Else IF"语句完整的查询如下所示:

CREATE  PROCEDURE `SP_DeleteFileInfo`(IN pFileID int)
BEGIN    
Set @FileRef= null;
    select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
    DELETE From filesinfo WHERE filesinfo.FileID= pFileID;
    IF EXISTS(SELECT * from filesrefrences where filesrefrences.ReferenceID= @FileRef and filesrefrences.RefrenceCount>1) then 
        update filesrefrences
        set  filesrefrences.RefrenceCount= filesrefrences.RefrenceCount-1
        where  filesrefrences.ReferenceID= @FileRef;
        SELECT @FileRef;
    ELSE IF(@FileRef is Not NULL) THEN
        DELETE from filesrefrences WHERE filesrefrences.ReferenceID = @FileRef;
        SELECT -77;
    ELSE
        SELECT -99;
    end if;
end if;
END;

Answer 3

您有多个语法错误

DROP PROCEDURE IF EXISTS `SP_DeleteFileInfo`;
-- MISSING DELIMITER //
CREATE  PROCEDURE `SP_DeleteFileInfo`(pFileID int)
-- MISSING BEGIN
  Set @FileRef= null;
  select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;
  DELETE From filesinfo
  WHERE filesinfo.FileID= pFileID;
  IF 
    EXISTS(SELECT * from filesrefrences where filesrefrences.ReferenceID= @FileRef and filesrefrences.RefrenceCount>1) 
  then 
    update filesrefrences
    set 
        filesrefrences.RefrenceCount= filesrefrences.RefrenceCount-1
    where 
        filesrefrences.ReferenceID= @FileRef;
    SELECT @FileRef;
  ELSE 
    IF
      (@FileRef is Not NULL) 
    THEN
      DELETE from filesrefrences WHERE filesrefrences.ReferenceID = @FileRef;
      SELECT -77;
    ELSE
      SELECT -99;
    end if;
-- MISSING END IF;
-- MISSING END//
-- MISSING DELIMITER ;

你会得到这个错误

[SQL] select filesinfo。ReferenceID转换为@FileRef FROM filesinfo wherefilesinfo。文件标识= pFileID;[Err] 1054 -未知列'pFileID'"where子句"

因为您创建的存储过程是这个

CREATE  PROCEDURE `SP_DeleteFileInfo`(pFileID int)
Set @FileRef= null;

所有其他语句都是正常的单个语句，因此pFileID在此上下文中是未知的。

如果你自己单独尝试这个select语句，你会得到同样的错误。

select filesinfo.ReferenceID into @FileRef FROM filesinfo where filesinfo.FileID =pFileID;