循环
第一种循环(WHILE )
BEGIN
DECLARE i int DEFAULT 0;
WHILE (i<10) DO
BEGIN
select i;
set i= i+1;
INSERT into `user`(user_id) VALUE (i);
end ;
end WHILE;
END
第二种循环(REPEAT)
BEGIN
DECLARE i int DEFAULT 100;
REPEAT
BEGIN
select i;
set i= i+1;
INSERT into `user`(user_id) VALUE (i);
end ;
UNTIL i>=110
end REPEAT;
END
游标
BEGIN
#定义一个游标的停止状态
DECLARE stopflag int DEFAULT 0;
#定义一个变量
DECLARE var_user_id int DEFAULT 0;
#定义一个游标
DECLARE user_id_cur CURSOR FOR SELECT user_id FROM `user`;
#定义一个句柄,用于判断游标循环是不是结束了
DECLARE CONTINUE HANDLER FOR not found SET stopflag = 1;
#打开游标
OPEN user_id_cur;
#循环,游标取出来 一条记录,将值给变量。
FETCH user_id_cur into var_user_id;
WHILE(stopflag=0) DO
BEGIN
update `user` set user_id = 2;
#如果屏了他,这个存储过程会一直在那里跑,因为它只读了第一条数据,stopflag 不会=1;
#建议放在循环后的第一步
FETCH user_id_cur into var_user_id;
END;
END WHILE;
CLOSE user_id_cur;
END
函数
调用方式为 select + 函数名称
BEGIN
DECLARE v_user_name VARCHAR(32);
SELECT user_name into v_user_name from `user` where user_id = p_user_id;
RETURN v_user_name;
END
触发器
CREATE TRIGGER tr_user_insert AFTER insert on `user`
for each ROW
BEGIN
insert into `user`(user_id,user_name)
VALUES (NEW.user_id,NEW.user_name);
end;
DROP TRIGGER IF EXISTS tr_user_delete;
CREATE TRIGGER `tr_user_delete` AFTER INSERT ON `User` FOR EACH ROW BEGIN
insert into `test_t`(test_id,test_name)
VALUES (old.user_id,old.user_name);
end;