Caractéristiques Bâti sur le modèle relationnel

CaractéristiquesCaractéristiques

Bâti sur le modèle relationnel.

Destiné à un large public (informaticien ou non).

Langage non procédural.

Le seul langage pour dialoguer avec la base de données.

Son exploitation procédurale passe le PLSQL.

Présentation du langage SQL / ORACLEPrésentation du langage SQL / ORACLE

Langage Manipulation Données- LMD -

Langage Manipulation Données- LMD -

SQL ORACLESQL ORACLE

Langage Définition Données- LDD -

Langage Définition Données- LDD -

Contrôle Transactions

Contrôle Transactions

Contrôle sessionsContrôle sessions Contrôle SystèmeContrôle Système

SELECTINSERTUPDATEDELETETRUNCATECALLEXPLAIN PLANLOCK TABLE

CREATEALTERDROPGRANTREVOKEANALYZE AUDITCOMMENT

COMMITROLLBACKSAVEPOINTSET TRANSACTION

ALTER SESSIONSET ROLE

ALTER SYSTEM

Langage Manipulation des Données

LMD

SELECT [DISTINCT | ALL] { * | { [schema.]{table | view | snapshot}.* | expr } [ [AS] c_alias ] [, { [schema.]{table | view | snapshot}.* | expr } [ [AS] c_alias ] ] ... } FROM [schema.]{table | view | subquery | snapshot}[@dblink] [t_alias] [, [schema.]... ] ... [WHERE condition ]

[START WITH condition] [CONNECT BY condition]

