Re: code clean up help

From: eugene kim <eugene1977_at_hotmail.com>
Date: Fri, 13 Sep 2002 14:39:41 +0000
Message-ID: <als3v7$fqt$1@newsreader.mailgate.org>

thx a lot..

the code looks a lot better

i got one more question...
i tried
create table tmp_table (

        col_1 othertable.other_col_1%type );
which didn't work
is it impossible?

result code looks like this.. well current auto-indent of my editor doesn't work well --;

---

create or replace trigger games_view_trigger instead of insert on games_view for each row
declare
val schools.scid%type;
home_school_id schools.scid%type;
visitor_school_id schools.scid%type;
home_team_id teams.teamid%type;
visitor_team_id teams.teamid%type;
newrowtsc_1 schools.scname%type;
newrowtsc_2 schools.scname%type;
newrowtsport teams.sport%type;
newrowtgender teams.gender%type;
newrowgamedate games.gamedate%type;
newrowgamecity games.gamecity%type;

procedure each_team(
school_name in schools.scname%type,
school_id out schools.scid%type,
team_id out teams.teamid%type
)
is
begin
select count(*) into val
from schools s
where s.scname = school_name;

        if (val = 1) then
select scid into school_id
from schools s
where s.scname = school_name;
select count(*) into val
from teams t
where t.scid = school_id and

        t.sport = newrowtsport and
        t.gender = newrowtgender;
        if(val =1 ) then
        null;

select teamid into team_id
from teams t
where scid = school_id and

        t.sport = newrowtsport and
        t.gender = newrowtgender;
        else

select id_seq.nextval into team_id from dual; insert into teams ( teamid, scid, sport, gender ) values

( team_id, school_id , newrowtsport, newrowtgender);

        end if;
        elsif val = 0 then

select id_seq.nextval into school_id from dual; select id_seq.nextval into team_id from dual; insert into schools (scid, scname) values (school_id, school_name); insert into teams ( teamid, scid, sport, gender ) values

( team_id, school_id , newrowtsport, newrowtgender);

        else 
        null;                                                   
        end if;
        end each_team;


        begin 
        newrowtsc_1 := upper(:new.tsc_1);
        newrowtsc_2 := upper(:new.tsc_2);
        newrowtsport := upper(:new.tsport);
        newrowtgender := upper(:new.tgender);
        newrowgamedate := upper(:new.gamedate);
        newrowgamecity := upper(:new.gamecity);

select count(*) into val from games g
where (g.hometeamid = :new.visitorteamid or

        g.hometeamid = :new.hometeamid or
        g.visitorteamid = :new.hometeamid or 
        g.visitorteamid = :new.visitorteamid) and
        g.gamedate = newrowgamedate and
        g.gamecity <> newrowgamecity;

        if (val > 0) then
        dbms_output.put_line('aaaa');
        else
        dbms_output.put_line('val: ' || val);
        each_team(newrowtsc_1, home_school_id, home_team_id);
        each_team(newrowtsc_2, visitor_school_id, visitor_team_id);

        if ( (:new.visitorpoints <0) or (:new.homepoints < 0)) then
insert into games ( hometeamid, visitorteamid, gamedate, gamecity) 
        values ( home_team_id , visitor_team_id, newrowgamedate, newrowgamecity);
        else

insert into games ( hometeamid, visitorteamid, gamedate, gamecity, homepoints, visitorpoints )

        values ( home_team_id , visitor_team_id, newrowgamedate, 
        newrowgamecity, :new.homepoints, :new.visitorpoints);
        end if;
        end if;
        end;

/

show errors; Received on Fri Sep 13 2002 - 09:39:41 CDT