Cheking if an entry exists in another table using cursor

Aryan Sharma 0
Viewed 95

My task is to insert entries from one table to another by using parametrized cursor. Here's what I have tried

DECLARE 
    oldroll NUMBER;
    newroll NUMBER;
    oldname VARCHAR2(25);
    newname VARCHAR2(25);
    CURSOR c_orollcall (roll_no NUMBER,name VARCHAR2) IS SELECT * FROM o_rollcall;
    PROCEDURE procedure_2;
    
PROCEDURE procedure_2 AS
    BEGIN
        OPEN c_orollcall;
        LOOP
            FETCH c_orollcall INTO oldroll ,oldname;
            SET @count = 0;
            SELECT roll_no INTO @count FROM n_rollcall WHERE EXISTS (oldroll);
            IF @count>0  THEN
                DBMS_OUTPUT.PUT_LINE('ENTRY ALREADY EXISTS');
            ELSE
                INSERT INTO n_rollcall VALUES (oldroll,oldname);
            END IF;
            EXIT WHEN c_orollcall%NOTFOUND;
        END LOOP;
        CLOSE c_orollcall;
    END;
/
BEGIN
    procedure_2;
END
/

I am getting a bunch of errors and dont' understand how to proceed further.I previously posted a question about this too but it generated more errors . here's the problem statement:

Write a PL/SQL block of code using parameterized Cursor that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall. If the data in the first table already exist in the second table then that data should be skipped.

oracle-sqldeveloper sqlplus plsql sql
1 Answers

I would suggest you to use an extra variable to store the result and check it in IF codition

 SET count = 0;
 SELECT COUNT(roll_no) INTO count FROM n_rollcall WHERE EXISTS (oldroll);

Updated code:

CREATE OR REPLACE PROCEDURE procedure_2 AS
   DECLARE count INT default 0;
    BEGIN
        OPEN c_orollcall;
        LOOP
            FETCH c_orollcall INTO oldroll ,oldname;
            SET count = 0;
            SELECT roll_no INTO count FROM n_rollcall WHERE EXISTS (oldroll);
            IF count>0  THEN
                DBMS_OUTPUT.PUT_LINE('ENTRY ALREADY EXISTS');
            ELSE
                INSERT INTO n_rollcall VALUES (oldroll,oldname);
            END IF;
            EXIT WHEN c_orollcall%NOTFOUND;
        END LOOP;
        CLOSE c_orollcall;
    END;
/
lucumt0
Related