Requêtage

INSERT, UPDATE, DELETE

INSERT

Exemple 1:

INSERT INTO Table(nom colonnes) values (valeurs) 

Exemple 2:

INSERT INTO Table(nom colonnes) SELECT colonnes FROM Table2 WHERE … 

INSERT INTO clients 
(client_id, client_name, client_type) 
SELECT supplier_id, supplier_name, 'advertising' 
FROM suppliers 
WHERE not exists (select * from clients 
                  where clients.client_id = suppliers.supplier_id); 

UPDATE

UPDATE Nom_Table SET nom_colonne1=valeur1, nom_colonne2=valeur2 WHERE condition 

Avec le mot clé EXISTS:

UPDATE suppliers  
SET supplier_name =  
(SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id)  
WHERE EXISTS (SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id);

DELETE

DELETE FROM Nom_Table WHERE Condition

Avec le mot clé EXISTS:

DELETE FROM suppliers  
WHERE EXISTS (  
SELECT customers.customer_name FROM customers WHERE customers.customer_id = suppliers.supplier_id and customers.customer_name = 'IBM' );

IS NULL/IS NOT NULL

SELECT * FROM suppliers WHERE supplier_name IS NULL;

DISTINCT

SELECT DISTINCT city, state FROM suppliers;

Jointures

SELECT nom_colonnes FROM Table1
INNER JOIN Table2 ON Table1.colonne = Table2.colonne

Types de jointure

– INNER JOIN/JOIN: jointure classique = les éléments communs aux 2 tables.
– LEFT OUTER JOIN/LEFT JOIN: jointure ouverte = les éléments de la table de gauche plus les éléments communs.
– RIGHT OUTER JOIN/RIGHT JOIN: jointure ouverte = les éléments de la table de droite plus les éléments communs.
– FULL OUTER JOIN/FULL JOIN: les éléments des 2 tables plus les éléments communs.
La syntaxe est la même pour tous les types de jointure.

Equivalence avec les anciennes notations

Inner join

SELECT nom_colonnes FROM Table1  
INNER JOIN Table2 ON Table1.colonne = Table2.colonne

équivaut à:

SELECT nom_colonnes FROM Table1, Table2  
WHERE Table1.colonne = Table2.colonne

Left outer join

SELECT nom_colonnes FROM Table1 
LEFT OUTER JOIN Table2 ON Table1.colonne = Table2.colonne

équivaut à:

SELECT nom_colonnes FROM Table1, Table2  
WHERE Table1.colonne = Table2.colonne (+)

Right outer join

SELECT nom_colonnes FROM Table1 
RIGHT OUTER JOIN Table2 ON Table1.colonne = Table2.colonne

équivaut à:

SELECT nom_colonnes FROM Table1, Table2 
WHERE Table1.colonne (+) = Table2.colonne

Full outer join

SELECT nom_colonnes FROM Table1 
FULL OUTER JOIN Table2 ON Table1.colonne = Table2.colonne

n’a pas d’équivalent dans l’ancienne notation.

Sous-requêtes

Dans le WHERE

SELECT *  
FROM all_tables tabs  
WHERE tabs.table_name IN ( 
   SELECT cols.table_name  
   FROM all_tab_columns cols  
   WHERE cols.column_name = 'SUPPLIER_ID' 
);

Dans le FROM

SELECT suppliers.name, subquery1.total_amt  
FROM suppliers,  
( 
   SELECT supplier_id, SUM(orders.amount) as total_amt  
   FROM orders  
   GROUP BY supplier_id 
) subquery1  
WHERE subquery1.supplier_id = suppliers.supplier_id;

Dans le SELECT

SELECT tbls.owner, tbls.table_name,  
(SELECT COUNT(column_name) AS total_columns from all_tab_columns cols where cols.owner = tbls.owner and cols.table_name = tbls.table_name) subquery2 FROM all_tables tbls;

ORDER BY

On peut en mettre deux:

SELECT supplier_city, supplier_state  
FROM suppliers  
WHERE supplier_name = 'IBM'  
ORDER BY supplier_city DESC, supplier_state ASC;

On peut indiquer la position relative d’une colonne pour le tri:

SELECT supplier_city, supplier_state  
FROM suppliers  
WHERE supplier_name = 'IBM'  
ORDER BY 1;

Dans ce cas le tri se fera par "supplier_city".

COUNT

Pour compter le nombre d’éléments pour une colonne donnée i.e. les éléments nuls ne seront pas comptés:

SELECT COUNT(State) FROM suppliers;

Pour compter le nombre d’éléments différents pour une colonne donnée:

SELECT COUNT(DISTINCT department) as "Unique departments"  
FROM employees WHERE salary > 25000;

SUM

On peut sommer les éléments différents:

