Chapitre 3

Télécharger au format pdf ou txt
Télécharger au format pdf ou txt
Vous êtes sur la page 1sur 47

Génie Informatique - S3

ENSA Hoceima

Administration des Bases de Données


Avancées
Chapitre 3

Pr. ZANNOU ABDERRAHIM

2021/2022
1
Contenu du chapitre

• Méthodes pour collections

• Les curseurs

• Records (Enregistrements )

2
Méthodes pour collection

• Il existe plusieurs fonctions natives (méthodes) définies

pour toutes les collections.

• Ces méthodes sont appelées à l'aide de la notation point :

nom_collection.nom_methode[(paramètres)]

3
Méthodes pour collection

COUNT () : Renvoie le nombre d'éléments de la collection.

FIRST () : Renvoie le plus petit indice utilisé. Renvoie NULL

lorsqu'elle est appliquée à des collections initialisées vides.

LAST() : Renvoie le plus grand indice utilisé. Renvoie NULL

lorsqu'elle est appliquée à des collections initialisées vides.

NEXT(i) : Renvoie l'indice qui suit immédiatement l'élément

(indice ) i . Renvoie NULL si i est supérieur ou égal à COUNT.

4
Méthodes pour collection

PRIOR(i) : Renvoie l'indice qui précède immédiatement l'élément

(indice i). Renvoie NULL si i est inférieur ou égal à FIRST.

DELETE () : Supprime tous les éléments de la collection.

DELETE (i) ou DELETE (i,j): Supprime l'élement d’indice i ou

les éléments des indices i à j d'une table imbriquée ou d'une table

associative . Ne s'applique pas aux VARRAY.

5
Méthodes pour collection

EXISTS (i) : Renvoie TRUE ou FALSE pour indiquer si l'élément

(indice) i existe. Si la collection est une table imbriquée non

initialisée ou un VARRAY, renvoie FALSE.

LIMIT : Renvoie le nombre maximum d'éléments autorisés dans

un VARRAY. Renvoie NULL pour les tableaux associatifs et les

tables imbriquées.

6
Méthodes pour collection

TRIM(n): Supprime n éléments à la fin d'une collection. n est

optionnel et vaut 1 par défaut. Si n est NULL, TRIM ne fait rien.

Les tableaux associatifs ne peuvent être traités par cette

procédure.

7
Méthodes pour collection

La méthode EXTEND alloue l'espace pour un ou plusieurs

éléments dans un type de table imbriquée ou un type VARRAY

pour stocker les éléments.

Extend() : alloue un espace d’élément.

Extend(n) : alloue n espace(s) d'élément(s).

8
Méthodes pour collection

Extend(n, i) : le premier paramètre n représente le nombre

d'espaces d'éléments à allouer et le deuxième paramètre i

représente la valeur d'index de l'élément qui doit être copié dans

les espaces nouvellement alloués.

