How to Run the Same Query Multiple Times Using Loop in Pl/Sql

How can you run the same query multiple times using loop in PL/SQL?

The substitution variables &counter, &id and &name are each evaluated once, when the PL/SQL block is compiled - not as it is being executed.

The variables are not, and cannot be, re-evaluated or re-promoted within the PL/SQL block. The block is executed as a single unit within the database - once it has been submitted for execution it is independent of the client, which just waits for it to complete (unless you interrupt it, which the client also handles). PL/SQL is not an interactive language, and you shouldn't confuse client functionality (e.g. substitution variables) with SQL or PL/SQL functionality.

---

Just for fun, you could generate a script based on counter which does the appropriate number of prompts for IDs and names, and gets them into a format that could be used by a simple insert:

set serveroutput on
set feedback off
set echo off
set verify off
set termout off

accept counter "How many value pairs do you want to insert?"

var ids varchar2(4000);
var names varchar2(4000);

spool /tmp/prompter.sql

begin
  -- prompt for all the value pairs
  for i in 1..&counter loop
    dbms_output.put_line('accept id' ||i|| ' number  "Enter ID ' ||i|| '"');
    dbms_output.put_line('accept name' ||i|| '  char "Enter name ' ||i|| '"');
  end loop;

  -- concatenate the IDs into one variable
  dbms_output.put('define ids="');
  for i in 1..&counter loop
    if i > 1 then
      dbms_output.put(',');
    end if;
    dbms_output.put('&'||'id'||i);
  end loop;
  dbms_output.put_line('"');

  -- concatenate the names into one variable
  dbms_output.put('define names="');
  for i in 1..&counter loop
    if i > 1 then
      dbms_output.put(',');
    end if;
    -- each name wrapped in single quotes
    dbms_output.put(q'['&]'||'name'||i||q'[']');
  end loop;
  dbms_output.put_line('"');
end;
/
spool off

@/tmp/prompter

insert into customer (id, name)
select i.id, n.name
from (
  select rownum as rid, column_value as id 
  from table(sys.odcinumberlist(&ids))
) i
join (
  select rownum as rid, column_value as name
  from table(sys.odcivarchar2list(&names))
) n
on n.rid = i.rid;

select * from customer;

That creates a file called prompter.sql (I've put it in /tmp; put it somewhere suitable for your environment!); with the 'number of value pairs' prompt answered as 2 that temporary script would look contain:

accept id1 number  "Enter ID 1"
accept name1  char "Enter name 1"
accept id2 number  "Enter ID 2"
accept name2  char "Enter name 2"
define ids="&id1,&id2"
define names="'&name1','&name2'"

That temporary script is then run with @, prompting the user for all those individual values. And then table collections built from the combined substitution variables are used in a select, which is used by the insert.

Loop through multiple tables to execute same query

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

basically, inside your loop, build a string representing the query you want to run, and use dynamic SQL to run the query.

To do anything useful, you're probably going to want to insert the records into a temporary table, then select ordered by by date descending.

How to repeat Select statements in a loop in Oracle?

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;

---

Related Topics