我可以在单个存储过程中使用2个表吗

Question

我正在尝试编写一个存储过程，其中我使用2个表来获取基于条件的数据，但它给出了错误。

我的SP是:

DELIMITER //
CREATE PROCEDURE select_notifications(IN datePosted DATETIME,IN recipientId INT,IN packageId INT,IN accountId INT)
BEGIN
 IF datePosted < NOW() THEN
  SELECT tn.subject_line,tn.message,tn.message_type FROM track_notifications AS tn WHERE tn.package_id=packageId AND tn.recipient_id=recipientId AND tn.account_id=accountId AND tn.date_posted = datePosted;
 ELSE IF datePosted > NOW()
  SELECT subject_line,message,message_type FROM track_notifications_new WHERE package_id=packageId AND recipient_id=recipientId AND account_id=accountId AND date_posted = datePosted;
 END IF;
END
END //

错误：

以下查询失败：“CREATE DEFINER=root@localhostPROCEDUREselect_track_notifications（在datePostedDATETIME中，在recipientIdINT中，在packageIdINT中，以及在.accountIdINT中）不确定性包含SQL安全定义器，如果dateposteds

Answer 1

• 您在语句的末尾有一个额外的＜code＞END＜/code＞
• 此外，您还需要将分隔符重置为＜code＞ 在最后
• 如果语法为＜code＞ELSEIF…则（Else和if之间没有空格）
• 此外，我认为您可能需要使用＜code＞SELECT..INTO＜/code＞子句将SELECT查询的结果存储到变量中（您将对这些变量执行进一步的操作）

请尝试以下方法：

DELIMITER //
DROP PROCEDURE IF EXISTS select_notifications //
CREATE PROCEDURE select_notifications(IN datePosted DATETIME,
                                      IN recipientId INT,
                                      IN packageId INT,
                                      IN accountId INT)
BEGIN
 IF datePosted < NOW() THEN
  SELECT tn.subject_line,
         tn.message,
         tn.message_type 
  FROM track_notifications AS tn 
  WHERE tn.package_id=packageId AND 
        tn.recipient_id=recipientId AND 
        tn.account_id=accountId AND 
        tn.date_posted = datePosted;

 ELSEIF datePosted > NOW() THEN 
  SELECT subject_line,
         message,
         message_type 
  FROM track_notifications_new 
  WHERE package_id=packageId AND 
        recipient_id=recipientId AND 
        account_id=accountId AND 
        date_posted = datePosted;
 END IF;
END //

DELIMITER ;