时间戳

首页 > 解决方案 > 我可以在单个存储过程中使用 2 个表吗

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

问题描述

我正在尝试编写一个存储过程，其中我使用 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 //

mysql错误：

以下查询失败：“CREATE DEFINER= root@ localhost PROCEDURE select_track_notifications(IN datePostedDATETIME, IN recipientIdINT, IN packageIdINT, IN accountIdINT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 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() 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"

标签： mysqlstored-procedures

解决方案

  • END您在陈述的末尾有一个额外的内容。
  • 此外，您需要将分隔符重置为;最后。
  • Else if 语法是ELSEIF .... THEN（ELSE 和 IF 之间没有空格）。
  • 另外，我认为您可能需要使用SELECT .. INTO子句将选择查询的结果存储到变量中（您将在变量上执行进一步操作）。

请尝试以下操作：

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 ;

推荐阅读