I have two select, how can I do the same using a loop and a variable in PL SQL Developer. I have:
select 1 as nm from dual;
select 2 as nm from dual;
I need something similar:
DECLARE x NUMBER;
BEGIN
FOR X IN 1..2
LOOP
EXECUTE IMMEDIATE ('SELECT ' || X || ' AS NM FROM DUAL;');
END LOOP;
END;
In PL SQL Developer, each select is displayed in a separate tab:
If you need to join the result of two queries you can use UNION ALL clause:
select 1 as nm from dual
union all
select 2 as nm from dual;
It would be additional DISTINCT step using UNION without ALL. We don't need this in this case.
If you need to generate record set with subsequential records, good solution is to "misuse" CONNECT BY clause:
select level nm from dual connect by level <= 2; -- Instead of 2 may be any number
But this structure is not efficient in large scale. Let say you have to generate 10 millions records. In this case the best solution is to use CARTESIAN JOIN on smaller subqueries:
select rownum rn
from
(select level rn from dual connect by level <= 1000) t1,
(select level rn from dual connect by level <= 1000) t2,
(select level rn from dual connect by level <= 10) t3;
Total rowcount will be multiplication the rowcounts of all subqeries: 1,000 * 1,000 * 10 = 10,000,000.
