Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Stored Procedure
Hi
I'm attempting to write a procedure and I'd like to know if I'm on the right track - here's the scenario :-
Create a stored procedure - Invite_to_workshop. It should verify that the student is currently enrolled on that module and has not already attended that workshop. Assume that if the Invitation_acceptance_flag column is set to 1, i.e TRUE, that the student has attended the workshop. If the column is set to zero then the invitation was turned down, and a NULL indicates that a response has not yet been recieved.
Then use Get_workshop (a function - already written) to retrieve an available workshop. If a workshop is available an invitation should be inserted into the database and the invitation be sent, otherwise an error should be returned as a parameter of the procedure. Assume Send_invitation exists, for physically sending the invitations, and expects an input of an invitation letter number.
So far I have this idea for the control structure.
Create procedure Invite_to_workshop (Student_id NUMBER, Invitation_acceptance_flag NUMBER) -- Would I declare the Get_workshop function and send_invitation here also?
BEGIN
IF student.student_id = invitation.student_id AND Invitation.invitation_acceptance_flag = 1 THEN END; ELSE (IF?) student.student_id = invitation.student_id AND Invitation.invitation_acceptance_flag = 0THEN END;
WHEN NO_DATA_FOUND THEN RAISE/CALL? get_workshop -- returns a workshop_number if oneexists, else returns 99999
Can I have IF THEN ELSE logic in the exception handler or could I carry on the ELSE IF to go through the logic of - IF get_workshop = true THEN CALL/RAISE Send_invitation ELSE RETURN 'error message'.
Any help is greatly appreciated as I'm getting more and more confused the longer I think about it :)
Regards
Barry Received on Fri Nov 01 2002 - 05:03:08 CST