cours1 pl sql

8/8/2019 Cours1 Pl SQL

http://slidepdf.com/reader/full/cours1-pl-sql 1/26

Complément sur le langage SQL

Nadhem Bel Hadj OCA DBA 1 [email protected]

COMPLEMENT SUR LE LANGAGE SQL

1 Lescontraintes d’intégrité Spécification d’une clé primaire

CREATE TABLE nom_table (Nom_colonne1 type_données [NOT NULL],…Nom_colonne_n t ype_données [NOT NULL],[Constraint nom_contrainte][Primary key (nom_colonneA, nom_colonneB,…,nom_colonneX)]) ;

ü nom_contrainte est un nom optionnel qui identifie la contrainteü nom_colonneA à nom_colonneX représentent les colonnes qui composent la clé primaire

de la table.

Exemple:

CREATE TABLE Ligne_Facture (Numfact Number(5),Codprod varchar2(5),Qtefact Number(9,3) NOT NULLConstraint CP_LIG_FACTPrimary key (Numfact, Codprod)) ;

Remarque : La contrainte NOT NULL n’est pas utilisée pour les colonnes Numfact et Codprod car lacontrainte Primary Key remplace, entre autre, NOT NULL.

Spécification d’une clé étrangèreCREATE TABLE nom_table (

Spécification_colonne1,…Spécification_colonneN,[Constraint nom_contrainte_CE Foreign Key (nom_colonneF1,…,nom_colonneFN)referencestable_référence (nom_colonneP1,…,nom_colonnePN),]

[Constraint nom_contrainte_CP][Primary key (nom_colonneA, nom_colonneB,…,nom_colonneX)]) ;

ü Spécification_colonne1 à spécificatin_colonneN sont les spécifications des colonnesvalides (décrites ci-après en détail).

ü nom_contrainte_CE est le nom optionnel de la contrainte de clé étrangère.ü nom_colonneF1 à nom_colonneFN représentent les colonnes qui composent la clé

étrangère.ü table_référence représente la table à laquelle se réfère la déclaration de la clé étrangère.ü Nom_colonneP1 à nom_colonnePN représentent la clé primaire de la table référencée.

La syntaxe pour spécification colonne est la suivante :

Create PDF with GO2PDF for free, if you wish to remove this line, click here to buy Virtual PDF Printer

http://www.go2pdf.com/






nom_colonne type_données [DEFAULT valeur_par_défaut][Constraint nom_contrainte] [NULL] | [NOT NULL] | [UNIQUE] | CHECK (condition)

ü valeur_par_défaul est une valeur affectée à la colonne lors d’une insertion.

ü Nom_contrainte désigne le nom de la contrainte.ü Condition est un condition booléenne que doit vérifier les valeurs affectées à la colonne.

Exemple:

CREATE TABLE Facture (Numfact Number(5) ,Datefact date default SYSDATE,Mode_paie varchar2(10) Constraint CK_mode CHECK (mode_paie I N

(‘cheque’,’espèce’)),Codcli varchar2(5),Constraint CP_FACT Primary key (Numfact),

Constraint CE_CODCLI Foreign Key (Codcli) referencesClient(codcli)) ;

2 Lesfonctionsintégrées de SQLM anipulation des chaînes

v LENGTH (chaîne) : cette fonction retourne la longueur d’une chaîne dans une colonned’une table.Exemple : Select nomcli, LENGTH (nomcli) from client ;Si le nom est ‘ALI’ la fonction retourne 3

v SUBSTR ( chaîne, position_départ, nombre_caractères) : :cette fonction permet d’extraireune portion d’uneExemple :

Select nomcli, SUBSTR (nomcli,1,4) from client ;Si le nom est ‘mohamed‘ la fonction retourne ‘moha’

v REPLACE (chaîne, chaîne_existante, [chaîne_remplacement]) : cette fonction sert àremplacer une chaîne par une autre dans une colonne.Exemple : Update Cours

Set titre=REPLACE(titre, ’séminaire’,’formation’) ;

Note : si on ne spécifie pas la chaîne de remplacement, la chaîne existante sera suppriméede la colonne.

v LTRIM (chaîne): cette fonction élimine les espaces (blanc) de début de chaînev RTRIM (chaîne): cette fonction élimine les espaces de fin de chaînev LPAD (chaîne, n, chaîne_remplissage) : cette fonction permet de compléter une valeur de

chaîne en lui ajoutant des caractères au début.Ø n : est la longuer totale de la chaîne retournée par la fonctionØ chaîne_remplissage : est la chaîne placée au début de chaîne

Exemple : Si la colonne nom est ‘ALI’ alors si on appelle la fonction :








LPAD(nom,5,’*’)Le résultat sera ‘ALI**’Note : si on ne spécifie pas la chîne de remplissage la chîne sera complétée par desespaces.

v RPAD (chaîne, n, chaîne_remplissage) : cette fonction fait la même chose que LPAD mais

à la fin de la chaîne.

v LOWER (chaîne) : convertit tous les caractères de la chaîne en minusculesv UPPER (chaîne) : convertit tous les caractères de la chaîne en majusculesv DECODE (expression, valeur1,valeur1_retourné,…,

valeurN, valeurN_retournée[,valeur_défaut_retournée])

Ø valeurN est une valeur possible à laquelle valeurN peut être égale.Ø valeurN_retournée est la valeur retournée par DECODE si expression est égale à

