420-5D5 : Oracle – Labo.example
5 octobre 2023
Contexte
Oracle.Tutorial fournit une base de données ‘Exemple’ pour l’expérimentation avec le SGBD Oracle.
Nous utiliserons cette DB pour les apprentissages sur les fonctions, procédures, déclencheurs, …
Les directives pour l’installation de la BD sont disponibles ici
Voici le schéma de la base de données
Laboratoire partie A
Il faut créer, sur le SGBD à 192.168.138.58 (user:sys, pws: 4205D3) une PDB nommé ‘M’+ votre matricule, avec un utilisateur nommé ‘ot’.
-- Par exemple, show con_name; -- Si pas au contexte racine alors: ALTER SESSION SET CONTAINER = CDB$ROOT CREATE PLUGGABLE DATABASE M12345678 ADMIN ot IDENTIFIED BY password create_file_dest='/home/oracle' ; show pdbs; ALTER SESSION SET CONTAINER = M12345678 ; ALTER DATABASE OPEN; GRANT CONNECT, RESOURCE, DBA TO ot; -- Puis, ouvrir une nouvelle connexion avec l'utilisateur 'ot'
Créer le schéma de la base de données
-- regions
CREATE TABLE regions
(
region_id NUMBER GENERATED BY DEFAULT AS IDENTITY
START WITH 5 PRIMARY KEY,
region_name VARCHAR2( 50 ) NOT NULL
);
-- countries table
CREATE TABLE countries
(
country_id CHAR( 2 ) PRIMARY KEY ,
country_name VARCHAR2( 40 ) NOT NULL,
region_id NUMBER , -- fk
CONSTRAINT fk_countries_regions FOREIGN KEY( region_id )
REFERENCES regions( region_id )
ON DELETE CASCADE
);
-- location
CREATE TABLE locations
(
location_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 24
PRIMARY KEY ,
address VARCHAR2( 255 ) NOT NULL,
postal_code VARCHAR2( 20 ) ,
city VARCHAR2( 50 ) ,
state VARCHAR2( 50 ) ,
country_id CHAR( 2 ) , -- fk
CONSTRAINT fk_locations_countries
FOREIGN KEY( country_id )
REFERENCES countries( country_id )
ON DELETE CASCADE
);
-- warehouses
CREATE TABLE warehouses
(
warehouse_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 10
PRIMARY KEY,
warehouse_name VARCHAR( 255 ) ,
location_id NUMBER( 12, 0 ), -- fk
CONSTRAINT fk_warehouses_locations
FOREIGN KEY( location_id )
REFERENCES locations( location_id )
ON DELETE CASCADE
);
-- employees
CREATE TABLE employees
(
employee_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 108
PRIMARY KEY,
first_name VARCHAR( 255 ) NOT NULL,
last_name VARCHAR( 255 ) NOT NULL,
email VARCHAR( 255 ) NOT NULL,
phone VARCHAR( 50 ) NOT NULL ,
hire_date DATE NOT NULL ,
manager_id NUMBER( 12, 0 ) , -- fk
job_title VARCHAR( 255 ) NOT NULL,
CONSTRAINT fk_employees_manager
FOREIGN KEY( manager_id )
REFERENCES employees( employee_id )
ON DELETE CASCADE
);
-- product category
CREATE TABLE product_categories
(
category_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 6
PRIMARY KEY,
category_name VARCHAR2( 255 ) NOT NULL
);
-- products table
CREATE TABLE products
(
product_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 289
PRIMARY KEY,
product_name VARCHAR2( 255 ) NOT NULL,
description VARCHAR2( 2000 ) ,
standard_cost NUMBER( 9, 2 ) ,
list_price NUMBER( 9, 2 ) ,
category_id NUMBER NOT NULL ,
CONSTRAINT fk_products_categories
FOREIGN KEY( category_id )
REFERENCES product_categories( category_id )
ON DELETE CASCADE
);
-- customers
CREATE TABLE customers
(
customer_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 320
PRIMARY KEY,
name VARCHAR2( 255 ) NOT NULL,
address VARCHAR2( 255 ) ,
website VARCHAR2( 255 ) ,
credit_limit NUMBER( 8, 2 )
);
-- contacts
CREATE TABLE contacts
(
contact_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 320
PRIMARY KEY,
first_name VARCHAR2( 255 ) NOT NULL,
last_name VARCHAR2( 255 ) NOT NULL,
email VARCHAR2( 255 ) NOT NULL,
phone VARCHAR2( 20 ) ,
customer_id NUMBER ,
CONSTRAINT fk_contacts_customers
FOREIGN KEY( customer_id )
REFERENCES customers( customer_id )
ON DELETE CASCADE
);
-- orders table
CREATE TABLE orders
(
order_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 106
PRIMARY KEY,
customer_id NUMBER( 6, 0 ) NOT NULL, -- fk
status VARCHAR( 20 ) NOT NULL ,
salesman_id NUMBER( 6, 0 ) , -- fk
order_date DATE NOT NULL ,
CONSTRAINT fk_orders_customers
FOREIGN KEY( customer_id )
REFERENCES customers( customer_id )
ON DELETE CASCADE,
CONSTRAINT fk_orders_employees
FOREIGN KEY( salesman_id )
REFERENCES employees( employee_id )
ON DELETE SET NULL
);
-- order items
CREATE TABLE order_items
(
order_id NUMBER( 12, 0 ) , -- fk
item_id NUMBER( 12, 0 ) ,
product_id NUMBER( 12, 0 ) NOT NULL , -- fk
quantity NUMBER( 8, 2 ) NOT NULL ,
unit_price NUMBER( 8, 2 ) NOT NULL ,
CONSTRAINT pk_order_items
PRIMARY KEY( order_id, item_id ),
CONSTRAINT fk_order_items_products
FOREIGN KEY( product_id )
REFERENCES products( product_id )
ON DELETE CASCADE,
CONSTRAINT fk_order_items_orders
FOREIGN KEY( order_id )
REFERENCES orders( order_id )
ON DELETE CASCADE
);
-- inventories
CREATE TABLE inventories
(
product_id NUMBER( 12, 0 ) , -- fk
warehouse_id NUMBER( 12, 0 ) , -- fk
quantity NUMBER( 8, 0 ) NOT NULL,
CONSTRAINT pk_inventories
PRIMARY KEY( product_id, warehouse_id ),
CONSTRAINT fk_inventories_products
FOREIGN KEY( product_id )
REFERENCES products( product_id )
ON DELETE CASCADE,
CONSTRAINT fk_inventories_warehouses
FOREIGN KEY( warehouse_id )
REFERENCES warehouses( warehouse_id )
ON DELETE CASCADE
);
Insérer les enregistrements dans la BD
Il faut utiliser le contenu du fichier ot_data.sql
NOTE: Vous allez rencontrer des problèmes:
- Format des dates
- Référence à la table ‘EMPLOYEES’
À VOUS DE TROUVER DES SOLUTIONS
Tester les données en affichant la liste des employés.
Au besoin, un DROP et on recommence
DROP TABLE order_items; DROP TABLE orders; DROP TABLE inventories; DROP TABLE products; DROP TABLE product_categories; DROP TABLE warehouses; DROP TABLE employees; DROP TABLE contacts; DROP TABLE customers; DROP TABLE locations; DROP TABLE countries; DROP TABLE regions;
Laboratoire partie B
Mettre en place l’infrastructure requise pour les parties évaluées du cours
- Créer une VM dans le cloud de google, sur un projet CSTJ
- 2 CPU
- 4 GO de RAM
- Disque de 40GO
- Installer un OS (Ubuntu?)
- Installer docker.io
- Lancer un conteneur Oracle 23c ou,
- pour les plus téméraires, installer Oracle 23c dans la VM
- Définir une règle ‘Ingress’ dans le ‘firewall’ du projet google cloud, de type ‘TAG’, pour le port utilisé par Oracle et l’associer à votre VM.
- Mettre en place la base de données Démo
- Tester une connexion à partir de Oracle SQL Developper sur votre poste de travail
- En cas de problème, il faut proposer une solution, firewall, docker, …?
- Faire un test à partir d’un terminal sur le serveur: $ sqlplus …
- En cas de problème, il faut proposer une solution, firewall, docker, …?
- Voir le document – connexion avec php pour la suite …
NOTE TELLEMENT IMPORTANTE: Il faut FERMER la VM lorsqu’elle n’est pas utilisée!!!
FIN DU DOCUMENT
--- Scratch PAD
--- Exemple d'un champ de type AUTO-INCREMENT
--- GENERATED BY DEFAULT AS IDENTITY (facultaitf)
--- GENERATED ALWAYS AS IDENTITY (séquence automatique obligatoire)
--- (START WITH 10 INCREMENT BY 5)
CREATE TABLE Pays (
pays_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
nom VARCHAR2(80)
);
INSERT INTO pays(nom) VALUES ('Québec');
INSERT INTO pays(pays_id, nom) VALUES (2, 'Canada');
SELECT * FROM pays;
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--- Des bandes dessinées
CREATE TABLE Auteurs (
auteur_id NUMBER PRIMARY KEY,
nom VARCHAR2(60)
);
INSERT INTO Auteurs (auteur_id, nom) VALUES (1, 'Herge');
CREATE TABLE Albums (
album_id NUMBER PRIMARY KEY,
titre VARCHAR2(255),
annee_publication NUMBER,
auteur_id NUMBER,
FOREIGN KEY (auteur_id) REFERENCES Auteurs(auteur_id),
nombre_pages NUMBER,
isbn VARCHAR2(20),
photo_couverture VARCHAR2(255)
);
----
INSERT INTO Albums (titre, annee_publication, auteur_id, nombre_pages, isbn, photo_couverture)
VALUES
('Tintin au pays des Soviets', 1929, 1, 144, '978-2203001016', 'tintin_soviets.jpg'),
('Tintin au Congo', 1931, 1, 62, '978-2203001023', 'tintin_congo.jpg'),
('Tintin en Amérique', 1932, 1, 120, '978-2203001030', 'tintin_amerique.jpg'),
('Les Cigares du Pharaon', 1934, 1, 128, '978-2203001047', 'tintin_cigares_pharaon.jpg'),
('Le Lotus bleu', 1936, 1, 80, '978-2203001054', 'tintin_lotus_bleu.jpg'),
('L''Oreille cassée', 1943, 1, 62, '978-2203001061', 'tintin_oreille_cassee.jpg'),
('L''Île noire', 1938, 1, 62, '978-2203001078', 'tintin_ile_noire.jpg'),
('L''Étoile mystérieuse', 1942, 1, 62, '978-2203001108', 'tintin_etoile_mysterieuse.jpg'),
('Le Secret de La Licorne', 1943, 1, 62, '978-2203001115', 'tintin_secret_licorne.jpg'),
('Le Trésor de Rackham le Rouge', 1944, 1, 62, '978-2203001122', 'tintin_tresor_rackham_rouge.jpg');
--- Modifier le champ album_id pour le rendre AUTO-INCREMENT
ALTER TABLE Albums
ALTER COLUMN album_id
SET GENERATED BY DEFAULT;
--- Pour enlever le compteur
ALTER TABLE Albums
ALTER COLUMN album_id
DROP IDENTITY IF EXISTS;
------------------------------------------------
select titre, auteurs.nom from albums inner JOIN auteurs ON albums.auteur_id = auteurs.auteur_id ;
select al.titre, a.nom from albums al inner JOIN auteurs a ON al.auteur_id = a.auteur_id ;