Oracle PL/SQL for loop for fetch & insert to temp table

Question

Here is my first table. it has start_serial column and end_serial column (got from calculate)

**START_SERIAL  END_SERIAL**
120500216057150 120500216057155
120500216057156 120500216057161

**DENOMINATION  SERIAL_NO           AGENT_NAME  SUB_AGENT**
100          121000056003871    NALINR            A
100          121000056003855    NALINR            A
100          121000056003860    NALINR            A
100          121000056003858    NALINR            A
100          121000056003853    NALINR            A

here is my second table.it has all serials one by one

now i need to write pl/sql for below scenario 1. select one record from sales_details [1st Table] ex :- 120500216057148 120500216057150 [2 cards]

then need to check if they exist in table 2 ex :- 1st check 120500216057148 to 120500216057150 one by one then check next record ..

like that :-)

3.if exist insert to temp_data_table if not message for dbms_output no matching records found.

Please help me to sort out this issue

Best if you remove the screenshots and provide us with table DDLs and some sample data, best on SQLFiddle, with expected result. — Przemyslaw Kruglej
– Przemyslaw Kruglej, Commented Nov 17, 2013 at 16:40

Mina Tadros · Accepted Answer · 2013-11-17 22:26:47Z

this is oracle example to make for loop on the data from select statement and process each record. You can customize this example as your needs. Check also this site for more examples http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/a_samps.htm

-- available online in file 'sample2'
DECLARE
   CURSOR c1 is
       SELECT ename, empno, sal FROM emp
       ORDER BY sal DESC;   -- start with highest paid employee
   my_ename VARCHAR2(10);
   my_empno NUMBER(4);
   my_sal   NUMBER(7,2);
BEGIN
   OPEN c1;
   FOR i IN 1..5 LOOP
      FETCH c1 INTO my_ename, my_empno, my_sal;
      EXIT WHEN c1%NOTFOUND;  /* in case the number requested */
                          /* is more than the total       */
                          /* number of employees          */
      INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
      COMMIT;
   END LOOP;
   CLOSE c1;
END;

Hamidreza · Accepted Answer · 2013-11-18 05:47:12Z

1

try this query:

insert into temp select * from table1 where START_SERIAL in 
(select START_SERIAL from table2);

answered Nov 18, 2013 at 5:47

Hamidreza

3,1581 gold badge20 silver badges15 bronze badges

1 Comment

Priyan RockZ Over a year ago

no dear Hamidreza.its need to iterate start to end serial one by one

Collectives™ on Stack Overflow

Oracle PL/SQL for loop for fetch & insert to temp table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related