Cours : Oracle ™





télécharger 68.94 Kb.
titreCours : Oracle ™
date de publication14.10.2019
taille68.94 Kb.
typeCours
m.20-bal.com > documents > Cours





Licence Pro RTICPMO



Responsable des Technologies d’Information et de la

Communication en Petite et Moyennes Organisations






Bases de données

modèles et outils
Cours : Oracle ™

Introduction au langage PL SQL

et à Developper 2000

Pierre-Jean Charrel et Jean-Marc Thévenin

CHAPITRE 1

Le langage PL/SQL


1. Structure du langage 3

1.1 Syntaxe générale 3

1.1.1. 3

1.1.2. 4

1.1.3. 4

2. Exemple 6

3. Requêtes SQL adaptées 6

3.1 Select … into… ; 6

Exemple 6

3.2 Les curseurs 7

3.2.1. Déclaration d'un curseur 7

3.2.2. Utilisation d'un curseur 7

3.2.3. Exemple 8

4. Le traitement des exceptions 8

5. Déclencheurs "base de données" 9

5.1 Définition 9

5.2 Exemple 1 10

5.3 Exemple 2 10


Le langage PL/SQL est un langage de programmation qui s’intègre au modules de programmes écrits avec les outils de développements Oracle. On présente ici la fraction du langage commune à tous les types de clients Oracle. Chaque client possède ses propres extensions au langage PL/SQL, pour traiter en particulier les entrées-sorties (cf. chapitre 2 et l'outil Forms Builder).

1.Structure du langage

1.1Syntaxe générale



::= []

[
BEGIN



[]

END;

1.1.1.


syntaxe

[PROCEDURE [] ;]

[FUNCTION []

RETURNS ;]



IN / OUT / IN OUT


: nom d'une variable virtuelle locale qui est remplacé par l'argument lors de l'appel de la procédure,

IN : paramètre d'entrée = donnée "consommée" par la procédure,

OUT : paramètre de sortie = résultat "produit" par la procédure,

IN OUT : paramètre d'entrée-sortie = information modifiée par la procédure (consommée et retournée au module appelant).

1.1.2.


syntaxe

DECLARE [DEFAULT / := ] ;

- simple

prédéfini : NUMBER, NUMBER (n,m), VARCHAR2, VARCHAR2 (n), DATE, BOOLEAN

défini dans l’application
- référence à la base de données

%TYPE , même type que l’ désigné

%TYPE, même type qu’un n-uplet de la

1.1.3.


Types d’instructions :

1- structures de contrôles algorithmiques,

2- affectation de valeurs à des variables,

3- appels de modules (procédures et fonctions) prédéfinis (packaged) ou définis par un développeur,

4- requêtes SQL standard et adaptées.

1 - Structures de contrôle algorithmiques

Séquence


L’ordre naturel des lignes de commande. Toute instruction se termine par un “ ; 

Alternative



SI ... ALORS...

[SINON...]



SI ... ALORS...

SINON SI...ALORS

SINON SI...

IF

THEN ;

[ ;]*

[ELSE ;

[ ;]*]

END IF ;


IF

THEN... ;

ELSE IF

THEN... ;

ELSE IF...

END IF;--3è IF

END IF ; -- 2è IF

END IF ; -- 1er IF

IF

THEN... ;

ELSIF

THEN... ;

ELSIF...

END IF ; -- 1er IF


Répétition


TANTQUE


Boucle à compteur

WHILE LOOP

;

[ ;]*

END LOOP;



FOR IN [REVERSE] .. LOOP

;

[ ;]*

END LOOP;



Affectation de valeurs à des variables


syntaxe

:= ;

Appel de modules


- Procédure :

syntaxe

;

( ) ;

l’ordre et le type des arguments correspondent à l’ordre et au type des paramètres de la procédure

- Fonction :

invocation du dans une ou une dont le type est celui de la fonction.

Requêtes SQL standard et adaptées


Toute requête SQL (SELECT, INSERT, UPDATE...) interprétable ou adaptée aux traitement par curseur (cf. section 3 infra).


2.Exemple


Le module suivant insère 10 n-uplets dans la relation EQUIPES de la BD "Coupe du monde". Pour chaque n-uplet NE vaut de 0 à 9, et PAYS vaut 'pays n°0' jusqu'à 'pays n°9'.

DECLARE

i number DEFAULT 0; -- compteur

code EQUIPES.NE%TYPE;

nom EQUIPES.PAYS%TYPE DEFAULT 'pays n° ';

BEGIN

FOR i IN 0..9 LOOP

code := i;

INSERT

INTO EQUIPES (NE,NATIONALITE)

VALUES (code, nom || TO_CHAR(code));

COMMIT;

END LOOP;

END;

3.Requêtes SQL adaptées


Intégrer l’instruction SELECT dans un langage de programmation pose souvent un problème. En effet, cette instruction retourne un ensemble de lignes et les langages de programmation ne disposent pas du type de données ensemble. Ainsi, les langages de programmation sont amenés à proposer une nouvelle structure de données permettant de traiter ligne par ligne le résultat d’une requête SELECT. Dans le cas de PL/SQL il s’agit des curseurs.

De plus, afin de simplifier l’écriture des programmes, une adaptation de l’instruction SELECT est proposée dans PL/SQL pour les requêtes SQL dont on est sûr qu’elles ne retournent qu’une seule ligne.

3.1Select … into… ;


Pour les requêtes retournant une seule ligne, l’instruction select … into permet d’associer, à chaque colonne de la clause select, une variable à laquelle sera affecté un résultat.

Exemple


On est dans la situation où une nouvelle équipe vient d'être ajoutée. Le module PL/SQL résout en partie la contrainte d'intégrité que représente la cardinalité 1,n de la patte qui relie, dans le MCD, la classe d'entités EQUIPES à la classe d'entités JOUEURS : le module retrouve cette équipe et ajoute un joueur 'inconnu' à cette équipe.

declare

n_equipe equipes.ne%type;

n_j joueurs.nj%type;

ko varchar2(5);

begin

ko:= 'false';

select 'true' into ko

from EQUIPES

where not exists (select nj

from JOUEURS

where joueurs.ne = equipes.ne);

if ko = 'true' then

/* extraction du n° de l'équipe qui vient d'être enregistrée */

select max (ne)

into n_equipe

from equipes;

/* choix d'un n° de joueur qui n'existe pas */

select max (nj)+1

into n_j

from joueurs;

/* insertion d'un joueur 'joueur inconnu' dans la table JOUEUR */

insert

into joueurs

values (n_j, 'joueur inconnu ', 'prénom inconnu', 'position inconnue',0, n_equipe);

end if;

end;

3.2Les curseurs


Un curseur est un fichier séquentiel créé lors de l'exécution d'une requête SQL de type SELECT et qui accueille la liste des n-uplets extraits par la requête.

3.2.1.Déclaration d'un curseur


Dans la liste de déclarations d'un module, on ajoute

DECLARE

...

CURSOR []

IS SELECT ...

[FOR UPDATE OF ]

[ORDER BY
;

Les 2 dernières options s'excluent.

3.2.2.Utilisation d'un curseur

- lancement de la requête (ouverture du curseur)


OPEN ;

- effacement du fichier des n-uplets extraits (fermeture du curseur)


CLOSE ;

- récupération d'un n-uplet extrait


FETCH INTO ;

les variables doivent être de même type que les colonnes du SELECT et placées dans le même ordre.

Le 1er FETCH récupère le 1er n-uplet extrait et le transfère dans les variables.

Chaque FETCH qui suit avance d'un rang dans la liste des n-uplets.

- compte-rendu d'exécution


4 variables déclarées implicitement avec tout curseur déclaré:

%ISOPEN = TRUE s'il est ouvert, FALSE sinon

%FOUND = TRUE si le FETCH le plus récent a pu récupérer 1 n-uplet, FALSE sinon

%NOTFOUND = l'opposé

%ROWCOUNT = le nombre de n-uplet récupérés jusque-là par des FETCH.

- mise à jour par curseur


Utiliser l'option FOR UPDATE OF dans la déclaration

Modification du n-uplet que pointe le curseur:

UPDATE

SET

WHERE CURRENT OF ;

Destruction du n-uplet que pointe le curseur :

DELETE

FROM

WHERE CURRENT OF ;

3.2.3.Exemple


On extrait de la table EMP la liste des 5 employés dont le salaire est le plus élevé, triés dans l'ordre du salaire décroissant, et on les enregistre dans cet ordre dans une table 'temp'

DECLARE

CURSOR c1 is

SELECT ename, empno, sal FROM emp

ORDER BY sal DESC; -- start with highest paid employee

my_ename emp.ename%type(10);

my_empno emp.empno%type;

my_sal emp.sal%type;
BEGIN

OPEN c1;

i := 1 ;

FETCH c1 INTO my_ename, my_empno, my_sal;

WHILE i <= 5 and c1%FOUND /* in case the number requested */

/* is more than the total */

/* number of employees */

INSERT INTO temp VALUES (my_sal, my_empno, my_ename);

COMMIT;

FETCH c1 INTO my_ename, my_empno, my_sal;

i := i + 1 ;

END LOOP;

CLOSE c1;

END;

4.Le traitement des exceptions


Une exception est un événement qui peut se produire au cours de l'exécution d'un module et perturber son déroulement standard. Par exemple, une requête SQL qui n'extrait aucun n-uplet de la base. Traiter une exception revient à prévoir cet événement dans le programme et coder les instructions à exécuter quand il se produit. Dans la plupart des cas, l'événement associé à une exception est prédéfini dans le système, et fait l'objet d'un traitement par défaut (édition d'un message, abandon du programme…).

Le traitement de chaque exception fait l'objet d'une section à la fin d'un module, appelé gestionnaire d'exceptions (exception handler). Ce traitement n'est connu que dans le module où il est défini.

EXCEPTION WHEN THEN

;

[;*]

Lorsque l'événement correspondant à une exception se produit, on dit qu'on "lève" (RAISE) l'exception. Pour les exceptions prédéfinies, c'est le système qui lève l'exception quand l'événement se produit. La levée de l'exception déclenche le traitement prévu dans le module, s'il existe, ou le traitement par défaut du système.

Exemples d'exceptions prédéfinies

- NO_DATA_FOUND : une requête SQL n'a extrait aucune ligne

- TOO_MANY_ROWS : le nombre de lignes extraites par une requête SQL est incompatible avec les variables qui les reçoivent

- ZERO_DIVIDE : une division par zéro a été rencontrée

- VALUE_ERROR : une conversion de type échoue

Remarques


  • Le mot clé OTHERS désigne toutes les exceptions qui ne sont pas explicitées dans un module. C'est un moyen de désactiver toutes les levées d'exceptions faites par le système sauf les exceptions prévues.

  • Quand on souhaite tout simplement ne rien faire à la rencontre d'une exception, on emploie l'instruction NULL;

Exemple



WHEN NO_DATA_FOUND THEN

raise_application_failure (-20001,'pas de n-uplet extrait');

WHEN OTHERS THEN NULL;

5.Déclencheurs "base de données"

5.1Définition


Un déclencheur est une procédure PL/SQL dont l’exécution est déclenchée par un événement de mise à jour de la base de donnée. Au niveau du schéma de la base de données, un déclencheur est forcément associé à une table. Trois types d’évènements permettent d’associer un déclencheur à une table :

  • insertion

  • suppression

  • mise à jour d’une colonne.

Le déclencheur associé à un évènement peut être activé juste avant ou juste après l’événement.

Le déclencheur associé à un évènement peut être activé une seule fois par instruction SQL (option par défaut) ou pour toutes les mises à jour de n_uplets déclenchées par l’instruction SQL (for each row).

Le corps du déclencheur peut faire appel aux expressions :OLD.nomAttribut et :NEW.nomAttribut pour accéder aux valeurs "avant" et "après" mise à jour de la table associée au déclencheur.
syntaxe
CREATE [OR REPLACE] TRIGGER [.]

(AFTER |BEFORE ) ( INSERT |

DELETE |

UPDATE [OF [,]*])

ON [.]

[REFERENCING (OLD |NEW) as (|)

[,(OLD |NEW) as (|)]

([FOR EACH ROW] | [FOR EACH STATEMENT])

[WHEN ]

 ;
 : nom d’un utilisateur 

 : nom que l’on souhaite donner au déclencheur

 : nom d’un attribut 

 : nom d’une table

 : nom donné par l’utilisateur pour renommer le mot clé OLD

 : nom donné par l’utilisateur pour renommer le mot clé NEW

 : module PL/SQL sans entête.

5.2Exemple 1


Ce déclencheur attaché à la table EQUIPES, de type "après chaque insertion", a pour objet d’insérer un joueur ‘inconnu' dans la relation JOUEURS pour la nouvelle équipe.

En réalité, la contrainte d’intégrité référentielle portant sur NE dans JOUEURS empêche l’insertion d’un joueur dans une équipe dont le NE n’est pas encore enregistré. Par conséquent, une insertion dans une table comportant ce type de contrainte n’est pas possible à l’intérieur d’un déclencheur. C’est la raison pour laquelle le déclencheur ci-dessous utilise un clone de la table JOUEURS (JOUEURS_SANS_FK) qui n’a pas de contrainte d’intégrité référentielle.
CREATE TRIGGER DCH_EQUIPES

AFTER INSERT ON EQUIPES

FOR EACH ROW
DECLARE

n_equipe EQUIPES.NE%type;

n_j JOUEURS.NJ%type;
BEGIN

/* :new.NE contient le n° de l'équipe qui vient d'être créée */

n_equipe := :new.NE

/* choix d'un n° de joueur qui n'existe pas */

SELECT MAX (NJ)+1

INTO n_j

FROM JOUEURS;

/* insertion d'un joueur 'joueur inconnu' dans un clone de la table

JOUEURS pour laquelle NE n’est pas déclaré comme clé étrangère */

INSERT

INTO JOUEURS_SANS_FK

VALUES

(n_j, 'joueur inconnu ', 'prénom inconnu', 'position inconnue',0, n_equipe);

END;

5.3Exemple 2


Le déclencheur ci-dessous est activé lorsque l’on met un carton jaune à un joueur. Si le nombre de cartons jaunes du joueur vaut 2, le carton jaune se transforme automatiquement en carton rouge et le joueur est exclu.
CREATE OR REPLACE TRIGGER "MONDIAL"."expulsion"

BEFORE UPDATE OF carton_jaune ON "MONDIAL"."PARTICIPANTS"

REFERENCING OLD AS O NEW AS N

FOR EACH ROW

WHEN (n.carton_jaune=2)

BEGIN

:n.carton_rouge:=1;

dbms_output.put_line('joueur '||:o.nj||' expulsé');

END;


Licence Pro RTICPMO, Université Toulouse 1, P. A. France, 31042 Toulouse Cedex,
Secrétariat : tél 05 61 63 35 11, email : rticpmo@univ-tlse1.fr

similaire:

Cours : Oracle ™ iconCe cours est destiné à toute personne voulant utiliser efficacement,...

Cours : Oracle ™ icon1. Composants de l'architecture oracle 4

Cours : Oracle ™ iconOracle brm, bscs, crm, oss/bss

Cours : Oracle ™ iconPartie 2 le langage procedural d’oracle : le langage pl/sql

Cours : Oracle ™ iconProgrammes à optimiser 3 2 Analyse du programme 9
«performance database» permet de consulter le top 40 pour une journée, une semaine ou un mois donné. IL existe en fait deux tops...

Cours : Oracle ™ iconL’oracle
Je suis vraiment désolé de venir troubler ainsi ta journée mais notre Protecteur m’est apparu en songe IL y a trois nuits. IL m’a...

Cours : Oracle ™ iconCours très important pour bien comprendre la pathologie expliquée...
«Il y aura probablement une question sur mon cours pour le contrôle de la rentrée, je ne peux pas vous l’assurer, mais habituellement...

Cours : Oracle ™ iconCours dsp isen parti ppt; cours dsp isen parti ppt; cours dsp isen parti ppt
«Digital Signal Processor». Ce Cours doit vous permettre de mettre en œuvre les concepts et les techniques que vous avez vus dans...

Cours : Oracle ™ iconCours de mme tenenbaum
«bis». IL n’y aura pas de résumé ou topo du cours présenté par le(a) chargé(e) de td au début de chaque séance. Le cours doit donc...

Cours : Oracle ™ iconAu cours d’une cirrhose hépatique, on retrouve des signes d'htp (hypertension...





Tous droits réservés. Copyright © 2016
contacts
m.20-bal.com