PL/SQL: How to insert depending on column value

user_5121348 0
Viewed 77

I'm a novice at PL/SQL. I have attempted various approaches to use a Cursor to insert into a temp table depending on whether or not the value already exists in the temp table. I either get too many rows or nothing is inserted. This is my last pseudocode approach and is the bare essence of what I'm attempt to accomplish: DB: Oracle 12 Using SQL Developer Goal: Take duplicate accountno info from table1 and merge / combine into single row in temptable 1. Add initial accountno info if it doesn’t already exists in temptable 2. If accountno exists in temptable add the additional info to accountno row

Suggestions are greatly appreciated.

Pseudocode

Declare
V_cnt number (20);
CURSOR c1 is select * from table1;
C1d c1%rowtype;
BEGIN
--
OPEN C1; 
        LOOP           
            FETCH C1 INTO c1d;
            EXIT WHEN C1%NOTFOUND;
--    Limit attempts
            IF LINE > 5 THEN EXIT; END IF;

 select accountno INTO v_cnt from table1 where Exists(select 1 from temptable where accountno <> c1d.accountno);                

           IF v_cnt is NULL THEN
            INSERT INTO temptable (accountno)
                values(c1d.accountno);
           END IF;

            LINE:= LINE + 1;

            END LOOP;

CLOSE C1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line ('NO DATA');   
END;
sql-insert oracle-sqldeveloper plsql oracle
1 Answers

If you strictly want to correct your pseudo code, You may try -

Declare
       V_cnt number (20);
       CURSOR c1 is  select * from table1;
       C1d c1%rowtype;
BEGIN
--
     OPEN C1; 
          LOOP           
              FETCH C1 INTO c1d;
              EXIT WHEN C1%NOTFOUND;
--    Limit attempts
              IF LINE > 5 THEN
                 EXIT;
              END IF;
              BEGIN
                   SELECT accountno
                     INTO V_cnt
                     FROM temptable
                    WHERE accountno = c1d.accountno
                      AND ROWNUM = 1;
              EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                            V_cnt := NULL;
              END;

              IF V_cnt is NULL THEN
                 INSERT INTO temptable (accountno)
                                 values(c1d.accountno);
              END IF;

              LINE:= LINE + 1;

          END LOOP;
     CLOSE C1;
EXCEPTION
         WHEN NO_DATA_FOUND THEN
              dbms_output.put_line ('NO DATA');   
END;

I would strongly recommend to use below pseudo code -

Declare
       V_cnt number (20);
       CURSOR c1 is  select * from table1;
       C1d c1%rowtype;
BEGIN
--
     OPEN C1; 
          LOOP           
              FETCH C1 INTO c1d;
              EXIT WHEN C1%NOTFOUND;
--    Limit attempts
              IF LINE > 5 THEN
                 EXIT;
              END IF;
              MERGE INTO temptable
              USING table1
              ON (accountno = c1d.accountno)
              WHEN NOT MATCHED THEN
                               INSERT (accountno)
                               values(c1d.accountno);
              LINE:= LINE + 1;

          END LOOP;
     CLOSE C1;
EXCEPTION
         WHEN NO_DATA_FOUND THEN
              dbms_output.put_line ('NO DATA');   
END;
user_36277560
Related