| Connexion SQL*Plus / SQLcl | |
|---|---|
| Objectif | Commande |
| Se connecter (SQL*Plus) | sqlplus user/motdepasse@//host:1521/servicename |
| SQLcl (outil moderne Oracle) | sql user/motdepasse@//host:1521/servicename |
| Commandes SQL*Plus utiles | SET LINESIZE 200 |
| Paramètres de session | ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'; |
| DDL - Définition des Données | |
| Créer une table | CREATE TABLE employes (
id NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
matricule VARCHAR2(20) NOT NULL UNIQUE,
nom VARCHAR2(100) NOT NULL,
prenom VARCHAR2(100),
email VARCHAR2(255),
departement VARCHAR2(50),
salaire NUMBER(10,2) DEFAULT 0,
date_embauche DATE DEFAULT SYSDATE,
actif NUMBER(1) DEFAULT 1 CHECK (actif IN (0,1)),
CONSTRAINT uq_email UNIQUE (email)
) TABLESPACE USERS; |
| ALTER TABLE | ALTER TABLE employes ADD telephone VARCHAR2(20); |
| Types de données Oracle |
Chaînes : CHAR(n) fixe, VARCHAR2(n) variable (max 4000), CLOB (gros textes), NVARCHAR2(n) UnicodeNumériques : NUMBER(p,s) p=précision s=échelle, INTEGER, FLOAT, BINARY_FLOAT, BINARY_DOUBLEDates : DATE (date+heure), TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVALBinaires : BLOB, RAW(n), LONG RAWDivers : XMLTYPE, JSON (Oracle 21c), BOOLEAN (Oracle 23c)
|
| Séquences | CREATE SEQUENCE seq_users |
| Synonymes | -- Alias pour une table (ou autre schéma) |
| Index | CREATE INDEX idx_nom ON employes(nom); |
| VUE / MATERIALIZED VIEW | -- Vue standard |
| DML - Manipulation des Données | |
| INSERT | INSERT INTO employes (matricule, nom, salaire) VALUES ('E001', 'MARTIN', 3500); |
| UPDATE | UPDATE employes SET salaire = salaire * 1.05 WHERE departement = 'IT'; |
| DELETE | DELETE FROM employes WHERE actif = 0; |
| MERGE (Upsert) | MERGE INTO employes e
USING source_employes s ON (e.matricule = s.matricule)
WHEN MATCHED THEN
UPDATE SET e.salaire = s.salaire, e.nom = s.nom
WHEN NOT MATCHED THEN
INSERT (matricule, nom, salaire)
VALUES (s.matricule, s.nom, s.salaire); |
| COMMIT / ROLLBACK / SAVEPOINT | COMMIT; -- valider |
| SELECT et Spécificités Oracle | |
| DUAL et expressions | SELECT SYSDATE FROM DUAL; -- date/heure courante |
| ROWNUM et ROWID | -- Limiter les lignes (ancienne méthode, avant Oracle 12c) |
| NULL en Oracle | -- Attention : '' (chaîne vide) = NULL en Oracle ! |
| DECODE et CASE | -- DECODE (propre à Oracle) |
| Fonctions Spécifiques Oracle | |
| Chaînes | SUBSTR(chaine, debut, longueur) -- commence à 1 |
| Dates | SYSDATE -- date+heure serveur |
| Fonctions analytiques / fenêtre | SELECT nom, departement, salaire,
RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) AS rang,
ROW_NUMBER() OVER (PARTITION BY departement ORDER BY salaire DESC) AS no_ligne,
DENSE_RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) AS rang_dense,
PERCENT_RANK()OVER (ORDER BY salaire) AS pct,
NTILE(4) OVER (ORDER BY salaire) AS quartile,
SUM(salaire) OVER (PARTITION BY departement) AS total_dept,
AVG(salaire) OVER (PARTITION BY departement) AS moy_dept,
LAG(salaire, 1, 0) OVER (ORDER BY date_embauche) AS salaire_prec,
LEAD(salaire, 1, 0) OVER (ORDER BY date_embauche) AS salaire_suiv,
FIRST_VALUE(nom) OVER (PARTITION BY departement ORDER BY salaire DESC) AS top_earner
FROM employes; |
| Jointures et Sous-requêtes | |
| Syntaxe ANSI (recommandée) | -- INNER JOIN |
| CTE (WITH) | WITH stats AS ( |
| Hiérarchie (CONNECT BY) | -- Requête hiérarchique (arbre organisationnel) |
| PL/SQL - Blocs et Structures | |
| Bloc anonyme | DECLARE
v_nom employes.nom%TYPE;
v_sal NUMBER(10,2);
v_msg VARCHAR2(200);
BEGIN
SELECT nom, salaire INTO v_nom, v_sal
FROM employes
WHERE id = 1;
IF v_sal > 5000 THEN
v_msg := v_nom || ' est senior';
ELSE
v_msg := v_nom || ' est junior';
END IF;
DBMS_OUTPUT.PUT_LINE(v_msg);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employé introuvable');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Plusieurs résultats');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erreur : ' || SQLERRM);
END;
/ |
| Curseurs | DECLARE
CURSOR c_emp IS
SELECT nom, salaire FROM employes WHERE actif = 1 ORDER BY nom;
v_rec c_emp%ROWTYPE;
BEGIN
-- Boucle FOR (recommandée, plus simple)
FOR rec IN (SELECT nom, salaire FROM employes WHERE actif = 1) LOOP
DBMS_OUTPUT.PUT_LINE(rec.nom || ' : ' || rec.salaire);
END LOOP;
-- Curseur explicite
OPEN c_emp;
LOOP
FETCH c_emp INTO v_rec;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_rec.nom);
END LOOP;
CLOSE c_emp;
END;
/ |
| Procédure stockée | CREATE OR REPLACE PROCEDURE augmenter_salaires (
p_departement IN VARCHAR2,
p_pourcentage IN NUMBER,
p_nb_modif OUT NUMBER
) AS
BEGIN
UPDATE employes
SET salaire = salaire * (1 + p_pourcentage/100)
WHERE departement = p_departement;
p_nb_modif := SQL%ROWCOUNT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END augmenter_salaires;
/
-- Appel
DECLARE v_nb NUMBER;
BEGIN
augmenter_salaires('IT', 10, v_nb);
DBMS_OUTPUT.PUT_LINE(v_nb || ' employés augmentés');
END;
/ |
| Fonction | CREATE OR REPLACE FUNCTION get_salaire_annuel (p_id IN NUMBER)
RETURN NUMBER AS
v_salaire NUMBER;
BEGIN
SELECT salaire * 12 INTO v_salaire FROM employes WHERE id = p_id;
RETURN v_salaire;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
/
-- Utilisation
SELECT nom, get_salaire_annuel(id) AS sal_annuel FROM employes; |
| Package | -- Spécification
CREATE OR REPLACE PACKAGE pkg_employes AS
PROCEDURE creer(p_nom VARCHAR2, p_sal NUMBER);
FUNCTION compter RETURN NUMBER;
END pkg_employes;
/
-- Corps
CREATE OR REPLACE PACKAGE BODY pkg_employes AS
PROCEDURE creer(p_nom VARCHAR2, p_sal NUMBER) AS
BEGIN
INSERT INTO employes(nom, salaire) VALUES(p_nom, p_sal);
COMMIT;
END creer;
FUNCTION compter RETURN NUMBER AS
v_nb NUMBER;
BEGIN
SELECT COUNT(*) INTO v_nb FROM employes;
RETURN v_nb;
END compter;
END pkg_employes;
/
-- Appel
EXEC pkg_employes.creer('Alice', 3500);
SELECT pkg_employes.compter() FROM DUAL; |
| Trigger | CREATE OR REPLACE TRIGGER trg_audit_employes
BEFORE INSERT OR UPDATE OR DELETE ON employes
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audit_log(action, table_name, rec_id, date_action)
VALUES ('INSERT', 'EMPLOYES', :NEW.id, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO audit_log(action, table_name, rec_id, date_action)
VALUES ('UPDATE', 'EMPLOYES', :OLD.id, SYSDATE);
ELSIF DELETING THEN
INSERT INTO audit_log(action, table_name, rec_id, date_action)
VALUES ('DELETE', 'EMPLOYES', :OLD.id, SYSDATE);
END IF;
END;
/ |
| Administration et Requêtes Système | |
| Gestion des utilisateurs / schémas | -- Créer un utilisateur (Oracle 12c+ multitenant : C## en CDB) |
| Vues système utiles (DBA_*/ALL_*/USER_*) | -- Tables et colonnes |
| Plan d'exécution (EXPLAIN PLAN) | EXPLAIN PLAN FOR |
| Tuer une session | -- Trouver les sessions |
| Export / Import (Data Pump) | # Export schéma complet |