Oracle SQL 中的 for 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15012247/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
For loop in Oracle SQL
提问by user964147
I am new to Oracle and I am unaware about using for loop in Oracle SQL (not PL/SQL).
我是 Oracle 的新手，我不知道在 Oracle SQL（不是 PL/SQL）中使用 for 循环。
I had a requirement to increase the number by +1 in the query and execute the statements; is it possible to use a for loop in SQL?
我需要在查询中将数字增加 +1 并执行语句；是否可以在 SQL 中使用 for 循环？
回答by Art
You are pretty confused my friend. There are no LOOPS in SQL, only in PL/SQL. Here's a few examples based on existing Oracle table - copy/paste to see results:
你很困惑我的朋友。SQL 中没有 LOOPS，只有 PL/SQL 中才有。以下是一些基于现有 Oracle 表的示例 - 复制/粘贴以查看结果：
-- Numeric FOR loop --
set serveroutput on -->> do not use in TOAD --
DECLARE
k NUMBER:= 0;
BEGIN
FOR i IN 1..10 LOOP
k:= k+1;
dbms_output.put_line(i||' '||k);
END LOOP;
END;
/
-- Cursor FOR loop --
set serveroutput on
DECLARE
CURSOR c1 IS SELECT * FROM scott.emp;
i NUMBER:= 0;
BEGIN
FOR e_rec IN c1 LOOP
i:= i+1;
dbms_output.put_line(i||chr(9)||e_rec.empno||chr(9)||e_rec.ename);
END LOOP;
END;
/
-- SQL example to generate 10 rows --
SELECT 1 + LEVEL-1 idx
FROM dual
CONNECT BY LEVEL <= 10
/
回答by MarianP
You will certainly be able to do that using WITH clause, or use analytic functionsavailable in Oracle SQL.
您当然可以使用WITH 子句或使用Oracle SQL 中可用的分析函数来做到这一点。
With some effort you'd be able to get anything out of them in terms of cycles as in ordinary procedural languages. Both approaches are pretty powerful compared to ordinary SQL.
通过一些努力，您可以像在普通程序语言中一样从循环方面得到任何东西。与普通 SQL 相比，这两种方法都非常强大。
http://www.dba-oracle.com/t_with_clause.htm
http://www.dba-oracle.com/t_with_clause.htm
It requires some effort though. Don't be afraid to post a concrete example.
但这需要一些努力。不要害怕发布一个具体的例子。
Using simple pseudo table DUAL helps too.
使用简单的伪表 DUAL 也有帮助。
