기말고사 프로젝트 - 7 발표

delete from league where league_name='신한은행배스타리그';
drop table sinhanbank;

CREATE TABLE sinhanbank(
sin16_id number(2) CONSTRAINT sin_pk PRIMARY KEY,
sin16_group varchar2(1),
sin16_num char(1),
sin16_name varchar2(10),
sin16_main varchar2(8),
sin16_win number(3,1),
sin8_win number(1),
sin4_win number(1),
sin2_win number(1),
final_result varchar2(10)
);
 
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(1,'A',1);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(2,'A',2);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(3,'A',3);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(4,'A',4);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(5,'B',1);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(6,'B',2);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(7,'B',3);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(8,'B',4);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(9,'C',1);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(10,'C',2);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(11,'C',3);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(12,'C',4);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(13,'D',1);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(14,'D',2);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(15,'D',3);
insert into sinhanbank(sin16_id,sin16_group,sin16_num) values(16,'D',4);
 
update sinhanbank set sin16_win=0;
update sinhanbank set sin8_win=0;
update sinhanbank set sin4_win=0;
update sinhanbank set sin2_win=0;
update sinhanbank set final_result='16강진출';
select *from sinhanbank;

PL SQL
set serveroutput on

1 DB접속
exec sin_pass('이대회',3333);
exec sin_pass('이대회',4444);

2 대회개최
exec sin_insert_league('신한은행배스타리그','대전','신한은행',30000000,15000000,'개인전');

3 16명의 선수 입력
exec sin_insert_gamer('오영종','박지호','서지훈','한동욱','A');
exec sin_insert_gamer('임요환','박성준P','변형태','송병구','B');
exec sin_insert_gamer('최연성','안기효','김근백','차재욱','C');
exec sin_insert_gamer('전상욱','김성제','이병민','박성준K','D');

4 경기결과 입력
exec sin_match16('오영종','박지호','오영종','개척시대',22);
exec sin_match16('서지훈','한동욱','서지훈','러쉬아워2',31);
exec sin_match16('오영종','서지훈','서지훈','Ride_of_valkyries',15);
exec sin_match16('박지호','한동욱','박지호','Ride_of_valkyries',42);
exec sin_match16('한동욱','오영종','오영종','신815',28);
exec sin_match16('박지호','서지훈','서지훈','신815',21);

exec sin_match16('박성준P','변형태','박성준P','러쉬아워2',31);
exec sin_match16('임요환','송병구','임요환','Ride_of_valkyries',24);
exec sin_match16('송병구','변형태','송병구','신815',42);
exec sin_match16('임요환','박성준P','박성준P','신815',35);
exec sin_match16('송병구','박성준P','박성준P','개척시대',41);
exec sin_match16('변형태','임요환','임요환','개척시대',13);
 
exec sin_match16('최연성','김근백','최연성','Ride_of_valkyries',38);
exec sin_match16('차재욱','안기효','안기효','신815',28);
exec sin_match16('최연성','차재욱','최연성','개척시대',42);
exec sin_match16('김근백','안기효','김근백','개척시대',19);
exec sin_match16('안기효','최연성','최연성','러쉬아워2',30);
exec sin_match16('김근백','차재욱','김근백','러쉬아워2',33);
 
exec sin_match16('김성제','박성준K','김성제','신815',21);
exec sin_match16('전상욱','이병민','전상욱','개척시대',28);
exec sin_match16('이병민','박성준K','이병민','러쉬아워2',24);
exec sin_match16('전상욱','김성제','전상욱','러쉬아워2',32);
exec sin_match16('이병민','김성제','김성제','Ride_of_valkyries',31);
exec sin_match16('박성준K','전상욱','전상욱','Ride_of_valkyries',16);

5 16강 승점확인

exec sin_viewpoint('A');
exec sin_viewpoint('B');
exec sin_viewpoint('C');
exec sin_viewpoint('D');

6 8강 조별로 2명씩 입력

exec sin_go8('서지훈','오영종','A');
exec sin_go8('박성준P','임요환','B');
exec sin_go8('최연성','김근백','C');
exec sin_go8('전상욱','김성제','D');

8강 순서대로 경기입력
exec sin_match8('오영종','서지훈','오영종','신815',21);
exec sin_match8('오영종','서지훈','서지훈','개척시대',28);
exec sin_match8('오영종','서지훈','서지훈','러쉬아워2',24);
exec sin_match8('임요환','박성준P','박성준P','러쉬아워2',32);
exec sin_match8('임요환','박성준P','임요환','개척시대',17);
exec sin_match8('임요환','박성준P','박성준P','Ride_of_valkyries',22);
exec sin_match8('최연성','김근백','최연성','Ride_of_valkyries',31);
exec sin_match8('최연성','김근백','김근백','개척시대',28);
exec sin_match8('최연성','김근백','김근백','신815',21);
exec sin_match8('전상욱','김성제','김성제','Ride_of_valkyries',16);
exec sin_match8('전상욱','김성제','전상욱','개척시대',28);
exec sin_match8('전상욱','김성제','전상욱','러쉬아워2',24);

7 조별로 1명씩 4강으로 입력

exec sin_go4('A');
exec sin_go4('B');
exec sin_go4('C');
exec sin_go4('D');

exec sin_match4('서지훈','박성준P','서지훈', '신815',21);
exec sin_match4('서지훈','박성준P','박성준P','개척시대',28);
exec sin_match4('서지훈','박성준P','서지훈','러쉬아워2',24);
exec sin_match4('서지훈','박성준P','박성준P','러쉬아워2',32);
exec sin_match4('서지훈','박성준P','서지훈','개척시대',17);
 
exec sin_match4('전상욱','김근백','전상욱','Ride_of_valkyries',31);
exec sin_match4('전상욱','김근백','김근백','개척시대',28);
exec sin_match4('전상욱','김근백','김근백','신815',21);
exec sin_match4('전상욱','김근백','전상욱','Ride_of_valkyries',16);
exec sin_match4('전상욱','김근백','전상욱','개척시대',28);

8 2명 결승으로 입력

exec sin_go2('A','B');
exec sin_go2('C','D');

exec sin_match2('전상욱','서지훈','전상욱','Ride_of_valkyries',31);
exec sin_match2('전상욱','서지훈','서지훈','개척시대',28);
exec sin_match2('전상욱','서지훈','서지훈','신815',21);
exec sin_match2('전상욱','서지훈','전상욱','Ride_of_valkyries',16);
exec sin_match2('전상욱','서지훈','전상욱','개척시대',28);

9 우승자 준우승자 입력

exec sin_final;

10 대회총결과

exec sin_info;