9
Table VARRAY : les fonctions – Exemple
DECLARE
TYPE t IS VARRAY(7) OF VARCHAR2(10) ;
ta t := t();
BEGIN
ta.EXTEND(5);
ta(1):='Hi';
ta(2):='Every';
ta(3):='One';
ta(4):='and ';
ta(5):='welcom';
DBMS_OUTPUT.PUT_LINE('Le premier indice est ' || ta.FIRST());
DBMS_OUTPUT.PUT_LINE('Le dernier indice est ' || ta.LAST());
DBMS_OUTPUT.PUT_LINE('Le nombre des élements est ' || ta.COUNT());
DBMS_OUTPUT.PUT_LINE('Le maximum nombre des elements ' || ta.LIMIT());
DBMS_OUTPUT.PUT_LINE('Indice avant indice 3 est ' || ta.PRIOR(3));
DBMS_OUTPUT.PUT_LINE('Indice apres indice 3 est ' || ta.NEXT(3));
ta.TRIM(2);
DBMS_OUTPUT.PUT_LINE('Le dernier indice est ' || ta.LAST());
DBMS_OUTPUT.PUT_LINE( 'Indice existence ' || sys.DIUTIL.bool_to_int(ta.EXISTS(8)));
ta.EXTEND(2,1);
DBMS_OUTPUT.PUT_LINE('Indice de 5 ' || ta(5));
--ta.DELETE(2); /* pas applicable*/
--ta.DELETE(4,5); /* pas applicable*/
END;
/
10
Table VARRAY : les fonctions – Exemple (Affichage)
Affichage:
Le premier indice est 1
DECLARE
TYPE t IS VARRAY(7) OF VARCHAR2(10) ;
Le dernier indice est 5
ta t := t(); Le nombre des élements est 5
BEGIN Le maximum nombre des elements 7
ta.EXTEND(5); Indice avant indice 3 est 2
ta(1):='Hi'; Indice apres indice 3 est 4
ta(2):='Every'; Le dernier indice est 3
ta(3):='One';
Indice existence 0
ta(4):='and ';
ta(5):='welcom';
Indice de 5 Hi
DBMS_OUTPUT.PUT_LINE('Le premier indice est ' || ta.FIRST());
DBMS_OUTPUT.PUT_LINE('Le dernier indice est ' || ta.LAST());
DBMS_OUTPUT.PUT_LINE('Le nombre des élements est ' || ta.COUNT());
DBMS_OUTPUT.PUT_LINE('Le maximum nombre des elements ' || ta.LIMIT());
DBMS_OUTPUT.PUT_LINE('Indice avant indice 3 est ' || ta.PRIOR(3));
DBMS_OUTPUT.PUT_LINE('Indice apres indice 3 est ' || ta.NEXT(3));
ta.TRIM(2);
DBMS_OUTPUT.PUT_LINE('Le dernier indice est ' || ta.LAST());
DBMS_OUTPUT.PUT_LINE( 'Indice existence ' || sys.DIUTIL.bool_to_int(ta.EXISTS(8)));
ta.EXTEND(2,1);
DBMS_OUTPUT.PUT_LINE('Indice de 5 ' || ta(5));
--ta.DELETE(2); /* pas applicable*/
--ta.DELETE(4,5); /* pas applicable*/
END;
/
11
Table impriqué: les fonctions - Exemple
DECLARE
TYPE t IS TABLE OF VARCHAR2(10) ;
ta t := t();
BEGIN
ta.EXTEND(5);
ta(1):='Hi';
ta(2):='Every';
ta(3):='One';
ta(4):='and ';
ta(5):='welcom';
DBMS_OUTPUT.PUT_LINE('Le premier indice est ' || ta.FIRST());
DBMS_OUTPUT.PUT_LINE('Le dernier indice est ' || ta.LAST());
DBMS_OUTPUT.PUT_LINE('Le nombre des élements est ' || ta.COUNT());
DBMS_OUTPUT.PUT_LINE('Le maximum nombre des elements ' || ta.LIMIT());
DBMS_OUTPUT.PUT_LINE('Indice avant indice 3 est ' || ta.PRIOR(3));
DBMS_OUTPUT.PUT_LINE('Indice apres indice 3 est ' || ta.NEXT(3));
ta.TRIM(2);
DBMS_OUTPUT.PUT_LINE('Le dernier indice apre TRIM' || ta.LAST());
DBMS_OUTPUT.PUT_LINE( 'Indice existence ' || sys.DIUTIL.bool_to_int(ta.EXISTS(8)));
ta.EXTEND(2,1);
DBMS_OUTPUT.PUT_LINE('Indice de 5 ' || ta(5));
DBMS_OUTPUT.PUT_LINE('Le dernier indice apres EXTEND ' || ta.LAST());
ta.DELETE(2); /* Supprimer element de d'indice 2*/
ta.DELETE(4,5); /* Supprimer les element entre des indices 4 et 5*/
END;
/
12
Table impriqué: les fonctions - Exemple
Le premier indice est 1
DECLARE
Le dernier indice est 5
TYPE t IS TABLE OF VARCHAR2(10) ; Le nombre des éléments est 5
ta t := t(); Le maximum nombre des éléments
BEGIN Indice avant indice 3 est 2
ta.EXTEND(5); Indice après indice 3 est 4
ta(1):='Hi'; Le dernier indice après TRIM 3
ta(2):='Every';
Indice existence 0
ta(3):='One';
ta(4):='and ';
Indice de 5 Hi
ta(5):='welcom'; Le dernier indice après EXTEND 5
DBMS_OUTPUT.PUT_LINE('Le premier indice est ' || ta.FIRST());
DBMS_OUTPUT.PUT_LINE('Le dernier indice est ' || ta.LAST());
DBMS_OUTPUT.PUT_LINE('Le nombre des élements est ' || ta.COUNT());
DBMS_OUTPUT.PUT_LINE('Le maximum nombre des elements ' || ta.LIMIT());
DBMS_OUTPUT.PUT_LINE('Indice avant indice 3 est ' || ta.PRIOR(3));
DBMS_OUTPUT.PUT_LINE('Indice apres indice 3 est ' || ta.NEXT(3));
ta.TRIM(2);
DBMS_OUTPUT.PUT_LINE('Le dernier indice apre TRIM' || ta.LAST());
DBMS_OUTPUT.PUT_LINE( 'Indice existence ' || sys.DIUTIL.bool_to_int(ta.EXISTS(8)));
ta.EXTEND(2,1);
DBMS_OUTPUT.PUT_LINE('Indice de 5 ' || ta(5));
DBMS_OUTPUT.PUT_LINE('Le dernier indice apres EXTEND ' || ta.LAST());
ta.DELETE(2); /* Supprimer element de d'indice 2*/
ta.DELETE(4,5); /* Supprimer les element entre des indices 4 et 5*/
END;
/ 13
Table associative : les fonctions - Exemple
DECLARE
TYPE t IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
--ta t := t();
ta t ;
BEGIN
--ta.EXTEND(5); /* n’est pas applicable*/
ta(1):='Hi';
ta(2):='Every';
ta(4):='One';
ta(5):='and ';
ta(9):='welcom';
DBMS_OUTPUT.PUT_LINE('Le premier indice est ' || ta.FIRST());
DBMS_OUTPUT.PUT_LINE('Le dernier indice est ' || ta.LAST());
DBMS_OUTPUT.PUT_LINE('Le nombre des élements est ' || ta.COUNT());
DBMS_OUTPUT.PUT_LINE('Le maximum nombre des elements ' || ta.LIMIT());
DBMS_OUTPUT.PUT_LINE('Indice avant indice 3 est ' || ta.PRIOR(3));
DBMS_OUTPUT.PUT_LINE('Indice apres indice 3 est ' || ta.NEXT(3));
--ta.TRIM(2); /* n’est pas applicable*/
DBMS_OUTPUT.PUT_LINE('Le dernier indice apre TRIM' || ta.LAST());
DBMS_OUTPUT.PUT_LINE( 'Indice existence ' || sys.DIUTIL.bool_to_int(ta.EXISTS(8)));
--ta.EXTEND(2,1); /* inapplicable*/
ta.DELETE(2); /* Supprimer element de d'indice 2*/
ta.DELETE(6,9); /* Supprimer les element entre des indices 6 et 9*/
DBMS_OUTPUT.PUT_LINE('Le nombre des élements apres delete est ' || ta.COUNT());
END;
/
14
Table associative : les fonctions - Exemple
DECLARE Le premier indice est 1
TYPE t IS TABLE OF VARCHAR2(10) Le dernier indice est 9
INDEX BY PLS_INTEGER; Le nombre des élements est 5
--ta t := t();
ta t ;
Le maximum nombre des elements
BEGIN Indice avant indice 3 est 2
--ta.EXTEND(5); /* n’est pas applicable*/ Indice apres indice 3 est 4
ta(1):='Hi'; Le dernier indice apre TRIM9
ta(2):='Every'; Indice existence 0
ta(4):='One'; Le nombre des élements apres delete 3
ta(5):='and ';
ta(9):='welcom';
DBMS_OUTPUT.PUT_LINE('Le premier indice est ' || ta.FIRST());
DBMS_OUTPUT.PUT_LINE('Le dernier indice est ' || ta.LAST());
DBMS_OUTPUT.PUT_LINE('Le nombre des élements est ' || ta.COUNT());
DBMS_OUTPUT.PUT_LINE('Le maximum nombre des elements ' || ta.LIMIT());
DBMS_OUTPUT.PUT_LINE('Indice avant indice 3 est ' || ta.PRIOR(3));
DBMS_OUTPUT.PUT_LINE('Indice apres indice 3 est ' || ta.NEXT(3));
--ta.TRIM(2); /* n’est pas applicable*/
DBMS_OUTPUT.PUT_LINE('Le dernier indice apre TRIM' || ta.LAST());
DBMS_OUTPUT.PUT_LINE( 'Indice existence ' || sys.DIUTIL.bool_to_int(ta.EXISTS(8)));
--ta.EXTEND(2,1); /* inapplicable*/
ta.DELETE(2); /* Supprimer element de d'indice 2*/
ta.DELETE(6,9); /* Supprimer les element entre des indices 6 et 9*/
DBMS_OUTPUT.PUT_LINE('Le nombre des élements apres delete est ' || ta.COUNT());
END;
/
15
Les curseurs

