数据库的循环

最新推荐文章于 2024-05-29 15:08:06 发布

朝闻道-夕死可矣

最新推荐文章于 2024-05-29 15:08:06 发布

阅读量588

点赞数

CC 4.0 BY-SA版权

分类专栏： DB&SQL

本文链接：https://blog.youkuaiyun.com/jc_benben/article/details/78199876

DB&SQL 专栏收录该内容

70 篇文章

订阅专栏

本文通过多个示例介绍了Oracle、SQL Server及MySQL中流程控制语句的应用，包括GOTO、WHILE、FOR等，并展示了如何在MySQL存储过程中实现循环删除大表记录的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

oracle

-- oracle
 -- GOTO
 DECLARE
   L_I NUMBER;
 BEGIN
   L_I := 10;
   <<REPEAT_LOOP>>
   L_I := L_I - 1;
   DBMS_OUTPUT.PUT_LINE(L_I);
   IF L_I > 0 THEN
     GOTO REPEAT_LOOP; -- 当小于10时候GOTO到REPEAT_LOOP
   END IF;
 END;

-- FOR
DECLARE
  L_I NUMBER; --声明变量  
BEGIN
  L_I := 0; --给初值  
  FOR L_I IN REVERSE 1 .. 10 LOOP  
    DBMS_OUTPUT.PUT_LINE('L_I=' || L_I);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('END LOOP:L_I=' || L_I);
END;
/

-- while
DECLARE
  L_D DATE;
BEGIN
  L_D := TO_DATE('2016-01-01', 'YYYY-MM-DD');
  WHILE L_D < TO_DATE('2017-10-10', 'YYYY-MM-DD') LOOP
    INSERT INTO T1_BAK
      SELECT *
        FROM T
       WHERE DATATIME > L_D
         AND DATATIME <= L_D + 30;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('L_D=' || L_D);
    L_D := L_D + 30;
  END LOOP;
END;

-- LOOP
DECLARE
  L_I number;
BEGIN
  L_I := 0;
  LOOP
    L_I := L_I + 1;
    EXIT WHEN L_I > 10;
    DBMS_OUTPUT.PUT_LINE('L_I=' || L_I);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('OUT_LOOP L_I=' || L_I);
END;
/

SQLServer

declare @stime1 datetime;
declare @etime1 datetime;
set @stime1='2016-01-01'
set @etime1='2016-02-01'
while (@stime1<'2017-01-01')
begin
	exec dbo.[P_TEST] -- 调用过程
	  @sdate=@stime1,
	  @edate=@etime1

	select @stime1;
	set @stime1=dateadd(m,1,@stime1);
	set @etime1=dateadd(m,1,@etime1);
    -- WAITFOR DELAY '00:00:30' sleep30秒
end;

MySQL：

DELIMITER $$

DROP PROCEDURE IF EXISTS `P_TEST`$$

CREATE PROCEDURE `P_TEST`()

BEGIN
DECLARE v_cnt INT;

SELECT 1 INTO v_cnt ;
	WHILE v_cnt<=10 DO
		SET v_cnt=v_cnt+1;
	END WHILE;

END$$

DELIMITER ;

说明：MySQL没有匿名过程，但是可以在过程中使用

一个实用的例子，循环删除大表记录：

DELIMITER $$

DROP PROCEDURE IF EXISTS `P_DEL_HISDATA`$$

CREATE  PROCEDURE `P_DEL_HISDATA`()
BEGIN
DECLARE v_cnt INT;
DECLARE v_date DATETIME DEFAULT '2019-08-01';
DECLARE v_tbl VARCHAR(30);
BEGIN
  DECLARE no_more_data INTEGER DEFAULT 0; 
  DECLARE cur_tbl CURSOR FOR 
    SELECT t.table_name
    FROM information_schema.tables t 
    WHERE t.table_name  IN ('T_ETL_HISTAB1','T_ETL_HISTAB2')
    AND t.table_schema='DB1';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data=1; 
  OPEN cur_tbl ;
  REPEAT
    BEGIN
      FETCH cur_tbl INTO v_tbl;
      SET @v_sql=CONCAT('select count(1) into @p_num FROM ', v_tbl,' where datatime <\'', v_date, '\' limit 1');
      PREPARE p_sql FROM @v_sql;
	-- select @v_sql;
     EXECUTE  p_sql;
      DEALLOCATE PREPARE p_sql;
      WHILE @p_num>0 DO
        SET @v_sql=CONCAT('DELETE FROM ', v_tbl,' where datatime <\'', v_date, '\' LIMIT 1000');
        PREPARE p_sql FROM @v_sql;
	-- select @v_sql;
        EXECUTE  p_sql;
        DEALLOCATE PREPARE p_sql;
     
        SET @v_sql=CONCAT('select count(1) into @p_num FROM ', v_tbl,' where datatime <\'', v_date, '\' limit 1');
        PREPARE p_sql FROM @v_sql;
	-- select @v_sql;
        EXECUTE  p_sql;
        DEALLOCATE PREPARE p_sql;
      END WHILE;
  END;
  UNTIL no_more_data  END REPEAT;    
  CLOSE cur_tbl;
END;
END$$

DELIMITER ;

MySQL的存储过程中，定义变量有两种方式：
1.使用SET或SELECT直接赋值，变量名以 @ 开头.
例如:SET @VAR=1;
可以在一个会话的任何地方声明，作用域是整个会话，称为会话变量。

2.以 DECLARE 关键字声明的变量，只能在存储过程中使用，称为存储过程变量，例如：
DECLARE VAR1 INT DEFAULT 0;
主要用在存储过程中，或者是给存储传参数中。

另外在存储过程中，使用动态语句，预处理时，动态内容必须赋给一个会话变量。
例：
SET @V_SQL= "SELECT NOW()";
PREPARE STMT FROM @V_SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;