mysql 存储过程

注意点：

@val   会话变量  不需要声明 直接使用

set @val = "abc";

局部变量 需要声明

declare val char(10) default "";

set val = "abd"

 

另 在prepare语句中 要使用会话变量，见下面代码

 

DECLARE done INT DEFAULT 0; 
	declare code char(10) default NULL;  
	declare i int default 0;
	declare j int default 0;
	declare v double default 0;
	declare str varchar(200) default NULL;
	declare pstr text default NULL;

	declare cur cursor for SELECT random_code from tickets_publish_code where tid = tid;  
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
	set @execSql = "insert into tickets_publish_code(tid, random_code) select ?, random_code from tickets_random_code where used = 0 limit ?";
	
	set @tid = tid;
	set @num = num;
	prepare stmt from @execSql;  
  EXECUTE stmt using @tid, @num;  
  deallocate prepare stmt;

	open cur;    
	set i=0;
	set str = "update tickets_random_code set used = 1 where random_code in (";
	repeat  
			FETCH cur INTO code;  
			if not done then
				set pstr = CONCAT_WS(',', pstr, "'", code, "'");
				
				set i = i + 1;
				set j = i % 1000;
				if j = 0 then
					set @execSql = concat(str, pstr, ')');  
					PREPARE stmt FROM @execSql;  
					EXECUTE stmt;    
					DEALLOCATE PREPARE stmt;

					set v = FORMAT(i / num, 2);
					update tickets_random set status = v where id=tid;

					set pstr = NULL;
				end if;
			ELSEIF pstr is NOT NULL THEN
				set @execSql = concat(str, pstr, ')');  
				PREPARE stmt FROM @execSql;  
				EXECUTE stmt;  
				DEALLOCATE PREPARE stmt; 	

				set v = FORMAT(i / num, 2);
				update tickets_random set status = v where id=tid;
			end if;
			until done
	end repeat;  
	close cur;