SELECT SUM(DISTINCT salary) as "Total Salary"  
FROM employees WHERE salary > 25000;

LIKE

A placer dans les conditions:
Pour appliquer une condition sur une partie d’une chaîne de caractères quelque soit le nombre de caractères:

SELECT * FROM suppliers WHERE supplier_name LIKE 'Hew%'; 
SELECT * FROM suppliers WHERE supplier_name LIKE '%Hew%'; 
SELECT * FROM suppliers WHERE supplier_name LIKE '%Hew';

Pour appliquer une condition sur une partie d’une chaîne de caractères en prenant en compte le nombre de caractères:

SELECT * FROM suppliers WHERE supplier_name LIKE 'Sm_th';

Pour appliquer les conditions sur des chaînes de caractères contenant le mot clé ‘%’ ou ‘_’, il faut définir le caractère d’échappement:

SELECT * FROM suppliers WHERE supplier_name LIKE 'H%!_' ESCAPE '!';

IN/NOT IN

SELECT * FROM suppliers  
WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');

BETWEEN

SELECT * FROM suppliers WHERE supplier_id BETWEEN 5000 AND 5010;

EXISTS/NOT EXISTS

Permet de définir une condition en utilisant une sous-requête:

SELECT * FROM suppliers  
WHERE EXISTS  
( 
    SELECT * FROM orders WHERE suppliers.supplier_id = orders.supplier_id 
);

GROUP BY

SELECT department, SUM(sales) as "Total sales"  
FROM order_details  
GROUP BY department

HAVING

Permet de rajouter une condition avec les opérateurs SUM, COUNT, MIN, MAX:

SELECT department, SUM(sales) as "Total sales"  
FROM order_details  
GROUP BY department  
HAVING SUM(sales) > 1000;

UNION

Pour additionner les résultats de 2 requêtes sans afficher les doublons:

SELECT supplier_id FROM suppliers  
UNION  
SELECT supplier_id FROM orders;

On peut ordonner les résultats en utilisant ORDER BY:

SELECT supplier_id, supplier_name FROM suppliers WHERE supplier_id > 2000 UNION  
SELECT company_id, company_name FROM companies WHERE company_id > 1000  
ORDER BY 2;

Pour afficher les doublons, il faut utiliser UNION ALL:

SELECT upplier_id FROM suppliers  
UNION ALL  
SELECT supplier_id FROM orders;

INTERSECT

Syntaxe similaire à UNION mais il permet de ramener seulement l’intersection des deux requêtes:

SELECT supplier_id FROM suppliers  
INTERSECT  
SELECT supplier_id FROM orders;

MINUS

Permet de récupérer les lignes de la 1ère requête qui ne sont pas présentes dans la 2e requête.
Même syntaxe que UNION.

Opérations sur les tables et les vues

CREATE TABLE

CREATE TABLE suppliers  
(  
    supplier_id number(10) not null,  
    supplier_name varchar2(50) not null,  
    contact_name varchar2(50)  
);

Clé primaire

CONSTRAINT customers_pk PRIMARY KEY (customer_id)

Avec la requête:

CREATE TABLE suppliers  
(  
    supplier_id number(10) not null,  
    supplier_name varchar2(50) not null,  
    contact_name varchar2(50), 
    CONSTRAINT customers_pk PRIMARY KEY (customer_id) 
);

Clé étrangère

CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id)

Avec la requête:

CREATE TABLE employees  
(  
    employee_number number(10) not null,  
    employee_name varchar2(50) not null,  
    department_id number(10), 
    salary number(6),  
    CONSTRAINT employees_pk PRIMARY KEY (employee_number),  
    CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id)  
);

ON DELETE CASCADE
Permet de lier l’entrée de la table mère à l’entrée de la table fille. Ainsi si il existe une clé étrangère entre les 2 tables et si on supprime l’entrée dans la classe mère, l’entrée liée par la clé étrangère dans la classe fille sera aussi supprimée.

CREATE TABLE products  
(  
    product_id numeric(10) not null,  
    supplier_id numeric(10) not null,  
    CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE CASCADE  
);

ON DELETE SET NULL
Permet de paramètrer "null" dans la colonne de la table fille avec la clé étrangère si l’entrée de la classe mère est supprimée:

CREATE TABLE products  
(  
    product_id numeric(10) not null,  
    supplier_id numeric(10),  
    CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE SET NULL  
);

Supprimer la contrainte sur la clé étrangère:

ALTER TABLE products drop CONSTRAINT fk_supplier;

Pour créer une table à partir d’une autre

CREATE TABLE suppliers AS  
(SELECT * FROM companies WHERE id > 1000);

ALTER TABLE

Renommer une table:

ALTER TABLE suppliers  
RENAME TO vendors;

Ajouter une ou plusieurs colonnes

