Hi there,
I am using PL/SQL developer and the database I am using is Oracle 11G R2 database with Visual Studio 2013. I use ODP.net as well.
Currently i load data for a set of Students using a stored proc e.g ClassNumber, Ethnicity etc. The data I load is dependent on what has been requested by the end user.
After i have loaded the data for the respctive students I would like to log the data for the requested students. Now currently the only way i know how to do it is to do a database call for each requested student by passing in a single StudentBioId and pass in the array of StudentBioIdentifiers. A StudentBioIdentifier is an identifier relating to the student information e.g ClassNumber, Ethnicity etc.
My original idea was to pass an array of StudentIds and array of StudentBioIds into the stored procedure but i wouldn't be able to relate the StudentBioId back to the Student because Student A may have Ethnicity value of XYZ but Student B could have ABC Ethnicity value. How can i work out which StudentBioId belongs to that Student without doing heavy select queries?
My goal is to make the data insertions faster, insert data correctly and potentially reduce database calls.
So currently the code in my stored proc looks like this:
CREATE OR REPLACE TYPE StudentBioIdArray IS VARRAY(100) OF NUMBER(2);
PROCEDURE InsertDummyStudentAuditData(studentID IN NUMBER, studentBioIds IN StudentBioIdArray) IS
BEGIN
IF studentBioIds IS NOT NULL AND studentBioIds.Count > 0 THEN
FORALL i IN 1..studentBioIds.COUNT
INSERT INTO audit_table(Id, Student_id, StudentBioId)
VALUES (-1, studentID, studentBioIds(i));
END IF;
END InsertDummyStudentAuditData;
Questions
Even if i call the stored procedure for each student would it still be faster?
Is there a better alternative?