CREATE DEFINER=root
@localhost
PROCEDURE NewProc
(IN a
int,OUT b
VARCHAR(100))
BEGIN
#delimiter //设置自定义结束符防止未执行完整段语句就结束
DECLARE _role_id INT;
DECLARE _role_parent VARCHAR(100);#宣布参数类型
DECLARE yyy CURSOR FOR SELECT role_id,role_parent FROM role WHERE role.role_parent = a ;#游标(光标)
OPEN yyy;
FETCH yyy INTO _role_id,_role_parent;
FETCH yyy INTO _role_id,_role_parent;
SET b = _role_id;
CLOSE yyy;
#if判断
IF a = 0 THEN
SELECT "是等于0";
ELSE
SELECT b;
END IF;
#case判断
CASE a
WHEN 1 THEN SELECT "是等于1";
WHEN 2 THEN SELECT "是等于2";
WHEN 3 THEN SELECT "是等于3";
ELSE
SELECT "是不等于";
END CASE;
#LOOP循环
bbb : LOOP
SET _role_id = 1;
IF a>0 THEN
LEAVE bbb; #跳出LOOP循环
END IF;
END LOOP bbb;
END
#调用存储过程
CALL NewProc(2,@b)