Re: How create stored procedure (or package).....

db_senior wrote:

> hi,
> I have to create a stored procedure (or package) in order to reserve
> the free rooms to the students in the period comprised between the
> DATE_START and DATE_END.
>
> step1: verify if the student is in table em:
> if the student is not in table em, then insert new student
>
> step2: find available room
> if find it reserve room INTO rmpct
>
> step3:verify if there are free double or triple rooms (D or T) between
> those occupied in that period.
> if find it reserve room INTO rmpct
>
> step4: update students in em
>
> step5: update sex of room or apartment floor in rm table.
>
> step6: if no room is available raise error
>
>
> I tried this:
>
> Have you any idea??
>
> Thanks in advance!

We don't do homework but we do give hints.

1. This is a simple IF/ELSE situation.
2. Seems like another IF/ELSE based on a count.
3. Looks like #2 with a different WHERE clause
4. One update statement
5. Another update statement
6. Create a user-defined exception in the DECLARE section and RAISE it.

Looks like about 30-60 minutes worth of work if you've been paying attention in class.

We are not going to do it for you so tackle each of the 6 pieces individually and post questions about each piece separately along with your code.

Later on assemble the six pieces into a proc.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)