主页 / coding / 问题 / 77839156
Accepted
SCP-1762
Asked: 2024-01-18 19:51:27 +0800 CST

mariadb 存储过程中出现未知错误

  • 772

我正在创建一个练习网站，我需要创建一个 SQL 数据库来存储用户及其权限。我想制作一个存储过程来检索所选用户的权限，但由于某种原因，它说我有语法错误，但我很困惑它是什么这一切都在 xampp 上托管的 phpmyadmin 上，不幸的是我我对 sql 非常不习惯，并尝试到处搜索时出现错误

程序代码：

CREATE PROCEDURE CHECKUSERPRIVILEGES(
    IN username VARCHAR(32),
    IN pass VARCHAR(32)
)
BEGIN
    DECLARE
        userid INT(11); <-- error here
    SELECT
        UserID
    INTO userid
FROM
    users
WHERE
    Username = username AND Pass = pass; DECLARE priviligeid BIGINT(16);
SELECT
    PrivilegeID
INTO privilegeid
FROM
    userprivleges
WHERE
    UserID = userid;
SELECT
    *
FROM PRIVILEGES
WHERE
    PrivilegeID = privilegeid
GO;

错误：

#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 '' at line 7

我也尝试过将INT(11)for justINT或INTEGER但仍然是相同的错误

我在这里发现了类似的错误，但我不明白答案在做什么以及如何将其应用于我的问题

编辑：我尝试将DECLAREs 移至开头，但仍然出现相同的错误

CREATE PROCEDURE CHECKUSERPRIVILEGES(
    IN username VARCHAR(32),
    IN pass VARCHAR(32)
)
BEGIN
    DECLARE
        userid INT(11);
    DECLARE
        priviligeid BIGINT(16);
    SELECT
        UserID
    INTO userid
FROM
    users
WHERE
    Username = username AND Pass = pass;
SELECT
    PrivilegeID
INTO privilegeid
FROM
    userprivleges
WHERE
    UserID = userid;
SELECT
    *
FROM PRIVILEGES
WHERE
    PrivilegeID = privilegeid
GO;
mariadb

1 个回答

  1. Best Answer

    如果您有兴趣，我从头开始编写了您的存储过程，您也可以使用下面的 SP，或者您可以考虑错误处理并在 SP 中使用选择计数。

    DELIMITER //

CREATE PROCEDURE CheckUserPrivileges(
IN pUserID INT,
IN pPass VARCHAR(255)
)
BEGIN
DECLARE privilegeCount INT;

-- Check if the user exists
SELECT COUNT(*) INTO privilegeCount
FROM users
WHERE UserID = pUserID AND Pass = pPass;

IF privilegeCount = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'User not found or incorrect password';
ELSE
    -- Check user privileges
    SELECT COUNT(*) INTO privilegeCount
    FROM userprivileges
    WHERE UserID = pUserID;

    IF privilegeCount > 0 THEN
        -- User has privileges, you can add more logic if needed
        SELECT p.PrivilegeID
        FROM userprivileges up
        JOIN privileges p ON up.PrivilegeID = p.PrivilegeID
        WHERE up.UserID = pUserID;
    ELSE
        -- User has no privileges
        SELECT 'User has no privileges';
    END IF;
END IF;
END //
DELIMITER ;
    • 0

相关问题