mysql - 使用 Toad 时出现无效脚本错误

Question

我正在尝试为存储过程开发 SQL 语句，该存储过程将为我提供一些计数，我将使用变量 (@repeated) 指定这些计数。

我正在使用 Toad for MySQL，但我不断收到语法错误(描述性不强)。这是我的 SQL:

DECLARE @repeated INT DEFAULT 1;
DECLARE @mailingmonth DATE;

SET @mailingmonth = '2015-10-01';

WHILE @repeated < 12 DO
SELECT A.LoanNumber, Repeated FROM MKT_Mailing_Hist A 
 INNER JOIN (SELECT LoanNumber, COUNT(*) As Repeated
             FROM MKT_Mailing_Hist B
             WHERE MailingMonth <= @mailingmonth
             GROUP BY LoanNumber) AS Months ON A.LoanNumber = Months.LoanNumber
 WHERE A.MailingMonth = @mailingmonth
 AND Repeated = @repeated
 SET @repeated = @repeated + 1;
 END WHILE;
 END;

我认为这种语法可能存在几个问题，但我不断进行更改，但似乎没有任何效果。

更新

我想将类似的代码封装在带有参数的存储过程中，以便我可以更改参数以满足我的需要。这就是我所拥有的:

DELIMITER $$

CREATE procedure sp_MKT_Counts_Test2 (vRepeated INT, vMailingmonth DATE)
BEGIN

WHILE vRepeated < 12 DO
SELECT COUNT(A.LoanNumber) FROM MKT_Mailing_Hist A 
 INNER JOIN (SELECT LoanNumber, COUNT(*) As Repeated
             FROM MKT_Mailing_Hist B
             WHERE MailingMonth <= vMailingmonth
             GROUP BY LoanNumber) AS Months ON A.LoanNumber = Months.LoanNumber
 WHERE A.MailingMonth = vMailingmonth
 AND Repeated = vRepeated;
 SET vRepeated = vRepeated + 1;
 END WHILE;
  END;
 $$
 DELIMITER ;

但是，当我使用两个参数调用存储过程时，我只收到一条消息，通知我该语句执行成功，但没有得到结果集。为什么没有结果集？我需要输出参数吗？

Answer 1

这将使您克服语法错误:

DELIMITER $$
create procedure myProc876()
BEGIN

DECLARE vRepeated INT DEFAULT 1;
DECLARE vMailingmonth DATE;

SET vMailingmonth = '2015-10-01';

WHILE vRepeated < 12 DO
SELECT A.LoanNumber, Repeated FROM MKT_Mailing_Hist A 
 INNER JOIN (SELECT LoanNumber, COUNT(*) As Repeated
             FROM MKT_Mailing_Hist B
             WHERE MailingMonth <= mailingmonth
             GROUP BY LoanNumber) AS Months ON A.LoanNumber = Months.LoanNumber
 WHERE A.MailingMonth = vMailingmonth
 AND Repeated = vRepeated;
 SET vRepeated = vRepeated + 1;
 END WHILE;
 END;
 $$
 DELIMITER ;

它有一个 WHILE DO，更改了一些变量名称，并用 DELIMITER 括起来。分隔符很重要，因为否则找到的第一个分号就会结束一切。所以 $$ 包含了整个事情。然后将用户 session 分隔符设置回分号。

请注意，您的AND Repeated = @repeated 最后也没有半

Delimiters对于存储过程以及创建事件和触发器很重要。