1.--创建过程 create or replace procedure pro_cg(gen in varchar2) as begin for cur1 in (select * from computergame where genre=gen) loop dbms_output.put_line(cur1.id||' '||cur1.title||' '||cur1.genre ||' '||cur1.release_date||' '||cur1.publisher); end loop; end pro_cg;--调用 begin pro_cg('Family'); end;
declare v_genre computergame.genre%type := &genre; v_flag number := 0; begin select count(*) into v_flag from genre where type = v_genre; if v_flag > 0 then for i in (select * from computergame where genre = v_genre) loop dbms_output.put_line(i.id || ' '||i.title||' '||i.genre||' '||i.release_date||' '||i.publisher); end loop; else dbms_output.put_line('No game found'); end if; end; /alert table computergame add (last_sold_date date default sysdate);create or replace trigger tri_test after insert on purchase for each row begin update computergame set last_sold_date = :NEW.purchase_date where id = :NEW.game_id; end; /
2. --添加字段 alter table computergame add last_sold_date date;--创建触发器 create or replace trigger tg_lastsold after insert on purchase for each row begin update computergame set last_sold_date=:new.purchase_date where id=:new.game_id; end tg_lastsold;
create or replace procedure pro_cg(gen in varchar2)
as
begin
for cur1 in (select * from computergame where genre=gen)
loop
dbms_output.put_line(cur1.id||' '||cur1.title||' '||cur1.genre
||' '||cur1.release_date||' '||cur1.publisher);
end loop;
end pro_cg;--调用
begin
pro_cg('Family');
end;
v_genre computergame.genre%type := &genre;
v_flag number := 0;
begin
select count(*) into v_flag from genre where type = v_genre;
if v_flag > 0 then
for i in (select * from computergame where genre = v_genre)
loop
dbms_output.put_line(i.id || ' '||i.title||' '||i.genre||' '||i.release_date||' '||i.publisher);
end loop;
else
dbms_output.put_line('No game found');
end if;
end;
/alert table computergame add (last_sold_date date default sysdate);create or replace trigger tri_test
after insert on purchase for each row
begin
update computergame set last_sold_date = :NEW.purchase_date where id = :NEW.game_id;
end;
/
--添加字段
alter table computergame add last_sold_date date;--创建触发器
create or replace trigger tg_lastsold
after insert
on purchase
for each row
begin
update computergame set last_sold_date=:new.purchase_date
where id=:new.game_id;
end tg_lastsold;