Re: code clean up help

Martin Doherty wrote:

> Nope, the %type construct is purely a PL/SQL syntax and is not valid in DDL. You have
> to explicitly state the column type.
>
> Martin Doherty
>
> eugene kim wrote:
>
> > 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;

One of us misunderstood the original question ... I think it was me.

Daniel Morgan Received on Fri Sep 13 2002 - 12:53:41 CDT