mysql 循环和游标使用

循环

-- 求 1-n 的和
/*  while循环语法：
while 条件 DO
            循环体;
end while;
*/
 create procedure sum1(a int) 
 begin
     declare sum int default 0;  -- default 是指定该变量的默认值
     declare i int default 1;
 	 while i<=a DO -- 循环开始
     	set sum=sum+i;
     	set i=i+1;
 	 end while; -- 循环结束
 	 select sum;  -- 输出结果
 end;
 -- 执行存储过程
 call sum1(100);
 -- 删除存储过程
 drop procedure if exists sum1；

  /*loop 循环语法：
  loop_name:loop
          if 条件 THEN -- 满足条件时离开循环
               leave loop_name;  -- 和 break 差不多都是结束训话
       end if；
  end loop;
  */
create procedure sums(a int)
begin
        declare sum int default 0;
        declare i int default 1;
        loop_name:loop -- 循环开始
            if i>a then 
                leave loop_name;  -- 判断条件成立则结束循环  好比java中的 boeak
            end if;
            set sum=sum+i;
            set i=i+1;
        end loop;  -- 循环结束
        select sum; -- 输出结果
end;
 -- 执行存储过程
call sums(100);
-- 删除存储过程
drop procedure if exists  sums;

  /*repeat 循环语法
  repeat
      循环体
  until 条件 end repeat;
  */
-- 实例；
create procedure sum55(a int)
begin
    declare sum int default 0;
   declare i int default 1;
    repeat -- 循环开始
        set sum=sum+i;
        set i=i+1;
    until i>a end repeat; -- 循环结束
    select sum; -- 输出结果
end;

-- 执行存储过程
  call sum55(100);
-- 删除存储过程
drop procedure if exists sum55;

存储过程批量插入大量数据

CREATE DEFINER=`root`@`%` PROCEDURE `idata4`()
begin
	declare i int;		
	START TRANSACTION;	-- 开启事务
		set i=1;
		while(i<=100000)do
			insert into t2 values(i, i, i);
			set i=i+1;
		end while;
	COMMIT;		-- 提交事务
end

游标

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
    -- 定义局部变量
    DECLARE num INT;
    -- 定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- 打开游标
    OPEN ordernumbers;
    -- 获取第一行数据
    FETCH ordernumbers INTO num;
    -- 查询结果
    SELECT num;
    -- 关闭游标
    CLOSE ordernumbers;
END;
CALL processorder();

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义局部变量
	DECLARE done BOOLEAN DEFAULT false;
	DECLARE num INT;
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	-- 定义CONTINUE HANDLER
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;
	-- 打开游标
	OPEN ordernumbers;
	-- 循环所有行
	REPEAT
		-- 获取第一行数据
		FETCH ordernumbers INTO num;
	-- 结束循环
	UNTIL done END REPEAT;
	-- 查询结果
	SELECT num;
	-- 关闭游标
	CLOSE ordernumbers;
END;
CALL processorder();

-- 临时表
CREATE TEMPORARY TABLE StgSummary(
 Name VARCHAR(50) NOT NULL,
 StgId INT NOT NULL DEFAULT 0
);
INSERT INTO StgSummary(Name,StgId) select '临时数据1',1;
INSERT INTO StgSummary(Name,StgId) select '临时数据2',2;
INSERT INTO StgSummary(Name,StgId) select '临时数据3',3;
INSERT INTO StgSummary(Name,StgId) select '临时数据4',4;
INSERT INTO StgSummary(Name,StgId) select '临时数据5',5;
INSERT INTO StgSummary(Name,StgId) select '临时数据6',6;
INSERT INTO StgSummary(Name,StgId) select '临时数据7',7;
INSERT INTO StgSummary(Name,StgId) select '临时数据8',8;
INSERT INTO StgSummary(Name,StgId) select '临时数据9',9;
INSERT INTO StgSummary(Name,StgId) select '临时数据10',10;

select * from StgSummary;

-- 不能用RENAME来重命名一个临时表，但是可以用ALTER TABLE代替
-- ALTER TABLE orig_name RENAME new_name;
-- 临时表使用完以后需要主动Drop掉
DROP TEMPORARY TABLE IF EXISTS StgTempTable;

CREATE TABLE StgSummary_bak(
 Name VARCHAR(50) NOT NULL,
 StgId INT NOT NULL DEFAULT 0
);

-- 存储过程
drop procedure if exists ry.pro_test;
delimiter $$
create procedure if not exists ry.pro_test()
begin
    -- 定义变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE field_1 VARCHAR(20);
    DECLARE field_2 VARCHAR(20);
    -- 创建游标，并存储数据
    DECLARE cur_list CURSOR FOR SELECT StgId, name FROM StgSummary;
    -- 游标中的内容执行完后将done设置为true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    -- 打开游标
    OPEN cur_list;
    -- 执行循环
    read_loop : LOOP
        -- 取游标中的值
        FETCH cur_list INTO field_1, field_2;
        -- 判断是否结束循环，一定要放到FETCH之后，因为在fetch不到的时候才会设置done为true
        -- 如果放到fetch之前，先判断done，这个时候done的值还是之前的循环的值，因此就会导致循环一次
        IF done THEN
            LEAVE read_loop;
        END IF;

        --  执行SQL操作
        SET @sql_insert = CONCAT("insert into StgSummary_bak(StgId, name) VALUES ('", field_1, "','", field_2 ,"')");
        PREPARE sqlli FROM @sql_insert;
            EXECUTE  sqlli;
        COMMIT;
    END LOOP read_loop;
    -- 释放游标
    CLOSE cur_list;
end $$
delimiter ;

-- 调用存储过程
call pro_test()

select * from StgSummary_bak;

游标

• 优点：游标是面向集合与面向行的设计思想之间的一种桥梁，因为游标是针对行操作的，所以对从数据库中 SELECT 查询得到的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。
• 缺点：
  • 速度较慢
  • 会产生死锁现象
  • 内存大