FOR 语句
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
当迭代数字值序列时，FOR 循环提供了比以前类型的循环（包括 WHILE 循环）更有用的语法糖，尽管它们在功能上是等效的。
一个例子
-- PL/SQL FOR i IN 1 .. 10 LOOP INSERT INTO t (col) VALUES (i); END LOOP;
// All dialects
Variable<Integer> i = var("i", INTEGER);
for_(i).in(1, 10).loop(
insertInto(T).columns(T.COL).values(i)
)
除了简化最常见的情况外，还可以选择以相反的方式遍历参数，并使用额外的可选步长变量，例如
-- pgplsql FOR i IN REVERSE 10 .. 1 BY 2 LOOP INSERT INTO t (col) VALUES (i); END LOOP;
// All dialects
Variable<Integer> i = var("i", INTEGER);
for_(i).inReverse(10, 1).by(2).loop(
insertInto(T).columns(T.COL).values(i)
)
并非所有方言都支持此语法的全部内容，但幸运的是，jOOQ 可以使用 WHILE 在所有方言中模拟它
-- PL/SQL WHILE i >= 1 LOOP INSERT INTO t (col) VALUES (i); i := i - 2; END LOOP;
for_(i).in(1, 10).loop(insertInto(BOOK).columns(BOOK.ID).values(i))
翻译成以下特定方言的表达式
Aurora Postgres, CockroachDB, Exasol, Oracle, Postgres, YugabyteDB
FOR i IN 1 .. 10 LOOP INSERT INTO BOOK (ID) VALUES (i); END LOOP
BigQuery
BEGIN
DECLARE i int64 DEFAULT 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
DB2
BEGIN
DECLARE i integer;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
Firebird
DECLARE i integer DEFAULT 1; WHILE (:i <= 10) DO BEGIN INSERT INTO BOOK (ID) VALUES (:i); :i = (:i + 1); END
H2
for (Integer i = 1; i <= 10; i++) {
try (PreparedStatement s = c.prepareStatement(
"INSERT INTO BOOK (ID)\n" +
"VALUES (?)"
)) {
s.setObject(1, i);
s.execute();
}
}
Hana
BEGIN
DECLARE i integer;
FOR i IN 1 .. 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
END FOR;
END;
HSQLDB
BEGIN ATOMIC
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
Informix
BEGIN
DEFINE i integer;
LET i = 1;
FOR i IN (1 TO 10) LOOP
INSERT INTO BOOK (ID)
VALUES (i);
END LOOP;
END;
MariaDB
FOR i IN 1 .. 10 DO INSERT INTO BOOK (ID) VALUES (i); END FOR
MySQL
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
Snowflake
FOR i IN 1 TO 10 DO INSERT INTO BOOK (ID) SELECT :i; END FOR
SQLDataWarehouse
BEGIN
DECLARE @i int DEFAULT 1;
WHILE @i <= 10 BEGIN
INSERT INTO BOOK (ID)
SELECT @i;
SET @i = (@i + 1);
END;
END;
SQLServer
BEGIN
DECLARE @i int = 1;
WHILE @i <= 10 BEGIN
INSERT INTO BOOK (ID)
VALUES (@i);
SET @i = (@i + 1);
END;
END;
Trino
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END
ASE, Access, Aurora MySQL, ClickHouse, Databricks, Derby, DuckDB, MemSQL, Redshift, SQLite, Sybase, Teradata, Vertica
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听到它！