sql oracle

12/04/23

Partie I: Le langage SQLPr: E. Saad

12/04/23

Le langage SQL ( Structured Query Longage : Langage

d’interrogation structuré) , lié à la structure relationnelle des BD,

est un langage non procédural, il comporte plusieurs commandes

qui se repartissent en trois familles fonctionnellement distinctes:

• Langage de manipulation des données (LMD): sélectionner,

insérer, modifier, ou supprimer des données dans une table.

• Langage de définition des données(LDD): créer des tables dans

une BDR, ainsi d’en modifier ou de supprimer leur structure.

• Langage de contrôle de données(LCD): gérer la sécurité et les

permissions au niveau des utilisateurs d’une BDR.

Aspect générale du langage SQL

12/04/23

Tables utilisées dans le Cours

Table Emp

Table SALGRADE

Table Dept

12/04/23

L’ordre Select

L’utilisation la plus fréquente de SQL s’effectue dans les requêtes

afin de rechercher les données dans une base de données.

Syntaxe

select [distinct] * | <liste des champs >

From <Liste des tables>

[where <critère de sélection >]

[group by <critères de regroupement>]

[having <conditions de filtrage sur les groupes>]

[order by <liste des champs > asc | desc ] ;

12/04/23

Écriture des Ordres SQL• Les ordres SQL peuvent être écrits indifféremment en

majuscules et/ou minuscules.

• Les ordres SQL peuvent être écrits sur plusieurs lignes.

• Les mots-clés ne doivent pas être abrégés ni scindés sur deux

lignes différentes.

• Les clauses sont généralement placées sur des lignes

distinctes.

12/04/23

Exemple de SELECT

DEPTNO DNAME LOC--------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

SELECT *FROM dept;

DEPTNO LOC--------- ------------- 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON

SELECT deptno, locFROM dept;

12/04/23

SELECT deptnoFROM emp;SELECT deptnoFROM emp;

DEPTNO--------- 10 30 10 20...14 rows selected.

DEPTNO--------- 10 20 30

SELECT DISTINCT deptnoFROM emp;

Exemple de SELECT

DEPTNO DNAME LOC--------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

SELECT * FROM dept;

12/04/23

L’Alias de Colonne• Permet de renommer un en-tête de colonne (champs)

• Suit immédiatement le nom de la colonne ; le mot-clé AS peut

être placé entre le nom et l’alias est optionnel

• Doit obligatoirement être inclus entre guillemets (") s’il

contient des espaces, des caractères spéciaux ou si les

majuscules/minuscules doivent être différenciées

SELECT ename AS name, sal salaryFROM emp;

12/04/23

Le littéral • Un littéral est un caractère, une expression, ou un nombre

inclus dans la liste SELECT.

• Les valeurs littérales de type date et caractère doivent être

placées entre simples quotes (').

SELECT ename, job, deptnoFROM empWHERE job='CLERK';

ENAME JOB DEPTNO---------- --------- ---------JAMES CLERK 30SMITH CLERK 20ADAMS CLERK 20MILLER CLERK 10

12/04/23

Les opérateurs

12/04/23

Utilisation des Opérateurs

ENAME SAL---------- ---------MARTIN 1250TURNER 1500WARD 1250ADAMS 1100MILLER 1300

SELECT ename, salFROM empWHERE sal BETWEEN 1000 AND 1500;

Limite

inférieure

Limite

supérieure

SELECT empno, ename, sal, mgrFROM empWHERE mgr IN (7902, 7566, 7788);

EMPNO ENAME SAL MGR--------- ---------- --------- --------- 7902 FORD 3000 7566 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788

12/04/23

Utilisation des Opérateurs de Comparaison

SELECT enameFROM empWHERE ename LIKE 'S%';

SELECT enameFROM empWHERE ename LIKE '_A%';

ENAME---------- JAMES WARD

12/04/23

Utilisation des Opérateurs logiques

SELECT ename, jobFROM empWHERE job NOT IN ('CLERK','MANAGER','ANALYST');

ENAME JOB---------- ---------KING PRESIDENTMARTIN SALESMANALLEN SALESMANTURNER SALESMANWARD SALESMAN

... WHERE ... NOT BETWEEN ... AND ...

... WHERE ... IS NOT IN ...

... WHERE ... NOT LIKE ...

... WHERE ... IS NOT NULL

12/04/23

Utilisation de l’opérateurs de Concaténation

Employee Details-------------------------KING is a PRESIDENTBLAKE is a MANAGERCLARK is a MANAGERJONES is a MANAGERMARTIN is a SALESMAN...14 rows selected.

Employee Details-------------------------KING is a PRESIDENTBLAKE is a MANAGERCLARK is a MANAGERJONES is a MANAGERMARTIN is a SALESMAN...14 rows selected.

SELECT ename ||' is a '||job AS "Employee Details"

FROM emp;

12/04/23

Exemple de tri des enregistrements

SELECT ename, deptno, salFROM empORDER BY deptno, sal DESC;

ENAME DEPTNO SAL---------- --------- ---------KING 10 5000CLARK 10 2450MILLER 10 1300FORD 20 3000...14 rows selected.

12/04/23

Types de Jointures en SQL

EquijointureEquijointure Non-équijointureNon-équijointure

Jointure externeJointure externe AutojointureAutojointure

12/04/23

Exemple d’équijointure

EMPNO ENAME DEPTNO DEPTNO LOC----- ------ ------ ------ --------- 7839 KING 10 10 NEW YORK 7698 BLAKE 30 30 CHICAGO 7782 CLARK 10 10 NEW YORK 7566 JONES 20 20 DALLAS...14 rows selected.

SELECT empno, ename, emp.deptno, dept.deptno , dept.locFROM emp, deptWHERE emp.deptno=dept.deptno;

12/04/23

Exemple de non-équijointure

ENAME SAL GRADE---------- --------- ---------JAMES 950 1SMITH 800 1ADAMS 1100 1...14 rows selected.

SELECT e.ename, e.sal, s.grade

FROM emp e, salgrade s

WHERE e.sal

BETWEEN s.losal AND s.hisal;

12/04/23

Exemple de Jointures ExternesSELECTe.ename, d.deptno, d.dname

FROM emp e, dept d

WHERE e.deptno(+) = d.deptno

ORDER BY e.deptno;

ENAME DEPTNO DNAME---------- --------- -------------KING 10 ACCOUNTINGCLARK 10 ACCOUNTING... 40 OPERATIONS15 rows selected.

12/04/23

Exemple de l’autojointures

WORKER.ENAME||'WORKSFOR'||MANAG-------------------------------BLAKE works for KINGCLARK works for KINGJONES works for KINGMARTIN works for BLAKE...13 rows selected.

WORKER.ENAME||'WORKSFOR'||MANAG-------------------------------BLAKE works for KINGCLARK works for KINGJONES works for KINGMARTIN works for BLAKE...13 rows selected.

SELECT worker.ename||' works for '||manager.ename

FROM emp worker, emp manager

WHERE worker.mgr = manager.empno;

12/04/23

Fonction de regroupement de données

Les fonctions de groupe agissent sur des groupes de lignes et

donnent un résultat par groupe

• AVG ([DISTINCT|ALL]n)

• COUNT ({ *|[DISTINCT|ALL]expr})

• MAX ([DISTINCT|ALL]expr)

• MIN ([DISTINCT|ALL]expr)

• SUM ([DISTINCT|ALL]n)

12/04/23

Utilisation des fonctions de regroupement

AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)-------- --------- --------- --------- 1400 1600 1250 5600

SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)FROM empWHERE job LIKE 'SALES%';

