需求是要把数据库中的一个字段如果是15个字节长的话全部截成9个字节长;
//这个貌似是;的转义
DELIMITER $$
DROP PROCEDURE IF EXISTS `xsgw`.`goods_mark_up`$$
CREATE DEFINER=`51xs`@`%` PROCEDURE `goods_mark_up`()
BEGIN
//声明变量
DECLARE done INT DEFAULT 0;
DECLARE a,c INT;
DECLARE b VARCHAR(255);
//游标
declare cur1 CURSOR FOR SELECT spec_id,LEFT(goods_mark,9),LENGTH(goods_mark) from xs_goods_spec;
//写一个标识来判断游标已经结束了
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a, b,c;
IF NOT done THEN
IF c = 15 THEN
UPDATE xs_goods_spec set goods_mark = b WHERE spec_id = a;
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END$$
DELIMITER ;
大概就是这样,呵呵,有半年的时间没写过这个东西,现在用起来还是有点生疏了!