CREATE DEFINER = 'root'@'localhost' PROCEDURE `test_proc`(IN aaa VARCHAR(50), IN bbb VARCHAR(200), OUT ccc INTEGER(11))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF EXISTS (SELECT * FROM test WHERE bbb=bbb) THEN
BEGIN
set ccc = -1;
END;
ELSE
BEGIN
insert into test(aaa,bbb)
values (aaa,bbb);
set ccc = 0;
END;
END IF;
END;
---------------------------
请问,为什么不管是否exists (SELECT * FROM test WHERE bbb=bbb)
输出结果ccc=null??mysql> call test_proc('111','222',@a);
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF EXISTS (SELECT * FROM test WHERE bbb=bbb) THEN
BEGIN
set ccc = -1;
END;
ELSE
BEGIN
insert into test(aaa,bbb)
values (aaa,bbb);
set ccc = 0;
END;
END IF;
END;
---------------------------
请问,为什么不管是否exists (SELECT * FROM test WHERE bbb=bbb)
输出结果ccc=null??mysql> call test_proc('111','222',@a);
结果都是@a=-1,其实应该执行下面的语句
唉,不知道怎么回事
就永远是-1
这句为假,@a也为-1,所以就郁闷在这里
这句永远为真,不可能为假。想判断有没有bbb这条记录可以这样:DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`test_proc`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN aaa VARCHAR(50), IN bbb VARCHAR(200), OUT ccc INTEGER(11))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ' '
BEGIN
select count(1) from test where bbb=bbb into @cnt;
if @cnt > 1 then
set ccc = -1;
else
insert into test(aaa,bbb)
values (aaa,bbb);
set ccc = 0;
end if;
END$$DELIMITER ;