COUNT(*)--------- 6

SELECT COUNT(*)FROM empWHERE deptno = 30;

12/04/23

Utilisation des fonctions de regroupement

SELECT AVG(NVL(comm,0))FROM emp;

AVG(NVL(COMM,0))---------------- 157.14286

SELECT AVG(comm)FROM emp;

AVG(COMM)--------- 550

12/04/23

Création de Groupes de DonnéesEMPEMP

"salaire "salaire moyen pour moyen pour chaque chaque ddéépartement partement de la table de la table EMP"EMP"

2916.66672916.6667

21752175

1566.66671566.6667

DEPTNO SAL--------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250

DEPTNO AVG(SAL)

------- ---------

10 2916.6667

20 2175

30 1566.6667

SELECT deptno, AVG(sal)FROM empGROUP BY deptno;

12/04/23

Exemple de création de groupes de données

SELECT deptno, job, sum(sal)FROM empGROUP BY deptno, job;

DEPTNO JOB SUM(SAL)--------- --------- --------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900...9 rows selected.

12/04/23

SELECT max(avg(sal))FROM empGROUP BY deptno;

MAX(AVG(SAL))------------- 2916.6667

Imbrication des Fonctions de Groupe

12/04/23

Utilisation de la clause HAVING

La clause HAVING permet restreindre les groupes

• Les lignes sont regroupées.

• La fonction de groupe est appliquée.

• Les groupes qui correspondent à la clause HAVING sont

affichés.

12/04/23

Exemple de l’utilisation de HAVINGSELECT deptno, max(sal)FROM empGROUP BY deptnoHAVING max(sal)>2900;

DEPTNO MAX(SAL)--------- --------- 10 5000 20 3000

12/04/23

Exemple de l’utilisation de HAVING

SELECT job, SUM(sal) PAYROLLFROM empWHERE job NOT LIKE 'SALES%'GROUP BY jobHAVING SUM(sal)>5000ORDER BY SUM(sal);

JOB PAYROLL--------- ---------ANALYST 6000MANAGER 8275

12/04/23

Les Sous-InterrogationsSyntaxe

SELECT select_list FROM tables WHERE expr operator (SELECT select_list FROM tables… ) …;

• La sous-interrogation (requête interne) est exécutée une fois

avant la requête principale (une sous-interrogation ne doit

pas contenir la clause ORDER BY).

• Le résultat de la sous-interrogation est utilisé par la requête

principale (externe).

12/04/23

Types de Sous-InterrogationsSous-interrogation

mono-ligneSous-interrogation

mono-ligneSous-interrogation

multi-ligneSous-interrogation

multi-ligne

Opérateurs mono-ligne Opérateurs multi-ligne

12/04/23

Exemple de Sous-Interrogations mono-ligne

CLERK

1100

ENAME JOB---------- ---------MILLER CLERK

ENAME JOB---------- ---------MILLER CLERK

SELECT ename, jobFROM empWHERE job =

(SELECT job FROM emp WHERE ename = 'ADAMS')AND sal > (SELECT sal

FROM emp WHERE ename = 'ADAMS');

12/04/23

Exemple de Sous-Interrogations mono-ligne

800

ENAME JOB SAL---------- --------- ---------SMITH CLERK 800

ENAME JOB SAL---------- --------- ---------SMITH CLERK 800

SELECT ename, job, salFROM empWHERE sal =

(SELECT MIN(sal) FROM emp);

800

SELECT deptno, MIN(sal)FROM empGROUP BY deptnoHAVING MIN(sal) >

(SELECT MIN(sal) FROM emp

WHERE deptno = 20);

12/04/23

SELECT empno, ename, jobFROM empWHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK‘)AND job <> ''CLERK';

Exemple de Sous-Interrogations multi-ligne

9508001100

1300

EMPNO ENAME JOB--------- ---------- --------- 7654 MARTIN SALESMAN 7521 WARD SALESMAN

EMPNO ENAME JOB--------- ---------- --------- 7654 MARTIN SALESMAN 7521 WARD SALESMAN

12/04/23

Exemple de Sous-Interrogations multi-ligne

EMPNO ENAME JOB--------- ---------- --------- 7839 KING PRESIDENT 7566 JONES MANAGER 7902 FORD ANALYST 7788 SCOTT ANALYST

EMPNO ENAME JOB--------- ---------- --------- 7839 KING PRESIDENT 7566 JONES MANAGER 7902 FORD ANALYST 7788 SCOTT ANALYST

SELECT empno, ename, jobFROM empWHERE sal > ALL

(SELECT avg(sal) FROM emp

GROUP BY deptno)

2916.6667

2175

1566.6667

12/04/23

Syntaxe: SELECT outer1, outer2, ... FROM table1 alias1 WHERE outer1 operator (SELECT inner1

FROM table2 alias2 WHERE alias1.outer2 = alias2.inner1);

La sous-interrogation est exécutée pour chaque enregistrement de la requête principale.

Les Sous-Interrogations Synchronisées

Nouvelle ligne candidate (requête externe)

Exécuter la requête interne utilisant une valeur de la ligne candidate

Utiliser la valeur(s) de la requête interne qualifiant la ligne candidate

12/04/23

EMPNO SAL DEPTNO-------- --------- --------- 7839 5000 10 7698 2850 30 7566 2975 20 ... 6 rows selected.

EMPNO SAL DEPTNO-------- --------- --------- 7839 5000 10 7698 2850 30 7566 2975 20 ... 6 rows selected.

Exemple de Sous-Interrogations SynchroniséesRecherchez tous les employés dont le salaire est supérieur au

salaire moyen de leur départementSELECT empno, sal, deptnoFROM emp outerWHERE sal > (SELECT AVG(sal) FROM emp inner WHERE outer.deptno= inner.deptno);

Chaque fois que la requêteexterne est traitée,

la requête interne est exécutée.

Opérateur mono.ligne ou muti-ligne

12/04/23

Les Sous-Interrogations Synchronisées et l'Opérateur EXISTS

•Pour chaque enregistrement de la requête principale, une

recherche d’enregistrement est effectuée dans la sous-

interrogation.

•La recherche dans la sous-interrogation est interrompue dès

une ligne a été trouvée, la condition de la requête principale

est vraie.

•La condition de la requête principale est fausse si aucune ligne

n’a été trouvée dans la sous-interrogation.

12/04/23

Exemple de l’utilisation de l’opérateur EXISTSRecherchez les employés ayant au moins une personne sous leur

responsabilité.

EMPNO ENAME JOB DEPTNO--------- ---------- --------- --------- 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20...6 rows selected.

SELECT empno, ename, job, deptnoFROM emp outerWHERE EXISTS (SELECT empno FROM emp inner WHERE inner.mgr = outer.empno);

