How to repeat the selection of statements in a loop in Oracle?

Question

I read a lot of things about repeating Select statements in a loop, but I'm having some difficulties, since so far I have not found anything clear. I want to execute several queries (Select queries) several times, for example, in a FOR loop. Can someone help with some example please?

Answer 1

The basic structure of what you are asking can be seen below. Please provide additional information for a more specific code example.

DECLARE l_output NUMBER; BEGIN FOR i IN 1..10 LOOP SELECT 1 INTO l_output FROM dual; DBMS_OUTPUT.PUT_LINE('Result: ' || l_output); END LOOP; END; 

PS: If you need to include output in SQL * Plus, you may need to run the command
SET SERVEROUTPUT ON

UPDATE

To paste the results into another table:

 DECLARE -- Store the SELECT query in a cursor CURSOR l_cur IS SELECT SYSDATE DT FROM DUAL; --Create a variable that will hold each result from the cursor l_cur_rec l_cur%ROWTYPE; BEGIN -- Open the Cursor so that we may retrieve results OPEN l_cur; LOOP -- Get a result from the SELECT query and store it in the variable FETCH l_cur INTO l_cur_rec; -- EXIT the loop if there are no more results EXIT WHEN l_cur%NOTFOUND; -- INSERT INTO another table that has the same structure as your results INSERT INTO a_table VALUES l_cur_rec; END LOOP; -- Close the cursor to release the memory CLOSE l_cur; END; 

To create an idea of ​​your results, see the example below:

 CREATE VIEW scott.my_view AS SELECT * FROM scott.emp; 

To view results using a view:

 SELECT * FROM scott.my_view;