Re: Stored Procedure

Barry Warner wrote:

> 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 = 0
> THEN END;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> RAISE/CALL? get_workshop -- returns a workshop_number if one
> exists, 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

I think you need to spend some time clarifying your thinking. Your ELSE statement is irrelevant.

You should also either study more, or take more care, in your syntax.

Daniel Morgan Received on Fri Nov 01 2002 - 12:21:52 CST