You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
47 lines
1.1 KiB
47 lines
1.1 KiB
@2020nba-setup.sql;
|
|
|
|
DROP TABLE TLIGNE;
|
|
CREATE TABLE TLIGNE(LIGNE VARCHAR2(200));
|
|
|
|
variable vidgame varchar2(10)
|
|
prompt Entrer un game
|
|
accept vidgame
|
|
|
|
DECLARE
|
|
dnom varchar2(200);
|
|
dprenom varchar2(200);
|
|
dposte char(2);
|
|
didjoueur varchar2(20);
|
|
dnbfautes number;
|
|
dfautes number;
|
|
|
|
CURSOR Cnom IS SELECT j.id_joueur, j.Nom, j.Prenom, j.Poste
|
|
FROM JOUEUR j, JOUE e
|
|
WHERE j.id_joueur = e.id_joueur AND e.id_game = '&vidgame';
|
|
|
|
CURSOR Cfaute IS SELECT FAUTES, COUNT(ID_game) FROM JOUE WHERE id_joueur = didjoueur GROUP BY FAUTES ORDER BY FAUTES;
|
|
|
|
|
|
BEGIN
|
|
INSERT INTO tligne VALUES ('Nom Prenom Poste');
|
|
|
|
OPEN Cnom;
|
|
FETCH Cnom INTO didjoueur, dnom, dprenom, dposte;
|
|
WHILE Cnom%found LOOP
|
|
INSERT INTO tligne VALUES (dnom||' '||dprenom||' '||dposte);
|
|
OPEN Cfaute;
|
|
INSERT INTO tligne VALUES ('Faute Nb Matchs');
|
|
FETCH Cfaute INTO dfautes, dnbfautes;
|
|
WHILE Cfaute%found LOOP
|
|
INSERT INTO tligne VALUES (TO_CHAR(dfautes)||' '||TO_CHAR(dnbfautes));
|
|
FETCH Cfaute INTO dfautes, dnbfautes;
|
|
END LOOP;
|
|
CLOSE Cfaute;
|
|
FETCH Cnom INTO didjoueur, dnom, dprenom, dposte;
|
|
END LOOP;
|
|
CLOSE Cnom;
|
|
END;
|
|
.
|
|
/
|
|
SELECT * FROM tligne ;
|