16
Les curseurs

• Le serveur Oracle utilise des zones de travail appelées


Zone Sql Privée pour exécuter les instructions SQL et
pour stocker les informations en cours de traitement.
• Vous pouvez utilisez des curseurs PL/SQL pour nommer
une zone SQL privée et accéder aux données qu’elle
contient.

17
Les curseurs

IL existe 2 types de curseurs :


- Curseur implicite :
Curseur SQL généré et géré par le noyau pour chaque ordre SQL
d'un bloc.
- Curseur explicite :
Curseur SQL généré et géré par l'utilisateur pour traiter un ordre
SELECT qui ramène plus d'une ligne.

18
Les curseurs

Curseur explicite

4 étapes :

- Déclaration du curseur

- Ouverture du curseur

- Traitement des lignes

- Fermeture du curseur

19
Les curseurs

Curseur explicite : déclaration du curseur

Déclaration dans la section DECLARE du bloc. On indique le nom

du curseur et l'ordre SQL associé.

Syntaxe :

CURSOR nom_curseur IS ordre_select ;

20
Les curseurs

Curseur explicite : Ouverture du curseur

L'ouverture du curseur lance l'exécution de l'odre SELECT associé

au curseur. Ouverture dans la section BEGIN du bloc.

Syntaxe :

OPEN nom_curseur ;

