| William 2002-06-08, 2:16 am |
| hi everyone,
I am practicing PL/SQL. I have no problem of understanding the concepts but when I try to type it in, I find myself in a very mess situation. I would be appreciated if anyone can answer the following questions.
1. Record of a result to an event given by its Id.
Type athlete: <lastname: string, firstname: string>
RecordResult ( e: integer, g, s, b: athlete)
TheAthletes (aid, firstname, lastname, country)
TheEnrolls ( aid, eid)
TheResults ( eid, gold, silver, bronze) /*gold, sliver, bronze TheAthletes.aid%TYPE*/
Create OR REPLACE PACKAGE pkg AS
TYPE athlete IS RECORD (firstname TheAthletes%TYPE,
lastname TheAthletes%TYPE)
PROCEDURE RecordResults(eventId TheEnrolls.eid%TYPE,
g athlete,
s athlete,
b athlete) IS
currentEid TheEnrolls.eid%TYPE;
currentGid TheResults.gold%TYPE;
currentSid TheResults.silver%TYPE;
currentBid TheResults.bronze%TYPE;
sameAthleteInputs exception;
cons_violate exception;
pragma exception_init(cons_violate,-00001);
BEGIN
select A1.aid into currentGid
from TheEnrolls En1, TheAthletes A1
where En1.aid = A1.aid and A1.firstname=g.firstname and A1.lastname = g.lastname;
select A2.aid into currentSid
from TheEnrolls En2, TheAthletes A2
where En2.aid = A2.aid and A2.firstname=s.firstname and A2.lastname = s.firstname;
select A3.aid into currentBid
from TheEnrolls En3, TheAthletes A3
where En3.aid = A3.aid and A3.firstname=b.firstname and A3.lastname = s.firstname;
select Ev1.eid into currentEid
from TheEvents Ev1
where Ev1.eid = eventId;
/*
select En1.eid into currentEid
from TheEnrolls En1
where En1.aid = currentGid;
select En2.eid into currentEid
from TheEnrolls En2
where En2.aid = currentSid;
select En3.eid into currentEid
from TheEnrolls En3
where En3.aid = currentBid;
*/
if currentGid = currentBid
raise sameAthleteInputs
if currentBid = currentSid
raise sameAthleteInputs
if currentGid = currentSid
raise sameAthleteInputs
insert into TheResults(eid, gold, silver, bronze)
values( currentEid, currentGid, currentSid, currentBid);
commit;
exception
WHEN no_data_found THEN
raise_application_error (-20101, 'unknown inputs');
WHEN cons_violate THEN
dbms_output.put_line('must be unique');
WHEN sameAthleteInputs THEN
dbms_output.put_line('same Athletes input');
end;
/
Question1: when I try to execute it, how can I pass value in? I also want to separate out the exceptions (this implementation will only raise no_data_found exception, the sameAthleteInputs doesn・t work. why?). The section I commented out because I don・t know how to implement it. If possible can you please give me a better implementation.
Question2: how can I extract only one row (one data)
Eg. If I have two tables and a schema
TheAccomodations (aid, uid)
TheAthletes
Aid | country
M12| c
M55| h
F66| e
F55| c
M45| c
M44| h
M90| c
TheAccomodations
Aid | uid
M12| u1
M45| u1
M90| u1
M55| u2
M44| u2
If I want to know the uid as you can see for people coming from the same country they have the same uid. So, any of the Mxx is fine but if I do:
Select aid
From TheAthletes
Where country = ．c・
Will give me all people from the same country. The only way I can think of is to use ：distinct；, is this the only way
 |