Stored Procedure with multiple statements?

Plural · #1 (**permalink**) 12-07-11, 12:22

I would like to know if it is possible to include all these statements within one stored procedure.

Procedure name: sp_transferB
Parameters (first half for inserting into Patient and second half for inserting into Pat_Doc_Treat):

Code:

(par_patientNumbA CHAR, par_patientNumb CHAR, par_SS CHAR, par_patientName VARCHAR2,
par_dateOfBirth DATE, par_address VARCHAR2, par_dateAdmitted DATE,
par_doctorID CHAR, par_treatmentCode CHAR, par_treatmentDate DATE, par_comments VARCHAR2)

Code:

INSERT INTO Patient@B
	VALUES
	(par_patientNumb, par_SS, par_patientName, par_dateOfBirth, par_address, par_dateAdmitted, 'B');
		
	INSERT INTO Pat_Doc_Treat@B
	VALUES
	(par_patientNumb, par_doctorID, par_treatmentCode, par_treatmentDate, par_comments)
		
	DELETE FROM Pat_Doc_Treat
	WHERE patientNumb IN
	(SELECT patientNumb FROM Patient
	WHERE patientNumb=par_patientNumbA);
		
	DELETE FROM Patient
	WHERE patientNumb=par_patientNumbA;

And if there is a failure, there would be an EXCEPTION for ROLLBACK.

Assume that this procedure is used by a nurse/receptionist to transfer a patient from one clinic to another.

I was going to using the
INSERT INTO Patient Select ... but it wouldn't change the clinic to 'B' and the PatientNumb is a primary key so it can't be NULL.

Also, if the procedures were created separate...would there be any of the phenomena such as "dirty read", "fuzzy read", "phantom"?

Internet Services

Web Development

Digital Marketing

Consumer Tech