21
Les curseurs

Curseur explicite : traitement des lignes

Après l'exécution du SELECT les lignes ramenées sont traitées une

par une, la valeur de chaque colonne du SELECT doit être stockée

dans une variable réceptrice.

Syntaxe :

FETCH nom_curseur INTO liste_variables ;

22
Les curseurs

• Curseur explicite : fermeture du curseur

Pour libérer la mémoire prise par le curseur, il faut le fermer dès

qu'on n'en a plus besoin.

Syntaxe :

CLOSE nom_curseur ;

23
Les attributs d'un curseur

Pour tout curseur (implice ou explicite) il existe des


indicateurs sur leur état.
 %FOUND
 %NOTFOUND
 %ISOPEN
 %ROWCOUNT

24
Les attributs d'un curseur : %FOUND

• Curseur implicite : SQL%FOUND

TRUE

 Si INSERT, UPDATE, DELETE traite au moins une ligne

 Si SELECT ... INTO ... ramène une seule ligne

• Curseur explicite : nom_curseur%FOUND

TRUE

 si le dernier FETCH a ramené une ligne.

25
Les attributs d'un curseur : %NOTFOUND

• Curseur implicite : SQL%NOTFOUND

TRUE

 Si INSERT, UPDATE, DELETE ne traite aucune ligne

 Si SELECT ... INTO ... Ne ramène pas de ligne

