SQL*Plus: Release [Link].0 Production on Mer. Nov.
27 19:35:15 2024
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> CREATE TABLE APPAREIL (
2 CodeType VARCHAR2(20) PRIMARY KEY,
3 NbPlace NUMBER,
4 Design VARCHAR2(100)
5 );
Table created.
SQL> CREATE TABLE AVION (
2 NuAvion NUMBER PRIMARY KEY,
3 Type VARCHAR2(20),
4 AnnServ NUMBER,
5 Nom VARCHAR2(50),
6 NbHvol NUMBER,
7 CONSTRAINT FK_Avion_Type FOREIGN KEY (Type) REFERENCES APPAREIL (CodeType)
8 );
Table created.
SQL> CREATE TABLE PILOTE (
2 Nopilot NUMBER PRIMARY KEY,
3 Nom VARCHAR2(50),
4 Adresse VARCHAR2(100),
5 Salaire NUMBER,
6 Comm NUMBER,
7 Embauche DATE
8 );
Table created.
SQL> CREATE TABLE VOL (
2 NoVol NUMBER PRIMARY KEY,
3 VilDep VARCHAR2(50),
4 VilArr VARCHAR2(50),
5 Dep_H NUMBER,
6 Dep_Mn NUMBER,
7 Ar_H NUMBER,
8 Ar_Mn NUMBER,
9 Ch_Jour NUMBER
10 );
Table created.
SQL> CREATE TABLE AFFECTATION (
2 Vol NUMBER,
3 DateVol DATE,
4 Pilote NUMBER,
5 Avion NUMBER,
6 NbPass NUMBER,
7 PRIMARY KEY (Vol, DateVol),
8 CONSTRAINT FK_Affectation_Vol FOREIGN KEY (Vol) REFERENCES VOL (NoVol),
9 CONSTRAINT FK_Affectation_Pilote FOREIGN KEY (Pilote) REFERENCES PILOTE
(Nopilot),
10 CONSTRAINT FK_Affectation_Avion FOREIGN KEY (Avion) REFERENCES AVION
(NuAvion)
11 );
Table created.
SQL> INSERT INTO APPAREIL VALUES ('B737', 180, 'Boeing 737 - Standard');
1 row created.
SQL> INSERT INTO APPAREIL VALUES ('A320', 160, 'Airbus A320 - Economy');
1 row created.
SQL> INSERT INTO AVION VALUES (101, 'B737', 2015, 'Airbus', 1200);
1 row created.
SQL> INSERT INTO AVION VALUES (102, 'A320', 2018, 'Boeing', 900);
1 row created.
SQL> INSERT INTO PILOTE VALUES (1, 'Jean Dupont', '123 Rue Paris', 3500, 500,
TO_DATE('2020-01-15', 'YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO PILOTE VALUES (2, 'Alice Moreau', '456 Rue Lyon', 4200, 800,
TO_DATE('2018-06-01', 'YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO VOL VALUES (301, 'Paris', 'Londres', 10, 30, 12, 45, 5);
1 row created.
SQL> INSERT INTO VOL VALUES (302, 'Tunis', 'Rome', 8, 0, 10, 15, 2);
1 row created.
SQL> INSERT INTO AFFECTATION VALUES (301, TO_DATE('2024-11-25', 'YYYY-MM-DD'), 1,
101, 150);
1 row created.
SQL> INSERT INTO AFFECTATION VALUES (302, TO_DATE('2024-11-26', 'YYYY-MM-DD'), 2,
102, 140);
1 row created.
SQL> create or replace procedure afficher( villedep in [Link] %type)is ep
number :=0;
2 exp exception;
3 begin
4 select count (*) into cp from vol
5 WHERE vildep = villedep
6 if cp =0 then raise exp;
7 end if;
8 for VOL in (
9 SELECT novol,[Link],dep_h.ar_h from TBL_VOL
10 where vildep = villedep) loop
11 dbms_output.put_line('numero de vol:' || [Link]);
12 dbms_output.put_line('ville de depart:' ||[Link]);
13 dbms_output.put_line('ville d arrivee: || [Link]);
14 dbms_output.put_line('| heure de depart: || VOL.dep_h);
15 dbms_output.put_line('| heure d arrivee:'|| VOL.ar_h);
16 end loop;
17 exception
18 WHEN exp then
19 dbms_output.put_line('ville de depart n existe pas ');
20 end;
21 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE AFFICHER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
5/25 PL/SQL: ORA-00933: SQL command not properly ended
7/5 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:
; <an identifier> <a double-quoted delimited-identifier>
current delete exists prior <a single-quoted SQL string>
SQL> create or replace procedure afficher( villedep in [Link] %type)is
2 cp number :=0;
3 exp exception;
4 begin
5 select count (*) into cp from vol
6 WHERE vildep = villedep
7 if cp =0 then raise exp;
8 end if;
9 for VOL in (
10 SELECT novol,[Link],dep_h.ar_h from TBL_VOL
11 where vildep = villedep) loop
12 dbms_output.put_line('numero de vol:' || [Link]);
13 dbms_output.put_line('ville de depart:' ||[Link]);
14 dbms_output.put_line('ville d arrivee: || [Link]);
15 dbms_output.put_line('| heure de depart: || VOL.dep_h);
16 dbms_output.put_line('| heure d arrivee:'|| VOL.ar_h);
17 end loop;
18 exception
19 WHEN exp then
20 dbms_output.put_line('ville de depart n existe pas ');
21 end;
22 /
Warning: Procedure created with compilation errors.
SQL> CREATE OR REPLACE PROCEDURE afficher(villedep IN [Link]%TYPE) IS
2 cp NUMBER := 0;
3 exp EXCEPTION;
4 BEGIN
5 SELECT COUNT(*)
6 INTO cp
7 FROM VOL
8 WHERE vildep = villedep;
9 IF cp = 0 THEN
10 RAISE exp;
11 END IF;
12 FOR vol_rec IN (
13 SELECT novol, vildep, vilarr, dep_h, ar_h
14 FROM VOL
15 WHERE vildep = villedep
16 ) LOOP
17 DBMS_OUTPUT.PUT_LINE('Numéro de vol: ' || vol_rec.novol);
18 DBMS_OUTPUT.PUT_LINE('Ville de départ: ' || vol_rec.vildep);
19 DBMS_OUTPUT.PUT_LINE('Ville d''arrivée: ' || vol_rec.vilarr);
20 DBMS_OUTPUT.PUT_LINE('Heure de départ: ' || vol_rec.dep_h);
21 DBMS_OUTPUT.PUT_LINE('Heure d''arrivée: ' || vol_rec.ar_h);
22 END LOOP;
23 EXCEPTION
24 WHEN exp THEN
25 DBMS_OUTPUT.PUT_LINE('Ville de départ n''existe pas.');
26 END;
27 /
Procedure created.
SQL> CREATE OR REPLACE FUNCTION salaire_moyen(salairemin IN [Link]%TYPE)
RETURN NUMBER IS
2 moy NUMBER := 0;
3 BEGIN
4 SELECT AVG(salaire)
5 INTO moy
6 FROM PILOTE
7 WHERE salaire > salairemin;
8
9 IF moy IS NULL THEN
10 RETURN 0;
11 END IF;
12
13 RETURN moy;
14
15 EXCEPTION
16 WHEN OTHERS THEN
17 RETURN 0; -- Return 0 in case of any exception
18 END salaire_moyen;
19 /
Function created.
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
2 salaire_moyen_calcule NUMBER;
3 ville_depart VARCHAR2(50) := 'MARSEILLE';
4 salaire_minimum NUMBER := 1500;
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('*** Informations sur les vols ***');
7 afficher(ville_depart);
8 DBMS_OUTPUT.PUT_LINE('*** Calcul du salaire moyen ***');
9 salaire_moyen_calcule := salaire_moyen(salaire_minimum);
10 DBMS_OUTPUT.PUT_LINE('Le salaire moyen pour un salaire supérieur à ' ||
salaire_minimum || ' est : ' || salaire_moyen_calcule);
11
12 EXCEPTION
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE('Une erreur est survenue : ' || SQLERRM);
15 END;
16 /
*** Informations sur les vols ***
Ville de départ n'existe pas.
*** Calcul du salaire moyen ***
Le salaire moyen pour un salaire supérieur à 1500 est : 3850
PL/SQL procedure successfully completed.
SQL> CREATE TABLE History (
2 idav NUMBER,
3 date_operation DATE,
4 type_operation VARCHAR2(50)
5 );
Table created.
SQL> CREATE OR REPLACE TRIGGER trg_avion_history
2 AFTER UPDATE ON Avion
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO History (idav, date_operation, type_operation)
6 VALUES (:[Link], SYSDATE, 'UPDATE');
7 END;
8 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER Tot_vol
2 AFTER INSERT OR DELETE ON Vol
3 DECLARE
4 total_vols NUMBER;
5 BEGIN
6 SELECT COUNT(*) INTO total_vols FROM Vol;
7 DBMS_OUTPUT.PUT_LINE('Nombre total de vols : ' || total_vols);
8 END;
9 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER Modif_sal_pil
2 BEFORE UPDATE OF Salaire ON Pilote
3 FOR EACH ROW
4 WHEN ([Link] > [Link])
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('Ancien salaire : ' || :[Link]);
7 DBMS_OUTPUT.PUT_LINE('Nouveau salaire : ' || :[Link]);
8 DBMS_OUTPUT.PUT_LINE('Différence : ' || (:[Link] - :[Link]));
9 END;
10 /
Trigger created.
SQL> CREATE SEQUENCE Seq_Pilote
2 START WITH 8000
3 INCREMENT BY 10;
Sequence created.
SQL> CREATE OR REPLACE TRIGGER Trig_NumP
2 BEFORE INSERT ON Pilote
3 FOR EACH ROW
4 BEGIN
5 :[Link] := Seq_Pilote.NEXTVAL;
6 END;
7 /
Trigger created.
SQL> INSERT INTO Pilote (Nom, Adresse, Salaire, Comm, Embauche)
2 VALUES ('Pierre Martin', '789 Rue Nice', 4000, 300, TO_DATE('2024-01-01',
'YYYY-MM-DD'));
1 row created.
SQL> SELECT * FROM Pilote;
NOPILOT NOM
---------- --------------------------------------------------
ADRESSE
--------------------------------------------------------------------------------
SALAIRE COMM EMBAUCHE
---------- ---------- --------
1 Jean Dupont
123 Rue Paris
3500 500 15/01/20
2 Alice Moreau
456 Rue Lyon
4200 800 01/06/18
NOPILOT NOM
---------- --------------------------------------------------
ADRESSE
--------------------------------------------------------------------------------
SALAIRE COMM EMBAUCHE
---------- ---------- --------
8000 Pierre Martin
789 Rue Nice
4000 300 01/01/24
SQL>