12/04/23

Les Opérateurs Ensemblistes• Intersect

•Union / Union All

•MinusTable Emp Table EMPHISTORY

12/04/23

Exemple de l'Opérateur UNIONAffichez le nom, le poste et le département de tous les employés

ENAME JOB DEPTNO---------- --------- ---------ADAMS CLERK 30ALLEN SALESMAN 30ALLEN SALESMAN 20BALFORD CLERK 20BLAKE MANAGER 30...20 rows selected.

SELECT ename, job, deptnoFROM empUNIONSELECT name, title, deptidFROM emp_history;

12/04/23

Exemple de l'Opérateur INTERSECTAffichez les différents noms, numéros et postes des employés présents dans les tables EMP et EMPHISTORY.

ENAME EMPNO JOB---------- --------- ---------ALLEN 7499 SALESMANCLARK 7782 MANAGERSCOTT 7788 ANALYST

ENAME EMPNO JOB---------- --------- ---------ALLEN 7499 SALESMANCLARK 7782 MANAGERSCOTT 7788 ANALYST

SELECT ename, empno, jobFROM empINTERSECTSELECT name, empid, titleFROM emp_history;

12/04/23

Exemple de l'Opérateur MINUSAffichez le nom,le numéro et le poste de tous les employés ayant quitté la société.

NAME EMPID TITLE---------- --------- ---------BALFORD 6235 CLERKBRIGGS 7225 PAY CLERKJEWELL 7001 ANALYSTSPENCER 6087 OPERATOR...6 rows selected.

NAME EMPID TITLE---------- --------- ---------BALFORD 6235 CLERKBRIGGS 7225 PAY CLERKJEWELL 7001 ANALYSTSPENCER 6087 OPERATOR...6 rows selected.

SELECT name, empid, titleFROM emp_historyMINUSSELECT ename, empno, jobFROM emp;

12/04/23

Ajout d‘enregistrement dans une Table

• Indiquez les valeurs dans l'ordre par défaut des colonnes dans

la table.

•Placez les valeurs de type caractère et date entre simples

quotes.

INSERT INTO Nomtable [(champs [, champs ...])]

VALUES (valeur [, valeur...] );


VALUES (valeur [, valeur...] );

Syntaxe 1:

L'ordre INSERT permet d'ajouter un nouveau enregistrement dans une table.

12/04/23

INSERT INTO dept (deptno, dname, loc)

VALUES (50, 'FINANCES', 'PARIS');

INSERT INTO emp (empno, ename, job,

mgr, hiredate, sal, deptno)

VALUES (8000, ‘SAAD', 'ANALYST',

7839, SYSDATE, 4000, 20);

INSERT INTO emp (empno, ename, job,

mgr, hiredate, sal, deptno)

VALUES (8000, ‘SAAD', 'ANALYST',

7839, SYSDATE, 4000, 20);


12/04/23


Les champs de la clause INSERT doit correspondre à ceux de la sous-interrogation


(select …);


(select …);

Syntaxe 2:

INSERT INTO SALSES (mat, nom, sal, hiredate, deptno)

SELECT empno, ename, hiredate, sal+ comm, deptno

FROM emp

WHERE job like 'SALSES%';

INSERT INTO SALSES (mat, nom, sal, hiredate, deptno)

SELECT empno, ename, hiredate, sal+ comm, deptno

FROM emp

WHERE job like 'SALSES%';

Exemple:

12/04/23

Modification des données d’une Table

• La clause WHERE permet de modifier une ou plusieurs lignes spécifiques; en cas d’absence de cette clause toutes les lignes seront modifiées.

•Chaque opération de modification doit respecter les contraintes d’intégrité référentielle de la base de données.

L'ordre UPDATE permet de modifier les enregistrements d’une table.

UPDATE Nomtable

SET Champs = valeur [, champs = valeur]

[WHERE condition];

UPDATE Nomtable

SET Champs = valeur [, champs = valeur]

[WHERE condition];

Syntaxe :

12/04/23

Modification des données d’une Table

UPDATE emp

SET mgr = (SELECT empno

FROM emp

WHERE (deptno = 30) AND (job like 'MANAGER') ) ,

job ='SALSESMEN'

WHERE deptno = 30;

UPDATE emp

SET mgr = (SELECT empno

FROM emp

WHERE (deptno = 30) AND (job like 'MANAGER') ) ,

job ='SALSESMEN'

WHERE deptno = 30;

UPDATE emp

SET com =0

WHERE com is NULL;

UPDATE emp

SET com =0

WHERE com is NULL;

12/04/23

Suppression des enregistrements d’une Table

• La clause WHERE permet de supprimer une ou plusieurs lignes spécifiques en l’absence de cette clause, toutes les lignes sont supprimées.

•Chaque opération de modification doit respecter les contraintes d’intégrité référentielle de la base de données.

L'ordre DELETE permet de supprimer des enregistrements a partir d’une table.

DELETE [FROM] Nomtable

[WHERE condition];

DELETE [FROM] Nomtable

[WHERE condition];

Syntaxe :

12/04/23

Suppression des enregistrements d’une Table

DELETE dept

WHERE dname = 'FINANCES';

DELETE dept

WHERE dname = 'FINANCES';

DELETE FROM emp

WHERE deptno =

(SELECT deptno

FROM dept

WHERE dname ='SALES');

DELETE FROM emp

WHERE deptno =

(SELECT deptno

FROM dept

WHERE dname ='SALES');

12/04/23

Langage de définition des Données

Un ordre du LDD permet de créer , de supprimer et de modifier la

structure d’une base de données relationnelle; les objets que le

LDD peut gérer sont:

• Table

• Vue

• Séquence

• Index

• Synonyme

12/04/23

Création de Tables

Syntaxe :

Les identificateurs utilisés doivent respecter les règles suivantes:•Commencer par une lettre et contenir que les caractères A à

Z, 0 à 9, _, $, et # (30 caractères maximum).•Être différent des autres objets appartenant au même

utilisateur et des mots réservé à Oracle

CREATE TABLE nomtable (champs typechamps [DEFAULT valeur], ...);

CREATE TABLE nomtable [(champs1, champs2...)] as sousinterrogation;

12/04/23

Types de données Description

VARCHAR2(taille) Données caractères de longueur variable (2000 caractères maximum)

CHAR(taille) Données caractères de longueur fixe (255 caractères maximum)

NUMBER(taille,décimale) Numérique de longueur variable

DATE Valeurs de date et d'heure

LONG Données caractères de longueur variable,jusqu'à 2 giga-octets

LONG RAW Binaire(image)

Création de Tables

12/04/23

CREATE TABLE dept

( deptno NUMBER(2),

dname VARCHAR2(15),

loc VARCHAR2(12));

CREATE TABLE dept

( deptno NUMBER(2),

dname VARCHAR2(15),

loc VARCHAR2(12));

CREATE TABLE Empclerk

AS SELECT empno mat, ename, nom, sal + comm as salaire,

hiredate, deptno

FROM emp

WHERE job like 'clerk');

CREATE TABLE Empclerk

AS SELECT empno mat, ename, nom, sal + comm as salaire,

hiredate, deptno

FROM emp

