外围的IF条件是什么,与 if a=1 then .... else ... end if; 没有区别
drop procedure if exists addcol; delimiter // create procedure addcol(in sche varchar(100),in tabname varchar(100),in colname varchar(100),typ varchar(100)) begin declare a varchar(100); declare done int default 0; declare cur cursor for select column_name from information_schema.COLUMNS where TABLE_SCHEMA= sche and TABLE_NAME=tabname; declare continue handler for not found set done=1; open cur; repeat fetch cur into a; if done =0 then if a=colname then select '该列已经存在'; set done=1; end if; else if a<>colname then set @sql=concat('alter table ',tabname,' add column ',colname,' ',typ); prepare stmt from @sql; execute stmt; deallocate prepare stmt; select '添加成功'; set done =1; end if; end if; until done =1 end repeat; close cur; set done =0; end // delimiter ;这个存储过程,可以正常使用,但是把这2个红色的注释掉之后,虽然可以创建成功,但是要是遇到相同列的话,报错.想知道为什么会这样..
感觉是REPEAT的问题 你修改为 WHILE done=0 DO ....
drop procedure if exists addcol; delimiter // create procedure addcol(in sche varchar(100),in tabname varchar(100),in colname varchar(100),typ varchar(100)) begin declare a varchar(100); declare done int default 0; declare cur cursor for select column_name from information_schema.COLUMNS where TABLE_SCHEMA= sche and TABLE_NAME=tabname; declare continue handler for not found set done=1; open cur; #repeat while done =0 DO fetch cur into a; #if done =0 then if a=colname then select '该列已经存在'; set done=1; #end if; else #if a<>colname then set @sql=concat('alter table ',tabname,' add column ',colname,' ',typ); prepare stmt from @sql; execute stmt; deallocate prepare stmt; select '添加成功'; set done =1; #end if; end if; end while; #until done =1 end repeat; close cur; set done =0; end // delimiter ;修改成这样也一样,和注释掉红字 一样的情况;
fetch cur into a; end while; 没有取新值哦
他们有什么区别吗? 1,if ... then ..else... end if; 2,if ...then ...end if; else if ...then... end if; 1这个的情况,不就是如果if里面的条件满足就跳过else,不满足就执行else后面的语句, 2这个的情况,好像是如果if里面的条件满足,就结束这个if语句,要是不满足 和1的情况不是一样的吗? 我上面的理解对吗?
他们有什么区别吗? 1,if ... then ..else... end if; 2,if ...then ...end if; else if ...then... end if; 1这个的情况,不就是如果if里面的条件满足就跳过else,不满足就执行else后面的语句, 2这个的情况,好像是如果if里面的条件满足,就结束这个if语句,要是不满足 和1的情况不是一样的吗? 我上面的理解对吗?
那该怎么取....我觉得游标有点问题,上面用while do 创建好后,只对该表的第一列有效果,会出现'该列已经存在',其他列的话 创建会报错.
DELIMITER $$USE `qq`$$DROP PROCEDURE IF EXISTS `addcol`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `addcol`(IN sche VARCHAR(100),IN tabname VARCHAR(100),IN colname VARCHAR(100),typ VARCHAR(100)) BEGIN DECLARE a VARCHAR(100); DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= sche AND TABLE_NAME=tabname AND COLUMN_NAME=colname; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cur; #repeat WHILE done =0 DO FETCH cur INTO a; IF a IS NOT NULL THEN #if done =0 then SELECT '该列已经存在'; SET done=1; #end if; #if a<>colname then#end if; ELSE SET @SQL=CONCAT('alter table ',tabname,' add column ',colname,' ',typ); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT '添加成功'; END IF; END WHILE; #until done =1 end repeat; CLOSE cur; SET done =0; END$$DELIMITER ;
noELIMITER $$USE `qq`$$DROP PROCEDURE IF EXISTS `addcol1`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `addcol1`(IN sche VARCHAR(100),IN tabname VARCHAR(100),IN colname VARCHAR(100),typ VARCHAR(100)) BEGIN DECLARE a VARCHAR(100); DECLARE bz INT DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= sche AND TABLE_NAME=tabname; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cur; #repeat WHILE done =0 DO FETCH cur INTO a; #if done =0 then IF a=colname THEN SET bz=0; SELECT '该列已经存在'; SET done=1; #end if; ELSE #if a<>colname then SET bz=1; #set done =1; #end if; END IF; END WHILE; #until done =1 end repeat; CLOSE cur; SET done =0; IF bz=1 THEN SET @SQL=CONCAT('alter table ',tabname,' add column ',colname,' ',typ); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT '添加成功'; END IF; END$$DELIMITER ;
如果只判断A是否=1,
用CASE WHEN
if a=1 then
....
else
...
end if;
没有区别
delimiter //
create procedure addcol(in sche varchar(100),in tabname varchar(100),in colname varchar(100),typ varchar(100))
begin
declare a varchar(100);
declare done int default 0;
declare cur cursor for select column_name from information_schema.COLUMNS where TABLE_SCHEMA= sche and TABLE_NAME=tabname;
declare continue handler for not found set done=1;
open cur;
repeat
fetch cur into a;
if done =0 then
if a=colname then
select '该列已经存在';
set done=1;
end if;
else
if a<>colname then
set @sql=concat('alter table ',tabname,' add column ',colname,' ',typ);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
select '添加成功';
set done =1;
end if;
end if;
until done =1 end repeat;
close cur;
set done =0;
end //
delimiter ;这个存储过程,可以正常使用,但是把这2个红色的注释掉之后,虽然可以创建成功,但是要是遇到相同列的话,报错.想知道为什么会这样..
你修改为
WHILE done=0 DO
....
delimiter //
create procedure addcol(in sche varchar(100),in tabname varchar(100),in colname varchar(100),typ varchar(100))
begin
declare a varchar(100);
declare done int default 0;
declare cur cursor for select column_name from information_schema.COLUMNS where TABLE_SCHEMA= sche and TABLE_NAME=tabname;
declare continue handler for not found set done=1;
open cur;
#repeat
while done =0 DO
fetch cur into a;
#if done =0 then
if a=colname then
select '该列已经存在';
set done=1;
#end if;
else
#if a<>colname then
set @sql=concat('alter table ',tabname,' add column ',colname,' ',typ);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
select '添加成功';
set done =1;
#end if;
end if;
end while;
#until done =1 end repeat;
close cur;
set done =0;
end //
delimiter ;修改成这样也一样,和注释掉红字 一样的情况;
end while;
没有取新值哦
他们有什么区别吗?
1,if ... then ..else... end if;
2,if ...then ...end if; else if ...then... end if;
1这个的情况,不就是如果if里面的条件满足就跳过else,不满足就执行else后面的语句,
2这个的情况,好像是如果if里面的条件满足,就结束这个if语句,要是不满足 和1的情况不是一样的吗?
我上面的理解对吗?
他们有什么区别吗?
1,if ... then ..else... end if;
2,if ...then ...end if; else if ...then... end if;
1这个的情况,不就是如果if里面的条件满足就跳过else,不满足就执行else后面的语句,
2这个的情况,好像是如果if里面的条件满足,就结束这个if语句,要是不满足 和1的情况不是一样的吗?
我上面的理解对吗?
BEGIN
DECLARE a VARCHAR(100);
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= sche AND TABLE_NAME=tabname AND COLUMN_NAME=colname;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur;
#repeat
WHILE done =0 DO
FETCH cur INTO a;
IF a IS NOT NULL THEN
#if done =0 then
SELECT '该列已经存在';
SET done=1;
#end if;
#if a<>colname then#end if;
ELSE
SET @SQL=CONCAT('alter table ',tabname,' add column ',colname,' ',typ);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT '添加成功';
END IF;
END WHILE;
#until done =1 end repeat;
CLOSE cur;
SET done =0;
END$$DELIMITER ;
BEGIN
DECLARE a VARCHAR(100);
DECLARE bz INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= sche AND TABLE_NAME=tabname;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur;
#repeat
WHILE done =0 DO
FETCH cur INTO a;
#if done =0 then
IF a=colname THEN
SET bz=0;
SELECT '该列已经存在';
SET done=1;
#end if;
ELSE
#if a<>colname then
SET bz=1;
#set done =1;
#end if;
END IF;
END WHILE;
#until done =1 end repeat;
CLOSE cur;
SET done =0;
IF bz=1 THEN
SET @SQL=CONCAT('alter table ',tabname,' add column ',colname,' ',typ);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT '添加成功';
END IF;
END$$DELIMITER ;