valeurN.Ø valeur_défaut_retournée est la valeur retournée si expression n’est égale à aucune des

valeurs de valeur1 à valeurN.Exemple :Select jour, DECODE(jour, 1,’DIM’,2,’LUN’,3,’MAR’,4,’MER’,5,’JEU’,6,’VEN’,7,’SAM’)From vente ;v ASCII (chaîne ) retourne le code ASCII du premier caractère de chaîne.

M anipulation des datesOracle possède un certain nombre de valeur appelées pseudo-colonnes auquelles il estpossible de se référer dans des instructions SQL. Une de ces valeurs est SYSDATE quiretourne la date actuelle (heure comprise à la seconde près).

Voici à présent un tableau contenant les formats de date possibles :

Tableau 1 : lesformatsdedate

Format Description Plage de valeursSS Seconde 0-59

SSSS Seconde après minuit 0-86399

MI Minute 0-59

HH Heure 0-12

HH24 Heure militaire 0-23DD Jour du mois 1-31

DAY Jour de la semaine en entier DIMANCHE-SAMDID Jour de la semaine 1-7

DDD Jour de l’année 1-366MM Numéro du mois 1-12

MON Mois abrégé JAN-DEC

MONTH Mois en entier JANVIER-DECEMBREYY Deux derniers chiffres de l’années 99 par exemple

YYYY Année en entier 1999 par exempleYEAR Année en entier MIILLENEUFCENTQUATREVI

NGTDIXNEUF par exemple

CC Siècle 19 par exempleQ Trimestre 1-4








W Semaine du mois 1-5

WW Semaine de l’année 1-52

Remarque : Le format de date par défaut d’oracle est DD-MON-YY, par exemple 01-JAN-99.

v TO_CHAR (valeur_date, format_date) cette fonction permet de convertir une date en unechaîne de caractères.

Pour les formats de date valides voir le tableau 1 : les formats de date ci_dessus.

Exemple : Select codetud, TO_CHAR(date_naiss,’MONTH DD, YYYY’) DATE_NAISSfrom Etudiant ;

Une ligne résultat de cette requête serait :

Codetud DATE_NAISS-------------------------------------------------------------------------ET001 AVRIL 12, 1978

v TO_DATE (chaîne, format_date) cette fonction permet de convertir une chaîne decaractères en une date, le paramètre format_date est le même que la fonctionTO_CHAR.

Exemple : Select SYSDATE – TO_DATE(‘07-06-1978’,’MM-DD-YYYY’) from dual ;

Le resultat de cette requête est :

SYSDATE-TO_DATE('07-06-1978','MM-DD-YYYY')------------------------------------------

7882 ,41303Remarque : dual est une table système à laquelle l’utilisateur ne peut ajouter aucune ligne car cettetable ne doit contenir qu’une seule ligne veillant au fonctionnement correcte de certainoutils Oracle. SYSDATE est une colonne de la table dual.

M anipulation des nombresv TO _CHAR (nombre, [,format]) cette fonction permet de convertir un nombre en une

chaîne de caractères , format est le modèle de format optionnel que la fonction peututiliser. Pour spécifier le nombre de chiffres affichés, il faut remplacer chaque chiffre parun 9 comme l’indique les exemples suivants :

Exemples : Select cred_cli , TO_CHAR(cred_cli) from client ;

Select TO_CHAR(cred_cli,’9,999.99’) from client ;

Select TO_CHAR(cred_cli,’09.99’) from client ;








Select TO_CHAR(cred_cli,’$999.99’) from client ;

v TO_NUMBER (chaîne [, format]) cette fonction permet de convertir une chaîne en unnombre, format est le modèle de format optionnel que la fonction peut utiliser

Exemple : Update produit set Puprod = TO_NUMBER(‘$2.81’,’$999.99’)Where codprod = ‘po888’ ;

v ROUND (valeur ,[échelle]) cette fonction permet d’arrondir un nombre, elle reçoit deuxparamètres : une expression numérique et un nombre optionnel correspondant au nombrede chiffres utilisés lors du calcul de l’arrondi. Si le deuxième paramètre n’est pas fourni lafonction arrondi l’expression numérique à l’entier le plus proche. Si en revanche il estindiqué la valeur numérique sera arrondie au nombre décimal le plus proche avec, à droitede la virgule, le nombre de chiffres spécifié (échelle).

Exemple : Select ROUND(123.2) from dual ;

Le résultat sera :ROUND(123.2)------------

123

Select ROUND(123.27,1) from dual ;

ROUND(123.27,1)

---------------123,3

Select ROUND(101.8) from dual ;

ROUND(101.8)------------

102

v TRUNC (valeur ,[échelle]) cette fonction est similaire à la fonction ROUND. Toutefois,

au lieu d’arrondir, elle supprime la partie décimale de son paramètre numérique.

Exemple : Select TRUNC(123.33), TRUNC(123.567,2) from dual ;

TRUNC(123.33) TRUNC(123.567,2)------------- ----------------

123 123,56

v FLOOR (valeur) cette fonction est presque identique à la fonction TRUNC, à cettedifférence près qu’elle ne peut tronquer un nombre décimal. Elle retourne l’entier qui est

inférieur ou égale à son paramètre.








Exemple : Select FLOOR (128.3), FLOOR (129.8) from dual ;

FLOOR(128.3) FLOOR(129.8)

------------ ------------128 129

v CEIL (valeur) cette fonction retourne l’entier immédiatement supérieur ou égale à sonparamètre numérique.