• Curseur explicite : nom_curseur%NOTFOUND

TRUE

 Si le dernier FETCH n’a ramené pas une ligne.

26
Les attributs d'un curseur : %ISOPEN

• Curseur implicite : SQL%ISOPEN

Toujours à FALSE car ORACLE referme les curseurs après

utilisation.

• Curseur explicite : nom_curseur%ISOPEN

TRUE si le curseur est ouvert.

27
Les attributs d'un curseur : %ROWCOUNT

• Curseur implicite : SQL%ROWCOUNT

Nombre de lignes traitées par INSERT, UPDATE, DELETE

0 : SELECT ... INTO : ne ramène aucune ligne

n : SELECT ... INTO : ramène plus n lignes

• Curseur explicite : nom_curseur%ROWCOUNT

traduit la nième ligne ramenée par le FETCH

28
Curseur implicite : Exemple

DECLARE

nb_lignes INTEGER;

BEGIN

DELETE FROM emp WHERE dept = 10;

nb_lignes := SQL%ROWCOUNT;

29
Curseur implicite : Exemple
DECLARE
nbLignes NUMBER(2);
op BOOLEAN;
BEGIN
UPDATE emp SET sal = sal + 0;
op := SQL%ISOPEN;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Pas des lignes modififées');
ELSIF SQL%FOUND THEN
nbLignes := SQL%ROWCOUNT;
END IF;
DBMS_OUTPUT.PUT_LINE('nombe de lignes'|| nbLignes );
DBMS_OUTPUT.PUT_LINE( ' Open ' || sys.DIUTIL.bool_to_int(op));
END;
/
30
Les curseurs - Curseur explicite – Exemple

DECLARE
CURSOR moncurseur IS SELECT ename,sal FROM emp WHERE deptno=10;
salaire emp.sal%TYPE;
nom emp.ename%TYPE;
BEGIN
OPEN moncurseur;
FETCH moncurseur INTO nom,salaire;
DBMS_OUTPUT.PUT_LINE(nom);
FETCH moncurseur INTO nom,salaire;
DBMS_OUTPUT.PUT_LINE( nom);
CLOSE moncurseur;
END;
/

31
Les curseurs - Curseur explicite – Exemple

DECLARE
CURSOR moncurseur IS SELECT ename,sal FROM emp WHERE deptno=10;
salaire emp.sal%TYPE;
nom emp.ename%TYPE;
BEGIN
OPEN moncurseur;
FETCH moncurseur INTO nom,salaire;
DBMS_OUTPUT.PUT_LINE(nom);
FETCH moncurseur INTO nom,salaire;
DBMS_OUTPUT.PUT_LINE( nom);
CLOSE moncurseur;
END;
KING
/ CLARK

32
Les curseurs - Curseur explicite – Exemple

DECLARE
CURSOR moncurseur IS SELECT ename,sal FROM emp WHERE deptno=10;
salaire emp.sal%TYPE;
nom emp.ename%TYPE;
BEGIN
OPEN moncurseur;
LOOP
FETCH moncurseur INTO nom,salaire;
EXIT WHEN moncurseur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( nom || ' ' || salaire);
END LOOP;
CLOSE moncurseur;
END;
/

33
Déclaration de variables

Au lieu de déclarer autant de variables que d'attributs


ramenés par le SELECT du curseur, on peut utiliser une
structure.
Syntaxe :
DECLARE
CURSOR nom_curseur IS ordre_select;
nom_structure nom_curseur%ROWTYPE;

34
Simplification d'écriture

• Déclaration de variables

 Pour renseigner la structure :

FETCH nom_curseur INTO nom_structure;

 Pour accéder aux éléments de la structure :

nom_structure.nom_colonne;

35
Simplification d'écriture – Exemple

