SQL触发器无法从SELECT语句中设置变量。

huangapple go评论60阅读模式
英文:

sql trigger unable to set variable from select

问题

我知道这是一个被问及的问题,然而我尝试了一些修改,但没有解决方案。

我的触发器:

BEGIN
DECLARE bookid INT;
DECLARE roomtype varchar(20);
DECLARE amount INT;
DECLARE m_count INT;
DECLARE curr_m varchar(20);
SET @bookid := NEW.id;
SET roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=@bookid);
SET amount := (SELECT amount from pm_booking_payment WHERE id_booking=@bookid);
SET @curr_m:= (MONTHNAME(NOW()));
SET @m_count:= (SELECT count(*) FROM pm_report WHERE month=@curr_m);
INSERT INTO `pm_report`(`month`, `room_type`, `amount`) VALUES(@curr_m,@roomtype,@amount); 
END

当我检查表格时,它插入了,但只有@curr,也就是月份的名字。其他的都插入了NULL。

我尝试过:

SET @roomtype SELECT title FROM pm_booking_room WHERE id_booking=@bookid

但仍然是相同的,NULL。

也尝试过:

SET @roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=@bookid);

但仍然是NULL。

我正在使用PhpMyadmin创建:

SQL触发器无法从SELECT语句中设置变量。

请帮忙。

英文:

I know its asked question ,however I have tried few amendments but to no solution .<br>
My trigger:

BEGIN
DECLARE bookid INT;
DECLARE roomtype varchar(20);
DECLARE amount INT;
DECLARE m_count INT;
DECLARE curr_m varchar(20);
SET @bookid := NEW.id;
SET roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=@bookid);
SET amount := (SELECT amount from pm_booking_payment WHERE id_booking=@bookid);
SET @curr_m:= (MONTHNAME(NOW()));
SET @m_count:= (SELECT count(*) FROM pm_report WHERE month=@curr_m);
INSERT INTO `pm_report`(`month`, `room_type`, `amount`) VALUES(@curr_m,@roomtype,@amount); 
END

whwn I check table, it inserts, but only @curr which is month name. Rest it inserts NULL
I tried
SET @roomtype SELECT title FROM pm_booking_room WHERE id_booking=@bookid but still same,NULL .
Also tried SET @roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=@bookid); but still NULL.<br>
I am using PhpMyadmin to create :

SQL触发器无法从SELECT语句中设置变量。
Please help.

答案1

得分: 1

请注意,roomtype@roomtype 是两个不同的变量。

您使用 local variable DECLARE statement 声明的变量在一个存储过程的主体内具有作用域。它们从不带有 @ 标记。

带有 @ 标记的 用户定义变量 具有 MySQL 会话的作用域。您不需要声明这些类型的变量。只需将变量设置为一个值即可隐式创建变量。

您不能使用 SET roomtype = ... 并期望从 @roomtype 变量中读取该字符串。反之亦然。

您似乎声明了局部变量,因此您应该一致使用它们。但在某些情况下,您的变量名称与列名相同,如果您在引用包含这些列的表的 SQL 语句中使用变量,这将导致歧义。因此,您应采用一种命名约定来使它们保持不同。

BEGIN
DECLARE v_bookid INT;
DECLARE v_roomtype varchar(20);
DECLARE v_amount INT;
DECLARE v_count INT;
DECLARE v_month varchar(20);
SET v_bookid := NEW.id;
SET v_roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=v_bookid);
SET v_amount := (SELECT amount from pm_booking_payment WHERE id_booking=v_bookid);
SET v_month:= (MONTHNAME(NOW()));
SET v_count:= (SELECT count(*) FROM pm_report WHERE month=v_month);
INSERT INTO `pm_report`(`month`, `room_type`, `amount`) VALUES(v_month,v_roomtype,v_amount); 
END

(此代码未经测试,如果有任何错误,敬请原谅。它仅用于演示如何一致使用非标记变量。)

英文:

Please be aware that roomtype and @roomtype are two different variables.

The variables you declare with the local variable DECLARE statement have a scope within the body of one stored routine. They are never spelled with a @ sigil.

The user-defined variables with the @ sigil have a scope of a MySQL session. You don't need to declare these kinds of variables. Just setting the variable to a value implicitly creates the variable.

You cannot SET roomtype = ... and expect that string to be read from the @roomtype variable. Nor vice-versa.

You appear to declare local variables, so you should use them consistently. But in some cases, your variable names are the same as column names, which will result in ambiguity if you use the variables in SQL statements that also reference tables with those columns. So you should adopt a naming convention to keep them distinct.

BEGIN
DECLARE v_bookid INT;
DECLARE v_roomtype varchar(20);
DECLARE v_amount INT;
DECLARE v_count INT;
DECLARE v_month varchar(20);
SET v_bookid := NEW.id;
SET v_roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=v_bookid);
SET v_amount := (SELECT amount from pm_booking_payment WHERE id_booking=v_bookid);
SET v_month:= (MONTHNAME(NOW()));
SET v_count:= (SELECT count(*) FROM pm_report WHERE month=v_month);
INSERT INTO `pm_report`(`month`, `room_type`, `amount`) VALUES(v_month,v_roomtype,v_amount); 
END

(This code is not tested, so apologies if there are any mistakes. It is meant only to demonstrate using non-sigil variables consistently.)

huangapple
  • 本文由 发表于 2023年2月13日 22:49:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/75437463.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定