Exemple : Select CEIL(128.3), CEIL(129.8) from dual ;

CEIL(128.3) CEIL(129.8)----------- -----------

129 130v NVL (valeur_colonne, valeur_substitution) cette fonction retourne valeur_substitution si

valeur colonne est NULL sinon valeur_colonne.

Exemple : Select cocli, NVL (cred_cli, 0) from client ;






Le langage PL/SQL


LE LANGAGE PL/SQL

1 / INTRODUCTION :SQL est un langage complet pour travailler sur une base de donnée relationnelle, mais il necomporte pas des instructions procédurales. PL/SQL comprend quant à lui :

v La partie LID (Langage d’Interrogation des données) de SQL (Select),v la partie LMD (Langage de Manipulation des Données) de SQL (Update, Insert,…)v la gestion des transaction (Commit, Rollback…)v les fonctions standard de SQLv plus une partie procédurale (IF, WHILE,…)

PL/SQL est donc un langage algorithmique complet

REMARQUE : PL/SQL ne comporte pas d’instructions de LDD (Create, Alter…)

2/ STRUCTURE D’UN BLOC PL/SQL

PL/SQL n'interprète pas une commande, mais un ensemble de commandes contenues dans unprogramme ou bloc PL/SQL.La structure d’un bloc est la suivante:

DECLAREDéclarations de variables, constantes, exception;BEGINSection obligatoire contenant des instructions SQL et PL/SQL ou des blocs fils (Possibilitésd’imbrication de blocs)EXCEPTION

Traitement des exceptions (gestion des erreurs)END;

REM ARQUES : Les sections Declare et Exception sont optionnellesChaque instruction de n’importe quelle section doit se terminer par un ‘ ;’






Le langage PL/SQL


Exemple d’un bloc PL/SQL :

DECLAREqte_stock number(5);

BEGIN

Select quantite into qte_stock from PRODUITSwhere codprod= ‘p1’;

-- contrôle du stock suffisantIf qte_stock > 0

then update produits set quantite=quantite-1where codprod= ‘p1’;