DECLARE
CURSOR moncurseur IS SELECT ename,sal FROM emp WHERE deptno=10;
nom_structure moncurseur%ROWTYPE;
BEGIN
OPEN moncurseur;
LOOP
FETCH moncurseur INTO nom_structure;
EXIT WHEN moncurseur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(nom_structure.ename || ' ' || nom_structure.sal);
END LOOP;
CLOSE moncurseur;
END;
/

36
Curseur avec FOR LOOP

• "FOR LOOP" peut être utilisée pour travailler avec des curseurs.

• Nous pouvons donner le nom du curseur au lieu de la limite de

plage dans l'instruction de boucle FOR afin que la boucle fonctionne

du premier enregistrement du curseur au dernier enregistrement du

curseur.

• La variable curseur, l'ouverture du curseur, la récupération et la

fermeture du curseur se feront implicitement par la boucle FOR

LOOP.

37
Curseur avec FOR LOOP– Exemple

DECLARE
CURSOR moncurseur IS SELECT ename,sal FROM emp WHERE deptno=10;
nom_structure moncurseur%ROWTYPE;
BEGIN
FOR nom_structure IN moncurseur LOOP
EXIT WHEN moncurseur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( nom_structure.ename);
END LOOP;
END;
/

38
Les enregistrements (RECORDS)

39
Les enregistrements

• Un enregistrement ressemble à une structure d'un L3G.


• Il est composé de champs qui peuvent être de type
différent.
• Un enregistrement est une structure de données composée
d’un nombre fixe d’éléments qui peuvent être de types
différents.
• Les enregistrements sont semblables aux lignes d’une
table de base de données.

40
Les enregistrements

PL/SQL offre trois types d’enregistrements :

 un enregistrement défini par le programmeur,

 un enregistrement basé sur des colonnes d’une table

 un enregistrement basé sur la liste des colonnes d’un

curseur explicite.

41
Un enregistrement défini par l’utilisateur

TYPE nom_type IS RECORD

nom_champ1 type_champ1 [NOT NULL { :=|DEFAULT} expression,]

nom_champ2 type_champ2 [NOT NULL { :=|DEFAULT} expression,]

….

);

nom_var_record nom_type;

42
un enregistrement défini par l’utilisateur – Exemple

DECLARE
type ligneEmp IS RECORD (
nom varchar2(50),
metier emp.job%type,
salaire emp.sal%type );
mavar ligneEmp;
BEGIN
select ename,job, sal into mavar.nom,mavar.metier,mavar.salaire FROM emp WHERE
empno=7839;
DBMS_OUTPUT.PUT_LINE('le nom est ' || mavar.nom);
END;
/

43
un enregistrement basé sur le table

L’attribut %ROWTYPE permet de déclarer un


enregistrement basé sur la liste de colonnes d’une table
ou d’une vue. Les noms et types de données des
champs du record sont identiques à ceux des colonnes
de la table ou de la vue

44
un enregistrement basé sur le table – Exemple

DECLARE
ligneEMP emp%ROWTYPE;
BEGIN
SELECT * INTO ligneEMP FROM emp WHERE
empno=7839;
DBMS_OUTPUT.PUT_LINE('le nom est ' || ligneEMP.ename);
END;
/

45
un enregistrement basé sur le curseur

Vous pouvez définir un enregistrement basé sur un


curseur. Tout d'abord, vous devez définir un curseur. Et
puis vous utilisez %ROWTYPE avec la variable
curseur pour déclarer un enregistrement. Les champs de
l'enregistrement correspondent aux colonnes de
l'instruction SELECT du curseur.

46
un enregistrement basé sur le curseur – Exemple

DECLARE
CURSOR nom_curseur IS SELECT * FROM emp;
ligneEMP nom_curseur%ROWTYPE;
BEGIN
OPEN nom_curseur;
LOOP
FETCH nom_curseur INTO ligneEMP;
EXIT WHEN nom_curseur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('le nom est ' || ligneEMP.ename);
END LOOP;
END;
/

47

Vous aimerez peut-être aussi