Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to repeat Select statements in a loop in Oracle?

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?

like image 574
Noah Martin Avatar asked Aug 06 '13 14:08

Noah Martin


People also ask

Can we use FOR LOOP in Oracle SQL query?

So, while Oracle SQL does not directly support while loops of for loops, there is extended syntax for looping within some stored procedures that are embedded into Oracle SQL.

Can we use cursor in FOR LOOP with Oracle?

Never use a cursor FOR loop if the loop body executes non-query data manipulation language (DML): INSERT, UPDATE, DELETE, MERGE. If you expect to retrieve just one row, use an implicit SELECT INTO query (which I further recommend that you place inside its own retrieval function).

Can we use loop in select statement?

The for-loop-name can be used to qualify the column names in the result set as returned by the select-statement. The cursor-name simply names the cursor that is used to select the rows from the result set.


1 Answers

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;
like image 167
Yiannis Nennes Avatar answered Sep 22 '22 00:09

Yiannis Nennes