ALTER TABLE supplier  
ADD (supplier_name varchar2(50), city varchar2(45));

Modifier une colonne

ALTER TABLE supplier  
MODIFY (supplier_name varchar2(100) not null, city varchar2(75));

Supprimer une colonne

ALTER TABLE supplier  
RENAME COLUMN supplier_name to sname;

DROP TABLE

DROP TABLE supplier;

TRUNCATE TABLE

Pour vider le contenu d’une table

TRUNCATE TABLE supplier;

Tables temporaires

Leur existence persiste seulement pendant la durée de la session.

CREATE GLOBAL TEMPORARY TABLE supplier  
(  
    supplier_id numeric(10) not null,  
    supplier_name varchar2(50) not null,  
    contact_name varchar2(50) 
);

Création d’une vue

Les vues sont des tables virtuelles qui n’existent pas en réalité.

CREATE VIEW sup_orders AS  
SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = 'IBM';

Suppression d’une vue

DROP VIEW view_name;

Contraintes

UNIQUE

Permet d’indiquer que les entrées de la colonne sont uniques. Cette colonne n’est pas la clé primaire (car il ne peut y avoir une colonne en tant que clé primaire et avec la contrainte unique).

CREATE TABLE table_name  
(  
    column1 datatype null/not null,  
    column2 datatype null/not null,  
    ...  
    CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)  
);

CHECK

Permet d’effectuer un contrôle entre la colonne avec la contrainte et une autre colonne de la table. Une contrainte CHECK ne peut référencer une autre table.

CREATE TABLE suppliers  
(  
    supplier_id numeric(4),  
    supplier_name varchar2(50),  
    CONSTRAINT check_supplier_name CHECK (supplier_name = upper(supplier_name))  
);

ou

...
CONSTRAINT check_supplier_id CHECK (supplier_id BETWEEN 100 and 9999)

Fonctions particulières

Conversion d’une chaîne de caractères en date

A utilisant dans une condition par exemple:

TO_DATE('2003/12/31','yyy/mm/dd')

CASE…WHEN…ELSE…END

Peut être utiliser dans une clause SELECT:

SELECT table_name,  
CASE owner
    WHEN 'SYS' THEN 'The owner is SYS'  
    WHEN 'SYSTEM' THEN 'The owner is SYSTEM'  
    ELSE 'The owner is another value'  
END  
FROM all_tables;

CONCAT

CONCAT( string1, string2 )

CURRENT_DATE

select CURRENT_DATE from dual;

DECODE

Espèce de If…then…else:

SELECT supplier_name,  
DECODE(supplier_id,  
    10000, 'IBM',  
    10001, 'Microsoft',  
    10002, 'Hewlett Packard',  
    'Gateway') result  
FROM suppliers;

est équivalent à:

if (supplier_id == 10000) 
  result := 'IBM' 
else if (supplier_id == 10001) 
  result := 'Microsoft' 
etc...

TRANSLATE

Permet de remplacer une séquence de caractères, caractère par caractère.

TRANSLATE(string1, string_to_replace, replacement_string)

TRANSLATE('1tech23', '123', '456'); would return '4tech56' 
TRANSLATE('222tech', '2ec', '3it'); would return '333tith'

NVL

NVL( string1, replace_with_if_null )

Si ‘string1’ est non nul alors ‘string1’ sinon ‘replace_with_if_null’

LPAD/RPAD

Permet de rajouter des caractères avant/après une chaîne de caractères.

LPAD('tech', 8, '0');

renvoie '0000tech'

Utilisation de LIKE dans des requêtes SQL

LIKE s’utilise avec quelques caractères pour indiquer des conditions de recherche:

Caractère d’échappement

Permet d’échapper ainsi:

PL/SQL

Différences entre une fonction et une procédure

– Une fonction ne peut ramener qu’une seule valeur et obligatoire une valeur.
– Une procédure stockée peut ramener une ou plusieurs valeurs.
– Une fonction peut être appelée par une procédure alors qu’une procédure ne peut être appelée par une fonction.
– Les procédures permettent de gérer les transactions contrairement aux fonctions.
– Les fonctions peuvent être appeler dans des clauses WHERE, HAVING et SELECT.

Déclaration d’une fonction

CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 )  
    RETURN number  
IS  
    cnumber number;  
    cursor c1 IS  
        select course_number from courses_tbl  
        where course_name = name_in;  
BEGIN  
    open c1;  
    fetch c1 into cnumber;  
    if c1%notfound then  
        cnumber := 9999;  
    end if;  
    close c1;  
RETURN cnumber;  
EXCEPTION  
WHEN OTHERS THEN  
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);  
END;

Appel d’une fonction:

SELECT FindCourse('Yop') FROM DUAL;
SELECT course_name, FindCourse(course_name) AS course_id FROM courses

