php - phpmyadmin 中的存储过程

Question

我是第一次使用phpmyadmin。我不知道在我的存储过程中我正在使用我的代码。我不知道如何在 phpmyadmin 中创建存储过程。

这是我的代码:

DELIMITER $$
CREATE PROCEDURE sp_1_service_card_report(IN service_card_no INT)
BEGIN
    DECLARE error_1 INT;

    SELECT  COUNT(service_card.customer_id)
    INTO    error_1 
    FROM    service_card
    WHERE   service_card.id =  27;

    DECLARE error_2 INT;

    SELECT  COUNT(service_card.serial_no_id)
    INTO    error_2 
    FROM    service_card
    WHERE   service_card.id =  27;

    IF error_1 > 0 AND error_2 > 0 THEN
     SELECT
       service_card.id as yes from service_card where service_card.id=27;
    ELSE
      SELECT service_card.customer_id as no from service_card.id=27;
    END IF ;

END $$
DELIMITER ;

这是我的错误:

MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE error_2 INT;

SELECT COUNT(service_card.serial_no_id)
INTO ' at line 11

你们能帮我解决这个问题吗？我也花了 1 小时在 stackoverflow 上搜索答案。

谢谢。

Answer 1

DECLARE 语句必须首先放置，并且在过程末尾的查询中存在 select 拼写错误:

DELIMITER $$
CREATE PROCEDURE sp_1_service_card_report(IN service_card_no INT)
BEGIN
    DECLARE error_1 INT;
    DECLARE error_2 INT;

    SELECT  COUNT(service_card.customer_id)
    INTO    error_1 
    FROM    service_card
    WHERE   service_card.id =  27;


    SELECT  COUNT(service_card.serial_no_id)
    INTO    error_2 
    FROM    service_card
    WHERE   service_card.id =  27;

    IF error_1 > 0 AND error_2 > 0 THEN
     SELECT
       service_card.id as yes from service_card where service_card.id=27;
    ELSE
      SELECT service_card.customer_id as no from service_card where service_card.id=27;
    END IF ;

END $$
DELIMITER ;