http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html#if-statement 20.2.12.1. IF语句 IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF DELIMITER //CREATE FUNCTION SimpleCompare(n INT, m INT) RETURNS VARCHAR(20) BEGIN DECLARE s VARCHAR(20); IF n > m THEN SET s = '>'; ELSEIF n = m THEN SET s = '='; ELSE SET s = '<'; END IF; SET s = CONCAT(n, ' ', s, ' ', m); RETURN s; END //DELIMITER ;
我想实现 create procedure p(int count int) begin declare v int default 7;
case count when 1 then v=8; when 2 then v=9; end case; end; 这样能行吗?
DELIMITER $$DROP PROCEDURE IF EXISTS `pkb_compare`.`ccc`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ccc`(in qq int)begin declare qq1 int; declare sq varchar(40); if qq<=0 then set qq1=7; else if qq =1 set qq1=8; else set qq1=9; end if;set sq=concat("select ",qq1); set @sQuery =sq;prepare stmt from @sQuery;execute stmt;end$$delimiter; 这样运行提示说有错。怎么的?
@sQuery 的内容就是sq呀 我运行 DELIMITER $$DROP PROCEDURE IF EXISTS `pkb_compare`.`ccc`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ccc`(in qq int)begin declare qq1 int default 7; declare sq varchar(40); case qq when 1 then qq1=8; when 2 then qq1=9; end case; set sq=concat("select ",qq1); set @sQuery =sq;prepare stmt from @sQuery;execute stmt;end$$ 也提示说有错的
老兄看贴要认真啊。注意 ELSEIF 和 else if 的差别。
老兄看贴要认真啊。注意 ELSEIF 和 else if 的差别。
DELIMITER $$ DROP PROCEDURE IF EXISTS `pkb_compare`.`ccc`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `ccc`(in qq int) begin declare qq1 int; declare sq varchar(40); if qq <=0 then set qq1=7; elseif qq =1 set qq1=8; else set qq1=9; end if; set sq=concat("select ",qq1); set @sQuery =sq; prepare stmt from @sQuery; execute stmt; end$$ delimiter; 改成elseif后还是要出错,错误是 错误码: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set qq1=8; else set qq1=9; end if; set sq=concat("select ",qq1); ' at line 11
这个代码测试OK mysql> CREATE PROCEDURE `ccc`(in qq int) -> -> begin -> declare qq1 int; -> declare sq varchar(40); -> -> if qq <=0 then -> set qq1=7; -> elseif qq =1 then -> set qq1=8; -> else -> set qq1=9; -> end if; -> -> set sq=concat("select ",qq1); -> -> set @sQuery =sq; -> -> prepare stmt from @sQuery; -> -> execute stmt; -> -> end$$ Query OK, 0 rows affected (0.00 sec)mysql>
换成用case mysql> CREATE PROCEDURE `ccc`(in qq int) -> -> begin -> declare qq1 int; -> declare sq varchar(40); -> -> CASE -> WHEN qq<=0 THEN set qq1=7; -> WHEN qq=1 THEN set qq1=8; -> ELSE set qq1=9; -> END CASE; -> -> select qq1; -> -> end$$ Query OK, 0 rows affected (0.00 sec)mysql> mysql> delimiter ;
用CASE WHEN不是更好? IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IFdeclare qq,qq1 int; set qq=2; if qq <=0 then set qq1=7; elseif qq =1 then set qq1=8; else set qq1=9; end if; select qq1;
BEGIN
DECLARE v INT DEFAULT 1;
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
END;
END CASE;
END;
|
20.2.12.2. CASE语句
20.2.12.1. IF语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
DELIMITER //CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20) BEGIN
DECLARE s VARCHAR(20); IF n > m THEN SET s = '>';
ELSEIF n = m THEN SET s = '=';
ELSE SET s = '<';
END IF; SET s = CONCAT(n, ' ', s, ' ', m); RETURN s;
END //DELIMITER ;
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
create procedure p(int count int)
begin
declare v int default 7;
case count
when 1 then v=8;
when 2 then v=9;
end case;
end;
这样能行吗?
declare qq1 int;
declare sq varchar(40);
if qq<=0 then
set qq1=7;
else if qq =1
set qq1=8;
else
set qq1=9;
end if;set sq=concat("select ",qq1);
set @sQuery =sq;prepare stmt from @sQuery;execute stmt;end$$delimiter;
这样运行提示说有错。怎么的?
我运行
DELIMITER $$DROP PROCEDURE IF EXISTS `pkb_compare`.`ccc`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ccc`(in qq int)begin
declare qq1 int default 7;
declare sq varchar(40); case qq
when 1 then qq1=8;
when 2 then qq1=9;
end case; set sq=concat("select ",qq1);
set @sQuery =sq;prepare stmt from @sQuery;execute stmt;end$$
也提示说有错的
declare qq1 int;
declare sq varchar(40); if qq <=0 then
set qq1=7; elseif qq =1
set qq1=8; else
set qq1=9;
end if; set sq=concat("select ",qq1);
set @sQuery =sq; prepare stmt from @sQuery; execute stmt; end$$ delimiter;
改成elseif后还是要出错,错误是
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set qq1=8; else
set qq1=9;
end if; set sq=concat("select ",qq1); ' at line 11
mysql> CREATE PROCEDURE `ccc`(in qq int)
->
-> begin
-> declare qq1 int;
-> declare sq varchar(40);
->
-> if qq <=0 then
-> set qq1=7;
-> elseif qq =1 then
-> set qq1=8;
-> else
-> set qq1=9;
-> end if;
->
-> set sq=concat("select ",qq1);
->
-> set @sQuery =sq;
->
-> prepare stmt from @sQuery;
->
-> execute stmt;
->
-> end$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE PROCEDURE `ccc`(in qq int)
->
-> begin
-> declare qq1 int;
-> declare sq varchar(40);
->
-> CASE
-> WHEN qq<=0 THEN set qq1=7;
-> WHEN qq=1 THEN set qq1=8;
-> ELSE set qq1=9;
-> END CASE;
->
-> select qq1;
->
-> end$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IFdeclare qq,qq1 int;
set qq=2;
if qq <=0 then
set qq1=7; elseif qq =1 then
set qq1=8; else
set qq1=9;
end if;
select qq1;