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?
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.
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).
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.
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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With