Oracle DBMS Maze Game

create table stage1(rid varchar2(3), c1 varchar2(1), c2 varchar2(1), c3 varchar2(1), c4 varchar2(1), c5 varchar2(1), c6 varchar2(1), c7 varchar2(1), c8 varchar2(1), c9 varchar2(1), c10 varchar2(1), c11 varchar2(1), c12 varchar2(1), c13 varchar2(1), c14 varchar2(1), c15 varchar2(1), c16 varchar2(1), c17 varchar2(1), c18 varchar2(1), c19 varchar2(1), c20 varchar2(1)); insert into stage1 values('1', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w','w','w'); insert into stage1 values('2', 'w', 'p', 'w', 'w', ' ', ' ', ' ', 'a', ' ', ' ', ' ', ' ', 'm', ' ', ' ', 'w', '0', '0','0','w'); insert into stage1 values('3', 'w', ' ', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('4', 'w', ' ', 'w', 'w', ' ', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('5', 'w', ' ', 'w', 'w', ' ', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', 'a', 'w', '0', '0','0','w'); insert into stage1 values('6', 'w', ' ', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('7', 'w', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 'w', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('8', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'a', 'w', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('9', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', ' ', 'w', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('10', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', ' ', 'w', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('11', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', ' ', 'w', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('12', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', ' ', 'w', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('13', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', 'm', 'w', '0', '0', 'w', ' ', 'w', '0', '0','0','w'); insert into stage1 values('14', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', ' ', 'w', 'w', 'w', 'w', ' ', 'w', 'w', 'w','w','w'); insert into stage1 values('15', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', ' ', ' ', ' ', 'a', ' ', ' ', ' ', 'a', ' ',' ','w'); insert into stage1 values('16', 'w', '0', '0', '0', '0', '0', '0', '0', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w',' ','w'); insert into stage1 values('17', 'w', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 'w',' ','w'); insert into stage1 values('18', 'w', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 'w',' ','w'); insert into stage1 values('19', 'w', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 'w','g','w'); insert into stage1 values('20', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w','w','w'); create table stage2(rid varchar2(3), c1 varchar2(1), c2 varchar2(1), c3 varchar2(1), c4 varchar2(1), c5 varchar2(1), c6 varchar2(1), c7 varchar2(1), c8 varchar2(1), c9 varchar2(1), c10 varchar2(1), c11 varchar2(1), c12 varchar2(1), c13 varchar2(1), c14 varchar2(1), c15 varchar2(1), c16 varchar2(1), c17 varchar2(1), c18 varchar2(1), c19 varchar2(1), c20 varchar2(1)); insert into stage2 values('1', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w','w','w'); insert into stage2 values('2', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w','w','w'); insert into stage2 values('3', 'w', 'w', 'w', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 'w','w','w'); insert into stage2 values('4', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('5', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('6', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('7', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('8', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('9', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('10', 'w', 'p', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('11', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('12', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('13', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'a', 'w', 'w', 'w', 'w', 'w', 'm', 'w','w','w'); insert into stage2 values('14', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('15', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('16', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('17', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', ' ', 'w','w','w'); insert into stage2 values('18', 'w', 'w', 'w', 'w', 'w', 'w', 'g', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w','w','w'); insert into stage2 values('19', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w','w','w'); insert into stage2 values('20', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w', 'w','w','w'); -- c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 create table player1 as (select * from stage1); create or replace procedure goLeft(a in number) is cursor rowofplayer is select * from player1 where c1 = 'p' or c2 = 'p' or c3 = 'p' or c4 = 'p' or c5 = 'p' or c6 = 'p' or c7 = 'p' or c8 = 'p' or c9 = 'p' or c10 = 'p' or c11 = 'p' or c12 = 'p' or c13 = 'p' or c14 = 'p' or c15 = 'p' or c16 = 'p' or c17 = 'p' or c18 = 'p' or c19 = 'p' or c20 = 'p'; rowdata player1%rowtype; varCurrentStage varchar2(1); varNewStage varchar2(1); username varchar2(20); begin for rowdata in rowofplayer loop if rowdata.c3 = 'p' and rowdata.c2 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c3 = 'p' and rowdata.c2 <> 'w' then update player1 set c2 = 'p', c3 = ' ' where rid = rowdata.rid; elsif rowdata.c4 = 'p' and rowdata.c3 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c4 = 'p' and rowdata.c3 <> 'w' then update player1 set c3 = 'p', c4 = ' ' where rid = rowdata.rid; elsif rowdata.c5 = 'p' and rowdata.c4 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c5 = 'p' and rowdata.c4 <> 'w' then update player1 set c4 = 'p', c5 = ' ' where rid = rowdata.rid; elsif rowdata.c6 = 'p' and rowdata.c5 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c6 = 'p' and rowdata.c5 <> 'w' then update player1 set c5 = 'p', c6 = ' ' where rid = rowdata.rid; elsif rowdata.c7 = 'p' and rowdata.c6 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c7 = 'p' and rowdata.c6 <> 'w' then update player1 set c6 = 'p', c7 = ' ' where rid = rowdata.rid; elsif rowdata.c8 = 'p' and rowdata.c7 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c8 = 'p' and rowdata.c7 <> 'w' then update player1 set c7 = 'p', c8 = ' ' where rid = rowdata.rid; elsif rowdata.c9 = 'p' and rowdata.c8 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c9 = 'p' and rowdata.c8 <> 'w' then update player1 set c8 = 'p', c9 = ' ' where rid = rowdata.rid; elsif rowdata.c10 = 'p' and rowdata.c9 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c10 = 'p' and rowdata.c9 <> 'w' then update player1 set c9 = 'p', c10 = ' ' where rid = rowdata.rid; elsif rowdata.c11 = 'p' and rowdata.c10 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c11 = 'p' and rowdata.c10 <> 'w' then update player1 set c10 = 'p', c11 = ' ' where rid = rowdata.rid; elsif rowdata.c12 = 'p' and rowdata.c11 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c12 = 'p' and rowdata.c11 <> 'w' then update player1 set c11 = 'p', c12 = ' ' where rid = rowdata.rid; elsif rowdata.c13 = 'p' and rowdata.c12 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c13 = 'p' and rowdata.c12 <> 'w' then update player1 set c12 = 'p', c13 = ' ' where rid = rowdata.rid; elsif rowdata.c14 = 'p' and rowdata.c13 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c14 = 'p' and rowdata.c13 <> 'w' then update player1 set c13 = 'p', c14 = ' ' where rid = rowdata.rid; elsif rowdata.c15 = 'p' and rowdata.c14 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c15 = 'p' and rowdata.c14 <> 'w' then update player1 set c14 = 'p', c15 = ' ' where rid = rowdata.rid; elsif rowdata.c16 = 'p' and rowdata.c15 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c16 = 'p' and rowdata.c15 <> 'w' then update player1 set c15 = 'p', c16 = ' ' where rid = rowdata.rid; elsif rowdata.c17 = 'p' and rowdata.c16 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c17 = 'p' and rowdata.c16 <> 'w' then update player1 set c16 = 'p', c17 = ' ' where rid = rowdata.rid; elsif rowdata.c18 = 'p' and rowdata.c17 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c18 = 'p' and rowdata.c17 <> 'w' then update player1 set c17 = 'p', c18 = ' ' where rid = rowdata.rid; elsif rowdata.c19 = 'p' and rowdata.c18 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c19 = 'p' and rowdata.c18 <> 'w' then update player1 set c18 = 'p', c19 = ' ' where rid = rowdata.rid; end if; end loop; showPlayer(1); end; / create or replace procedure goRight(a in number) is cursor rowofplayer is select * from player1 where c1 = 'p' or c2 = 'p' or c3 = 'p' or c4 = 'p' or c5 = 'p' or c6 = 'p' or c7 = 'p' or c8 = 'p' or c9 = 'p' or c10 = 'p' or c11 = 'p' or c12 = 'p' or c13 = 'p' or c14 = 'p' or c15 = 'p' or c16 = 'p' or c17 = 'p' or c18 = 'p' or c19 = 'p' or c20 = 'p'; rowdata player1%rowtype; varCurrentStage varchar2(1); varNewStage varchar2(1); username varchar2(20); begin for rowdata in rowofplayer loop if rowdata.c2 = 'p' and rowdata.c3 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c3 <> 'w' and rowdata.c2 = 'p' then update player1 set c2 = ' ', c3 = 'p' where rid = rowdata.rid; elsif rowdata.c3 = 'p' and rowdata.c4 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c4 <> 'w' and rowdata.c3 = 'p' then update player1 set c3 = ' ', c4 = 'p' where rid = rowdata.rid; elsif rowdata.c4 = 'p' and rowdata.c5 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c5 <> 'w' and rowdata.c4 = 'p' then update player1 set c4 = ' ', c5 = 'p' where rid = rowdata.rid; elsif rowdata.c5 = 'p' and rowdata.c6 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c6 <> 'w' and rowdata.c5 = 'p' then update player1 set c5 = ' ', c6 = 'p' where rid = rowdata.rid; elsif rowdata.c6 = 'p' and rowdata.c7 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c7 <> 'w' and rowdata.c6 = 'p' then update player1 set c6 = ' ', c7 = 'p' where rid = rowdata.rid; elsif rowdata.c7 = 'p' and rowdata.c8 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c8 <> 'w' and rowdata.c7 = 'p' then update player1 set c7 = ' ', c8 = 'p' where rid = rowdata.rid; elsif rowdata.c8 = 'p' and rowdata.c9 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c9 <> 'w' and rowdata.c8 = 'p' then update player1 set c8 = ' ', c9 = 'p' where rid = rowdata.rid; elsif rowdata.c9 = 'p' and rowdata.c10 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c10 <> 'w' and rowdata.c9 = 'p' then update player1 set c9 = ' ', c10 = 'p' where rid = rowdata.rid; elsif rowdata.c10 = 'p' and rowdata.c11 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c11 <> 'w' and rowdata.c10 = 'p' then update player1 set c10 = ' ', c11 = 'p' where rid = rowdata.rid; elsif rowdata.c11 = 'p' and rowdata.c12 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c12 <> 'w' and rowdata.c11 = 'p' then update player1 set c11 = ' ', c12 = 'p' where rid = rowdata.rid; elsif rowdata.c12 = 'p' and rowdata.c13 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c13 <> 'w' and rowdata.c12 = 'p' then update player1 set c12 = ' ', c13 = 'p' where rid = rowdata.rid; elsif rowdata.c13 = 'p' and rowdata.c14 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c14 <> 'w' and rowdata.c13 = 'p' then update player1 set c13 = ' ', c14 = 'p' where rid = rowdata.rid; elsif rowdata.c14 = 'p' and rowdata.c15 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c15 <> 'w' and rowdata.c14 = 'p' then update player1 set c14 = ' ', c15 = 'p' where rid = rowdata.rid; elsif rowdata.c15 = 'p' and rowdata.c16 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c16 <> 'w' and rowdata.c15 = 'p' then update player1 set c15 = ' ', c16 = 'p' where rid = rowdata.rid; elsif rowdata.c16 = 'p' and rowdata.c17 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c17 <> 'w' and rowdata.c16 = 'p' then update player1 set c16 = ' ', c17 = 'p' where rid = rowdata.rid; elsif rowdata.c17 = 'p' and rowdata.c18 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c18 <> 'w' and rowdata.c17 = 'p' then update player1 set c17 = ' ', c18 = 'p' where rid = rowdata.rid; elsif rowdata.c18 = 'p' and rowdata.c19 = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif rowdata.c19 <> 'w' and rowdata.c18 = 'p' then update player1 set c18 = ' ', c19 = 'p' where rid = rowdata.rid; end if; end loop; showPlayer(1); end; / create or replace procedure goUp(a in number) is cursor rowofplayer is select * from player1 where c1 = 'p' or c2 = 'p' or c3 = 'p' or c4 = 'p' or c5 = 'p' or c6 = 'p' or c7 = 'p' or c8 = 'p' or c9 = 'p' or c10 = 'p' or c11 = 'p' or c12 = 'p' or c13 = 'p' or c14 = 'p' or c15 = 'p' or c16 = 'p' or c17 = 'p' or c18 = 'p' or c19 = 'p' or c20 = 'p'; rowdata player1%rowtype; var varchar2(1); varCurrentStage varchar2(1); varNewStage varchar2(1); username varchar2(20); begin for rowdata in rowofplayer loop if rowdata.rid = '2' then exit; elsif rowdata.c2 = 'p' then select c2 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c2 = 'p' where rid = rowdata.rid-1; update player1 set c2 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c3 = 'p' then select c3 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c3 = 'p' where rid = rowdata.rid-1; update player1 set c3 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c4 = 'p' then select c4 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c4 = 'p' where rid = rowdata.rid-1; update player1 set c4 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c5 = 'p' then select c5 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c5 = 'p' where rid = rowdata.rid-1; update player1 set c5 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c6 = 'p' then select c6 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c6 = 'p' where rid = rowdata.rid-1; update player1 set c6 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c7 = 'p' then select c7 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c7 = 'p' where rid = rowdata.rid-1; update player1 set c7 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c8 = 'p' then select c8 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c8 = 'p' where rid = rowdata.rid-1; update player1 set c8 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c9 = 'p' then select c9 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c9 = 'p' where rid = rowdata.rid-1; update player1 set c9 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c10 = 'p' then select c10 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c10 = 'p' where rid = rowdata.rid-1; update player1 set c10 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c11 = 'p' then select c11 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c11 = 'p' where rid = rowdata.rid-1; update player1 set c11 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c12 = 'p' then select c12 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c12 = 'p' where rid = rowdata.rid-1; update player1 set c12 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c13 = 'p' then select c13 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c13 = 'p' where rid = rowdata.rid-1; update player1 set c13 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c14 = 'p' then select c14 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c14 = 'p' where rid = rowdata.rid-1; update player1 set c14 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c15 = 'p' then select c15 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c15 = 'p' where rid = rowdata.rid-1; update player1 set c15 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c16 = 'p' then select c16 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c16 = 'p' where rid = rowdata.rid-1; update player1 set c16 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c17 = 'p' then select c17 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c17 = 'p' where rid = rowdata.rid-1; update player1 set c17 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c18 = 'p' then select c18 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c18 = 'p' where rid = rowdata.rid-1; update player1 set c18 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c19 = 'p' then select c19 into var from player1 where rid = rowdata.rid-1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c19 = 'p', c20 = rowdata.c20 where rid = rowdata.rid-1; update player1 set c19 = ' ', c20 = rowdata.c20 where rid = rowdata.rid; end if; end if; end loop; showPlayer(1); end; / create or replace procedure goDown(a in number) is cursor rowofplayer is select * from player1 where c1 = 'p' or c2 = 'p' or c3 = 'p' or c4 = 'p' or c5 = 'p' or c6 = 'p' or c7 = 'p' or c8 = 'p' or c9 = 'p' or c10 = 'p' or c11 = 'p' or c12 = 'p' or c13 = 'p' or c14 = 'p' or c15 = 'p' or c16 = 'p' or c17 = 'p' or c18 = 'p' or c19 = 'p' or c20 = 'p'; rowdata player1%rowtype; var varchar2(1); varCurrentStage varchar2(1); varNewStage varchar2(1); username varchar2(20); begin for rowdata in rowofplayer loop if rowdata.rid = '19' then exit; elsif rowdata.c2 = 'p' then select c2 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c2 = 'p' where rid = rowdata.rid+1; update player1 set c2 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c3 = 'p' then select c3 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c3 = 'p' where rid = rowdata.rid+1; update player1 set c3 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c4 = 'p' then select c4 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c4 = 'p' where rid = rowdata.rid+1; update player1 set c4 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c5 = 'p' then select c5 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c5 = 'p' where rid = rowdata.rid+1; update player1 set c5 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c6 = 'p' then select c6 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c6 = 'p' where rid = rowdata.rid+1; update player1 set c6 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c7 = 'p' then select c7 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c7 = 'p' where rid = rowdata.rid+1; update player1 set c7 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c8 = 'p' then select c8 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c8 = 'p' where rid = rowdata.rid+1; update player1 set c8 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c9 = 'p' then select c9 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c9 = 'p' where rid = rowdata.rid+1; update player1 set c9 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c10 = 'p' then select c10 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c10 = 'p' where rid = rowdata.rid+1; update player1 set c10 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c11 = 'p' then select c11 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c11 = 'p' where rid = rowdata.rid+1; update player1 set c11 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c12 = 'p' then select c12 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c12 = 'p' where rid = rowdata.rid+1; update player1 set c12 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c13 = 'p' then select c13 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c13 = 'p' where rid = rowdata.rid+1; update player1 set c13 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c14 = 'p' then select c14 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c14 = 'p' where rid = rowdata.rid+1; update player1 set c14 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c15 = 'p' then select c15 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c15 = 'p' where rid = rowdata.rid+1; update player1 set c15 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c16 = 'p' then select c16 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c16 = 'p' where rid = rowdata.rid+1; update player1 set c16 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c17 = 'p' then select c17 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c17 = 'p' where rid = rowdata.rid+1; update player1 set c17 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c18 = 'p' then select c18 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c18 = 'p' where rid = rowdata.rid+1; update player1 set c18 = ' ' where rid = rowdata.rid; end if; elsif rowdata.c19 = 'p' then select c19 into var from player1 where rid = rowdata.rid+1; if var = 'g' then select stage into varCurrentStage from currentState; select t.username into username from currentstate t; select toStage into varNewStage from relateStage where fromStage = varCurrentStage; delete from player1; insert into player1(select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = varNewStage); update currentState set stage = varNewStage; elsif var <> 'w' then update player1 set c19 = 'p' where rid = rowdata.rid+1; update player1 set c19 = ' ' where rid = rowdata.rid; end if; end if; end loop; showPlayer(1); end; / create table userlist( username varchar2(20) primary key, password varchar2(20), score number(4) default 0, stage number(2) default 1 ); create table currentState( username varchar2(20), stage number(2), score number(4) ); create table gameStore( username varchar2(20), rid varchar2(3), c1 varchar2(1), c2 varchar2(1), c3 varchar2(1), c4 varchar2(1), c5 varchar2(1), c6 varchar2(1), c7 varchar2(1), c8 varchar2(1), c9 varchar2(1), c10 varchar2(1), c11 varchar2(1), c12 varchar2(1), c13 varchar2(1), c14 varchar2(1), c15 varchar2(1), c16 varchar2(1), c17 varchar2(1), c18 varchar2(1), c19 varchar2(1), c20 varchar2(1) ); create or replace procedure registerPlayer(username in varchar2, password in varchar2) is begin insert into userlist values(username, password, 0, 1); insert into gameStore (select username,rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stagestore where stagenumber = '1'); end; / create or replace procedure logout(flag in number) is varUsername varchar2(20); varStage number(2); varScore number(4); begin select username into varUsername, stage into varStage, score into varScore from currentState; update userlist set score = varScore, stage = varStage where username = varUsername; delete from gameStore where username = varUsername; insert into gameStore (select varUsername,rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from player1); delete from player1; delete from currentState; end; / create or replace procedure logout(flag in number) is varUsername varchar2(20); varStage number(2); varScore number(4); cursor pointer is select * from currentState; rowpointer pointer%rowtype; begin for rowpointer in pointer loop varUsername:=rowpointer.username; varStage:=rowpointer.stage; varScore:=rowpointer.score; end loop; update userlist set score = varScore, stage = varStage where username = varUsername; delete from gameStore where username = varUsername; insert into gameStore (select varUsername,rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from player1); delete from player1; delete from currentState; dbms_output.put_line('Logout Successfull'); end; / create or replace procedure login(uname in varchar2, pass in varchar2) is cursor pointer is select * from gamestore g where g.username=uname; rowpointer pointer%rowtype; cursor userpointer is select count(*) as login from userlist u where u.username=uname and u.password=pass; rowuserpointer userpointer%rowtype; cursor detailpointer is select * from userlist u where u.username=uname and u.password=pass; rowdetailpointer detailpointer%rowtype; login number(1):=0; varStage number(2); varScore number(4); begin logout(1); for rowuserpointer in userpointer loop login:=rowuserpointer.login; end loop; if login=1 then for rowdetailpointer in detailpointer loop varStage:=rowdetailpointer.stage; varScore:=rowdetailpointer.score; end loop; insert into currentstate values(uname, varStage, varScore); insert into player1( select rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from gamestore where username = uname ); dbms_output.put_line('Login Successfull'); else dbms_output.put_line('Incorrect Credential'); end if; end; / create or replace procedure showPlayer(pno in number) is cursor rowofplayer is select * from player1; rowdata player1%rowtype; varScore currentState.score%type; begin select score into varScore from currentState; if pno = 1 then dbms_output.put_line('Player'); dbms_output.put_line('-------'); dbms_output.put_line('Score : '||varScore); dbms_output.put_line(' '); for rowdata in rowofplayer loop dbms_output.put_line(rowdata.c1||' '||rowdata.c2||' '||rowdata.c3||' '||rowdata.c4||' '||rowdata.c5||' '||rowdata.c6||' '||rowdata.c7||' '||rowdata.c8||' '||rowdata.c9||' '||rowdata.c10||' '||rowdata.c11||' '||rowdata.c12||' '||rowdata.c13||' '||rowdata.c14||' '||rowdata.c15||' '||rowdata.c16||' '||rowdata.c17||' '||rowdata.c18||' '||rowdata.c19||' '||rowdata.c20||' '); end loop; else dbms_output.put_line('Argument not equals 1'); end if; end; / create or replace trigger updateScoreTrigger1 before update on player1 for each row declare begin --dbms_output.put_line('Hello World New '||:new.c8||', Old '||:old.c8); if :new.c1 = 'p' and :old.c1 = 'a' then update currentState set score = score + 2; elsif :new.c2 = 'p' and :old.c2 = 'a' then update currentState set score = score + 2; elsif :new.c3 = 'p' and :old.c3 = 'a' then update currentState set score = score + 2; elsif :new.c4 = 'p' and :old.c4 = 'a' then update currentState set score = score + 2; elsif :new.c5 = 'p' and :old.c5 = 'a' then update currentState set score = score + 2; elsif :new.c6 = 'p' and :old.c6 = 'a' then update currentState set score = score + 2; elsif :new.c7 = 'p' and :old.c7 = 'a' then update currentState set score = score + 2; elsif :new.c8 = 'p' and :old.c8 = 'a' then update currentState set score = score + 2; elsif :new.c9 = 'p' and :old.c9 = 'a' then update currentState set score = score + 2; elsif :new.c10 = 'p' and :old.c10 = 'a' then update currentState set score = score + 2; elsif :new.c11 = 'p' and :old.c11 = 'a' then update currentState set score = score + 2; elsif :new.c12 = 'p' and :old.c12 = 'a' then update currentState set score = score + 2; elsif :new.c13 = 'p' and :old.c13 = 'a' then update currentState set score = score + 2; elsif :new.c14 = 'p' and :old.c14 = 'a' then update currentState set score = score + 2; elsif :new.c15 = 'p' and :old.c15 = 'a' then update currentState set score = score + 2; elsif :new.c16 = 'p' and :old.c16 = 'a' then update currentState set score = score + 2; elsif :new.c17 = 'p' and :old.c17 = 'a' then update currentState set score = score + 2; elsif :new.c18 = 'p' and :old.c18 = 'a' then update currentState set score = score + 2; elsif :new.c19 = 'p' and :old.c19 = 'a' then update currentState set score = score + 2; elsif :new.c20 = 'p' and :old.c20 = 'a' then update currentState set score = score + 2; end if; --raise_application_error(-20001, 'Hello World Error'); end; / create or replace trigger updateScoreTrigger2 before update on player1 for each row declare begin if :new.c1 = 'p' and :old.c1 = 'm' then update currentState set score = score + 5; elsif :new.c2 = 'p' and :old.c2 = 'm' then update currentState set score = score + 5; elsif :new.c3 = 'p' and :old.c3 = 'm' then update currentState set score = score + 5; elsif :new.c4 = 'p' and :old.c4 = 'm' then update currentState set score = score + 5; elsif :new.c5 = 'p' and :old.c5 = 'm' then update currentState set score = score + 5; elsif :new.c6 = 'p' and :old.c6 = 'm' then update currentState set score = score + 5; elsif :new.c7 = 'p' and :old.c7 = 'm' then update currentState set score = score + 5; elsif :new.c8 = 'p' and :old.c8 = 'm' then update currentState set score = score + 5; elsif :new.c9 = 'p' and :old.c9 = 'm' then update currentState set score = score + 5; elsif :new.c10 = 'p' and :old.c10 = 'm' then update currentState set score = score + 5; elsif :new.c11 = 'p' and :old.c11 = 'm' then update currentState set score = score + 5; elsif :new.c12 = 'p' and :old.c12 = 'm' then update currentState set score = score + 5; elsif :new.c13 = 'p' and :old.c13 = 'm' then update currentState set score = score + 5; elsif :new.c14 = 'p' and :old.c14 = 'm' then update currentState set score = score + 5; elsif :new.c15 = 'p' and :old.c15 = 'm' then update currentState set score = score + 5; elsif :new.c16 = 'p' and :old.c16 = 'm' then update currentState set score = score + 5; elsif :new.c17 = 'p' and :old.c17 = 'm' then update currentState set score = score + 5; elsif :new.c18 = 'p' and :old.c18 = 'm' then update currentState set score = score + 5; elsif :new.c19 = 'p' and :old.c19 = 'm' then update currentState set score = score + 5; elsif :new.c20 = 'p' and :old.c20 = 'm' then update currentState set score = score + 5; end if; end; / create table stagestore(stagenumber varchar2(1), rid varchar2(3), c1 varchar2(1), c2 varchar2(1), c3 varchar2(1), c4 varchar2(1), c5 varchar2(1), c6 varchar2(1), c7 varchar2(1), c8 varchar2(1), c9 varchar2(1), c10 varchar2(1), c11 varchar2(1), c12 varchar2(1), c13 varchar2(1), c14 varchar2(1), c15 varchar2(1), c16 varchar2(1), c17 varchar2(1), c18 varchar2(1), c19 varchar2(1), c20 varchar2(1)); insert into stagestore(select '1', rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stage1); insert into stagestore(select '2', rid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 from stage2); create table relateStage( fromStage varchar2(1), toStage varchar2(1) ); insert into relateStage values('1', '2'); insert into relateStage values('2', '1');