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.
73 lines
1.7 KiB
73 lines
1.7 KiB
@nba-setup.sql;
|
|
|
|
DROP TABLE tligne ;
|
|
CREATE TABLE tligne (ligne varchar2(100)) ;
|
|
|
|
set echo off;
|
|
set verify off;
|
|
set feedback off;
|
|
|
|
variable vannee number
|
|
prompt Entrer une annee :
|
|
accept vannee
|
|
|
|
DECLARE
|
|
dnom varchar2(20);
|
|
dprenom varchar2(20);
|
|
dposte char(2);
|
|
dnomequipe varchar2(20);
|
|
|
|
CURSOR Cest IS SELECT j.Nom, j.Prenom, j.Poste, e.Nom FROM JOUEUR j,
|
|
APPARTIENT a, EQUIPE e WHERE a.id_joueur = j.id_joueur and e.id_equipe
|
|
= a.id_equipe and e.conference='Est' and j.id_joueur in (SELECT
|
|
id_joueur FROM JOUE je, GAME g where g.Categorie='Allstar' and
|
|
g.id_game=je.id_game);
|
|
|
|
CURSOR Couest IS SELECT j.Nom, j.Prenom, j.Poste, e.Nom FROM JOUEUR j,
|
|
APPARTIENT a, EQUIPE e WHERE a.id_joueur = j.id_joueur and e.id_equipe
|
|
= a.id_equipe and e.conference='Ouest' and j.id_joueur in (SELECT
|
|
id_joueur FROM JOUE je, GAME g where g.Categorie='Allstar' and
|
|
g.id_game=je.id_game);
|
|
|
|
BEGIN
|
|
|
|
INSERT INTO tligne VALUES ('ALLSTAR GAME'||'&vannee');
|
|
INSERT INTO tligne VALUES ('Conference Est');
|
|
|
|
OPEN Cest;
|
|
|
|
INSERT INTO tligne VALUES ('Nom Prenom Poste Nom Equipe Actuelle');
|
|
FETCH Cest INTO dnom, dprenom, dposte, dnomequipe;
|
|
|
|
WHILE Cest%found
|
|
LOOP
|
|
INSERT INTO tligne VALUES (dnom||' '||dprenom||' '||dposte||' '||dnomequipe);
|
|
FETCH Cest INTO dnom, dprenom, dposte, dnomequipe;
|
|
END LOOP;
|
|
|
|
CLOSE Cest;
|
|
|
|
INSERT INTO tligne VALUES ('Conference Ouest');
|
|
|
|
OPEN Couest;
|
|
|
|
INSERT INTO tligne VALUES ('Nom Prenom Poste Nom Equipe Actuelle');
|
|
|
|
FETCH Couest INTO dnom, dprenom, dposte, dnomequipe;
|
|
WHILE Couest%found
|
|
LOOP
|
|
INSERT INTO tligne VALUES (dnom||' '||dprenom||' '||dposte||' '||dnomequipe);
|
|
FETCH Couest INTO dnom, dprenom, dposte, dnomequipe;
|
|
END LOOP;
|
|
|
|
CLOSE Couest;
|
|
|
|
END;
|
|
.
|
|
/
|
|
|
|
SELECT * FROM tligne ;
|
|
set verify on;
|
|
set feedback on;
|
|
set echo on;
|