Jijo K Jose

By using PL/SQL we can execute multiple queries at a time. The following example is used to insert student details from STUDENT_TEMP table to STUDENT table and if any error occurred during the insertion process ( like primary key violation, unique key violation etc ) then the corresponding errors will be captured by the EXCEPTION block and will continue the execution.

create or replace
PROCEDURE STUDENT_ADD_PL AS
BEGIN
    FOR REC IN
    (
    SELECT    
        STUDENT_ID,
        ROLL_NO,
        STUDENT_CODE,
        CLASS_ID,
        COLLEGE_NAME,
        PLACE
    FROM STUDENT_TEMP
    )
    LOOP
        BEGIN
            Insert into STUDENT
            (
                STUDENT_ID,
                ROLL_NO,
                STUDENT_CODE,
                CLASS_ID,
                COLLEGE_NAME,
                PLACE

            ) values (
                NEW_STUDENT_SEQ.nextval,
                ROLL_NO,
                STUDENT_CODE,
                CLASS_ID,
                COLLEGE_NAME,
                PLACE
            );

        COMMIT;
        EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
            NULL;
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
            NULL;
        END;
    END LOOP;
END STUDENT_ADD_PL;