仅在允许用户的情况下更新所选列的存储过程

发布于 2025-01-19 14:43:24 字数 720 浏览 4 评论 0原文

我需要编写一个存储过程，该过程允许用户更改表中的值，但仅在某些行中更改。基本上，这是为了使研究人员定义所需的传感器参数，因此他们无法更改其他用户的参数。

我有两个表：参数带有cruitationId，<代码>代码的表max_alert和min_alert列；和用户表显示用户ID和cruitationId（查看谁负责哪种文化以及每个人都能更改的行为））。

这是我写的代码，但我一直遇到错误，尤其是对于列参数。

CREATE PROCEDURE Alter_Param 
    (IN id INT, 
     IN cat INT, 
     IN column VARCHAR(20), 
     IN value INT)
BEGIN
    SELECT UserID 
    FROM Users 
    WHERE CultureID = id 
    INTO @v1;

    IF @v1 = USER() 
    THEN
        UPDATE Parameters
        SET Column = value
        WHERE id = 'CultureID' AND 'CODE' = cat ;
    END IF;  
END

真的很感谢帮助:)

原文

I need to write a stored procedure that would allow a user to change the values in a table, but only in certain rows. Basically this is for investigators to define the sensor parameters they want and so they cannot change the parameters of other users.

I have two tables: a Parameters table with CultureID, Code (each culture has 3 rows so this distinguishes which row user wants to change), Max_Alert and Min_Alert columns; and a Users table that shows with UserID and CultureID (to see who's responsible for what culture and what rows each person will be able to alter).

This was the code I wrote but I keep getting errors, especially with the column parameter.

CREATE PROCEDURE Alter_Param 
    (IN id INT, 
     IN cat INT, 
     IN column VARCHAR(20), 
     IN value INT)
BEGIN
    SELECT UserID 
    FROM Users 
    WHERE CultureID = id 
    INTO @v1;

    IF @v1 = USER() 
    THEN
        UPDATE Parameters
        SET Column = value
        WHERE id = 'CultureID' AND 'CODE' = cat ;
    END IF;  
END

Would really appreciate the help :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

好听的两个字的网名 2025-01-26 14:43:24

SET Column = value - mysql 不会对列名进行变量替换，您需要使用动态 sql 或测试并更新要更改的每个列

WHERE id = 'CultureID' - 这可能应该是列名 - 删除引号。

回复收藏 0 原文

飘然心甜 2025-01-26 14:43:24

感谢您的提示。我遵循您所说的话，而是选择将过程的一部分变成变量，以解决错误。这是最终版本，很棒！

BEGIN

SELECT IdUser 
FROM users 
WHERE IdCulture = id 
INTO @v1;

IF @v1 = SESSION_USER() 
THEN
    SET @alt := CONCAT('UPDATE test.parameters SET ', column, ' =', value,' WHERE IdCulture =', id,' AND Code =',cod,';');

    PREPARE stmt FROM @alt;
    EXECUTE stmt;
END IF;

END

感谢您的帮助！

Thanks for the tip. I followed what you said and instead opted to make that part of the procedure into a variable in order to go around the error. This was the final version which works great!

BEGIN

SELECT IdUser 
FROM users 
WHERE IdCulture = id 
INTO @v1;

IF @v1 = SESSION_USER() 
THEN
    SET @alt := CONCAT('UPDATE test.parameters SET ', column, ' =', value,' WHERE IdCulture =', id,' AND Code =',cod,';');

    PREPARE stmt FROM @alt;
    EXECUTE stmt;
END IF;

END

Thanks for the help!

回复收藏 0 原文

~没有更多了~