WHERE job like 'clerk');

Création de Tables

12/04/23

Modification de la structure d’une Tables

Syntaxe :

L'ordre ALTER TABLE permet d’ajouter de nouveau champs ou de modifier des champs existants.

ALTER TABLE NomtableADD (champs typechamps [DEFAULT valeur]

[, champs typechamps]...);

ALTER TABLE NomtableMODIFY (champs typechamps [DEFAULT valeur]

[, champs typechamps]...);

ALTER TABLE NomtableDROP COLUMN champs ;

ALTER TABLE tableRENAME COLUMN ancien_nom TO nouveau_nom

12/04/23

Gestion des tables

TRUNCATE TABLE NomTable;TRUNCATE TABLE NomTable;

Vider une Table

RENAME AncienNomTable TO NouveauNomTable;RENAME AncienNomTable TO NouveauNomTable;

Renommer une Table

DROP TABLE Nomtable;DROP TABLE Nomtable;

Supprimer une Table

12/04/23

Les Contraintes d’intégrités

Les types de contraintes reconnues dans SQL sont:

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK

12/04/23


CREATE TABLE [Schema.]NomTable

(Champs TypeChamps [DEFAULT Valeur]

[[CONSTRAINT NomContrainte] TypeContrainte],

… ,

[[CONSTRAINT NomContrainte] TypeContrainte(Champs, ...),]

… );

CREATE TABLE [Schema.]NomTable

(Champs TypeChamps [DEFAULT Valeur]

[[CONSTRAINT NomContrainte] TypeContrainte],

… ,

[[CONSTRAINT NomContrainte] TypeContrainte(Champs, ...),]

… );

Syntaxe :

ALTER TABLE NomTable

ADD [CONSTRAINT NomContrainte] TypeContrainte(Champs);


ADD [CONSTRAINT NomContrainte] TypeContrainte(Champs);

12/04/23


CREATE TABLE Employes(

empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(15) NOT NULL,

job VARCHAR2(10), mgr NUMBER(4), hiredate DATE, sal NUMBER(8,2),

comm NUMBER(8,2), deptno NUMBER(2) NOT NULL ,

CONSTRAINT un_dept UNIQUE (ename) );

CREATE TABLE Employes(

empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(15) NOT NULL,

job VARCHAR2(10), mgr NUMBER(4), hiredate DATE, sal NUMBER(8,2),

comm NUMBER(8,2), deptno NUMBER(2) NOT NULL ,

CONSTRAINT un_dept UNIQUE (ename) );

ALTER TABLE Employes

ADD CONSTRAINT Emp_Dept_Fk FOREIGN KEY (deptno)

REFERENCES dept (deptno) ON DELETE CASCADE;


ADD CONSTRAINT Emp_Dept_Fk FOREIGN KEY (deptno)

REFERENCES dept (deptno) ON DELETE CASCADE;


ADD CONSTRAINT CONSTRAINT CHECK (Comm > 0);


ADD CONSTRAINT CONSTRAINT CHECK (Comm > 0);

12/04/23

Gestion des contraintes


ENABLE CONSTRAINT NomContrainte [CASCADE];


ENABLE CONSTRAINT NomContrainte [CASCADE];

Activer une Contraintes


DISABLE CONSTRAINT NomContrainte [CASCADE];


DISABLE CONSTRAINT NomContrainte [CASCADE];

Désactiver une Contraintes


DROP CONSTRAINT NomContrainte [CASCADE];


DROP CONSTRAINT NomContrainte [CASCADE];

Supprimer une contrainte

12/04/23

Notion de vue

Une vue est une table virtuelle basée sur une ou plusieurs

table (ou sur d’autres vues). C’est une fenêtre par laquelle il

est possible de visualiser ou de modifier des données venant

de ces tables (appelés tables de base).

Les utilisateurs consultent la base, ou modifier la base (avec

certaines restrictions) à travers les vues (Limitation d'accès )

12/04/23

Création d'une Vue

CREATE VIEW NomVue [(alias[, alias]...)]AS SousInterogation[WITH CHECK OPTION][WITH READ ONLY]

CREATE VIEW NomVue [(alias[, alias]...)]AS SousInterogation[WITH CHECK OPTION][WITH READ ONLY]

Syntaxe

La SousInterogation(ordre select) ne doit pas comporter la clause ORDER BY.

« WITH CHECK OPTION » permet de garantir que les ordres LMD reste dans le domaine de la vue.

« WITH READ ONLY » assure qu'aucune opération LMD ne sera exécutée dans la vue.

12/04/23

Exemple de création d'une Vue

CREATE VIEW manager(matricule, nom, fonction, dateembouche, salaire, Departement )AS SELECT empno, ename, job,hiredate, sal + nvl(comm,0), dnameFROM emp, dept WHERE ((emp.deptno = dept.deptno) and empno in (select mgr from emp where mgr is not NULL))WITH READ ONLY

CREATE VIEW manager(matricule, nom, fonction, dateembouche, salaire, Departement )AS SELECT empno, ename, job,hiredate, sal + nvl(comm,0), dnameFROM emp, dept WHERE ((emp.deptno = dept.deptno) and empno in (select mgr from emp where mgr is not NULL))WITH READ ONLY

Select * from manager ;Select * from manager ;

12/04/23

Suppression d’une vue

DROP VIEW NomVue;DROP VIEW NomVue;

Syntaxe

L'ordre DROP VIEW permet supprimer une vue (les tables de

base correspondantes ne sont pas supprimées).

12/04/23

Contrôle des accès utilisateur

Dans un environnement multi-utilisateur, l'accès et l'utilisation

d’une base de données doit être sécurisé; une telle sécurité

peut être classée en deux catégories :

• La sécurité du système couvre l'accès à la base de données

