MySQL 中可以设置多少条件语句?
MySQL 手册说 CASE 语句( WHEN 部分)可以包含一个statement_list。
确切地说有多少语句?我需要执行一个相当大的查询,我想对其进行参数化。然而,这也意味着我需要更改正在连接的表。我读到这不能使用 CASE 语句来完成,因此我正在寻找一种解决方案,不必为存在的每个参数值重新创建存储过程...
更新 我尝试使用游标来解决它。我为我拥有的每个对象定义了一个游标。我想使用 IF-ELSE 语句来选择要打开、循环和关闭的游标。但是我无法获得正确的语法...
所有游标和变量(usb、obj、mm、stamp)都用不同的名称声明,但我已缩短它们以供阅读...
if OBJECTTYPE = 1 then
open CUR_1_BUILDING;
LOOP1: loop
fetch CUR_1_BUILDING into usb, obj, mm, stamp;
if no_more_rows then
close CUR_1_BUILDING;
leave loop1;
end if;
INSERT INTO ObjectCache (usb, obj, mm, date_checked) VALUES (usb, obj, mm, now());
end loop LOOP1;
CLOSE CUR_1_BUILDING;
else if OBJECTTYPE = 2 then
open CUR_2_CITY;
LOOP2: loop
fetch CUR_2_CITY into usb, obj, mm, stamp;
if no_more_rows then
close CUR_2_CITY;
leave loop2;
end if;
INSERT INTO ObjectCache (usb, obj, mm, date_checked) VALUES (usb, obj, mm, now());
end loop LOOP2;
close CUR_2_CITY;
end if;
这是否可以使用案例陈述?
BEGIN
CASE
when OBJECTTYPE = 1
INSERT INTO ObjectCache SELECT id FROM Building
when OBJECTTYPE = 2
INSERT INTO ObjectCache SELECT id FROM City
END CASE;
END
the MySQL manual says that a CASE statement (the WHEN part) can contain a statement_list.
How much statement is that to be exact? I need to perform a pretty big query which I would like to parametrize. However this also implies that I need to change tables that are being joined. I've read that this cannot be done using CASE statements, so I'm looking for a solution to not have to re-create the stored procedure for every parameter value that there is...
UPDATE
I tried to solve it using a CURSOR. I have defined a CURSOR for every object I have. I would like to use IF-ELSE-statements to choose which cursor to open, loop and close. However I cannot get the syntax right...
All cursors and variables (usb, obj, mm, stamp) are declared with different names, but I've shortened them for reading....
if OBJECTTYPE = 1 then
open CUR_1_BUILDING;
LOOP1: loop
fetch CUR_1_BUILDING into usb, obj, mm, stamp;
if no_more_rows then
close CUR_1_BUILDING;
leave loop1;
end if;
INSERT INTO ObjectCache (usb, obj, mm, date_checked) VALUES (usb, obj, mm, now());
end loop LOOP1;
CLOSE CUR_1_BUILDING;
else if OBJECTTYPE = 2 then
open CUR_2_CITY;
LOOP2: loop
fetch CUR_2_CITY into usb, obj, mm, stamp;
if no_more_rows then
close CUR_2_CITY;
leave loop2;
end if;
INSERT INTO ObjectCache (usb, obj, mm, date_checked) VALUES (usb, obj, mm, now());
end loop LOOP2;
close CUR_2_CITY;
end if;
Is this is any way possible to do using CASE statements?
BEGIN
CASE
when OBJECTTYPE = 1
INSERT INTO ObjectCache SELECT id FROM Building
when OBJECTTYPE = 2
INSERT INTO ObjectCache SELECT id FROM City
END CASE;
END
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以使用
IF ELSE
或者构建并执行动态 SQL。You could use
IF ELSE
or you could build and execute dynamic SQL.