MySql 存储过程 else if 和多查询

MySql stored procedure else if and multi queries

提问by Raed Alsaleh

In the following stored procedure I encountered an error:

在以下存储过程中，我遇到了错误：

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;

The error encountered is :

遇到的错误是：

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

What would the best solution to solve this error?

解决此错误的最佳解决方案是什么？

采纳答案by Raed Alsaleh

I had to add an additional "End IF"to cover "Else IF"statement the full query be like this :

我不得不添加一个额外的“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;

回答by John Woo

define pFileIDas INparameter, eg

定义pFileID为IN参数，例如

CREATE PROCEDURE SP_DeleteFileInfo(IN pFileID int)

full query,

完整查询，

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 ;

回答by Sir Rufo

You have more than one syntax error

您有多个语法错误

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 ;

And you get this error

你得到这个错误

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

[SQL] 选择 filesinfo.ReferenceID 到 @FileRef FROM filesinfo where filesinfo.FileID =pFileID; [Err] 1054 - “where 子句”中的未知列“pFileID”

because the Stored Procedure you create is this

因为你创建的存储过程是这样的

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

All other statements are normal single statements and therefor pFileIDis unknown to this context.

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

Just try this select statement standalone yourself and you will get the same error.

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

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