mysql - 编写脚本创建并调用存储过程

Question

编写一个脚本来创建并调用名为 test 的存储过程。这个存储过程应该声明一个变量并将其设置为 Products 表中所有产品的计数。如果计数更大小于或等于 7，存储过程应显示一条消息:“产品数量大于或等于7”。否则，应该说“产品数量少于 7”。

DROP PROCEDURE IF EXISTS test;
CREATE  procedure test()
BEGIN
DECLARE count_of_7 DECIMAL(10,2);

SELECT  count(product_id) 
into count_of_7

FROM products;
IF count_of_7 >= 7 THEN

SELECT 'The number of products is greater than or equal to 7' AS message;
ELSE

SELECT 'The number of products is less than 7' AS message;
end if;
call test();

21:38:55 CREATE procedure test() BEGIN DECLARE count_of_7 DECIMAL(10,2) 错误代码:1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 3 行 0.016 秒的 '' 附近使用的正确语法

Answer 1

在声明过程之前，您需要更改分隔符。此外，您还缺少 END 语句。这应该有效:

DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE  procedure test()
BEGIN
    DECLARE count_of_7 DECIMAL(10,2);

    SELECT count(product_id) into count_of_7 FROM products;
    IF count_of_7 >= 7 THEN
        SELECT 'The number of products is greater than or equal to 7' AS message;
    ELSE
        SELECT 'The number of products is less than 7' AS message;
    END IF;
END //
DELIMITER ;
call test();