-- creation des tables
create table Piece
(
IDPiece varchar2(3),
Nom varchar2(30) NOT NULL,
Couleur varchar2(5) NOT NULL CHECK (Couleur in ('Blanc','Noir')),
constraint Piece_Key Primary Key(IDPiece));
create table Partie
(
IDPartie varchar2(10),
Nom varchar2(30) NOT NULL,
Etat varchar2( NOT NULL CHECK (Etat in ('En cours','Finie')),
Blitz Date,
constraint Partie_Key Primary Key(IDPartie));
create table Echiquier
(
IDCase varchar2(3),
Lettre varchar2(1) NOT NULL,
Chiffre number(2) NOT NULL,
Couleur varchar2(5) NOT NULL CHECK (Couleur in ('Blanc','Noir')),
constraint Echiquier_Key Primary Key(IDCase));
create table Personne
(
IDJoueur varchar2(10),
Nom varchar2 (30) NOT NULL,
Prenom varchar2(30) NOT NULL,
Pseudo varchar2(30),
Niveau varchar2(30) NOT NULL,
Email varchar2(30) NOT NULL,
constraint Personne_Key Primary Key(IDJoueur));
insert into Personne Values ('aa1','PENG','Cheng','Cheyenne','3','cheyenne@att.com');
insert into Personne Values ('aa2','YI','Yuqing','Expert','yuqingyi@att.com');
insert into Echiquier values('A1','A',1,'Noir');
insert into Echiquier values('A2','A',2,'Blanc');
insert into Echiquier values('A3','A',3,'Noir');
insert into Echiquier values('A4','A',4,'Blanc');
insert into Echiquier values('A5','A',5,'Noir');
insert into Echiquier values('A6','A',6,'Blanc');
insert into Echiquier values('A7','A',7,'Noir');
insert into Echiquier values('A8','A',8,'Blanc');
insert into Echiquier values('B1','B',1,'Blanc');
insert into Echiquier values('B2','B',2,'Noir');
insert into Echiquier values('B3','B',3,'Blanc');
insert into Echiquier values('B4','B',4,'Noir');
insert into Echiquier values('B5','B',5,'Blanc');
insert into Echiquier values('B6','B',6,'Noir');
insert into Echiquier values('B7','B',7,'Blanc');
insert into Echiquier values('B8','B',8,'Noir');
insert into Echiquier values('C1','C',1,'Noir');
insert into Echiquier values('C2','C',2,'Blanc');
insert into Echiquier values('C3','C',3,'Noir');
insert into Echiquier values('C4','C',4,'Blanc');
insert into Echiquier values('C5','C',5,'Noir');
insert into Echiquier values('C6','C',6,'Blanc');
insert into Echiquier values('C7','C',7,'Noir');
insert into Echiquier values('C8','C',8,'Blanc');
insert into Echiquier values('D1','D',1,'Blanc');
insert into Echiquier values('D2','D',2,'Noir');
insert into Echiquier values('D3','D',3,'Blanc');
insert into Echiquier values('D4','D',4,'Noir');
insert into Echiquier values('D5','D',5,'Blanc');
insert into Echiquier values('D6','D',6,'Noir');
insert into Echiquier values('D7','D',7,'Blanc');
insert into Echiquier values('D8','D',8,'Noir');
insert into Echiquier values('E1','E',1,'Noir');
insert into Echiquier values('E2','E',2,'Blanc');
insert into Echiquier values('E3','E',3,'Noir');
insert into Echiquier values('E4','E',4,'Blanc');
insert into Echiquier values('E5','E',5,'Noir');
insert into Echiquier values('E6','E',6,'Blanc');
insert into Echiquier values('E7','E',7,'Noir');
insert into Echiquier values('E8','E',8,'Blanc');
insert into Echiquier values('F1','F',1,'Blanc');
insert into Echiquier values('F2','F',2,'Noir');
insert into Echiquier values('F3','F',3,'Blanc');
insert into Echiquier values('F4','F',4,'Noir');
insert into Echiquier values('F5','F',5,'Blanc');
insert into Echiquier values('F6','F',6,'Noir');
insert into Echiquier values('F7','F',7,'Blanc');
insert into Echiquier values('F8','F',8,'Noir');
insert into Echiquier values('G1','G',1,'Noir');
insert into Echiquier values('G2','G',2,'Blanc');
insert into Echiquier values('G3','G',3,'Noir');
insert into Echiquier values('G4','G',4,'Blanc');
insert into Echiquier values('G5','G',5,'Noir');
insert into Echiquier values('G6','G',6,'Blanc');
insert into Echiquier values('G7','G',7,'Noir');
insert into Echiquier values('G8','G',8,'Blanc');
insert into Echiquier values('H1','H',1,'Blanc');
insert into Echiquier values('H2','H',2,'Noir');
insert into Echiquier values('H3','H',3,'Blanc');
insert into Echiquier values('H4','H',4,'Noir');
insert into Echiquier values('H5','H',5,'Blanc');
insert into Echiquier values('H6','H',6,'Noir');
insert into Echiquier values('H7','H',7,'Blanc');
insert into Echiquier values('H8','H',8,'Noir');
insert into Piece values('P1B','Pion','Blanc');
insert into Piece values('P2B','Pion','Blanc');
insert into Piece values('P3B','Pion','Blanc');
insert into Piece values('P4B','Pion','Blanc');
insert into Piece values('P5B','Pion','Blanc');
insert into Piece values('P6B','Pion','Blanc');
insert into Piece values('P7B','Pion','Blanc');
insert into Piece values('P8B','Pion','Blanc');
insert into Piece values('T1B','Tour','Blanc');
insert into Piece values('T2B','Tour','Blanc');
insert into Piece values('F1B','Fou','Blanc');
insert into Piece values('F2B','Fou','Blanc');
insert into Piece values('C1B','Cavalier','Blanc');
insert into Piece values('C2B','Cavalier','Blanc');
insert into Piece values('R1B','Roi','Blanc');
insert into Piece values('D1B','Dame','Blanc');
insert into Piece values('P1N','Pion','Noir');
insert into Piece values('P2N','Pion','Noir');
insert into Piece values('P3N','Pion','Noir');
insert into Piece values('P4N','Pion','Noir');
insert into Piece values('P5N','Pion','Noir');
insert into Piece values('P6N','Pion','Noir');
insert into Piece values('P7N','Pion','Noir');
insert into Piece values('P8N','Pion','Noir');
insert into Piece values('T1N','Tour','Noir');
insert into Piece values('T2N','Tour','Noir');
insert into Piece values('F1N','Fou','Noir');
insert into Piece values('F2N','Fou','Noir');
insert into Piece values('C1N','Cavalier','Noir');
insert into Piece values('C2N','Cavalier','Noir');
insert into Piece values('R1N','Roi','Noir');
insert into Piece values('D1N','Dame','Noir');
----------------------------------------------------
insert into Partie values('0001','Kasparov vs. Deep Blue','En cours','');
insert into Personne values('Kaspa','Kasparov','G.','','Grand Maitre','Kasparov.com');
insert into Personne values('Deep','Deep','Blue','','Ordinateur','IBM.com');
insert into jouer values('0001','Kaspa','Deep','Blanc','');
insert into coup values('0001','C2B','F3',sysdate,'');
insert into coup values('0001','P4N','D5',sysdate,'');
insert into coup values('0001','P7B','G3',sysdate,'');
insert into coup values('0001','F1N','G4',sysdate,'');
insert into coup values('0001','P2B','B3',sysdate,'');
insert into coup values('0001','C1N','D7',sysdate,'');
insert into coup values('0001','F1B','B2',sysdate,'');
insert into coup values('0001','P5N','E6',sysdate,'');
insert into coup values('0001','F2B','G2',sysdate,'');
insert into coup values('0001','C2N','F6',sysdate,'');
insert into coup values('0001','R1B','G1',sysdate,'');
insert into coup values('0001','T2B','F1',sysdate,'');
insert into coup values('0001','P3N','C6',sysdate,'');
insert into coup values('0001','P4B','D3',sysdate,'');
insert into coup values('0001','F2N','D6',sysdate,'');
insert into coup values('0001','C1B','D2',sysdate,'');
insert into coup values('0001','R1N','G8',sysdate,'');
insert into coup values('0001','T2N','F8',sysdate,'');
insert into coup values('0001','P8B','H3',sysdate,'');
insert into coup values('0001','F1N','H5',sysdate,'');
insert into coup values('0001','P5B','E3',sysdate,'');
insert into coup values('0001','P8N','H6',sysdate,'');
insert into coup values('0001','D1B','E1',sysdate,'');
insert into coup values('0001','D1N','A5',sysdate,'');
insert into coup values('0001','P1B','A3',sysdate,'');
insert into coup values('0001','F2N','C7',sysdate,'');
insert into coup values('0001','C2B','H4',sysdate,'');
insert into coup values('0001','P7N','G5',sysdate,'');
insert into coup values('0001','C2B','F3',sysdate,'');
insert into coup values('0001','P5N','E5',sysdate,'');
insert into coup values('0001','P5B','E4',sysdate,'');
insert into coup values('0001','T2N','E8',sysdate,'');
insert into coup values('0001','C2B','H2',sysdate,'');
insert into coup values('0001','D1N','B6',sysdate,'');
insert into coup values('0001','D1B','C1',sysdate,'');
insert into coup values('0001','P1N','A5',sysdate,'');
insert into coup values('0001','T2B','E1',sysdate,'');
insert into coup values('0001','F2N','D6',sysdate,'');
insert into coup values('0001','C1B','F1',sysdate,'');
insert into coup values('0001','P4N','E4',sysdate,'');
insert into coup values('0001','P4B','E4',sysdate,'');
insert into coup values('0001','F2N','C5',sysdate,'');
insert into coup values('0001','C1B','E3',sysdate,'');
insert into coup values('0001','T1N','D8',sysdate,'');
insert into coup values('0001','C2B','F1',sysdate,'');
insert into coup values('0001','P7N','G4',sysdate,'');
insert into coup values('0001','P8B','G4',sysdate,'');
insert into coup values('0001','C2N','G4',sysdate,'');
insert into coup values('0001','P6B','F3',sysdate,'');
insert into coup values('0001','C2N','E3',sysdate,'');
insert into coup values('0001','C2B','E3',sysdate,'');
insert into coup values('0001','F2N','E7',sysdate,'');
-----------
insert into coup values('0001','R1B','H1',sysdate,'');
insert into coup values('0001','F2N','G5',sysdate,'');
insert into coup values('0001','T2B','E2',sysdate,'');
insert into coup values('0001','P1N','A4',sysdate,'');
insert into coup values('0001','P2B','B4',sysdate,'');
insert into coup values('0001','P6N','F5',sysdate,'');
insert into coup values('0001','P4B','F5',sysdate,'');
insert into coup values('0001','P5N','E4',sysdate,'');
insert into coup values('0001','P6B','F4',sysdate,'');
insert into coup values('0001','F1N','E2',sysdate,'');
insert into coup values('0001','P6B','G5',sysdate,'');
insert into coup values('0001','C1N','E5',sysdate,'');
insert into coup values('0001','P6B','G6',sysdate,'');
insert into coup values('0001','F1N','F3',sysdate,'');
insert into coup values('0001','F1B','C3',sysdate,'');
insert into coup values('0001','D1N','B5',sysdate,'');
insert into coup values('0001','D1B','F1',sysdate,'');
insert into coup values('0001','D1N','F1',sysdate,'');
insert into coup values('0001','T1B','F1',sysdate,'');
insert into coup values('0001','P8N','H5',sysdate,'');
insert into coup values('0001','R1B','G1',sysdate,'');
insert into coup values('0001','R1N','F8',sysdate,'');
insert into coup values('0001','F2B','H3',sysdate,'');
insert into coup values('0001','P2N','B5',sysdate,'');
insert into coup values('0001','R1B','F2',sysdate,'');
insert into coup values('0001','R1N','G7',sysdate,'');
insert into coup values('0001','P7B','G4',sysdate,'');
insert into coup values('0001','R1N','H6',sysdate,'');
insert into coup values('0001','T1B','G1',sysdate,'');
insert into coup values('0001','P8N','G4',sysdate,'');
insert into coup values('0001','F2B','G4',sysdate,'');
insert into coup values('0001','F1N','G4',sysdate,'');
insert into coup values('0001','C2B','G4',sysdate,'');
insert into coup values('0001','C1N','G4',sysdate,'');
insert into coup values('0001','T1B','G4',sysdate,'');
insert into coup values('0001','T1N','D5',sysdate,'');
insert into coup values('0001','P4B','F6',sysdate,'');
insert into coup values('0001','T1N','D1',sysdate,'');
insert into coup values('0001','P6B','G7',sysdate,'');
-- mettre partie finie
-------------------------------------------------------
insert into Partie values('0002','Test-Match 1','En cours','');
insert into Jouer values('0002','GA1','TIS1','Noir',NULL);
insert into coup values('0002','P5B','E4',sysdate,'');
insert into coup values('0002','P5N','E5',sysdate,'');
insert into coup values('0002','F2B','C4',sysdate,'');
insert into coup values('0002','P2N','B6',sysdate,'');
insert into coup values('0002','D1B','H5',sysdate,'');
insert into coup values('0002','P8N','H6',sysdate,'');
insert into coup values('0002','D1B','F7',sysdate,'');
insert into coup values('0002','R1N','E7',sysdate,'');
-----------------------------------------------
insert into reprisepartie values ('0003','0002',6);
insert into Partie values('0003','Test-Match2','En cours','');
insert into Jouer values('0003','GA1','TIS1','Noir',NULL);
insert into coup values('0003','D1B','F7',sysdate,'');
--------------------------------------------------
-- passant
insert into partie values ('0010','Situations','En cours','');
insert into jouer values ('0010','GA1','TIS1','Blanc','');
insert into coup values ('0010','P5B','E4',sysdate,'');
insert into coup values ('0010','C2N','F6',sysdate,'');
insert into coup values ('0010','P5B','E5',sysdate,'');
insert into coup values ('0010','P4N','D5',sysdate,'');
insert into coup values ('0010','P5B','D6',sysdate,'');
insert into coup values ('0010','P5B','E4',sysdate,'');
insert into coup values ('0010','P5B','E4',sysdate,'');
insert into coup values ('0010','P5B','E4',sysdate,'');
insert into coup values ('0010','P5B','E4',sysdate,'');
insert into coup values ('0010','P1B','A4',sysdate,'');
insert into coup values ('0010','P2N','B5',sysdate,'');
insert into coup values ('0010','P1B','B5',sysdate,'');
insert into coup values ('0010','C1N','C6',sysdate,'');
insert into coup values ('0010','P1B','B6',sysdate,'');
insert into coup values ('0010','C1N','A5',sysdate,'');
insert into coup values ('0010','P1B','B7',sysdate,'');
insert into coup values ('0010','C1N','C4',sysdate,'');
insert into coup values ('0010','P1B','B8',sysdate,'');
insert into Promotion values ('0010','P1B','D2B');
insert into coup values ('0010','C1N','A5',sysdate,'');
insert into coup values ('0010','D2B','B5',sysdate,'');作者: expert 时间: 2003-11-02 01:37 标题: 求教:用SQL来写一个国际象棋的程序 -- procedure.sql
-- Procedure d'initialisation des tables
create or replace procedure VarEchec_Init(IPartie Partie.IDPartie%Type)
is
Begin
insert into VarEchec values(IPartie,'Roc',0);
insert into VarEchec values(IPartie,'Passant',0);
end;
/
create or replace procedure EchecTrigger_Init
is
Begin
insert into Echec_Trigger values('Controle_partie_joueur','Inactif');
insert into Echec_Trigger values('controle_personne','Inactif');
insert into Echec_Trigger values('Controle_Partie','Inactif');
insert into Echec_Trigger values('Controle_Piece','Inactif');
insert into Echec_Trigger values('Controle_Echiquier','Inactif');
insert into Echec_Trigger values('ControleA_Coup','Inactif');
insert into Echec_Trigger values('ControleB_Coup','Inactif');
End;
/
Create or replace procedure Position_Initiale (IPartie Partie.IDPartie%Type)
IS
Begin
insert into Occupe values(IPartie,'P1B','A2');
insert into Occupe values(IPartie,'P2B','B2');
insert into Occupe values(IPartie,'P3B','C2');
insert into Occupe values(IPartie,'P4B','D2');
insert into Occupe values(IPartie,'P5B','E2');
insert into Occupe values(IPartie,'P6B','F2');
insert into Occupe values(IPartie,'P7B','G2');
insert into Occupe values(IPartie,'P8B','H2');
insert into Occupe values(IPartie,'P1N','A7');
insert into Occupe values(IPartie,'P2N','B7');
insert into Occupe values(IPartie,'P3N','C7');
insert into Occupe values(IPartie,'P4N','D7');
insert into Occupe values(IPartie,'P5N','E7');
insert into Occupe values(IPartie,'P6N','F7');
insert into Occupe values(IPartie,'P7N','G7');
insert into Occupe values(IPartie,'P8N','H7');
insert into Occupe values(IPartie,'T1B','A1');
insert into Occupe values(IPartie,'F1B','C1');
insert into Occupe values(IPartie,'C1B','B1');
insert into Occupe values(IPartie,'D1B','D1');
insert into Occupe values(IPartie,'R1B','E1');
insert into Occupe values(IPartie,'C2B','G1');
insert into Occupe values(IPartie,'F2B','F1');
insert into Occupe values(IPartie,'T2B','H1');
insert into Occupe values(IPartie,'T1N','A8');
insert into Occupe values(IPartie,'F1N','C8');
insert into Occupe values(IPartie,'C1N','B8');
insert into Occupe values(IPartie,'D1N','D8');
insert into Occupe values(IPartie,'R1N','E8');
insert into Occupe values(IPartie,'C2N','G8');
insert into Occupe values(IPartie,'F2N','F8');
insert into Occupe values(IPartie,'T2N','H8');
End Position_Initiale;
/
-- Permet d'initialiser les deplacements pour une nouvelle partie
Create or replace procedure Deplacement_Initiale (IPartie Partie.IDPartie%Type)
IS
Begin
insert into Deplacement values(IPartie,'P1B','A3');
insert into Deplacement values(IPartie,'P1B','A4');
insert into Deplacement values(IPartie,'P2B','B3');
insert into Deplacement values(IPartie,'P2B','B4');
insert into Deplacement values(IPartie,'P3B','C3');
insert into Deplacement values(IPartie,'P3B','C4');
insert into Deplacement values(IPartie,'P4B','D3');
insert into Deplacement values(IPartie,'P4B','D4');
insert into Deplacement values(IPartie,'P5B','E3');
insert into Deplacement values(IPartie,'P5B','E4');
insert into Deplacement values(IPartie,'P6B','F3');
insert into Deplacement values(IPartie,'P6B','F4');
insert into Deplacement values(IPartie,'P7B','G3');
insert into Deplacement values(IPartie,'P7B','G4');
insert into Deplacement values(IPartie,'P8B','H3');
insert into Deplacement values(IPartie,'P8B','H4');
insert into Deplacement values(IPartie,'P1N','A6');
insert into Deplacement values(IPartie,'P1N','A5');
insert into Deplacement values(IPartie,'P2N','B6');
insert into Deplacement values(IPartie,'P2N','B5');
insert into Deplacement values(IPartie,'P3N','C6');
insert into Deplacement values(IPartie,'P3N','C5');
insert into Deplacement values(IPartie,'P4N','D6');
insert into Deplacement values(IPartie,'P4N','D5');
insert into Deplacement values(IPartie,'P5N','E6');
insert into Deplacement values(IPartie,'P5N','E5');
insert into Deplacement values(IPartie,'P6N','F6');
insert into Deplacement values(IPartie,'P6N','F5');
insert into Deplacement values(IPartie,'P7N','G6');
insert into Deplacement values(IPartie,'P7N','G5');
insert into Deplacement values(IPartie,'P8N','H6');
insert into Deplacement values(IPartie,'P8N','H5');
insert into Deplacement values(IPartie,'C1B','A3');
insert into Deplacement values(IPartie,'C1B','C3');
insert into Deplacement values(IPartie,'C2B','F3');
insert into Deplacement values(IPartie,'C2B','H3');
insert into Deplacement values(IPartie,'C1N','A6');
insert into Deplacement values(IPartie,'C1N','C6');
insert into Deplacement values(IPartie,'C2N','F6');
insert into Deplacement values(IPartie,'C2N','H6');
End Deplacement_Initiale;
/
Create or replace procedure Depl_Possible(IPartie Partie.IDPartie%TYPE,IPiece Piece.IDPiece%TYPE)
Is
cursor Depl_poss is select * from deplacement where IDPartie=IPartie and IDPiece=IPiece;
Begin
dbms_output.put_line('Deplacement possible pour :'||IPiece);
For i in Depl_poss
Loop
dbms_output.put_line('Case possible :'||i.IDCase);
end loop;
Exception
When NO_DATA_FOUND Then
dbms_output.put_line('Aucun deplacement possible');
End Depl_Possible;
/
Create or replace procedure Info_Joueur(IJoueur Personne.IDJoueur%TYPE)
Is
NomA Personne.Nom%TYPE;
PrenomA Personne.Prenom%TYPE;
Ga integer:=0;
Per integer:=0;
Nul integer:=0;
Etat varchar2(10);
cursor InfoJoueur is
select * from Jouer where IDJoueur1=IJoueur or IDJoueur2=IJoueur;
cursor Joueur is
select * from Personne where IDJoueur=IJoueur;
cursor NomJ (ID Personne.IDJoueur%TYPE) is
select Nom,Prenom from Personne where IDJoueur=ID;
Begin
For i in Joueur
Loop
dbms_output.put_line('Information sur le joueur : '||i.Nom||' '||i.Prenom);
dbms_output.put_line('Niveau : '||i.Niveau);
dbms_output.put_line('Email : '||i.Email);
End loop;
For i in InfoJoueur
Loop
select Etat into Etat from partie where IDPartie=i.IDPartie;
If i.IDJoueur1=IJoueur Then
open NomJ(i.IDJoueur2);
Fetch NomJ into NomA,PrenomA;
Close NomJ;
dbms_output.put_line('Match contre '||NomA||' '||PrenomA||' '||Etat);
If i.Resultat='Egalite' Then
dbms_output.put_line('Resultat : match nul');
Nul:=Nul+1;
Elsif i.Resultat='Perdant' Then
dbms_output.put_line('Resultat : perdant');
Per:=Per+1;
Elsif i.Resultat='Gagnant' Then
dbms_output.put_line('Resultat : gagnant');
Ga:=Ga+1;
End if;
Else
open NomJ(i.IDJoueur1);
Fetch NomJ into NomA,PrenomA;
Close NomJ;
dbms_output.put_line('Partie contre '||NomA||' '||PrenomA||' '||Etat);
If i.Resultat='Egalite' Then
dbms_output.put_line('Resultat : match nul');
Nul:=Nul+1;
Elsif i.Resultat='Gagnant' Then
dbms_output.put_line('Resultat : perdant');
Per:=Per+1;
Elsif i.Resultat='Perdant' Then
dbms_output.put_line('Resultat : gagnant');
Ga:=Ga+1;
End if;
End if;
dbms_output.put_line('----------------------------------------------');
End loop;
Exception
when NO_DATA_FOUND Then
dbms_output.put_line('Ce joueur n existe pas');
End Info_Joueur;
/
-- procedure de listing des coups d'une partie
-- ?ameliorer en notation
create or replace procedure Affiche_Partie(IPartie Partie.IDPartie%TYPE)
IS
cursor RCoup is
select IDPiece,IDCase,DateCoup,Description from Coup where IDPartie=IPartie order by DateCoup;
cursor InfoPartie is
select * from Partie where IDPartie=IPartie;
cursor InfoJoueur(IJoueur Personne.IDJoueur%TYPE) is
select * from Personne where IDJoueur=IJoueur;
cursor Joueur is
select * from Jouer where IDPartie=IPartie;
Couleur1 varchar2(20);
Res varchar2(20):='';
NbCoup integer:=1;
Begin
For i in InfoPartie
Loop
dbms_output.put_line('Informations sur la partie'||IPartie);
dbms_output.put_line('Nom : '||i.Nom);
dbms_output.put_line('Etat : '||i.Etat);
End loop;
dbms_output.put_line('---------------------');
For i in Joueur
Loop
dbms_output.put_line('--- Premier Joueur ---');
For j in InfoJoueur(i.IDJoueur1)
Loop
dbms_output.put_line('Joueur : '||j.Nom||' '||j.Prenom);
dbms_output.put_line('Niveau : '||j.Niveau);
dbms_output.put_line('Couleur : '||i.Couleur);
dbms_output.put_line('Resultat : '||i.Resultat);
End loop;
dbms_output.put_line('--- Deuxieme joueur ---');
For j in InfoJoueur(i.IDJoueur2)
Loop
dbms_output.put_line('Joueur : '||j.Nom||' '||j.Prenom);
dbms_output.put_line('Niveau : '||j.Niveau);
if i.Couleur='Blanc' Then
Couleur1:='Noir';
Else
Couleur1:='Blanc';
End if;
dbms_output.put_line('Couleur : '||Couleur1);
If i.Resultat is not NULL then
if i.Resultat='Gagnant' Then
Res:='Perdant';
Elsif i.Resultat='Perdant' Then
Res:='Gagnant';
Else
Res:='Egalite';
End if;
End if;
dbms_output.put_line('Resultat : '||Res);
End loop;
End loop;
dbms_output.put_line('-------------------');
dbms_output.put_line('--- Coup ---');
For i in RCoup
Loop
dbms_output.put_line(NbCoup||'- '||i.IDPiece||' '||i.IDCase||' --'||i.Description);
NbCoup:=NbCoup+1;
End Loop;
End Affiche_Partie;
/
-- Procedure pour le deplacement des pieces
create or replace procedure Constr_Deplacement (IPiece Piece.IDPiece%Type,
IPartie Partie.IDPartie%Type,
CaseP Echiquier.IDCase%Type)
IS
NomP Piece.Nom%Type;
CouleurP Piece.Couleur%Type;
Lettre varchar2(2);
Chiffre varchar2(2);
LettreT varchar2(2);
ChiffreT varchar2(2);
CaseTmp varchar2(10);
CouleurTmp Piece.Couleur%Type;
PieceTmp Piece.IDPiece%Type;
Tmp1 number(2);
Tmp2 number(2);
i integer:=-1;
CaseTmp1 Echiquier.IdCase%Type:='H9'; -- sert pour le fou
CaseTmp2 Echiquier.IdCase%Type:='A0'; -- sert pour le fou
Entree integer:=1; -- sert pour le fou
Entree2 integer:=1; -- sert pour le fou
Id integer:=1;
LettreT2 varchar2(2);
ChiffreT2 varchar2(2);
-- verifie si la case est occupe
cursor RechCase (CaseR Echiquier.IDCase%Type) is
select count(*)
from Occupe where IDCase=CaseR and IDPartie=IPartie;
-- renvoie la couleur de la piece qui occupe cette case
cursor PieceOccupe (CaseR Echiquier.IDCase%TYPE) is
select IDPiece from Occupe
where Occupe.IDCase=CaseR and IDPartie=IPartie;
-- ramene toutes les cases possibles pour une tour avant traitement
cursor CaseTourC1 (CaseT Echiquier.IDCase%Type) is
select distinct IDCase from Echiquier
where substr(CaseT,1,1)=Lettre and IDCase!=CaseT;
cursor CaseTourC2 (CaseT Echiquier.IDCase%Type) is
select distinct IDCase from Echiquier
where substr(CaseT,1,1)=Lettre and IDCase!=CaseT order by IDCase desc;
cursor CaseTourL1 (CaseT Echiquier.IDCase%Type) is
select distinct IDCase from Echiquier
where substr(CaseT,2,2)=Chiffre and IDCase!=CaseT;
cursor CaseTourL2 (CaseT Echiquier.IDCase%Type) is
select distinct IDCase from Echiquier
where substr(CaseT,2,2)=Chiffre and IDCase!=CaseT order by IDCase desc;
-- verifie que la case existe
cursor VerifCase (CaseR Echiquier.IDCase%TYPE) is
select count(*) from Echiquier where IDCase=CaseR;
-- recherche les pions ?cote pour la prise en passant
cursor RechPion(Lettre char,Chiffre char,Coul char, Val integer) is
select IDPiece,IDCase from Occupe
where IDPartie=IPartie and substr(IDPiece,1,1)='P' and substr(IDPiece,3,3)!=Coul
and IDCase=CHR(ASCII(Lettre)+Val)||Chiffre;
Begin
NomP:=substr(IPiece,1,1);
CouleurP:=substr(IPiece,3,3);
-- verifier la prise en pasant
If CouleurP='B' Then
-- test de la ligne
if Chiffre='5' Then
-- verifier si un pion se trouve ?cote.
Loop
open RechPion (Lettre,Chiffre,CouleurP,i);
Fetch RechPion into PieceTmp,CaseTmp;
select count(*) into Tmp1 from CoupTmp where IDPartie=IPartie and IDPiece=PieceTmp;
if Tmp1=1 and RechPion%FOUND THEN
-- la prise en passant est possible
update VarEchec set Val=1 where IDPartie=IPartie and Nom='Passant';
dbms_output.put_line(IPartie||','||IPiece||','||substr(CaseTmp,1,1)||CHR(ASCII(substr(CaseTmp,2,2))+1));
insert into Deplacement values(IPartie,IPiece,substr(CaseTmp,1,1)||CHR(ASCII(substr(CaseTmp,2,2))+1));
end if;
i:=i+2;
close RechPion;
exit when i>;1;
End loop;
end if;
Chiffre:=CHR(ASCII(Chiffre)+1);
Else
-- test de la ligne
if Chiffre='4' Then
-- verifier si un pion se trouve ?cote.
Loop
open RechPion(Lettre,Chiffre,CouleurP,i);
Fetch RechPion into PieceTmp,CaseTmp;
select count(*) into Tmp1 from CoupTmp where IDPartie=IPartie and IDPiece=PieceTmp;
if Tmp1=1 and RechPion%FOUND THEN
-- la prise en passant est possible
update VarEchec set Val=1 where IDPartie=IPartie and Nom='Passant';
dbms_output.put_line('2:'||IPartie||','||IPiece||','||substr(CaseTmp,1,1)||CHR(ASCII(substr(CaseTmp,2,2))-1));
insert into Deplacement values(IPartie,IPiece,substr(CaseTmp,1,1)||CHR(ASCII(substr(CaseTmp,2,2))-1));
end if;
i:=i+2;
close RechPion;
exit when i>;1;
End loop;
end if;
Chiffre:=CHR(ASCII(Chiffre)-1);
End if;
-- verifie si on deplace pour la premiere fois
select count(*) Into Tmp2 from CoupTmp where IDPartie=IPartie and IDPiece=IPiece;
If Tmp2=0 Then
If CouleurP='B' Then
CaseTmp:=Lettre||CHR(ASCII(Chiffre)+1);
else
CaseTmp:=Lettre||CHR(ASCII(Chiffre)-1);
end if;
Open RechCase(CaseTmp);
Fetch RechCase into Tmp1;
Close RechCase;
if Tmp1=0 Then
insert into Deplacement values(IPartie,IPiece,CaseTmp);
end if;
End IF;
--verifie si la case est occupee
CaseTmp:=Lettre||Chiffre;
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp2;
Close VerifCase;
If Tmp2>;0 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp1;
Close RechCase;
if Tmp1=0 Then
insert into Deplacement values(IPartie,IPiece,CaseTmp);
end if;
end if;
--verification si on peut prendre un pion
--verifie si la case est occupee
CaseTmp:=CHR(ASCII(Lettre)+1)||Chiffre;
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp2;
Close VerifCase;
If Tmp2>;0 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp1;
Close RechCase;
if Tmp1>;0 Then
Open PieceOccupe (CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If CouleurP!=substr(PieceTmp,3,3) Then
insert into Deplacement values(IPartie,IPiece,CaseTmp);
else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
end if;
end if;
--verifie si la case est occupee
CaseTmp:=CHR(ASCII(Lettre)-1)||Chiffre;
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp2;
Close VerifCase;
If Tmp2>;0 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp1;
Close RechCase;
if Tmp1>;0 Then
Open PieceOccupe (CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If CouleurP!=substr(PieceTmp,3,3) Then
insert into Deplacement values(IPartie,IPiece,CaseTmp);
else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
End if;
end if;
end if;
End If;
-- Traitement de la tour
If NomP='T' or NomP='D' Then
For i in CaseTourC1(CaseP)
Loop
Open RechCase(i.IDCase);
Fetch RechCase into Tmp1;
Close RechCase;
if Tmp1=0 Then
if i.IDCase < CaseTmp1 and i.IDCase >; CaseP then
insert into Deplacement values(IPartie,IPiece,i.IDCase);
End If;
else
if i.IDCase < CaseTmp1 Then
if i.IDCase >; CaseP Then
CaseTmp1:=i.IDCase;
Open PieceOccupe (i.IDCase);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If CouleurP!=substr(PieceTmp,3,3) Then
insert into Deplacement values(IPartie,IPiece,i.IDCase);
else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
end if;
end if;
end if;
End loop;
For i in CaseTourC2(CaseP)
Loop
Open RechCase(i.IDCase);
Fetch RechCase into Tmp1;
Close RechCase;
if Tmp1=0 Then
if i.IDCase >; CaseTmp2 and i.IDCase < CaseP then
insert into Deplacement values(IPartie,IPiece,i.IDCase);
End If;
else
if i.IDCase >; CaseTmp2 Then
if i.IDCase < CaseP Then
CaseTmp2:=i.IDCase;
-- verifier la couleur du pion sur cette case si diff alors on ajoute
Open PieceOccupe (i.IDCase);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If CouleurP!=substr(PieceTmp,3,3) Then
insert into Deplacement values(IPartie,IPiece,i.IDCase);
else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
end if;
end if;
End if;
end Loop;
CaseTmp1:='H9';
CaseTmp2:='A0';
For i in CaseTourL1(CaseP)
Loop
Open RechCase(i.IDCase);
Fetch RechCase into Tmp1;
Close RechCase;
if Tmp1=0 Then
if i.IDCase < CaseTmp1 and i.IDCase >; CaseP then
insert into Deplacement values(IPartie,IPiece,i.IDCase);
End If;
else
if i.IDCase < CaseTmp1 Then
if i.IDCase >; CaseP Then
CaseTmp1:=i.IDCase;
Open PieceOccupe (i.IDCase);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If CouleurP!=substr(PieceTmp,3,3) Then
insert into Deplacement values(IPartie,IPiece,i.IDCase);
else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
End if;
End if;
End if;
end Loop;
For i in CaseTourL2(CaseP)
Loop
Open RechCase(i.IDCase);
Fetch RechCase into Tmp1;
Close RechCase;
if Tmp1=0 Then
if i.IDCase >; CaseTmp2 and i.IDCase < CaseP then
insert into Deplacement values(IPartie,IPiece,i.IDCase);
End If;
else
if i.IDCase >; CaseTmp2 Then
if i.IDCase < CaseP Then
CaseTmp2:=i.IDCase;
Open PieceOccupe (i.IDCase);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If CouleurP!=substr(PieceTmp,3,3) Then
insert into Deplacement values(IPartie,IPiece,i.IDCase);
else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
End if;
end if;
End if;
end Loop;
End If;
-- verifie que la case existe
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp1;
Close VerifCase;
If Tmp1>;0 and Entree=1 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp2;
Close RechCase;
If Tmp2=0 Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
Open PieceOccupe(CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If substr(PieceTmp,3,3)!=CouleurP Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
-- on n'est plus oblige de rentrer dans le if car une piece bloque le deplacement
Entree:=0;
end if;
If LettreT='H' or ChiffreT='8' Then
Entree:=0;
End if;
else
Entree:=0;
End if;
LettreT2:=CHR(ASCII(LettreT2)-1);
CaseTmp:=LettreT2||ChiffreT;
-- verifie que la case existe
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp1;
Close VerifCase;
If Tmp1>;0 and Entree2=1 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp2;
Close RechCase;
If Tmp2=0 Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
Open PieceOccupe(CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If substr(PieceTmp,3,3)!=CouleurP Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
-- on n'est plus oblige de rentrer dans le if car une piece bloque le deplacement
Entree2:=0;
end if;
If LettreT='A' or ChiffreT='8' Then
Entree2:=0;
End if;
else
Entree2:=0;
End if;
exit when Entree=0 and Entree2=0;
Id:=Id+1;
end Loop;
-- verifie que la case existe
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp1;
Close VerifCase;
If Tmp1>;0 and Entree=1 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp2;
Close RechCase;
If Tmp2=0 Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
Open PieceOccupe(CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If substr(PieceTmp,3,3)!=CouleurP Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
-- on n'est plus oblige de rentrer dans le if car une piece bloque le deplacement
Entree:=0;
end if;
If LettreT='H' or ChiffreT='1' Then
Entree:=0;
End if;
else
Entree:=0;
End if;
-- verifie que la case existe
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp1;
Close VerifCase;
If Tmp1>;0 and Entree2=1 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp2;
Close RechCase;
If Tmp2=0 Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
Open PieceOccupe(CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If substr(PieceTmp,3,3)!=CouleurP Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
-- on n'est plus oblige de rentrer dans le if car une piece bloque le deplacement
Entree2:=0;
end if;
If LettreT='A' or ChiffreT='1' Then
Entree2:=0;
End if;
else
Entree2:=0;
End if;
exit when Entree=0 and Entree2=0;
Id:=Id+1;
end Loop;
end if;
-- Traitement du cavalier
If NomP='C' Then
For i in -1..1
Loop
If i!=0 Then
LettreT:=CHR(ASCII(Lettre)+i);
For j in -2..2
Loop
If j=-2 or j=2 Then
ChiffreT:=CHR(ASCII(Chiffre)+j);
CaseTmp:=LettreT||ChiffreT;
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp1;
Close VerifCase;
If Tmp1>;0 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp2;
Close RechCase;
If Tmp2=0 Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
Open PieceOccupe(CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If substr(PieceTmp,3,3)!=CouleurP Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
end if;
End if;
end if;
end loop;
End if;
end loop;
For i in -1..1
Loop
If i!=0 Then
ChiffreT:=CHR(ASCII(Chiffre)+i);
For j in -2..2
Loop
If j=-2 or j=2 Then
LettreT:=CHR(ASCII(Lettre)+j);
CaseTmp:=LettreT||ChiffreT;
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp1;
Close VerifCase;
If Tmp1>;0 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp2;
Close RechCase;
If Tmp2=0 Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
Open PieceOccupe(CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If substr(PieceTmp,3,3)!=CouleurP Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
end if;
End if;
end if;
end loop;
End if;
end loop;
End if;
-- traitement du roi
If Nomp='R' Then
For i in -1..1
Loop
ChiffreT:=CHR(ASCII(Chiffre)+i);
For j in -1..1
Loop
LettreT:=CHR(ASCII(Lettre)+j);
CaseTmp:=LettreT||ChiffreT;
Open VerifCase(CaseTmp);
Fetch VerifCase into Tmp1;
Close VerifCase;
If Tmp1>;0 Then
Open RechCase(CaseTmp);
Fetch RechCase into Tmp2;
Close RechCase;
If Tmp2=0 Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
Else
Open PieceOccupe (CaseTmp);
Fetch PieceOccupe into PieceTmp;
Close PieceOccupe ;
If substr(PieceTmp,3,3)!=CouleurP Then
select count(*) into Tmp1 from Protection
where IDPieceCouv=PieceTmp and substr(IDPieceProtec,3,3)=substr(PieceTmp,3,3);
If Tmp1=0 Then
insert into Deplacement values (IPartie,IPiece,CaseTmp);
end if;
else
select count(*) into Tmp1 from Protection where IDPieceCouv=PieceTmp;
If Tmp1=0 Then
insert into Protection values(IPartie,IPiece,PieceTmp);
End if;
end if;
end if;
End if;
End loop;
end loop;
End if;
End Constr_Deplacement;
/
-- dbms_output.enable;
-- drop table Partie CASCADE CONSTRAINTS;
-- alter session enable commit in procedure
create or replace trigger Controle_partie_joueur
Before insert or update or delete on Jouer
For each row
Declare
EtatPartie Partie.Etat%TYPE;
ModeT Echec_Trigger.Mode_TRi%TYPE;
Begin
Update Echec_Trigger set Mode_TRi='Actif' where Nom='Controle_partie_joueur';
If Deleting Then
-- verifier dans la table des trigger le mode de controle partie;
Select Mode_TRi
Into ModeT
From Echec_Trigger
where Nom='Controle_Partie';
If ModeT!='Actif' Then
Select Etat
Into EtatPartie
From Partie
where IDPartie=ld.IDPartie;
If EtatPartie!='Finie' Then
delete from Partie where IDPartie=ld.IDPartie;
dbms_output.put_line('La partie a ete supprime');
End if;
End if;
ElsiF Inserting Then
If :new.IDJoueur1=:new.IDJoueur2 Then
RAISE_APPLICATION_ERROR (-20500,'Une partie doit avoir deux joueurs differents');
Elsif :new.Couleur is Null Then
RAISE_APPLICATION_ERROR (-20501,'Les joueurs doivent posseder une couleur de piece');
Elsif :new.Resultat is not Null Then
RAISE_APPLICATION_ERROR (-20502,'La partie n est pas jouee');
End if;
Elsif Updating('Resultat') Then
Select Etat
Into EtatPartie
From Partie
where IDPartie=ld.IDPartie;
If EtatPartie!='Finie' Then
RAISE_APPLICATION_ERROR (-20503,'La partie n est pas finie');
End if;
Elsif Updating('Couleur') Then
dbms_output.put_line('ATTENTION : vous inversez la couleur des joueurs!');
Elsif Updating('IDJoueur1') or Updating('IDJoueur2') or Updating('IDPartie') Then
RAISE_APPLICATION_ERROR (-20505,'Vous ne pouvez modifier ces attributs');
End if;
Update Echec_Trigger set Mode_TRi='Inactif' where Nom='Controle_partie_joueur';
End;
/
create or replace trigger controle_personne
After Delete or Update on Personne
For each row
Declare
ModeT Echec_Trigger.Mode_TRi%TYPE;
IPartie Partie.IDPartie%TYPE;
Begin
Update Echec_Trigger set Mode_TRi='Actif' where Nom='controle_personne';
If Deleting Then
Select Mode_TRi
Into ModeT
From Echec_Trigger
where Nom='Controle_Partie';
If ModeT!='Actif' Then
Delete from Jouer where IDJoueur1=ld.IDJoueur or IDJoueur2=ld.IDJoueur;
If SQL%FOUND then
dbms_output.put_line('Mise ?jour de Jouer effectuee');
End if;
End if;
End if;
If Updating('IDJoueur') Then
RAISE_APPLICATION_ERROR (-20503,'On ne peut pas modifier l identifiant d un joueur');
End if;
Update Echec_Trigger set Mode_TRi='Inactif' where Nom='controle_personne';
End;
/
create or replace trigger Controle_Partie
After Delete or Insert or Update on Partie
For each row
Declare
ModeT Echec_Trigger.Mode_TRi%TYPE;
Begin
Update Echec_Trigger set Mode_TRi='Actif' where Nom='Controle_Partie';
If deleting Then
Select Mode_TRi
Into ModeT
From Echec_Trigger
where Nom='Controle_partie_joueur';
If ModeT!='Actif' Then
delete from Jouer where IDPartie=ld.IDPartie;
If SQL%FOUND Then
dbms_output.put_line('Mise ?jour de Jouer effectuee');
End if;
End if;
delete from VarEchec where IDPartie=ld.IDPartie;
delete from CoupTmp where IDPartie=ld.IDPartie;
delete from Coup where IDPartie=ld.IDPartie;
If SQL%FOUND Then
dbms_output.put_line('Mise ?jour de Coup effectuee');
End if;
delete from Occupe where IDPartie=ld.IDPartie;
If SQL%FOUND Then
dbms_output.put_line('Mise ?jour de Occupe effectuee');
End if;
delete from Deplacement where IDPartie=:old.IDPartie;
If SQL%FOUND Then
dbms_output.put_line('Mise ?jour de Deplacement effectuee');
End if;
delete from Prise where IDPartie=:old.IDPartie;
If SQL%FOUND Then
dbms_output.put_line('Mise ?jour de Prise effectuee');
End if;
delete from Promotion where IDPartie=:old.IDPartie;
If SQL%FOUND Then
dbms_output.put_line('Mise ?jour de Promotion effectuee');
End if;
delete from Protection where IDPartie=:old.IDPartie;
If SQL%FOUND Then
dbms_output.put_line('Mise ?jour de Promotion effectuee');
End if;
delete from ReprisePartie where IDPartie=:old.IDPartie or IDPartie=:old.IDPartie;
If SQL%FOUND Then
dbms_output.put_line('Mise ?jour de Reprise Partie effectuee');
End if;
End if;
If Inserting Then
if :new.Etat!='En cours' Then
RAISE_APPLICATION_ERROR (-20506,'La partie doit avoir comme etat "En cours"');
Else
Position_Initiale(:new.IDPartie);
Deplacement_Initiale(:new.IDPartie);
VarEchec_Init(:new.IDPartie);
End if;
End if;
If Updating('IDPartie') Then
RAISE_APPLICATION_ERROR (-20503,'On ne peut pas modifier l identifiant d un joueur');
End if;
Update Echec_Trigger set Mode_TRi='Inactif' where Nom='Controle_Partie';
End;
/
create or replace trigger Controle_Piece
before Delete or Insert or Update on Piece
For each row
begin
If Deleting or Inserting Then
RAISE_APPLICATION_ERROR (-20504,'On ne peut pas supprimer ou inserer une ligne dans cette table');
Elsif Updating('IDPiece') Then
RAISE_APPLICATION_ERROR (-20503,'On ne peut pas modifier l identifiant d un joueur');
End if;
end;
/
create or replace trigger Controle_Echiquier
before Delete or Insert or Update on Echiquier
begin
If Deleting or Inserting Then
RAISE_APPLICATION_ERROR (-20504,'On ne peut pas supprimer ou inserer une ligne dans cette table');
Elsif Updating('IDCase') Then
RAISE_APPLICATION_ERROR (-20503,'On ne peut pas modifier l identifiant d un joueur');
End if;
end;
/
-- controler la promotion
create or replace trigger Controle_Promotion
Before Insert on Promotion
FOR EACH ROW
Declare
IP char;
IC char;
PriseP integer;
MaxP integer;
CaseP Echiquier.IDCase%TYPE;
cursor PieceT is
select IDPartie,IDPiece,IDCase
from Occupe where IDPartie=:new.IDPartie;
cursor Rech is
select IDCase from Coup where IDPartie=:new.IDPartie and IDPiece=:new.IDPiece order by DateCoup desc;
Begin
-- verifier que la piece est sur le damier
select count(*) into PriseP from Prise where IDPiece=:new.IDPiece and IDPartie=:new.IDPartie;
If PriseP>;0 Then
RAISE_APPLICATION_ERROR (-20507,'Cette piece n est plus sur le damier');
End if;
open Rech;
Fetch Rech into CaseP;
close Rech;
If substr(:new.IDPiece,3,3)='B' Then
If substr(CaseP,2,2)!='8' Then
RAISE_APPLICATION_ERROR (-20512,'Le pion blanc n est pas sur la derniere ligne');
End if;
Else
if substr(CaseP,2,2)!='1' Then
RAISE_APPLICATION_ERROR (-20512,'Le pion noir n est pas sur la premiere ligne');
End if;
End if;
If substr(:new.IDPiece,1,1)='P' Then
IP:=substr(:new.IDPiecePromo,1,1);
IC:=substr(:new.IDPiece,3,3);
-- test pour savoir si on a une bonne piece
if IP='T' or IP='F' or IP='C' or IP='D' Then
select Max(ASCII(substr(IDPiece,2,2))) into MaxP from Occupe
where substr(IDPiece,1,1)=IP and substr(IDPiece,3,3)=IC;
MaxP:=MaxP+1;
If :new.IDPiecePromo!=IP||CHR(MaxP)||IC Then
:new.IDPiecePromo:=IP||CHR(MaxP)||IC;
end if;
else
RAISE_APPLICATION_ERROR (-20512,'On ne peut pas promouvoir un pion que pour une tour, un fou, un cavalier ou une dame');
End if;
Else
RAISE_APPLICATION_ERROR (-20509,'On ne peut pas promouvoir une autre piece qu un pion');
End if;
delete from Occupe where IDPartie=:new.IDPartie and IDPiece=:new.IDPiece;
insert into Occupe values(:new.IDPartie,:new.IDPiecePromo,CaseP);
-- reconstruire d閜lacement
delete from Deplacement where IDPartie=:new.IDPartie;
delete from Protection where IDPartie=:new.IDPartie;
For i in PieceT
Loop
Constr_Deplacement (i.IDPiece,i.IDPartie,i.IDCase);
end Loop;
Exception
When NO_DATA_FOUND Then
:new.IDPiecePromo:=IP||'1'||IC;
delete from Occupe where IDPartie=:new.IDPartie and IDPiece=:new.IDPiece;
insert into Occupe values(:new.IDPartie,:new.IDPiecePromo,CaseP);
-- reconstruire d閜lacement
delete from Deplacement where IDPartie=:new.IDPartie;
delete from Protection where IDPartie=:new.IDPartie;
For i in PieceT
Loop
Constr_Deplacement (i.IDPiece,i.IDPartie,i.IDCase);
end Loop;
End;
/
-- trigger pour reprise partie
create or replace trigger Controle_Reprise
After insert on ReprisePartie
For each row
declare
cursor RecCoup is
select * from Coup where IDPartie=:new.IDPartieRep order by DateCoup;
Begin
For i in RecCoup
Loop
insert into Coup values (:new.IDPartie,i.IDPiece,i.IDCase,i.DateCoup,i.Description);
Exit when RecCoup%ROWCOUNT=:new.NCoup;
End Loop;
End;
/
create or replace trigger ControleA_Coup
After Insert or Delete on Coup
For each row
Declare
PieceP Piece.IDPiece%TYPE;
Verif integer;
RoiC Echiquier.IDCase%TYPE;
IDRoi Piece.IDPiece%TYPE;
Tmp1 integer;
NbTup integer:=0;
CouleurG varchar2(20);
NbEch integer:=0;
Fois integer:=0;
ModeT Echec_Trigger.Mode_TRi%TYPE;
cursor PieceT is
select IDPartie,IDPiece,IDCase
from Occupe where IDPartie=:new.IDPartie;
cursor RecCase (IPiece Piece.IDPiece%TYPE) is
select IDCase from deplacement
where IDPiece=IPiece and IDPartie=:new.IDPartie;
cursor CaseRoi(IPiece Piece.IDPiece%TYPE) is
select IDCase from deplacement where IDPartie=:new.IDPartie and IDPiece=IPiece;
Begin
--v閞ifier que l'on efface tout
Select Mode_TRi
Into ModeT
From Echec_Trigger
where Nom='Controle_Partie';
If ModeT!='Actif' Then
-- gerer la prise d'une piece
select count(*) into Verif from Occupe where IDCase=:new.IDCase and IDPartie=:new.IDPartie;
If Verif>;0 Then
select IDPiece into PieceP from Occupe where IDCase=:new.IDCase and IDPartie=:new.IDPartie;
insert into Prise values(:new.IDPartie,PieceP);
delete from Occupe where IDPartie=:new.IDPartie and IDPiece=PieceP;
delete from Deplacement where IDPartie=:new.IDPartie and IDPiece=PieceP;
dbms_output.put_line('La piece '||PieceP||' vient d 阾re prise');
End if;
-- construction de deplacement
delete from Deplacement where IDPartie=:new.IDPartie;
delete from Protection where IDPartie=:new.IDPartie;
update Occupe set IDCase=:new.IDCase
where IDPartie=:new.IDpartie and iDPiece=:new.IDPiece;
For i in PieceT
Loop
Constr_Deplacement (i.IDPiece,i.IDPartie,i.IDCase);
end Loop;
-- controle de l'echec
Loop
If Fois=0 Then
IDRoi:='R1N';
Else
IDRoi:='R1B';
End if;
Exit when Fois=2;
select IDCase into RoiC from Occupe where IDPartie=:new.IDPartie and IDPiece=IDRoi;
select count(*) into Tmp1 from Deplacement where IDPartie=:new.IDPartie and IDCase=RoiC;
If Tmp1>;0 Then
-- roi en echec
select count(*) into Tmp1 from Deplacement where IDPartie=:new.IDPartie and IDPiece=IDRoi;
If Tmp1=0 Then
-- roi en echec et mat
-- verifier qu'aucune piece ne peut venir bloquer l'echec
update partie set Etat='Finie' where IDPartie=:new.IDPartie;
select Couleur into CouleurG from Jouer where IDPartie=:new.IDPartie;
If substr(:new.IDPiece,3,3)=substr(CouleurG,1,1) Then
update Jouer set Resultat='Gagnant' where IDPartie=:new.IDPartie;
dbms_output.put_line('Le joueur '||CouleurG||' a gagne');
Else
update Jouer set Resultat='Perdant' where IDPartie=:new.IDPartie;
If CouleurG='Blanc' Then
dbms_output.put_line('Le joueur Noir a gagne');
Else
dbms_output.put_line('Le joueur Blanc a gagne');
End if;
End if;
Else
-- on verifie que les cases ou le roi peut se deplacer sont echec
for i in CaseRoi(IDRoi)
Loop
select count(*) into Tmp1 from Deplacement
where IDPartie=:new.IDPartie and IDCase=i.IDCase and substr(IDPiece,3,3)!=substr(IDRoi,3,3);
If Tmp1>;0 Then
NbEch:=NbEch+1;
end if;
NbTup:=NbTup+1;
End loop;
If NbTup=NbEch Then
-- partie finie
dbms_output.put_line('ECHEC ET MAT !!!');
update partie set Etat='Finie' where IDPartie=:new.IDPartie;
select Couleur into CouleurG from Jouer where IDPartie=:new.IDPartie;
If substr(:new.IDPiece,3,3)=substr(CouleurG,1,1) Then
update Jouer set Resultat='Gagnant' where IDPartie=:new.IDPartie;
dbms_output.put_line('Le joueur '||CouleurG||' a gagne');
Else
update Jouer set Resultat='Perdant' where IDPartie=:new.IDPartie;
If CouleurG='Blanc' Then
dbms_output.put_line('Le joueur Noir a gagne');
Else
dbms_output.put_line('Le joueur Blanc a gagne');
End if;
End if;
Else
dbms_output.put_line('Attention:Le roi '|| IDRoi||' est en echec ');
End if;
End if;
If substr(IDRoi,3,3)=substr(:new.IDPiece,3,3) and substr(:new.IDPiece,1,1)!='R' then
delete from CoupTmp where IDPartie=:new.IDPartie and IDPiece=:new.IDPiece;
RAISE_APPLICATION_ERROR (-20508,'Le roi '|| IDRoi||' est en echec vous ne pouvez pas deplacer cette piece :'||:new.IDPiece);
End if;
-- controler le pat
NbTup:=0;
NbEch:=0;
for i in RecCase(IDRoi)
Loop
NbTup:=NbTup+1;
select count(IDPiece) into Tmp1 from Deplacement
where IDCase=i.IDCase and substr(IDPiece,3,3)!=substr(:new.IDPiece,3,3);
If Tmp1>;0 Then
NbEch:=NbEch+1;
End if;
End loop;
If NbTup=NbEch Then
update Partie set Etat='Finie' where IDPartie=:new.IDPartie;
update Jouer set Resultat='Egalite' where IDPartie=:new.IDPartie;
End if;
End if;
Fois:=Fois+1;
End loop;
End if;
End;
/
create or replace trigger ControleB_Coup
Before Insert on Coup
For each row
Declare
Verif number(2);
PriseP number(2);
Tmp1 integer;
DerPiece Piece.Couleur%TYPE;
DerCase Echiquier.IDCase%TYPE;
DerCoup Date;
RoiC Echiquier.IDCase%TYPE;
IDRoi Piece.IDPiece%TYPE;
EtatP Partie.Etat%TYPE;
Roc_Erreur exception;
CaseTmp Echiquier.IDCase%TYPE;
Lettre integer;
Chiffre char;
PieceTmp Piece.Couleur%TYPE;
Roc integer:=0;
CoupVal integer:=1;
cursor Verif_Joueur(Partie Coup.IDPartie%TYPE) is
select Coup.IDPiece,Coup.IDCase,Coup.DateCoup from Piece,Coup
where Coup.IDPartie=Partie and Coup.IDPiece=Piece.IDPiece order by DateCoup;
cursor RecCaseRocG (ICaseT Echiquier.IDCase%TYPE,ICaseR Echiquier.IDCase%TYPE) is
select IDCase from Echiquier where IDCase>;ICaseT and IDCase<ICaseR;
cursor RecCaseRocP (ICaseT Echiquier.IDCase%TYPE,ICaseR Echiquier.IDCase%TYPE) is
select IDCase from Echiquier where IDCase<ICaseT and IDCase>;ICaseR;
Begin
-- verifier que la partie n'est pas finie
select Etat into EtatP from Partie where IDPartie=:new.IDPartie;
If EtatP!='En cours' Then
RAISE_APPLICATION_ERROR (-20509,'La partie est finie elle ne peut plus etre jouee');
End if;
-- verifier que la piece est dans l'echiquier
select count(*) into Tmp1 from Piece where IDPiece=:new.IDPiece;
If Tmp1=0 Then
-- verifier que la piece n'est pas une promotion
select count(*) into Tmp1 from Promotion where IDPiecePromo=:new.IDPiece and IDPartie=:new.IDPartie;
If Tmp1=0 Then
RAISE_APPLICATION_ERROR (-20507,'Cette piece n est pas valide');
End if;
End if;
-- verifier que la piece est sur le damier
select count(*) into PriseP from Prise where IDPiece=:new.IDPiece and IDPartie=:new.IDPartie;
If PriseP>;0 Then
RAISE_APPLICATION_ERROR (-20507,'Cette piece n est plus sur le damier');
End if;
-- recherche de la couleur du denier joueur
For i in Verif_Joueur(:new.IDPartie)
Loop
DerPiece:=i.IDPiece;
DerCase:=i.IDCase;
DerCoup:=i.DateCoup;
end loop;
-- verifier la prise en passant
select Val into CoupVal from VarEchec where IDPartie=:new.IDPartie and Nom='Passant';
if CoupVal=1 Then
if substr(:new.IDPiece,3,3)='B' Then
select IDPiece into PieceTmp from Occupe where IDPartie=:new.IDPartie and IDCase=substr(:new.IDCase,1,1)||CHR(ASCII(substr(:new.IDCase,2,2))-1);
insert into Prise values (:new.IDPartie,PieceTmp);
dbms_output.put_line('La pi鑓e '||PieceTmp||' a 閠?prise');
else
select IDPiece into PieceTmp from Occupe where IDPartie=:new.IDPartie and IDCase=substr(:new.IDCase,1,1)||CHR(ASCII(substr(:new.IDCase,2,2))+1);
insert into Prise values (:new.IDPartie,PieceTmp);
end if;
update VarEchec set Val=0 where IDPartie=:new.IDPartie and Nom='Passant';
end if;
-- pour le roc on peut verifier s'il le dep est valide!!!
If substr(:new.IDPiece,3,3)=substr(DerPiece,3,3) Then
-- on regarde pour le roc
If substr(DerPiece,1,1)='R' Then
If substr(:new.IDPiece,1,1)='T' Then
-- verifier que le roi la tour n'ont pas bouge
select count(*) into Tmp1 from Coup
where IDPiece=:new.IDPiece or IDPiece=DerPiece and DateCoup!=DerCoup and IDPartie=:new.IDPartie;
If Tmp1=0 Then
Roc:=1;
If substr(:new.IDPiece,2,2)='1' Then
For i in RecCaseRocG(:new.IDCase,DerCase)
Loop
select count(*) into Tmp1 from Occupe where IDCase=i.IDCase;
If Tmp1=0 Then
select count(*) into Tmp1 from Deplacement where IDCase=i.IDCase and substr(IDPiece,3,3)!=substr(:new.IDPiece,3,3);
If Tmp1>;0 Then
RAISE_APPLICATION_ERROR (-20511,'Roc impossible :le roi passe dans une case echec');
End if;
Else
RAISE_APPLICATION_ERROR (-20510,'Le roc n est pas possible , une piece est entre le roi et la tour');
End if;
End loop;
Else
For i in RecCaseRocP(:new.IDCase,DerCase)
Loop
select count(*) into Tmp1 from Occupe where IDCase=i.IDCase;
If Tmp1=0 Then
select count(*) into Tmp1 from Deplacement where IDCase=i.IDCase and substr(IDPiece,3,3)!=substr(:new.IDPiece,3,3);
If Tmp1>;0 Then
RAISE_APPLICATION_ERROR (-20511,'Roc impossible :le roi passe dans une case echec');
End if;
Else
RAISE_APPLICATION_ERROR (-20510,'Le roc n est pas possible , une piece est entre le roi et la tour');
End if;
End loop;
End if;
Else
RAISE_APPLICATION_ERROR (-20511,'Le roi ou la tour on dej?ete deplacees');
End if;
Else
select Val into CoupVal from VarEchec where IDPartie=:new.IDPartie and Nom='Roc';
if CoupVal=1 Then
raise Roc_Erreur;
End if;
RAISE_APPLICATION_ERROR (-20506,'C est ?l autre joueur');
End if;
Else
RAISE_APPLICATION_ERROR (-20506,'C est ?l autre joueur');
End if;
Else
select Val into CoupVal from VarEchec where IDPartie=:new.IDPartie and Nom='Roc';
if CoupVal=1 Then
raise Roc_Erreur;
update VarEchec set Val=0 where IDPartie=:new.IDPartie and Nom='Roc';
dbms_output.put_line('ERREUR:C est ?l autre joueur car le deplacement du roi n est pas valide');
End if;
select count(*) into Tmp1 from Coup where IDPartie=:new.IDPartie;
if substr(:new.IDPiece,3,3)!='B' and Tmp1=0 Then
RAISE_APPLICATION_ERROR (-20506,'C est au joueur blanc de jouer');
end if;
End if;
If Roc=0 Then
-- verifier que le coup existe dans deplacement
select count(*) into Verif from Deplacement where IDPartie=:new.IDPartie
and IDPiece=:new.IDPiece and IDCase=:new.IDCase;
If Verif=0 Then
If substr(:new.IDPiece,1,1)='R' Then
-- verifier qu'il ne s'est pas deplace
select count(*) into Tmp1 from Coup
where IDPiece=:new.IDPiece and IDPartie=:new.IDPartie;
If Tmp1=0 Then
-- verifier que le roi se deplace de +2 ou -2
select IDCase into DerCase from Occupe where IDPartie=:new.IDPartie and IDPiece=:new.IDPiece;
Lettre:=ASCII(substr(DerCase,1,1));
Chiffre:=substr(DerCase,2,2);
if CHR(Lettre+2)||Chiffre=:new.IDCase or CHR(Lettre-2)||Chiffre=:new.IDCase Then
update VarEchec set Val='1' where IDPartie=:new.IDPartie and Nom='Roc';
Else
RAISE_APPLICATION_ERROR (-20505,'Le coup n est pas valide');
End if;
Else
RAISE_APPLICATION_ERROR (-20505,'Le coup n est pas valide');
End if;
Else
RAISE_APPLICATION_ERROR (-20505,'Le coup n est pas valide');
End if;
end if;
End if;
insert into CoupTmp values(:new.IDPartie,:new.IDPiece,:new.IDCase,:new.DateCoup,:new.Description);
Exception
when Roc_Erreur Then
delete from Occupe where IDPartie=:new.IDPartie and IDPiece=DerPiece;
delete from Coup where IDPartie=:new.IDPartie and IDPiece=DerPiece;
delete from CoupTmp where IDPartie=:new.IDPartie and IDPiece=DerPiece;
if substr(DerPiece,3,3)='B' Then
DerCase:='E1';
Else
DerCase:='E8';
end if;
insert into Occupe values(:new.IDPartie,DerPiece,DerCase);
update VarEchec set Val=0 where IDPartie=:new.IDPartie and Nom='Roc';
dbms_output.put_line('ERREUR:C est ?l autre joueur car le deplacement du roi n est pas valide');
End;
/