MySQL存储过程和游标

好长时间没用存储过程和游标了，今天因为需要批量插入数据，看了下相关技术文章，顺便做个记录吧！

存储过程的定义：

DELIMITER $$

CREATE  存储过程名称 

    BEGIN

    END$$

DELIMITER ;

实例

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `proc`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN

    END$$

DELIMITER ;

游标的定义：

DECLARE member（名称） INT（类型）;  #申明局部变量
	DECLARE Select_cursor（游标名） CURSOR FOR （SELECT 列名，列名 FROM 表名 WHERE 条件）;  #申明游标
	OPEN Select_cursor;	#打开游标
	FETCH NEXT FROM Select_cursor INTO 列名; 	#获取游标中的下一个值
	loop_label:LOOP	  #循环开始
		IF(NOT EXISTS(SELECT member_no FROM member_info WHERE member)) THEN   #if判断
			LEAVE  loop_label;     #终止循环
		END IF;
			###进行插入操作
			INSERT INTO member_account(member_no,acct_balance,uninvoiced_amount,acct_status,r_create_time) VALUES(member,0,0,'E',NOW()); 	
			FETCH NEXT FROM Select_cursor INTO member;  #获取游标中的下一个值
			ITERATE  loop_label;  	#从loop处开始循环
	END LOOP;   
	CLOSE Select_cursor ;  	#关闭游标

实例

DECLARE member INT;
	DECLARE Select_cursor CURSOR FOR 
		(SELECT member_no FROM (SELECT mi.member_no AS member_no,ma.member_no AS member_no1 FROM member_info AS mi LEFT JOIN member_account AS ma ON mi.member_no = ma.member_no) tmp WHERE member_no1 IS NULL);
	OPEN Select_cursor;
	FETCH NEXT FROM Select_cursor INTO member;
	loop_label:LOOP	
		IF(NOT EXISTS(SELECT member_no FROM member_info WHERE member)) THEN
			LEAVE  loop_label;    
		END IF;
			INSERT INTO member_account(member_no,acct_balance,uninvoiced_amount,acct_status,r_create_time) VALUES(member,0,0,'E',NOW());
			FETCH NEXT FROM Select_cursor INTO member;
			ITERATE  loop_label;  
	END LOOP;   
	CLOSE Select_cursor ;  

call  存储过程名;     ---------------->调用存储过程
以前没怎么用存储过程和游标，所以这里只是个记录，没有详细的介绍MySQL的存储过程和游标；

http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html这个博客讲解存储过程还是比较详细的，如有需要可以去看下

http://blog.csdn.net/liguo9860/article/details/50848216这个讲解游标的

http://www.cnblogs.com/Gavinzhao/archive/2010/07/14/1777644.html游标的cursor用法详解