Insert into achat values (‘p1', SYSDATE)else

Insert into acheter values ('Plus de p1',SYSDATE);end If, commit, END;

3/. LES DECLARATIONS PL/SQL

La partie déclarative dans un bloc PL/SQL, peut comporter trois types de déclarations. Elleest délimitée par les mots-clé :v DECLARE, qui spécifie le début etv BEGIN, qui signifie la fin de la déclaration et le début de la partie des commandes.v Les types de déclarations possibles dans cette partie sont les suivants :ü déclaration des variables et des constantes,ü déclaration de curseurs,ü déclaration des exceptions.

3.1 TypesdedonnéesChaque variable ou constante utilisée dans un bloc PL/SQL, possède un type de données.PL/SQL offre deux variétés de types de données prédéfinies : scalaire et composé.

Lestypesscalaires:Binary_integer : entiers entre –231 et 231 -1Number : un numériquePositive : entiers positifs

Natural : entiers naturelsDecimal , float, integer, real sont des sous-types de numberChar : chaîne de caractère à taille fixe allant jusqu’à 32 767 caractère (au lieu de 255 dans ladéfinition des colonnes des tables)Varchar2 : est une chaîne de caractère de longueur variable allant jusqu’à 32 767 (au lieu de2000 dans la définition des colonnes des tables)Boolean : type booléen ses valeurs possibles sont TRUE, FALSE, NULLDate : type date d’OracleRowid : type interne à Oracle composé de 6 octets qui permet d’identifier une ligne uniqued’une table et d’offrir un accès rapide aux données.

LestypesComposés:Record : c’est le type enregistrement






Le langage PL/SQL


Table : c’est le type tableau

3.2 Variableset constantes

La déclaration d'une variable consiste à allouer un espace pour stocker et modifier une valeur.elle est typée et peut recevoir une valeur par défaut et/ou un statut NOT NULL.Une constante est définie comme une variable, mais l'utilisateur ne peut pas modifier soncontenu.

3.2.1. La définition des variables en PL/SQL

Les variables se définissent dans la partie DECLARE, du bloc PL/SQL en ut ilisant la syntaxesuivante :

nomvariable [CONSTANT]{type | variable%TYPE | colonne_de_table.%ROWTYPE}[NOT NULL][{ :=|DEFAULT} expression PL/SQL]

Remarques:L'attribut CONSTANT permet de figer l'affectation d'une variable.L'attribut Not NULL rend obligatoire d'initialiser la variable lors de sa définition.On peut faire référence à une colonne d'une table par la déclaration :

♦ nom_variable TABLE.COLONNE%TYPE,

On peut faire référence à une ligne d'une table par la déclaration♦ Nom_variable TABLE%ROWTYPE,

On peut faire référence à une variable précédemment définie par l'instruction

♦ Nom_variable Pnom variable%TYPE

♦ L'initialisation d'une variable se fait par l'opérateur ‘ :=’ suiviü d'une constante,ü d'une expression PL/SQL,ü d'une fonction PL/SQL.

Les variables peuvent également être définies dans l'environnement extérieur au Bloc PL/SQLpar exemple comme des champs de l'écran en Form Builder. Ces variables seront utiliséespréfixées de':'.

Exemplesde déclaration de variables:Total NUMBER(9,3);Nom CHAR(4) := ‘ISET’;Longeur NUMBER NOT NULL := LENGTH (Nom)*2;Date_Création DATE;Numéro EMPLOYE.EMPNO%TYPE;Dpt DEPARTEMENT%ROWTYPE;Prénom Nom%TYPE;

Pi CONSTANT NUMBER:= 3.14;






Le langage PL/SQL


3.2.2 L’affectation des variables PL/SOL

Deux possibilités d’affectation ou d’assignement sont disponibles:par l'opérateur d'affectation:':=',par la clause Select ... Into…

La difficulté dans l'utilisation de la clause Select résulte du nombre de lignes ou d'occurrencesretourné.Si le Select retourne une et une seule valeur l'affectation s'effectue correctement. Par contreSi le SELECT ne retourne aucune ligne, l’erreur PL/SQL NO_DATA_FOUND sera générée.Si le SELECT retourne plusieurs lignes : l’erreur TOO_MANY_ROWS sera générée.

3.3. Lestableaux en PL/SQL

Nous avons vu précédemment que le langage PL/SQL fournit deux types d'objets composés:

• les tableaux (TABLE),• les enregistrements (RECORD).Les tableaux sont conçus comme les tables de la base de données. Ils possèdent une cléprimaire(index) pour accéder aux lignes du tableau.Un tableau, comme une table, ne possède pas de limite de taille. De cette façon, le nombred'éléments d'un tableau va croître dynamiquement.

3.3.1 La déclaration d'un tableau

Les tableaux PL/SQL doivent être déclarés en deux étapes.1.Déclaration du type de la TABLE

2.Déclaration d’une table de ce type.

On peut déclarer un type TABLE dans la partie déclarative d'un bloc ou d'un sous-programmeen utilisant la syntaxe suivante :

TYPE nom_type I S TABLE OF{typecolonne | variable%TYPE | table.colonne%TYPE} [NOT NULL]INDEX BY BINARY_INTEGER ;

Norn_type : utilisé ultérieurement dans la déclaration des tables PL/SQL.

typecolonne : type de données comme CHAR, DATE, ou NUMBER.






Le langage PL/SQL


Lorsque le type est déclaré, on peut déclarer des tableaux de ce type de la façon suivante :

Nom_tab nom_type ;

3.3.2 L'accèsaux élémentsd'un tableauPour accéder à un élément du tableau, on doit spécifier une valeur de clé primaire en

respectant la syntaxe suivante :

Nom_tab(valeur_clé_primaire) ;

Valeurclé_primaire : doit être du type BINARY_INTEGER

Pour affecter la valeur d'une expression PL/SQL à un élément du tableau, on doit utiliser lasyntaxe suivante :

nom_tab(valeur_clé_primaire) := expression_Pl/sql ;

Exemple:

DECLARE

TYPE nom_tab_type IS TABLE OF CHAR(25) INDEX BY BINARY-INTEGER;…

tab_nom nom_tab_type ;

BEGIN

…tab_nom(l):='TUNISIE';…END;

3.4. Lesenregistrementsprédéfinis (record PL/SOL)

La restriction posée par l'utilisation du type %ROWTYPE pour déclarer un enregistrementréside dans le manque de spécification des types de données au niveau de l'enregistrement.

L'implémentation du nouveau type composé nommé RECORD a permis de lever cetterestriction.

3.4.1 La déclaration d'un enregistrement

Comme les tableaux PL/SQL, la déclaration d'un enregistrement se fait en deux étapes :






Le langage PL/SQL


1. Déclaration du type de l'enregistrement2. Déclaration de la variable du type défini.

On peut déclarer un Type RECORD dans la partie déclarative d'un bloc ou d'un sous-

programme en utilisant la syntaxe suivante:

TYPE nom_type IS RECORD

(champ {type_champ | table.colonne%TYPE [NOT NULL],champ (type_champ | table.colonne%TYPE [NOT NULL],...)

nomtype : utilisé ensuite dans la déclaration des enregistrements PL/SQL.Type-champ : type de données comme CHAR, DATE ou NUMBER.

Lorsque le type est déclaré,On peut déclarer des enregistrements de ce type de la façon

suivante :Nom_erg nom_type ;

3.4.2 L'accèsaux champs d'un enregistrementPour accéder à un élément d'une variable de type record, il suffit d'utiliser la syntaxe suivante

Nom_erg.nom_champ

Pour affecter la valeur d'une expression PL/SQL à un élément de l'enregistrement, on doitutiliser la syntaxe suivante :

Nom_erg.nom_champ := expression_pl/sql;

Exemple:

DECLARETYPE ADRESSE IS RECORD(Numero positive,Rue varchar2(35),CodePost char(5),Ville varchar2(25),Pays varchar2(30) );

TYPE CLIENT IS RECORD(NumCIi positive,NomCli varchar2(40),Adrcli ADRESSE,CA number(12,3));

monclient CLIENT ;BEGINmonclient.NumCIi := 1234;monclient.NomCIi := ‘Ben HUSSEIN’;monclient.AdrCli.Numero := 10;

END ;






Le langage PL/SQL


4 STRUCTRES DE CONTROLE

4.1. LestraitementsConditionnels

IF condition_plsql

THEN commandes[ELSE commandes]

[ELSIF condition_plsqlTHEN commandes

[ELSE commandes]]END I F;

La condition peut utiliser les variables définies ainsi que tous les opérateurs présents dansSQL =,<,>,<=,>=,<>, IS NULL, IS NOT NULL.Exemple:

DECLAREvjob CHAR(10);vnom employés.ename%type := ‘BEN SALAH’ ;msg CHAR(30) ;

BEGINSelect job into vjob from employés where ename = vnom;

--contrôle de la valeur de vjobif vjob is NULL

then msg := vnom || ‘pas de travail';elsif vjob = ‘Vendeur’ then

UPDATE employés set comm = 100 where ename=vnom ;Msg := vnom || ‘a 100 dinars de commission’ ;

ElseUPDATE employés set comm = 0 where ename=vnom ;Msg := vnom || ‘n’a pas de commission’ ;

End if ;DBMS_OUTPUT.PUT_LINE(msg) ;

commit;

END ;4.2 LesTraitements.Répétitifs4.2.1. l’instruction LOOP

LOOP permet de répéter une séquence de commandes. Cette séquence est comprise entre lemot-clé LOOP, indiquant le début d’une boucle et END LOOP, spécifiant sa fin.

Syntaxe :

LOOPinstructions

END LOOP ;






Le langage PL/SQL


Les commandes EXIT,EXIT WHEN condition permettent de quitter la boucle.Exemple:

DECLARE

somme number(10) := 0 ;nombre number(5);BEGINnombre:=0;

LOOPNombre:=nombre+1 ;somme := somme + nombre ;if nombre>=10then EXIT;

end if ;END LOOP;DBMS_OUTPUT.PUT_LINE(‘la somme des 10 premiers entiers est ‘ || to_char(somme));END;

4.2.2. L'instruction FOR …LOOP

FOR compteur IN [REVERSE ] var_debut.. var_fin LOOP

instructions

END LOOP ;

compteur. est une variable de type entier, locale à la boucle. Sa valeur de départ est égale pardéfaut à la valeur de l'expression entière de gauche (var_debut). Elle s'incrémente de 1, aprèschaque traitement du contenu de la boucle, jusqu'à ce qu'elle atteigne la valeur de droite(var_fin).

5. LES CURSEURS EN PL/SQLPour traiter une commande SQL, PL/SQL ouvre une zone de contexte pour exécuter lescommandes et stocker les informations.

5.1. DéfinitionLe curseur permet de nommer cette zone de contexte, d'accéder aux informations etéventuellement de contrôler le traitement. Cette zone de contexte est une mémoire de taillefixe, utilisée par le noyau pour analyser et interpréter tout ordre SQL.

5.2. Lestypesdecurseursv Le curseur expliciteIl est créé et géré par l'utilisateur pour traiter un ordre Select qui ramène plusieurs lignes.Le traitement du select se fera ligne par ligne.

vLe curseur impliciteIl est généré et géré par le noyau pour les autres commandes SQL.






Le langage PL/SQL


5.3. Lesétapesd'utilisation d'un curseur explicitePour traiter une requête qui retourne plusieurs lignes, l'utilisateur doit définir un curseur quilui permet d’extraire la totalité des lignes sélectionnées.

L’utilisation d'un curseur pour traiter un ordre Select ramenant plusieurs lignes, nécessite 4

étapes:

1. Déclaration du curseur2. Ouverture du curseur3. Traitement des lignes4. Fermeture du curseur.

5.3.1. La déclaration d'un curseurLa déclaration du curseur permet de stocker l'ordre Select dans le curseur.La Symtaxe de définition..

Le curseur se définit dans la partie DECALRE d'un bloc PL/SQL.Cursor nomcurseur [(nompararn type [,nomparam type, ...)]IS Commande_SELECT

Exemple:

DeclareCursor DEPT_10 is

select ename, sal from emp where depno = 10;

5.3.2. L'ouverture et la fermeture d’un curseurL'étape d'ouverture permet d'effectuer:1. l'allocation mémoire du curseur,2. l'analyse sémantique et syntaxique de l'ordre3. le positionnement de verrous éventuels (si select for update...)

L'étape de fermeture permet de libérer la place mémoire réservé.

La syntaxe :

OPEN nomcurseur [(nomparam1[,nomparam2 , ...)]

/* traitement des lignes */ CLOSE nomcurseur

Exemple:Begin

…OPEN DEPT_10

/* traitement des lignes*/ CLOSE DEPT_10






Le langage PL/SQL


5.3.3. Le traitement des lignesIl faut traiter les lignes une par une et renseigner les variables réceptrices définies dans lapartie Declare du bloc.

La syntaxe :

Dans la partie traitement du bloc.PL/Sql, il faut commencer par ouvrir le curseur puis

FETCH nomcurseur INTO {nomvariable [,nomvariable] | nomrecord}

L’ordre fetch ne ramène qu’une seule ligne à la fois. De ce fait il faut recommencer l’ordrepour traiter la ligne suivante.

Exemple:Declare

Cursor DEPT_10 is

select ename, sal from emp where depno = 10;Vnom emp.name%TYPE ;Vsalaire emp.sal%TYPE ;

BeginOPEN DEPT_10;LOOP

FETCH DEPT10 into vnom,vsalaire ;--Traitement ligne

END LOOP ;CLOSE DEPT_10;

End;

5.4 Lesattributsd’un curseurLes attributs d’un curseur nous fournissent des informations sur l’exécution d’un ordre. Ilssont conservés par PL/SQL après l’exécution du curseur (implicite ou explicite)

Ces attributs permettent de tester directement le résultat de l’exécution. Ils sont résumés dansle tableau suivant :






Le langage PL/SQL


Curseurs implicites Curseurs explicitesSql%Found Nomcurseur%Found

Sql%Notfound Nomcurseur %Notfound

Sql%Isopen Nomcurseur %IsopenSql%Rowcount Nomcurseur %RowcountSql%Rowtype Nomcurseur %Rowtype

5.4.1 l’attribut % Found

cet attribut est de type booléen (vrai ou faux)Pour les curseurs implicites, cet attribut est vrai si les instructions insert, update, delete traitentau moins une ligne. Pour les curseurs explicites, il est vrai si le fetch ramène au moins uneligne.

Exemple:

DeclareCURSOR num_cur1 IS SELECT num from tab1 ;CURSOR num_cur2 IS SELECT num from tab2 ;Num1 number ;Num2 number ;somme number := 0;

BeginOPEN num_cur1 ;OPEN num_cur2 ;

LOOPFFTCH num_cur1 INTO num1 ;FFTCH num_cur2 INTO num2;IF (num_cur1%Found) AND (num_cur2%Found) THEN

somme:= num1 + num2 ;INSERT INTO sum_tab VALUES (somme);

ELSEExit ;

END IF;END LOOP;

CLOSE numl_cur1 ;

CLOSE mun_cur2 ;COMMIT;END;

5.4.2. L 'attribut % NotFound

cet attribut est de type booléen (vrai ou faux)Pour les curseurs implicites, cet attribut est vrai si les instructions insert, update, delete netraitent aucune ligne. Pour les curseurs explicites, il est vrai si le fetch ne ramène plus deligne.






Le langage PL/SQL


Exemple:

DeclareCURSOR num_cur1 IS SELECT num from tab1 ;CURSOR num_cur2 IS SELECT num from tab2 ;

Num1 number ;Num2 number ;somme number := 0;

BeginOPEN num_cur1 ;OPEN num_cur2 ;

LOOPFFTCH num_cur1 INTO num1 ;FFTCH num_cur2 INTO num2;EXIT WHEN (num_cur1%NotFound) OR (num_cur2%NotFound) ;somme:= num1 + num2 ;

INSERT INTO sum_tab VALUES (somme);END LOOP;CLOSE numl_cur1 ;CLOSE mun_cur2 ;END;

5.4.3. L'attribut % I sOpenCet attribut est de type booléen soit vrai, soit faux.Le curseur implicite est toujours faux car Oracle referme toujours les curseurs qu'il ouvreaprès chaque utilisation.

Le curseur explicite est vrai si le curseur est ouvert.Exemple:

DeclareCursor Dept10 is

Select ename, sal from emp where deptno=10;BeginIf not (Dept10%lsopen) then

open Dept10 ;End if,Fetch Dept10 into …

--traitementEnd ;

5.4.4.'L'attribut % RowCount

Cet attribut est de type numérique. Le curseur implicite indique le nombre de lignes traités parles ordres insert, update, delete.Le curseur explicite est incrémenté à chaque ordre fetch, donc cet attribut traduit la nièmeligne traitée.






Le langage PL/SQL


Exemple

Declarecursor C1 isselect ename, empno, sal from emp order by sal desc ;

nom char(10);numero number(4);salaire number(7,2);BeginOpen Cl;Loop .Fetch cl into nom, numéro, salaire ;exit when (c1%rowcount > 25) or (cl%notfound);insert into temp values (salaire, numéro, nom);

End Loop;

Close C1 ;commit ;End;

5.4.5. L'attribut % Rowtype

Cet attribut permet la déclaration implicite d'une structure dont les éléments sont d'un typeidentique aux colonnes ramenées par le curseur.

Syntaxe :

Dans la partie déclarative du bloc.

CURSOR nomcurseur IS ordre select;nomrecord nomcurseur%Rowtype;

Les éléments de la structure sont identifiés par:nomrecord.nomcolonneLa structure est renseignée par le Fetch : Fetch nomcurseur into nomrecord;

Exemple

Declare /* la fonction nvl permet de tester la valeur Null d'une colonne si comm=NULL alors nvlretourne 0 sinon nvl retourne la valeur de comm */

cursor c1 is select sal + nvl(comm) saltot, ename from emp; /* l'enregistrement comporte deux colonnes: saltot, enamec1_record c1%Rowtype;

Beginopen c1;Loop

Fetch c1 into c1_record;Exit when c1 %notfound ;






Le langage PL/SQL


if c1_record.saltot > 2000 theninsert into temp values (c1_record.saltot, c1_record.ename);

end if,End Loop;close c1;

End;

5.5. Lesboucleset lescurseursL'objectif est de fournir au programmeur une structure simple et efficace pour utiliser lesstructures de boucle et les curseurs.

DeclareCursor nomcurseur is ordre_select ;Begin

For nomrecord in nomcurseur Loop /* traitementEnd Loop;End;

Le bloc PL/SQL ci-dessu permet d'obtenir une génération implicite de la structure suivante :

DeclareCursor nomcurseur is ordre select;nomrecord nomcurseur%rowtype;Begin

Open nomcurseur ;LoopFetch nomcurseur into nomrecord ;Exit when nomcurseur%notfound ; /* traitementEnd Loop;Close nomcurseur;End;

5.6. Le curseur paramètre

Il permet d'utiliser des variables dans le curseur. Principalement dans la clause where.Il faut pour cela spécifier les noms et les types des paramètres dans la déclaration du curseur.

Cursor nomcurseur (paraml type, param2 type,...)is select ordre_select ,

L'ordre_select utilise les paramètres.Les types possibles sont : char, number, date, boolean sans spécifier la longueur.

BeginOpen nomcurseur (valeurl , vaieur2, ....) ;






Le langage PL/SQL


Exemple:

Declarecursor c1 (depart number)is select sal, nvl(comm,0) commi

from emp where deptno=depart ;

total number(11,2) := 0 ;sal_sup number(4):=0 ;comm_sup number(4):=0;

Beginfor c1_rec in c1(20) Looptotal:= total+ c1_rec.sal + c1_rec.commi ;if c1_rec.sal > 4000then sal_sup := sal_sup + 1 ;

end if ;if c1_rec.commi > 3000then commi_sup:=commi_sup+1 ;end if;End Loop;insert into tempvalues (sal_Sup, comm_sup, 'total salaire’||to_Char(total));commit ;End;

5.7. La clause "current of…"Cette clause permet d'accéder directement à la ligne ramenée par l'ordre Fetch afin de latraiter (update, delete).

v Il faut se réserver la ligne lors de la déclaration du curseur par le positionnement d'unverrou d'intention : (For update of nom_colonne)

v Il faut spécifier que l'on veut traiter la ligne courante au Fetch par la clause :(Wherecurrent of nom_curseur)

Exemple:

Declare

Cursor cl is select ename, sal from empfor update of sal ;

BeginFor c1_record in c1 Loop

If c1_ record.sal > 1500 theninsert into resultat values (c1_record.sal, c1_record.sal*1.3, c1_record.ename);update emp set sal = sal * 1.3 where current of c1 ;

end if,End loop ;Commit;End ;






Le langage PL/SQL


6 Utilisation de sous-programmesPL/SQL accepte aussi l’utilisation de sous-programmese, nommés procédures et fonctions.Une procédure PL/SQL exécute certaines actions et peut accepter des paramètres optionnels.Une fonction PL/SQL retourne une valeur d’un certain type et peut aussi accepter des

paramètres optionnels.

6.1 Déclaration d’une procédureLa syntaxe de déclaration d’une procédure est :

PROCEDURE nom_procédure [(paramètre1 … [, pramètreN ] IS[déclaration des variables locales ]

BEGINInstructions PL/SQL[section_exception ]END [nom_procédure ]

• Paramètre1 à paramètreN représentent la déclaration des paramètres de la procédure sousla forme : nom_paramètre [IN|OUT|IN OUT] type_données [ { :=|default } valeur].

• déclaration des variables locales représentent les déclarations optionnelles de variables,de constantes, de fonctions et de procédures locales à la procédure.

• section_exception est la section optionnelle de gestion des exceptions de la procédure.

Exemple:

Declare

Vpatient_ID Patient.Patient_ID%type ;Fièvre_elevée constant real := 42 ;

Procedure enregistrer_Temp_Deg_C_Patient (patient_ID varchar2,Temp_Corps_Deg_C real) is

Temp_Deg_F real ;Begin

Temp_Deg_F := (9/5)* Temp_Corps_Deg_C + 32 ;Insert into Patient (Patient_ID, Temp_Corps_Deg_F)Values

(patient_ID, Tem_Deg_F) ;Commit ;

End ;Begin

Vpatient_ID := ‘ES8888’ ;enregistrer_Temp_Deg_C_Patient (Vpatient_ID, Fièvre_elevée) ;

End ;






Le langage PL/SQL


6.2 Déclaration d’une fonctionLa syntaxe de déclaration d’une fonction est :

FUNCTION nom_fonction [(paramètre1 … [, pramètreN ]RETURN type_données_fonction IS

[déclaration des variables locales ]

BEGINInstructions PL/SQLRETURN val_à_retournée [section_exception ]END [nom_fonction ]

Exemple:

Soit l’extrait suivant d’une base de données relationnelle d’une application de gestion descolarité d’une université :Etudiant(codetu, nometu,adretu)Matière(codmat, description_mat, coefmat)Moyenne(codetu,codmat, moyenne_mat)

La fonction meilleur_étu_mat retourne le code du meilleur étudiant d’une matière donnée.

DeclareCodmatière Matière.codmat%type;Vcode_etu etudiant.codetu%type;

FUNCTION meilleur_étu_mat (pcodmat varchar2) RETURN varchar2 ISVcodetu etudiant.codeetu%type ;

BEGIN

Select codetu into VcodetuFrom Moyenne MWhere M.moyenne_mat in (Select max(moyenne_mat from Moyenne M

Where M.codmat=pcodmat);Return Vcodetu;

End ;Begin

Codmatière := ‘M001’;Vcode_etu := meilleur_étu_mat(Codmatière);

DBMS_OUTPUT.PUT_LINE(‘Le meilleur étudiant de la matière’ || Codmatière || ‘est: ‘ || Vcode_etu);End ;






Le langage PL/SQL


6.3 Paramètresde procédure et fonctionUn ou plusieurs paramètres peuvent être passés à une procédure ou à une fonction, de troisfaçons : IN, OUT ou IN OUT

Exemple:

DeclareParam1 number ;Param2 number ;Param3 number ;

Procedure Différents_Param(P1 IN number, P2 OUT number, P3 IN OUT number) IsBegin

P2 := P1 ;P3 := P3 + 1 ;

END ;Begin

Param1 := 3.14 ;Param3 := 15 ;Différents_Param(Param1, Param2, Param3) ;DBMS_OUTPUT.PUT_LINE('param1 = ' || to_char(param1) || 'param2 = ' ||

to_char(param2) ||'param3 = ' || to_char(param3) );

End ;

7. GESTION DES ERREURSLe mécanisme de gestion d'erreurs dans PL/SQL est appelé gestionnaire des exceptions.Il permet au programmeur de planifier sa gestion et d'abandonner ou de continuer letraitement en présence d'une erreur.Il faut affecter un traitement approprié aux erreurs apparues dans un bloc PL/SQL.C'est pourquoi on distingue 2 typesd'erreurs ou d'exceptions:

1. Erreur interne Oracle (Sqlcode <= 0) : dans ce cas la main est rendue directement ausystème environnant.

2. Anomalie déterminée par 1'utilisateur.

La solution :1. Donner un nom à l'erreur (si elle n'est pas déjà prédéfinie),2. Définir les anomalies utilisateurs, leur associer un nom,3. Définir le traitement à effectuer.

7.1. LesexceptionsinternesUne erreur interne est produite quand un bloc PL/SQL viole une règle d'Oracle ou dépasseune limite dépendant du système d'exploitation. Les erreurs Oracle générées par le noyau sontnumérotées, or le gestionnaire des exceptions de PL/SQL, ne sait que gérer des erreursnommées.Pour cela PL/SQL a redéfini quelques erreurs Oracle comme des exceptions. Ainsi, pour gérer

d'autres erreurs Oracle, 1'utilisateur doit utiliser le gestionnaire OTHERS ouEXCEPTION_INIT pour nommer ces erreurs.






Le langage PL/SQL


Les exceptions fournies par Oracle sont regroupées dans ce tableau :

Nom d'exception V aleur SqlCode Erreur OracleCURSOR_ALREADY_OPEN -6511 ORA-06511

DUP_VAL_ON_INDEX -1 ORA-00001INVALID_CURSOR -1001 ORA-01001

INVALID_NUMBER -1722 ORA-01722

LOGIN_DENIED -1017 ORA-01717NO_DATA_FOUND -1403 ORA-01413

NOT_LOGGED_ON -1012 ORA-01012PROGRAM_ERROR -6501 ORA-06501

STORAGE_ERROR -6500 ORA-06500

TIMEOUT_ON_RESOURCE -51 ORA-00051

TOO_MANY_ROWS -1422 ORA-01422

TR.ANSACTION_BACKED_OUT -61 ORA-00061VALUE_ERROR -6502 ORA-06502

ZERO_DIVIDE -1476 ORA-01476

OTHERS : toutes les autres erreurs non explicitement nommées.

Pour gérer les exceptions le programmeurdoit écrire un gestionnaire des exceptions qui prendle contrôle du déroulement du bloc PL/SQL en présence d'une exception.Le gestionnaire d'exception fait partie du bloc PL/SQL et se trouve après les commandes Ilcommence par le mot clé EXCEPTION et se termine avec le même END du bloc.Chaque gestion d'exception consiste à spécifier son nom d'erreur après la clause WHEN et la

séquence de la commande à exécuter après le mot clé THEN, comme le montre l'exemplesuivant:

DECLAREWsal emp.sal%type;BEGINselect sal into wsal from emp;

EXCEPTIONWHEN TOO_MANY_ROWS then…

--gérer erreur trop de lignesWHEN NO_DATA_FOUND then…

--gérer erreur pas de ligneWHEN OTHERS then…

--gérer toutes les autres erreursEND ;

Remarques:

ü L'exception optionnelle OTHERS est toujours située à la fin des exceptions.ü Pour rattacher une séquence de commandes à plus d'une exception, l'utilisateur peut

utiliser l'opérateur booléen OR comme suit :WHEN erreurl OR erreur2 THEN-- gérer erreurl2






Le langage PL/SQL

7.2. Lesexceptionsutilisateur (externes)PL/SQL permet à l'utilisateur de définir ses propres exceptions.La gestion des anomalies utilisateur peut se faire dans un bloc PL/SQL en effectuant lesopérations suivantes:

1. Nommer l'anomalie (type exception) dans la partie Déclare du bloc.

DECLARENom_ano Exception;

2. Déterminer l’erreur et passer la main au traitement approprié par la commande Raise.

BEGIN…If (condition_anomalie) then raise Nom_ano3. Effectuer Ie traitement défini dans la partie EXCEPTION du Bloc.

EXCEPTIONWHEN (Nom_ano) then (traitement);

Exemple:

DECLAREwsal emp.sal%type ;

sal_zero Exception ;BEGINSelect sal into wsal from emp where empno=5;If wsal=0 then

Raise sal_zero;

EXCEPTIONWHEN sal_zero then…

-- gérer erreur salaireWHEN TOO_MANY_ROWS then...

--gérer erreur trop de lignes

WHEN_NO_DATA_FOUND then ...-- gérer erreur pas de ligne

WHEN OTRERS then ...--gérer toutes les autres erreurs

END;

Le programmeur Peut utiliser les fonctions Sqlcode et Sqlerrm pour coder les erreurs Oracleen Exception.sqlcode est une fonction propre à PL/SQL qui retourne le numéro (généralement négatif)de l'erreur courante.Sqlerrm reçoit en entrée le numéro de l'erreur et renvoie en sortie le message de l'erreur codé

sur196 octets.

cours1 pl sql

Documents