How to repeat Select statements in a loop in Oracle?

Question

I am reading a lot of stuff about repeating Select statements within a loop but I am having some difficulties as I have not found something clear till now. I want to execute some queries (Select queries) several times, like in a FOR loop. Can anyone help with some example please?

Solution

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

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 enable output in SQL*Plus, you may need to run the command
SET SERVEROUTPUT ON

UPDATE

To insert your results in 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 a View of your results, see the example below:

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

To view your results using the view:

SELECT * FROM scott.my_view;

Comments

[Noah Martin]

Thanks for your answer. This is pretty much what I want to achieve but I found this code even in other websites and this do not work. Can you give me an example that works please?

[Yiannis Nennes]

Sorry about that, please see my updated response. Is this what you need?

[Noah Martin]

Yes thanks again, this works. One last question, hoping I am not bothering you. I want to get the output of the structure through a select statement or better I want to create a View in database that will be filled with results from the procedure. Can you help?

[Yiannis Nennes]

I have added some code in my response, does this resolve your problem?

[Noah Martin]

Thank you very much, you saved my day :)