[[GROUP BY expr [, expr] ... [HAVING condition] ]

[{UNION | UNION ALL | INTERSECT | MINUS}

SELECT command ] [ORDER BY {expr|position} [ASC | DESC] [, {expr|position} [ASC | DESC]] ...][FOR UPDATE [OF [[schema.]{table | view}.]column [, [[schema.]{table | view}.]column] ..] [NOWAIT]

ORDRE SELECTORDRE SELECT

Clause WHERE

• Opérateurs de comparaison : = != <> < > <= >=

• Prédicat de comparaison : BETWEEN v1 AND v2 IN (v1,v2,v3) IN (SELECT * ……) LIKE ‘’%chaine%’’ chaîne impérativement en majuscule. IS NULL / IS NOT NULL. AND OR NOT. EXISTS

Clause ORDER BYTri par ordre croissant ASC ou décroissant DESC sur une ou plusieurs colonnes.

UNION UNION ALL INTERSECT MINUS Opérations ensemblistes sur les résultats des requêtes.

UNION : toutes les lignes sélectionnées, mais sans doublons. UNION ALL : toutes les lignes sélectionnées avec doublons. INTERSECT : Les lignes communes entre les deux requêtes. MINUS : Les lignes sélectionnées dans une requête et non dans la deuxième requête.

Clause CONNECT BY … START WITHEffectue une extraction des lignes selon un ordre hiérarchique prédéfini au niveau de la table.Utilisée dans le cas des relations réflexives.


Exemple SELECT no,nom,titre,no_supFROM e_empCONNECT BY PRIOR no=no_supSTART with titre=‘PRESIDENT’;

Ordre GROUP BY … HAVINGRéalise des regroupements sur plusieurs enregistrements selon un ou plusieurs critères, en vue d’un affichage groupé et/ou de l’utilisation des fonctions de groupe.

Les fonctions de groupe


FONCTION ROLE

AVG Moyenne arithmétique

SUM Somme

MIN Minimum d’une liste de valeurs

MAX Maximum d’une liste de valeurs

COUNT Nombre d’apparition (ligne ou colonne)

VARIANCE variance

STDDEV Écart type

Exemples

SELECT MIN(salaire) "MIN", MAX(salaire) "MAX", AVG(salaire) MOYENNE, STDDEV(salaire) Ecart_type, SUM(salaire) SOMMEFROM e_empWHERE service_no=41;

SELECT count(*) "Nombre Employés" FROM e_emp;

Remarques importantes Les colonnes figurant dans un SELECT ayant une clause GROUP ne sont que :

• Des colonnes figurant dans la clause GROUP BY.• Des fonctions de groupe.

Exemples

SELECT service_no,SUM(salaire) SALAIRESFROM e_empGROUP by service_no;

SELECT MAX(count(*))FROM e_empGROUP BY service_no;


Sélection

Equi-jointure/Jointure externe

Projection

Comportements d’une Requête SQL


Opérateurs et fonctionsOpérateurs et fonctions

Types numériques

Types numériques

Non typésNon typés Type dateType dateType alphanumérique

Type alphanumérique

DECODECASE

GREATESTLEAST

NVL

DECODECASE

GREATESTLEAST

NVL

SYSDATEADD_MONTHS

NEXT_DAYLAST_DAY

MONTHS_BETWEEN

SYSDATEADD_MONTHS

NEXT_DAYLAST_DAY

MONTHS_BETWEEN

SUBSTRINSTRUPPERLOWERLTRIMLPAD

…

SUBSTRINSTRUPPERLOWERLTRIMLPAD

…

ABSCEIL

FLOORMOD

POWERROUNDSQRTCOS

…

ABSCEIL

FLOORMOD

POWERROUNDSQRTCOS

…

Fonctions non typéesFonctions non typées

DECODE DECODE( crit, val1, result1 [, val2, result2] ... [, default] )

Elle renvoie result1 si critère =val1, result2 si critère=val2,… ou défault dans tous les autres cas.

Ex : SELECT DECODE (deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATION',

'NONE') FROM emp;,

CASE CASE WHEN cond1 THEN expr1 [cond2 THEN expr2 …] [ELSE expr] END

En fonction de la condition vérifiée, l'expression expr est éxecutée.

GREATEST GREATEST(expr1,expr2,…)

Renvoie la plus grande valeur parmi expr1,expr2,…

LEAST LEAST(expr1,expr2,

Renvoie la plus petite valeur parmi expr1,expr2,…

NVL NVL(expr1,expr2)

Renvoie la valeur de expr1 si elle est NOT NULL, sinon expr2.

Fonctions numériquesFonctions numériques

Fonction Syntaxe Rôle

ABS ABS(n NUMBER) : NUMBER Valeur absolue de n

CEIL CEIL(n NUMBER) : NUMBER Entier immédiatement >= à n

FLOOR FLOOR((n NUMBER) : NUMBER Entier immédiatement <= à n

MOD MOD(n1 NUMBER,n2 NUMBER) : NUMBER Reste de division de n1 sur n2

POWER POWER(n1 NUMBER,e NUMBER) : NUMBER Renvoie n1 puissance e

ROUND ROUND(left NUMBER,right BINARY_INTEGER) : NUMBER

SIGN SIGN(n NUMBER) : SIGNTYPE Renvoie un signe

SQRT SQRT(n NUMBER) : NUMBER Racine carrée de n

COS COS(n NUMBER) : NUMBER Cosinus de n

SIN SIN(n NUMBER) : NUMBER Sinus de n

TAN TAN(n NUMBER) : NUMBER Tangente de n

LN LN(n NUMBER) : NUMBER Logarithme népérien

EXP EXP(n NUMBER) : NUMBER Exponentielle de n

LOG LOG(n NUMBER) : NUMBER Logarithme décimal de n

Certaines des fonctions numériques utilisées sous SQL


LENGTH LENGTH(str VARCHAR2) : numérique Longueur d'une chaîne

SUBSTR SUBSTR(str VARCHAR2,pos,length) : chaîne Renvoie une sous-chaîne

INSTR INSTR(str1 VARCHAR2,str2 VARCHAR2,pos,nocc)

Pos et nocc sont facultatives

Position d'une ss-chaîne dans une chaîne

UPPER UPPER(str VARCHAR2) : chaîne Renvoie en MAJUSCULE

LOWER LOWER(str VARCHAR2) : chaîne Renvoie en minuscule

LPAD LPAD(char1,n [,char2])

pad est facultatif

Complète à gauche la chaîne par des pads.

RPAD RPAD(str VARCHAR2,len,pad) : chaîne

pad est facultatif

Complète à gauche la chaîne par des pads.

LTRIM LTRIM(VARCHAR2[,set]) Supprime les sets à gauche

RTRIM LTRIM(VARCHAR2[,set]) Supprime les sets à droite

REPLACE REPLACE(char, search_string [,replacement_string]) Remplacement d'une ss-chaîne dans une chaîne

ASCII ASCII(ch CHAR) Code ASCII d'un caractère

INITCAP INITCAP(str VARCHAR2) : chaîne Renvoie première lettre en CAPITAL

CHR CHR(n NUMBER) : NUMBER Caractère du code ASCII n

Fonctions alphanumériquesFonctions alphanumériques

Fonctions DATEFonctions DATE


SYSDATE SYSDATE Retourne la date OS

ADD_MONTHS ADD_MONTHS(date1,n) Rajoute n mois à la date date1

MONTHS_BETWEEN MONTHS_BETWEEN(date1,date2) Nombre de mois entre date1 et date2

LAST_DAY LAST_DAY(date1) Date du dernier du mois de date1

NEXT_DAY NEXT_DAY(date1,str) Date du prochain jour indiqué par str

TO_DATE TO_DATE(char [, fmt [, 'nlsparams'] ])

Conversion d'une chaîne de caractère en date selon le format fmt

ROUND ROUND(date1[,fmt]) Arrondit date1 selon le format fmt


INSERT INTO

[schema.]{table | view | subquery }[@dblink] [ (column [, column] ...) ] {VALUES (expr [, expr] ...) | subquery }

Exemple

INSERT INTO e_continentVALUES (7,'Arctiue');

1 ligne insérée

ORDRE UPDATEORDRE UPDATE

UPDATE [schema.]{table | view | subquery}[@dblink] [alias]

SET { (column [, column] ...) = (subquery) | column = { expr | (subquery) } } [, { (column [, column] ...) = (subquery) | column = { expr | (subquery) } } ] ...

[WHERE condition]

Exemple

UPDATE emp SET enom='BENOIT', salaire=2000WHERE empno=7155;

1 ligne mise à jour

ORDRE DELETEORDRE DELETE

DELETE

[FROM] [schema.]{table | view}[@dblink] [alias]

[WHERE condition]

Exemple

DELETE FROM empWHERE empno=7155;

1 ligne supprimée

ORDRE TRUNCATEORDRE TRUNCATE

TRUNCATE {TABLE [schema.]table | CLUSTER [schema.]cluster}

[ {DROP | REUSE} STORAGE]

Permet la suppression de toutes les lignes d'une table.

ORDRE EXPLAIN PLANORDRE EXPLAIN PLAN

Cette commande permet de déterminer le plan d'exécution d'un requête SQL, en donnant le coût d'exécution en terme de nombre d'accès.

Syntaxe EXPLAIN PLAN FORRequête SQL;

Les résultats sont consigés dans la view TABLE_PLAN, créée par le script UTLXPLAN.sql

SQL>desc TABLE_PLANExemple

EXPLAIN PLAN FORSELECT * FROM SCOTT.EMP;

SELECT LPAD(' ',2*(LEVEL-1)||operation "Opération",options,object_name,positionFROM plan_table;

ORDRE LOCK TABLEORDRE LOCK TABLE

Cette ordre permet de poser des verrous sur les enregistrements d'une table.

Syntaxe LOCK TABLE [schema.]{table | view}[@dblink] [, [schema.]{table | view}[@dblink] ]... IN lockmode MODE [NOWAIT]

Les valeurs du paramètre lockmode sont :ROW SHARE ROW EXCLUSIVE SHARE UPDATE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE

ExempleLOCK TABLE emp IN ROW SHARE MODE NOWAIT

Le mode NOWAIT : Si la table est déjà verrouillée, on est saisi par un message Oracle sans attente.

Langage Définition

des Données

LDD

Langage Définition

des Données

LDD

DROPDROP

ALTERALTER

CREATECREATE

TYPES DE DONNESTYPES DE DONNES

Type Syntaxe Remarques / Exemple

NUMBER NUMBER [(total [ , Décimal])] Total est max égal à 38.

Salaire NUMBER(7,2)

CHAR CHAR [(longueur)] Nom CHAR(15)

VARCHAR2 VARCHAR2 [(longueur)] Le type chaîne le plus efficace sous Oracle

DATE DATE Date_Naiss DATE

RAW RAW (longueur) Géré comme le VARCHAR2, mais pas affichable.

Seq RAW(100)

LONG LONG Permet de stocker des chaînes d'une longueur max de 2 Go.

Texte LONG

LONG RAW LONG RAW Stockage de fichiers binaires comme les images, son et de la vidéo.

Longueur max est 2Go

Photo LONG RAW

Type Syntaxe Remarques / Exemple

BLOB BLOB Binaires de type long

CLOB CLOB Caractères codées sur 2 octets

NCLOB NCLOB Caractères codés sur 4 octets

ROWID ROWID Chaîne de 18 caractère donnant l'adresse unique d'un enregistrement.

TYPES DE DONNESTYPES DE DONNES

Les LOB (Large Objects) permettent le stockage de 4 Go de données dans la base, ils existent depuis la version 8 d'Oracle, il existe 3 types (BLOB, CLOB, NCLOB).

LES TABLES LES TABLES

Création

CREATE TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint}

[,{ column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} ]...)

[ [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace] [STORAGE storage_clause] [ RECOVERABLE |

UNRECOVERABLE ]

[ PARALLEL ( [ DEGREE { integer | DEFAULT } ]

[ INSTANCES { integer | DEFAULT } ] ) | NOPARALLEL ] [ CACHE | NOCACHE ] |[CLUSTER cluster (column [, column]...)] ] [ ENABLE enable_clause |

DISABLE disable_clause ] ... [AS subquery]

Documents

Caractéristiques Bâti sur le modèle relationnel