Déclaration d’une procédure stockée

Les paramètres

– IN: peuvent être utilisés dans la procédure mais ils ne peuvent être modifiés.
– OUT: ils doivent obligatoirement être affectés dans la procédure.
– IN OUT: ils peuvent être utilisés dans la procédure et ils peuvent être modifiés.

Déclaration

CREATE OR REPLACE Procedure FindCourse ( name_in IN varchar2 )  
IS  
    cnumber number;  
    cursor c1 IS  
        select course_number from courses_tbl  
        where course_name = name_in;  
BEGIN  
    ... 
END;

Appel d’une procédure stockée:

Execute FindCourse('Yop');

Trigger

Procédure stockée exécutée lors d’ordres SQL: SELECT, UPDATE, INSERT ou DELETE.

On peut utiliser dans un trigger 2 variables particulières:
– OLD représentant la valeur avant modification. Elle est renseignée pour les ordres DELETE et UPDATE (elle n’est pas renseignée pour INSERT).
Pour utiliser OLD il faut utiliser la syntaxe ":OLD.[nom de la colonne]" dans le trigger.
– NEW représentant la valeur après modification. Elle est renseignée pour INSERT et UPDATE (elle n’est pas renseignée pour DELETE).
Pour utiliser NEW il faut utiliser la syntaxe ":NEW.[nom de la colonne]" dans le trigger.

La syntaxe est:

CREATE OR REPLACE TRIGGER [Nom Trigger] 
    BEFORE INSERT OR UPDATE OR DELETE 
    ON [Nom Table] 
    FOR EACH ROW  
Begin 
    If INSERTING Then 
        (…) 
    End if; 
    If UPDATING Then 
        (…) 
    End if; 
    If DELETING Then 
        (…) 
    End if; 
    -- OLD s'utilise comme si elle contenait une ligne modifiée: ":OLD.colonne1" par exemple 
    -- NEW s'utilise comme si elle contenait une ligne modifiée: ":NEW.colonne1" par exemple 
End;

Cursor

Permet d’effectuer une requête dans une function ou une procédure stockée:

CREATE OR REPLACE Function FindCourse (name_in IN varchar2)  
    RETURN number  
IS  
    cnumber number;  
    CURSOR c1 IS  
        SELECT course_number from courses_tbl where course_name = name_in;  
BEGIN  
    open c1;  
    fetch c1 into cnumber;  
    
    if c1%notfound then  
        cnumber := 9999;  
    end if;  
    close c1;  
RETURN cnumber;  
END;

On peut ramener une valeur et tester la valeur ramenée:
– %FOUND: TRUE si une valeur est ramenée et FALSE si aucune valeur n’est ramenée.
– %NOTFOUND: FALSE si une valeur est ramenée et FALSE si aucune valeur n’est ramenée.
– %ISOPEN: permet de tester si le curseur est ouvert.
– %ROWCOUNT: permet d’indiquer le nombre de lignes qui sont ramenées.

Cursor avec plusieurs lignes:
Si on doit effectuer des traitements sur toutes les lignes ramenées par le cursor:

open cTest;    
loop      
    fetch cTest into lvText;      
    exit when cTest%notfound;      
    dbms_output.put_line(lvText);    
end loop;    
close cTest;

Boucles dans une procédure stockée

IF…THEN…ELSE

IF condition  
THEN {...statements...}  
ELSIF condition  
THEN {...statements...}  
ELSE {...statements...}  
END IF;

FOR

FOR Lcntr IN 1..20  
LOOP  
    LCalc := Lcntr * 31;  
END LOOP;

LOOP

LOOP  
    monthly_value := daily_value * 31;  
    EXIT WHEN monthly_value > 4000;  
END LOOP;

WHILE

WHILE monthly_value <= 4000  
LOOP  
    monthly_value := daily_value * 31;  
END LOOP;

Autres précisions

Type de données

Différences entre CHAR et NCHAR

CHAR permet de stocker une taille fixe. Si la taille de la donnée est inférieure alors Oracle rajoute des caractères vides.

NCHAR permet de stocker jusqu’à 2000 caractères mais la chaine n’est pas ralongée si elle ne fait pas 2000 caractères.

NCHAR permet de stocker des caractères sur 2000 bytes mais il permet de stocker des caractères qui sont codés sur plusieurs bytes (Globalization support comme NVARCHAR2 et NCLOB).

Différences entre CHAR et VARCHAR2

CHAR permet de stocker jusqu’à 2000 bytes alors que VARCHAR2 permet de stocker 4000 bytes (32kb en PL/SQL):
– CHAR stockage chaîne ASCII taille fixe
– VARCHAR stockage chaîne ASCII taille variable
– VARCHAR2 stockage chaîne Unicode prenant en compte les paramètres régionaux.