For loop in Oracle SQL [closed]

Question

I am new to Oracle and I am unaware about using for loop in Oracle SQL (not PL/SQL).

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?

Answer 1

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:

-- 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
/