et son utilisation au niveau du système (nom de l'utilisateur

et mot de passe, espace disque alloué aux utilisateurs et

opérations système autorisées par l'utilisateur).

• La sécurité de la base de données couvre l'accès aux objets

de la base de données et leur utilisation, ainsi que les

actions exécutées sur ces objets par les utilisateurs.

12/04/23

Privilèges

Un privilège donne le droit d'exécuter certains opérations sur la

base de données(ordres SQL):

Privilèges système : autorisent l'accès à la base de données

Privilèges objet : autorisent la manipulation du contenu des

objets de la base de données

12/04/23

Compte utilisateur

CREATE USER loginIDENTIFIED BY motpasse;CREATE USER loginIDENTIFIED BY motpasse;

Syntaxe

L'ordre CREATE USER permet à l'administrateur de base de

données de créer de nouveau utilisateurs.

ALTER USER loginIDENTIFIED BY nouveau_motpasse;ALTER USER loginIDENTIFIED BY nouveau_motpasse;

Syntaxe

L'ordre ALTER USER permet de modifier le mot de passe d’un

utilisateur.

12/04/23

Création et Attribution d'un Rôle

CREATE ROLE NomRole;GRANT NomRole to Login1[, Login2, …];CREATE ROLE NomRole;GRANT NomRole to Login1[, Login2, …];

Syntaxe

Un rôle est un groupe d’utilisateur qu’ont les même privilèges. Un utilisateur peut avoir accès à plusieurs rôles, et le même rôle peut être attribué à plusieurs utilisateurs.

L'ordre CREATE ROLE permet à l'administrateur de base de données de créer de nouveau utilisateurs.

L'ordre GRANT permet d’attribue un rôle à des utilisateurs

12/04/23

Retrait d'un Rôle

REVOKE RoleXFROM utilisateur1[, utilisateur2...];REVOKE RoleXFROM utilisateur1[, utilisateur2...];

Syntaxe

L'ordre REVOKE permet de retirer un rôle à un utilisateur

12/04/23

Privilèges systèmes de l’utilisateur

L'administrateur de base de données peut accorder certaines

privilèges à un utilisateur.

Exemple de privilèges

Privilège Système Opérations autorisées CREATE SESSION Connexion à la base de données CREATE TABLE Création de tables dans le schéma de l’utilisateur CREATE VIEW Création d'une vue dans le schéma de l’utilisateur

Des rôles sont préfinis par Oracle: Exemple CONNECT permet à un utilisateur de se connecter et de créer des tables.

12/04/23

Octroi de privilèges système

GRANT Privilege1 [, Privilege2, …]TO {utilisateur1|Role1[,utilisateur2,…];GRANT Privilege1 [, Privilege2, …]TO {utilisateur1|Role1[,utilisateur2,…];

Syntaxe

L'ordre GRANT permet à l'administrateur d’accorder à un

utilisateur certains privilèges systèmes.

12/04/23

Retrait des privilèges systèmes

REVOKE {privilege1 [, privilege2...]}FROM {utilisateur1[, utilisateur2...]|role}REVOKE {privilege1 [, privilege2...]}FROM {utilisateur1[, utilisateur2...]|role}

Syntaxe

L’administrateur peut retirer à un utilisateur ou à un rôle certains privilèges systèmes qui il leurs a accordés.

12/04/23

Privilèges objets

Le propriétaire d’un objet d’une base de données peut accorder

certaines privilèges autorisant la manipulation du contenu de cet

objet.

Exemple de privilèges

SELECT, ALTER , DELETE , INSERT, REFERENCES, UPDATE

12/04/23

Octroi de privilèges objets

GRANT {privilege_objet [(champs)]…|ALL}ON nom_objetTO {utilisateur|nom_role|PUBLIC}[WITH GRANT OPTION];

GRANT {privilege_objet [(champs)]…|ALL}ON nom_objetTO {utilisateur|nom_role|PUBLIC}[WITH GRANT OPTION];

Syntaxe

L'ordre GRANT permet d’accorder à un utilisateur certains

privilèges objets.

WITH GRANT OPTION autorise le bénéficiaire à accorder les privilèges objet à d'autres utilisateurs et à des rôles

12/04/23

Retrait des privilèges objets

REVOKE {privilege1 [, privilege2...]|ALL}ON ObjetFROM {utilisateur1[, utilisateur2...]|role|PUBLIC}

REVOKE {privilege1 [, privilege2...]|ALL}ON ObjetFROM {utilisateur1[, utilisateur2...]|role|PUBLIC}

Syntaxe

L'ordre REVOKE permet de retirer à un utilisateur ou à un rôle

certains privilèges objets qui leurs étaient accordés.

Les privilèges accordés avec WITH GRANT OPTION seront

automatiquement retirés.

12/04/23

Transactions de Base de Données

• Une transaction se compose des éléments suivants : Ensemble d'ordres du LMD effectuant une opération cohérente

des données Un ordre du LDD ou LCD

• Une transaction commence à l'exécution d’un ordre SQL et se termine

par l'un des événements suivants : COMMIT ou ROLLBACK Exécution d'un ordre LDD ou LCD (validation automatique) Fin de session utilisateur Panne du système

12/04/23


•Avant toute opération de sauvegarde ou de restauration (COMMIT ou ROLLBACK) : Les résultats des ordres du LMD exécutés par l'utilisateur courant ne

peuvent pas être affichés par d'autres utilisateurs. Les lignes concernées sont verrouillées. Aucun autre utilisateur ne

peut les modifier•Aprés toute opération de sauvegarde ou de retoration (COMMIT

ou ROLLBACK) : Les modifications des données dans la base sont définitives (perte

de l'état précédent de la base) Tous les utilisateurs peuvent voir le résultat des modifications. Les lignes verrouillées sont libérées et peuvent de nouveau être

manipulées par d'autres utilisateurs

12/04/23


COMMIT;

Commit complete.

COMMIT;

Commit complete.

ROLLBACK;

Rollback complete.

ROLLBACK;

Rollback complete.

UPDATE ...

SAVEPOINT point1;

Savepoint created.

DELETE …

ROLLBACK TO point1;

Rollback complete.

UPDATE ...

SAVEPOINT point1;

Savepoint created.

DELETE …

ROLLBACK TO point1;

Rollback complete.

12/04/23

Partie II:Le langage PL/SQL

12/04/23

Le langage PL/SQL

Le PL/SQL est une extension du SQL avec des caractéristiques propres aux langages de programmation.

La manipulation et l’interrogation des données en SQL sont incluses dans le code procédural.

Permet une programmation modulaire en utilisant des structures de contrôle des langages procéduraux (traitement conditionnel, traitement répétitif, ...) et assure la gestion des erreurs.

12/04/23

Structure d’un Bloc PL/SQL

• DECLARE – Optionel

–Variables, curseurs• BEGIN – Obligatoire

–Instructions SQL–Instructions PL/SQL

• EXCEPTION – Optionel

–Actions à réaliser lorsqu’une erreur se produit

• END; – Obligatoire

• DECLARE – Optionel

–Variables, curseurs• BEGIN – Obligatoire

–Instructions SQL–Instructions PL/SQL

• EXCEPTION – Optionel

–Actions à réaliser lorsqu’une erreur se produit

• END; – Obligatoire

DECLAREDECLARE

BEGINBEGIN

EXCEPTIONEXCEPTION

END;END;

Le slash (/) permet d’exécuter un bloc PL/SQL anonyme dans

le buffer SQL*Plus ( Placer un point (.) pour fermer le

buffer SQL*Plus).

12/04/23

Types de Blocs

Anonyme Procédure Fonction

[DECLARE][DECLARE]

BEGINBEGIN --instructions--instructions

[EXCEPTION][EXCEPTION]

END;END;

[DECLARE][DECLARE]



END;END;

PROCEDURE nomPROCEDURE nomISIS



END;END;

PROCEDURE nomPROCEDURE nomISIS



END;END;

FUNCTION nomFUNCTION nomRETURN datatypeRETURN datatypeISISBEGINBEGIN --instructions--instructions RETURN valeur;RETURN valeur;[EXCEPTION][EXCEPTION]

END;END;

FUNCTION nomFUNCTION nomRETURN datatypeRETURN datatypeISISBEGINBEGIN --instructions--instructions RETURN valeur;RETURN valeur;[EXCEPTION][EXCEPTION]

END;END;

12/04/23

Les variables sont déclarées et initialisées dans la section de déclarative.

De nouvelles valeurs sont affectées aux variables dans la section exécutable.

Passer des valeurs à des procédures ou à des fonctions par l’intermédiaire de paramètres.

Les variables PL/SQLLes variables PL/SQL

SyntaxeSyntaxe

ExemplesExemples

identifiant [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

identifiant [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

Declare v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := 'Atlanta'; c_comm CONSTANT NUMBER := 1400; v_total_sal NUMBER(9,2) := 0; v_valid BOOLEAN NOT NULL := TRUE;

Declare v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := 'Atlanta'; c_comm CONSTANT NUMBER := 1400; v_total_sal NUMBER(9,2) := 0; v_valid BOOLEAN NOT NULL := TRUE;

12/04/23

L’Attribut %TYPE

Déclarer une variable à partir de :

la définition d’un champs de la base de données

la définition d’une variable précédemment déclarée

ExemplesExemples

... v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10;...

... v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10;...

12/04/23

Syntaxe et règles d’écriture

Les instructions peuvent s’étendre sur plusieurs lignes.

Commencer les commentaires sur une ligne avec deux tirets (--).

Placer les commentaires s’étendant sur plusieurs lignes entre les symboles /* et */.

Des instructions peuvent être imbriquées partout où une instruction exécutable est permise.

La portée d’un objet est la zone du programme qui peut s’y référer.

12/04/23

Opérateurs en PL/SQL

Opérateur Opération **, NOT Exponentielle, négation logique - négation *, / Multiplication, division +, -, || Addition, soustraction,

concaténation =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN

Comparaison

AND Conjonction OR Inclusion

12/04/23

Instructions SQL en PL/SQL

Extraire une ligne de la base de grâce à la commande SELECT.

Un seul jeu de valeurs peut être retourné.

Modifiez des lignes dans la base en utilisant des commandes

LMD.

Contrôlez une transaction avec les commandes COMMIT,

ROLLBACK, ou SAVEPOINT.

12/04/23

Instructions SELECT en PL / SQL

Syntaxe (La clause INTO est Obligatoire)SELECT select_listINTO {variable_name[, variable_name]...} FROM <tables>WHERE ...;

Exemple :Exemple :

DECLARE v_deptno NUMBER(2); v_loc VARCHAR2(15);BEGIN SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname = 'SALES'; ...END;

DECLARE v_deptno NUMBER(2); v_loc VARCHAR2(15);BEGIN SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname = 'SALES'; ...END;

12/04/23

Extraire des données en PL/SQL

Retournez la somme des salaires de tous les employés du service

spécifié.

ExempleDECLARE v_sum_sal emp.sal%TYPE; v_deptno NUMBER NOT NULL := 10; BEGIN SELECT SUM(sal) INTO v_sum_sal FROM emp WHERE deptno = v_deptno;END;

DECLARE v_sum_sal emp.sal%TYPE; v_deptno NUMBER NOT NULL := 10; BEGIN SELECT SUM(sal) INTO v_sum_sal FROM emp WHERE deptno = v_deptno;END;

12/04/23

Mettre à jour des données

Augmentez le salaire de tous les employés de la table emp dont la

fonction est Analyste.

Exemple

DECLARE v_sal_increase emp.sal%TYPE := 2000; BEGIN UPDATE emp SET sal = sal + v_sal_increase WHERE job = 'ANALYST';END;

12/04/23

Exercice

Ecrire un bloc anonyme PLSQL qui saisie le numéro (Empno) d'un employé et qui affiche

son nom(Ename), sa fonction(Job), son salaire(Sal) et le nom de son

département(Dname).

Ecrire un bloc anonyme PLSQL qui saisie le numéro (Empno) d'un employé et qui affiche

son nom(Ename), sa fonction(Job), son salaire(Sal) et le nom de son

département(Dname).

DECLARE num scott.emp.empno%TYPE; nomE scott.emp.ename%TYPE; salaire scott.emp.sal%TYPE; Fct scott.emp.job%TYPE; nomD scott.dept.dname%TYPE;beginnum= '&num';SELECT Ename, Job, Sal, Dname into nomE , Fct, salaire, nomD FROM emp , Dept where emp. Deptno=Dept.deptno and empno=num;dbms_output.put_line('Emploté : ' || nomE || ' Fonction :'|| Fct || ' Salaire :'|| salaire || ' Dept :'|| nomD);end;/

DECLARE num scott.emp.empno%TYPE; nomE scott.emp.ename%TYPE; salaire scott.emp.sal%TYPE; Fct scott.emp.job%TYPE; nomD scott.dept.dname%TYPE;beginnum= '&num';SELECT Ename, Job, Sal, Dname into nomE , Fct, salaire, nomD FROM emp , Dept where emp. Deptno=Dept.deptno and empno=num;dbms_output.put_line('Emploté : ' || nomE || ' Fonction :'|| Fct || ' Salaire :'|| salaire || ' Dept :'|| nomD);end;/

La Commande « set serveroutput on » permet de diriger les sorties de ligne vers l’écran plutôt que vers un buffer.

12/04/23

Exercice

Ecrire une procédure PLSQL nomme « Affiche » qui reçoit en entrée le numéro (Empno)

d'un employé et qui affiche son nom(Ename), sa fonction(Job), son salaire(Sal) et le nom

de son département(Dname).

Ecrire une procédure PLSQL nomme « Affiche » qui reçoit en entrée le numéro (Empno)

d'un employé et qui affiche son nom(Ename), sa fonction(Job), son salaire(Sal) et le nom

de son département(Dname).

create or replace procedure affiche(num scott.emp.empno%TYPE) as nomE scott.emp.ename%TYPE; salaire scott.emp.sal%TYPE; Fct scott.emp.job%TYPE; nomD scott.dept.dname%TYPE;beginSELECT Ename, Job, Sal, Dname into nomE , Fct, salaire, nomD FROM emp , Dept where emp. Deptno=Dept.deptno and empno=num;dbms_output.put_line('Emploté : ' || nomE || ' Fonction :'|| Fct || ' Salaire :'|| salaire || ' Dept :'|| nomD);end;/

create or replace procedure affiche(num scott.emp.empno%TYPE) as nomE scott.emp.ename%TYPE; salaire scott.emp.sal%TYPE; Fct scott.emp.job%TYPE; nomD scott.dept.dname%TYPE;beginSELECT Ename, Job, Sal, Dname into nomE , Fct, salaire, nomD FROM emp , Dept where emp. Deptno=Dept.deptno and empno=num;dbms_output.put_line('Emploté : ' || nomE || ' Fonction :'|| Fct || ' Salaire :'|| salaire || ' Dept :'|| nomD);end;/

12/04/23

Exercice

Ecrire une fonction PLSQL nommé « MoySal » qui reçoit le paramètre job à l’entrée et qui retourne le salaire moyen des employés qui ont ce job.

Ecrire une fonction PLSQL nommé « MoySal » qui reçoit le paramètre job à l’entrée et qui retourne le salaire moyen des employés qui ont ce job.

create or replace function MoySal(jobrech scott.emp.job%TYPE) return scott.emp.sal%TYPE as-- BLOC VARIABLES moy scott.emp.sal%TYPE;beginSELECT avg(sal) into moy FROM emp where job like jobrech;return moy;end;/

create or replace function MoySal(jobrech scott.emp.job%TYPE) return scott.emp.sal%TYPE as-- BLOC VARIABLES moy scott.emp.sal%TYPE;beginSELECT avg(sal) into moy FROM emp where job like jobrech;return moy;end;/

12/04/23

Exercice

En utilisant la procédures et la fonction précédente (Affiche et MoySal ), écrire un bloc PLSQL anonyme qui :

Demande le numéro d’un Employé (Empno) Affiche les informations de l’employé correspondant ainsi que le salaire moyen

des employés ayant le même job que cet employé.

En utilisant la procédures et la fonction précédente (Affiche et MoySal ), écrire un bloc PLSQL anonyme qui :

Demande le numéro d’un Employé (Empno) Affiche les informations de l’employé correspondant ainsi que le salaire moyen

des employés ayant le même job que cet employé.

DECLARE numE scott.emp.empno%TYPE; moy scott.emp.sal%TYPE; jobrech scott.emp.job%TYPE;beginnumE := '&numE';affiche(numE);SELECT job into jobrech FROM emp where empno = numE;moy:= MoySal(jobrech);dbms_output.put_line('Moy des salaires est :'|| moy);end;/

DECLARE numE scott.emp.empno%TYPE; moy scott.emp.sal%TYPE; jobrech scott.emp.job%TYPE;beginnumE := '&numE';affiche(numE);SELECT job into jobrech FROM emp where empno = numE;moy:= MoySal(jobrech);dbms_output.put_line('Moy des salaires est :'|| moy);end;/

12/04/23

instructions conditionnelles IF

On peut modifier l’enchaînement logique des instructions en

utilisant des instructions conditionnelles IF.

IF condition THEN instructions;[ELSIF condition THEN instructions;][ELSE instructions;]END IF;

IF condition THEN instructions;[ELSIF condition THEN instructions;][ELSE instructions;]END IF;

SyntaxeSyntaxe

12/04/23

Boucles BasiquesSyntaxe

LOOP instruction1; . . . EXIT [WHEN condition];END LOOP;

LOOP instruction1; . . . EXIT [WHEN condition];END LOOP;

-- initialisation-- initialisation-- instructions-- instructions-- EXIT instruction-- EXIT instruction-- fermeture de la boucle-- fermeture de la boucle

DECLARE v_ordid item.ordid%TYPE := 601; v_counter NUMBER(2) := 1;BEGIN LOOP INSERT INTO item(ordid, itemid) VALUES(v_ordid, v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP;END;

DECLARE v_ordid item.ordid%TYPE := 601; v_counter NUMBER(2) := 1;BEGIN LOOP INSERT INTO item(ordid, itemid) VALUES(v_ordid, v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP;END;

Exemple

12/04/23

Boucles FOR et WHILE

SyntaxeFOR counter in [REVERSE] borne_inférieure..borne_supérieure LOOP instruction1; instruction2; . . .END LOOP;

FOR counter in [REVERSE] borne_inférieure..borne_supérieure LOOP instruction1; instruction2; . . .END LOOP;

la déclaration du compteurla déclaration du compteurest impliciteest implicite

WHILE condition LOOP instruction1; instruction2; . . .END LOOP;

WHILE condition LOOP instruction1; instruction2; . . .END LOOP;

La condition est évaluée La condition est évaluée au début de chaque au début de chaque itération.itération.

12/04/23

Curseur SQL

• Un curseur est une zone de travail réservée à SQL.

• Le Serveur Oracle utilise les curseurs implicites pour analyser et

exécuter les instructions SQL.

• Les curseurs explicites sont déclarés de façon explicite par le

programmeur.

12/04/23

Contrôler les Curseurs Explicites

• Créer une Créer une zone SQL zone SQL nommée nommée

DECLAREDECLARE

• Identifier Identifier l’ensemble l’ensemble actif de actif de ligneslignes

OPENOPENOPENOPEN

• Charger la Charger la ligne ligne courante courante dans des dans des variablesvariables

FETCHFETCHFETCHFETCH

• Tester Tester l’existence l’existence de lignesde lignes

Existence?

• si OUI, si OUI, Retourner à Retourner à FETCHFETCH

OuiOui

• Libérer Libérer l’ensemble l’ensemble actifactif

CLOSECLOSECLOSECLOSENonNon

12/04/23

Déclarer un CurseurSyntaxe

N’incluez pas la clause INTO dans la déclaration du curseur. S’il est requis de traiter les lignes dans une séquence spécifique, utilisez la

clause ORDER BY dans la requête.

CURSOR cursor_name IS select_statement;

CURSOR cursor_name IS select_statement;

Exemple

DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp;

CURSOR dept_cursor IS SELECT * FROM dept WHERE deptno = 10;BEGIN ...

DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp;

CURSOR dept_cursor IS SELECT * FROM dept WHERE deptno = 10;BEGIN ...

12/04/23

Ouvrir et fermer un CurseurSyntaxe

• Ouvrir le curseur pour exécuter la requête et identifier l’Ensemble de lignes Actif.

• Si la requête ne retourne pas de ligne, aucune exception n’est signalée.

• Utilisez les attributs du curseur pour tester le résultat après affectation.

• Fermez le curseur après avoir terminé le traitement des lignes (réouvrir le curseur, si nécessaire).

• N’essayez pas de ramener les données d’un curseur s’il a été fermé.

OPEN cursor_name;

CLOSE cursor_name;

OPEN cursor_name;

CLOSE cursor_name;

12/04/23

Ramener les données à partir du CurseurSyntaxe

•Chargez les valeurs de la ligne courante dans des variables de sortie.

•Prévoir le même nombre de variables.•Ajuster la position des variables par rapport aux colonnes.

•Testez si le curseur contient des lignes.

FETCH cursor_name INTO [variable1, variable2, ...]; FETCH cursor_name INTO [variable1, variable2, ...];

ExempleExemple

FETCH emp_cursor INTO v_empno, v_ename;FETCH emp_cursor INTO v_empno, v_ename;

...OPEN defined_cursor;LOOP FETCH defined_cursor INTO defined_variables EXIT WHEN ...; ... -- Traiter les données ramenéesEND LOOP;...END;

...OPEN defined_cursor;LOOP FETCH defined_cursor INTO defined_variables EXIT WHEN ...; ... -- Traiter les données ramenéesEND LOOP;...END;

12/04/23

Les Attributs d’un Curseur Explicite

Attribut Type Description

%ISOPEN Boolean Évalué à TRUE si le curseur est ouvert.

%NOTFOUND Boolean Évalué à TRUE si le dernier fetchn’a pas retourné de ligne.

%FOUND Boolean Évalué à TRUE si le dernier fetch a retourné une ligne ;

complément de %NOTFOUND

%ROWCOUNT Number Contient le nombre total de lignes retournées jusqu’ici.

12/04/23

Contrôle des affectations multiples• Traitez plusieurs lignes à partir d’un curseur explicite en utilisant une

boucle.

• Affectez les lignes que lorsque le curseur est ouvert.

• Utilisez l’attribut du curseur : %ISOPEN, avant de réaliser une affectation, pour tester si le curseur est ouvert.

• Utilisez l’attribut du curseur %ROWCOUNT pour ramener un nombre exact de lignes.

• Affectez une ligne à chaque itération.

• Utilisez l’attribut du curseur «%NOTFOUND » pour tester l’échec d’une affectation et déterminer quand sortir de la boucle.

12/04/23

Exercice

Ecrire une procédure PLSQL nommée « Affiche » qui reçoit en

entrée le numéro (Empno) d'un employé et qui affiche son

nom(Ename) et sa fonction(Job).

Ecrire un bloc PLSQL anonyme qui :

• Demande le numéro d’un département (Deptno) et affiche le

nom du département correspondant.

• Affiche pour chaque employé qui travail dans le département

qui porte le numéro saisi, le nom et la fonction (utiliser la

procédure « Affiche »).

Ecrire une procédure PLSQL nommée « Affiche » qui reçoit en

entrée le numéro (Empno) d'un employé et qui affiche son

nom(Ename) et sa fonction(Job).

Ecrire un bloc PLSQL anonyme qui :

• Demande le numéro d’un département (Deptno) et affiche le

nom du département correspondant.

• Affiche pour chaque employé qui travail dans le département

qui porte le numéro saisi, le nom et la fonction (utiliser la

procédure « Affiche »).

12/04/23

Exercice

Ecrire un bloc PLSQL anonyme qui afficher pour un entier n, saisie au

début du bloc, les nièmes plus anciens embauchés (ename,

hiredate, job) de la table des employés. Testez les cas particuliers

tels que n = 0 et n>au nombre total d’employés.

Ecrire un bloc PLSQL anonyme qui afficher pour un entier n, saisie au

début du bloc, les nièmes plus anciens embauchés (ename,

hiredate, job) de la table des employés. Testez les cas particuliers

tels que n = 0 et n>au nombre total d’employés.

12/04/23

ExerciceDECLARE n number; i number :=0; nom scott.emp.ename%TYPE; Dtemb scott.emp.hiredate%TYPE; Fct scott.emp.job%TYPE; CURSOR E_Rech IS SELECT ename, hiredate, job FROM emp order by hiredate ASC;BEGIN n := '&nombre'; if (n =0) then dbms_output.put_line('N ne peut pas etre null'); else open E_Rech ; loop i:=i+1; FETCH E_Rech INTO nom,dtemb,Fct; if (E_Rech %FOUND AND i<=n) then dbms_output.put_line('Nom : ' || nom || ‘ Date embouche :' || Dtemb);

dbms_output.put_line('Fonction : '|| Fct); end if;

EXIT WHEN (E_Rech %NOTFOUND or i>n); end loop; if (E_Rech %NOTFOUND ) then dbms_output.put_line('N est plus grand que le nb des employé '); end if;end;

DECLARE n number; i number :=0; nom scott.emp.ename%TYPE; Dtemb scott.emp.hiredate%TYPE; Fct scott.emp.job%TYPE; CURSOR E_Rech IS SELECT ename, hiredate, job FROM emp order by hiredate ASC;BEGIN n := '&nombre'; if (n =0) then dbms_output.put_line('N ne peut pas etre null'); else open E_Rech ; loop i:=i+1; FETCH E_Rech INTO nom,dtemb,Fct; if (E_Rech %FOUND AND i<=n) then dbms_output.put_line('Nom : ' || nom || ‘ Date embouche :' || Dtemb);

dbms_output.put_line('Fonction : '|| Fct); end if;

EXIT WHEN (E_Rech %NOTFOUND or i>n); end loop; if (E_Rech %NOTFOUND ) then dbms_output.put_line('N est plus grand que le nb des employé '); end if;end;

12/04/23

Curseurs paramétrésSyntaxe

•Transmet les paramètres au curseur lors de son ouverture et à l’exécution de la requête (open cursor).

•Permet d’exécuter plusieurs fois un curseur explicite, avec un paramétrage différent à chaque fois.

CURSOR nom de_curseur [(nom_de_parametre type_de_donnée, ...)]IS instruction_SELECT;

CURSOR nom de_curseur [(nom_de_parametre type_de_donnée, ...)]IS instruction_SELECT;

Exemple : Passer le numéro de département et le job au curseur. DECLARE CURSOR emp_cursor (v_deptno NUMBER, v_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = v_deptno AND job = v_job;BEGIN OPEN emp_cursor(10, 'CLERK');...

12/04/23

Manipuler les Exceptions en PL/SQL

• Une exception est un identifiant PL/SQL, de type erreur, déclenché pendant l’exécution d’un bloc PLSQL soit implicitement, par une erreur Oracle ou explicitement, par appel du programme.

• Une exception est traitée en l’interceptant dans le traitement des exceptions ou en la propageant à l’environnement appelant

EXCEPTION WHEN exception1 [OR exception2 . . .] THEN instruction1; instruction2; . . .

EXCEPTION WHEN exception1 [OR exception2 . . .] THEN instruction1; instruction2; . . .

Syntaxe

12/04/23

Exception pré-définie

BEGIN SELECT ... COMMIT;EXCEPTION WHEN INVALID_CURSOR THEN instruction1; instruction2; WHEN TOO_MANY_ROWS THEN instruction1; WHEN OTHERS THEN instruction1; instruction2; instruction3;END;

Syntaxe

12/04/23

Fonctions d’interception des ErreursExempleDECLARE v_error_code NUMBER; v_error_message VARCHAR2(255);BEGIN...EXCEPTION... WHEN OTHERS THEN ROLLBACK; v_error_code := SQLCODE ; v_error_message := SQLERRM ;

INSERT INTO erreurs VALUES(v_error_code, v_error_message);

END; SQLCODE : Renvoie la valeur numérique associé au code de l’erreur SQLERRM : Renvoie le message associé au code de l’erreur

12/04/23

Exercice

Créer un script sql regroupant les taches suivantes : créer une table «EMPSAL » afin d’y stocker les noms et les salaires des employés . Créer un bloc PL/SQL qui détermine les employés ayant les salaires les plus élevés.

• Faire saisir à l’utilisateur une valeur n dans un paramètre de substitution

SQL*PLUS• Dans une boucle, récupérez les noms et salaires des personnes les mieux

rémunérées dans la table EMP.• Enregistrez les noms et salaires dans la table « EMPSAL ».• Testez les cas particuliers tels que n = 0 et n >au nombre total d’employés dans

EMP prevoir un message dans chacun de ces deux cas. Afficher le contenu de la table « EMPSAL » à l’écran.

Créer un script sql regroupant les taches suivantes : créer une table «EMPSAL » afin d’y stocker les noms et les salaires des employés . Créer un bloc PL/SQL qui détermine les employés ayant les salaires les plus élevés.

• Faire saisir à l’utilisateur une valeur n dans un paramètre de substitution

SQL*PLUS• Dans une boucle, récupérez les noms et salaires des personnes les mieux

rémunérées dans la table EMP.• Enregistrez les noms et salaires dans la table « EMPSAL ».• Testez les cas particuliers tels que n = 0 et n >au nombre total d’employés dans

EMP prevoir un message dans chacun de ces deux cas. Afficher le contenu de la table « EMPSAL » à l’écran.

sql oracle

Documents