MySQL：存储过程定义与调用

drop procedure if exists c_user_uuid_create;	-- 删除存储过程

-- 定义存储过程
create procedure c_user_uuid_create()
begin 
	DECLARE varId bigint;
	declare varUuid VARCHAR(32);
	declare users cursor for select id,uuid from c_user ;
open users;
point1:LOOP
	fetch users into varId,varUuid;
	 if varUuid is null THEN
			update c_user set uuid = REPLACE(UUID(),'-','') where id = varId ;
	 end IF;
end loop point1;
close users;
end;

call c_user_uuid_create();	-- 调用存储过程
drop procedure if exists c_user_uuid_create;	-- 删除存储过程

 

有输入参数的存储过程定义：

CREATE PROCEDURE proc_create_allUser_relations_for_roleId (in roleId BIGINT)
BEGIN
DECLARE varId BIGINT;
DECLARE varUuid VARCHAR (32);
DECLARE users CURSOR FOR SELECT	id,	uuid FROM	c_user;

OPEN users;
point1 :
LOOP
	FETCH users INTO varId,	varUuid;
IF varUuid IS NOT NULL THEN
	INSERT INTO bs_role_user_r (role_id,user_uuid,rur_status	) VALUES
	(roleId,varUuid, (	SELECT rl_status FROM	bs_role	WHERE	id = roleId) & (SELECT	active FROM	c_user WHERE uuid = varUuid));
END
IF;
END LOOP point1;
CLOSE users;
END;