Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Stored Procedure

Stored Procedure

From: Barry Warner <bwarner_at_blueyonder.co.uk>
Date: Fri, 1 Nov 2002 11:03:08 -0000
Message-ID: <wbtw9.8187$dv6.2058@news-binary.blueyonder.co.uk>


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 Received on Fri Nov 01 2002 - 05:03:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US