PL/SQL Programming and Concepts

26

Exemple

DECLARE

v_emp emp%ROWTYPE; v_nom emp.nome%TYPE;

BEGIN

v_nom := 'Dupond'; insert into emp (matr, nome) values (600, v_nom); v_emp.matr := 610; v_emp.nome := 'Durand'; insert into emp (matr, nome) values (v_emp.matr, v_emp.nome); commit;

END;

PL/SQL

-M. HAJJI-

27

Exemple

DECLARE

v_emp emp%rowtype;

BEGIN

SELECT * INTO v_emp FROM emp WHERE nome = 'LEROY'; v_emp.matr := v_emp.matr + 5; v_emp.nome := 'Toto'; INSERT INTO emp VALUES v_emp;

END;

PL/SQL

-M. HAJJI-

28

Extraction des données: Erreurs

 Si le select renvoie plus d’une ligne, une exception « TOO_MANY_ROWS » (ORA-01422) est levée.  Si le select ne renvoie aucune ligne, une exception « NO_DATA_FOUND » (ORA-01403) est levée.

PL/SQL

-M. HAJJI-

29

Les curseurs

30

PL/SQL

-M. HAJJI-

Fonctionnalités

 Toutes les requêtes SQL sont associées à un curseur  Ce curseur représente la zone mémoire utilisée pour

parser et exécuter la requête

 Le curseur peut être implicite (pas déclaré par

l’utilisateur) ou explicite

 Les curseurs explicites servent à retourner plusieurs

lignes avec un select

PL/SQL

-M. HAJJI-

31

Attributs des curseurs

 Tous les curseurs ont des attributs que l’utilisateur peut

utiliser  %ROWCOUNT : nombre de lignes traitées par le curseur  %FOUND : vrai si au moins une ligne a été traitée par la

requête ou le dernier fetch

 %NOTFOUND : vrai si aucune ligne n’a été traitée par la

requête ou le dernier fetch

 %ISOPEN : vrai si le curseur est ouvert (utile seulement pour

les curseurs explicites)

PL/SQL

-M. HAJJI-

32

Curseurs implicites

 Les curseurs implicites sont tous nommés SQL

DECLARE

nb_lignes integer;

BEGIN

DELETE FROM emp WHERE dept = 10; nb_lignes := SQL%ROWCOUNT; . . .

PL/SQL

-M. HAJJI-

33

Curseurs explicites

 Pour traiter les select qui renvoient plusieurs lignes  Ils doivent être déclarés  Le code doit les utiliser explicitement avec les ordres

OPEN, FETCH et CLOSE

 Le plus souvent on les utilise dans une boucle dont on sort quand l’attribut NOTFOUND du curseur est vrai

PL/SQL

-M. HAJJI-

34

Curseurs explicites

DECLARE

CURSOR salaires IS

SELECT sal FROM emp WHERE dept = 10;

salaire numeric(8, 2); total numeric(10, 2) := 0; BEGIN

OPEN salaires; LOOP

FETCH salaires INTO salaire; EXIT WHEN salaires%NOTFOUND;

IF salaire IS NOT NULL THEN

total := total + salaire; DBMS_OUTPUT.put_line(total);

END IF; END LOOP; CLOSE salaires; -- Ne pas oublier DBMS_OUTPUT.put_line(total);

END;

PL/SQL

-M. HAJJI-

35

Curseurs explicites

 On peut déclarer un type « row » associé à un curseur

DECLARE

CURSOR c IS SELECT matr, nome, sal FROM emp; employe c%ROWTYPE;

BEGIN OPEN c; FETCH c INTO employe; IF employe.sal IS NULL THEN …

PL/SQL

-M. HAJJI-

36

Curseurs explicites

 Elle simplifie la programmation car elle évite d’utiliser explicitement les instruction OPEN, FETCH, CLOSE

 En plus elle déclare implicitement une variable de type «

row » associée au curseur

DECLARE

CURSOR c IS

SELECT dept, nome FROM emp WHERE dept = 10;

BEGIN

FOR employe IN c LOOP

dbms_output.put_line(employe.nome);

END LOOP;

END;

PL/SQL

-M. HAJJI-

37

Curseurs paramétrés

 Un curseur paramétré peut servir plusieurs fois avec des

valeurs des paramètres différentes

 On doit fermer le curseur entre chaque utilisation de

paramètres différents (sauf si on utilise « FOR » qui ferme automatiquement le curseur )

DECLARE

CURSOR c(p_dept integer) IS

SELECT dept, nome FROM emp WHERE dept = p_dept;

BEGIN

FOR employe in c(10) LOOP

dbms_output.put_line(employe.nome);

END LOOP; FOR employe in c(20) LOOP

dbms_output.put_line(employe.nome);

END LOOP;

END;

PL/SQL

-M. HAJJI-

38

Ligne courante d’un curseur

 La ligne courante d’un curseur est déplacée à chaque

appel de l’instruction FETCH

 On est parfois amené à modifier la ligne courante

pendant le parcours du curseur

 Pour cela on peut utiliser la clause « WHERE CURRENT OF » pour désigner cette ligne courante dans un ordre LMD (insert, update, delete)

 Il est nécessaire d’avoir déclaré le curseur avec la clause

FOR UPDATE pour que le bloc compile

PL/SQL

-M. HAJJI-

39

La clause FOR UPDATE

 FOR UPDATE [OF col1, col2,…]  Cette clause bloque toute la ligne ou seulement les

colonnes spécifiées

 Les autres transactions ne pourront modifier les valeurs

tant que le curseur n’aura pas quitté cette ligne

PL/SQL

-M. HAJJI-

40

La clause FOR UPDATE

 Exemple

DECLARE

CURSOR c IS

SELECT matr, nome, sal FROM emp WHERE dept = 10 FOR UPDATE OF emp.sal;

… IF salaire IS NOT NULL THEN total := total + salaire; ELSE -- met 0 à la place de null UPDATE emp SET sal = 0 WHERE CURRENT of c; END IF;

PL/SQL

-M. HAJJI-

41

Les exceptions

42

PL/SQL

-M. HAJJI-

Introduction

 Une exception est une erreur qui survient durant une

exécution

 2 types d’exception :  prédéfinie par Oracle  définie par le programmeur

 Structure d’un bloc:

DECLARE

-- définitions de variables

BEGIN

-- Les instructions à exécuter

EXCEPTION

-- La récupération des erreurs

END;

PL/SQL

-M. HAJJI-

43

Saisi des exceptions

 Une exception ne provoque pas nécessairement l’arrêt du programme si elle est saisie par un bloc (dans la partie « EXCEPTION »)

 Une exception non saisie remonte dans la procédure

appelante (où elle peut être saisie)

 Exceptions prédéfinies  NO_DATA_FOUND  TOO_MANY_ROWS  VALUE_ERROR (erreur arithmétique)  ZERO_DIVIDE  …

PL/SQL

-M. HAJJI-

44

Traitement des exceptions

BEGIN …

EXCEPTION

WHEN NO_DATA_FOUND THEN . . . WHEN TOO_MANY_ROWS THEN . . . WHEN OTHERS THEN -- optionnel

. . . END;

PL/SQL

-M. HAJJI-

45

Exception utilisateur

 Elles doivent être déclarées avec le type EXCEPTION  On les lève avec l’instruction RAISE

DECLARE

salaire numeric(8,2); salaire_trop_bas EXCEPTION;

BEGIN

SELECT sal INTO salaire FROM emp WHERE matr = 50; IF salaire < 300 THEN

raise salaire_trop_bas;

END IF; -- suite du bloc EXCEPTION

WHEN salaire_trop_bas THEN . . .; WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

END;

PL/SQL

-M. HAJJI-

46

Exercices  Ecrire un bloc PL/SQL qui permet d’afficher les joueurs nais

après 1970.

 Ecrire un bloc PL/SQL qui permet d’afficher toutes les

rencontres qui se sont déroulées à Roland Garros en 1992. L’affichage doit se faire sous la forme:

« Gagnant » VS « Perdant »

 Ecrire un bloc PL/SQL qui permet d’afficher la moyenne des

gains pour chaque tournoi.

 Ecrire un bloc PL/SQL « sponsorJoueurs.sql » qui permet

d’afficher les joueurs de chaque Sponsor.

 Ecrire un bloc PL/SQL qui permet d’insérer dans une Table

« primeTournoi » le total des primes pour chaque tournoi et chaque année.

PL/SQL

-M. HAJJI-

47

Exercices

 Ecrire un bloc PL/SQL qui permet d’insérer dans une table « palmares » le numéro, nom et prénom d’un joueur, le nombre de match joué dans sa carrière, le nombre de match gagné, le nombre de match perdu et le total de ses gains.

 Ecrire un bloc PL/SQL qui permet de mettre à jour les

informations nombre de match joués, nombre de matchs gagnés, nombre de matchs perdus et total des gains.

PL/SQL

-M. HAJJI-

48

Les procédures et les fonctions

49

PL/SQL

-M. HAJJI-

Bloc anonyme ou nommé

 Un bloc anonyme PL/SQL est un bloc «DECLARE –

BEGIN –END» comme dans les exemples précédents  Dans SQL*PLUS on peut exécuter directement un bloc

PL/SQL anonyme en tapant sa définition

 Le plus souvent, on crée plutôt une procédure ou une

fonction nommée pour réutiliser le code

PL/SQL

-M. HAJJI-

50

Création d’une procédure

CREATE OR Replace PROCEDURE(<liste params>) IS -- déclaration des variables BEGIN -- code de la procédure END;

 Pas de DECLARE ; les variables sont déclarées entre IS et

BEGIN

 Si la procédure ne nécessite aucune déclaration, le code

est précédé de « IS BEGIN »

PL/SQL

-M. HAJJI-

51

Création d’une fonction

CREATE OR Replace FUNCTION(<liste params>) RETURN <type retour> IS -- déclaration des variables BEGIN -- code de la procédure END;

PL/SQL

-M. HAJJI-

52

Passage de paramètres

 Dans la définition d’une procédure on indique le type de

passage que l’on veut pour les paramètres :  IN pour le passage par valeur  IN OUT pour le passage par référence  OUT pour le passage par référence mais pour un paramètre

dont la valeur n’est pas utilisée en entrée

 Pour les fonctions, seul le passage par valeur (IN) est

autorisé

PL/SQL

-M. HAJJI-

53

Utilisation des procédures et fonctions

 Sous SQL*PLUS, il faut taper une dernière ligne contenant

« / » pour compiler une procédure ou une fonction  Les procédures et fonctions peuvent être utilisées dans d’autres procédures ou fonctions ou dans des blocs PL/SQL anonymes

 Les fonctions peuvent aussi être utilisées dans les

requêtes SQL

 Exemple CREATE OR REPLACE FUNCTION euro_to_fr(somme IN number) RETURN number IS

taux CONSTANT NUMBER := 6.55957;

BEGIN

return somme * taux;

END;

PL/SQL

-M. HAJJI-

54

Utilisation des procédures et fonctions

 Dans un bloc anonyme

DECLARE

CURSOR c(p_dept integer) IS

SELECT dept, nome, sal FROM emp WHERE dept = p_dept;

BEGIN

FOR employe IN c(10) LOOP

dbms_output.put_line(employe.nome

|| ' gagne ' || euro_to_fr(employe.sal) || ' francs'); END LOOP;

END;

 Dans une requête

SELECT nome, sal, euro_to_fr(sal) FROM emp;

PL/SQL

-M. HAJJI-

55

Exécution d’une procédure

 Sous SQL*PLUS on exécute une procédure PL/SQL avec

la commande EXECUTE : EXECUTE nomProcédure(param1, …);

 Sous SQL*PLUS on exécute une fonction PL/SQL avec la

commande select: SELECT nomFonction(param1, …) FROM dual;

PL/SQL

-M. HAJJI-

56

Exercices

 Ecrire une fonction MaxPS qui permet de retourner le

maximum entre deux valeurs « a » et « b ».

 Exécuter la fonction MaxPS directement sous SQL*PLUS,

en définissant les paramètres.

 Dans un programme principal, afficher pour un tournoi et une année donnés, le Nom et Prénom du joueur ayant eu la plus haute prime, en faisant appel à la fonction MaxPS.  Dans un programme principal, afficher le Nom et Prénom du joueur ayant eu le cumul le plus important durant une année donnée, en utilisant la fonction MaxPS.

PL/SQL

-M. HAJJI-

57

Exercices

 Ecrire une fonction RatePS qui permet de calculer le pourcentage d’une valeur par rapport à une valeur globale.

 Dans un programme principal, afficher pour un tournoi et une année donnés, le Nom, Prénom et le pourcentage de la prime de chaque joueur par rapport à la prime globale.

 Dans un programme principal, afficher le Nom et le

Prénom du joueur, ainsi que le pourcentage de sa prime par rapport au cumul de toutes les primes dans une année donnée, pour le joueur ayant eu le plus important pourcentage. On utilisera les fonctions RatePS et MaxPS.

 Exécuter la fonction RatePS sous SQL*Plus.

PL/SQL

-M. HAJJI-

58

Exercices

 Ecrire une procédure WinnersPS qui prend en paramètre le nom d’un tournoi, et qui affiche la liste des vainqueurs de ce tournoi (Nom et Prénom) ainsi que l’année.

 Exécuter la procédure WinnersPS à partir de SQL*Plus.  Dans un programme PL/SQL, faire appel à la procédure

WinnersPS pour afficher la liste des vainqueurs des tournois qui se sont passés dans un intervalle de dates donné.

 Ecrire une procédure UpdatePalmPS qui permet de mettre à jour les champs nombre de match joué, nombre de match gagné, nombre de match perdu et total des gains de la table « palmares » (TP précèdent). Les valeurs sont passées en paramètres.

PL/SQL

-M. HAJJI-

59

Les Triggers

60

PL/SQL

-M. HAJJI-

Introduction  Un trigger est un programme qui se déclenche

automatiquement suite à un évènement  A la différence d’une procédure stockée, on ne peut pas appeler un trigger explicitement.

 En base de données, l’évènement est une instruction du DML

qui modifie la base.(INSERT, DELETE, UPDATE)

 Ces triggers font partie du schéma de la base. Leur code

compilé est conservé (comme pour les programmes stockés)  Les triggers peuvent servir à vérifier des contraintes que l’on

ne peut pas définir de façon déclarative

 Ils peuvent aussi gérer de la redondance d’information.  Ils peuvent aussi servir à collecter des informations sur les

mises-à-jour de la base

PL/SQL

-M. HAJJI-

61

Syntaxe

CREATE [ OR REPLACE ] TRIGGER <nom_trigger>

<instant> <liste_evts>

ON <nom_table> [ FOR EACH ROW ]

[ WHEN ( <condition> ) ]

<corps>

PL/SQL

-M. HAJJI-

62

Entête du trigger

 On définit  La table  Les instructions qui déclenchent le trigger  Le moment où le trigger va se déclencher par rapport à

l’instruction (avant ou après)

 Si le trigger se déclenche

 une seule fois pour toute l’instruction (i.e. trigger instruction ou

trigger de table)

 ou une fois pour chaque ligne modifiée/insérée/supprimée (i.e. trigger

ligne, avec l’option FOR EACH ROW)

 Et éventuellement une condition supplémentaire de

déclenchement (clause WHEN) pour les triggers ligne.

PL/SQL

-M. HAJJI-

63

Conditions d’un trigger

 Le trigger se déclenche lorsqu’un événement précis survient : BEFORE UPDATE, AFTER DELETE, AFTER INSERT, …

 Ces événements sont importants car ils définissent le

moment d’exécution du trigger.

 Ainsi, lorsqu’un trigger BEFORE INSERT est programmé, il sera exécuté juste avant l’insertion d’un nouvel élément dans la table.

PL/SQL

-M. HAJJI-

64

Conditions d’un trigger

 Si le trigger doit déterminer si l’instruction est autorisée :

utiliser BEFORE

 Si le trigger doit ”fabriquer” la valeur d’une colonne pour pouvoir ensuite la mettre dans la table : utiliser BEFORE.  Par exemple : trigger ligne qui fabrique la valeur de clef

primaire à partir d’une séquence.

 Si on a besoin que l’instruction soit terminée pour

exécuter le corps du trigger : utiliser AFTER

 pour des triggers lignes, il se peut que l’utilisation de

Before ou After n’ait aucune importance.

PL/SQL

-M. HAJJI-

65

Code du trigger  Le trigger Oracle doit être écrit en PL/SQL.  La forme d’un trigger est encore très dépendante du SGBD utilisé.

CREATE OR REPLACE TRIGGER Print_salary_changes BEFORE UPDATE ON Emp_tab FOR EACH ROW WHEN (new.Empno > 0) DECLARE sal_diff number; BEGIN sal_diff := :new.sal - :old.sal; dbms_output.put(' Old : ' || :old.sal || 'New : ' || :new.sal || 'Difference : ' || sal_diff); END ;

 Ce trigger est déclenché lorsque la table Emp_tab est mise à jour.

 Pour chaque modification (lignes mises à jour), le trigger va calculer puis afficher respectivement l’ancien salaire, le nouveau salaire et la différence entre ces deux salaires.

 La condition précisée (WHEN new.Empno > 0) restreint le

déclenchement du trigger (exécuté uniquement si new.Empno > 0)

PL/SQL

-M. HAJJI-

66

Les types de triggers

 Il existe deux types de triggers différents : les triggers de

table (STATEMENT) et les triggers de ligne (ROW).

 Les triggers de table sont exécutés une seule fois lorsque des modifications surviennent sur une table (même si ces modifications concernent plusieurs lignes de la table).  Ils sont utiles si des opérations de groupe doivent être

réalisées (comme le calcul d’une moyenne, d’une somme totale, d’un compteur, …).

 Pour des raisons de performance, il est préférable

d’employer ces triggers plutôt que les triggers lignes.

PL/SQL

-M. HAJJI-

67

Les types de triggers  Exemple

CREATE TRIGGER log AFTER INSERT OR UPDATE ON Emp_tab BEGIN

INSERT INTO log(table, date, username, action) VALUES ('Emp_tab', sysdate, sys_context('USERENV',

'CURRENT_USER'), 'INSERT/UPDATE on Emp_tab') ; END ;

 Ce trigger table enregistre dans une table log la trace de la

modification de la table Emp_tab.

 On mémorise ici le moment de la modification et l’utilisateur

qui l’a provoqué.

 Il n’est donc exécuté qu’une seule fois par modification de la

table Emp_tab.

PL/SQL

-M. HAJJI-

68

Les types de triggers  Les triggers lignes sont exécutés « séparément » pour chaque

ligne modifiée dans la table.

 Ils sont très utiles s’il faut mesurer une évolution pour

certaines valeurs, effectuer des opérations pour chaque ligne en question.

 Lors de la création de triggers lignes, il est possible d’avoir

accès à la valeur ancienne et la valeur nouvelle grâce aux mots clés OLD et NEW.  Il faut préfixer ces qualificatifs de deux points (:) dans tout

ordre SQL ou PL/SQL les utilisant.

 Il ne faut pas utiliser de préfixe deux points (:) quand les qualificatifs

sont utilisés dans la condition de restriction WHEN

 Il n’est pas possible d’avoir accès à ces valeurs dans les triggers

de table.

PL/SQL

-M. HAJJI-

69

Les types de triggers

 Exemple

CREATE OR REPLACE TRIGGER totalSalaire AFTER UPDATE OF salaire ON emp REFERENCING OLD AS ancien, NEW AS nouveau FOR EACH ROW

update cumul set augmentation = augmentation + nouveau.salaire - ancien.salaire where matricule = ancien.matricule

PL/SQL

-M. HAJJI-

70

Les types de triggers

 :OLD et :NEW

CREATE OR REPLACE TRIGGER totalSalaire AFTER UPDATE OF salaire ON emp FOR EACH ROW

UPDATE cumul SET augmentation = augmentation + :NEW.salaire - :OLD.salaire WHERE matricule = :OLD.matricule

 La clause WHEN

CREATE OR REPLACE TRIGGER modifsalaire BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal) BEGIN

raise_application_error(-20001,'Interdit de baisser le salaire ! ('

|| :old.nome || ')'); END;

PL/SQL

-M. HAJJI-

71

Les types de trigger

 Trigger de table :

 Le trigger est exécuté une fois pour l’ensemble de l’instruction

 pas de notion de ligne courante.

 Trigger ligne :

 notion de ligne courante, :old désigne la ligne avant

modification et :new désigne la ligne après modification.

Insert

Delete

Update

PL/SQL

:old

null

:new

Valeur insérée

Valeur supprimée

Null

Valeur avant modification

Valeur après modification

-M. HAJJI-

72

Ordre d’exécution es triggers

 Pour une instruction du DML sur une table de la base, il peut y avoir 4 sortes de triggers possibles selon l’instant (before, after) et le type (instruction ou ligne).  Ces triggers se déclenchent dans l’ordre suivant :

 Trigger(s) instruction BEFORE  Pour chaque ligne concernée

 Trigger(s) ligne BEFORE  Trigger(s) ligne AFTER

 Trigger(s) instruction AFTER

PL/SQL

-M. HAJJI-

73

Activation / Désactivation des triggers

 Lorsqu’un trigger est créé, il est automatiquement activé  Désactiver un trigger :

ALTER TRIGGER trigger_name DISABLE

 Activer un trigger :

ALTER TRIGGER trigger_name ENABLE

 Activer ou désactiver tous les triggers d’une table :

ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

PL/SQL

-M. HAJJI-

74

Remarques

 Recompiler un trigger:

ALTER TRIGGER trigger_name COMPILE  le trigger est recompilé sans perdre sa validité (actif) ou son

invalidité(non actif).

 Afficher les erreurs de compilation : SHOW ERRORS

 Supprimer un trigger :

DROP TRIGGER nomTrigger

 Ordres interdits

 Les ordres COMMIT et ROLLBACK sont interdits dans un

trigger

PL/SQL

-M. HAJJI-

75

Remarques  Lorsque l’on veut appeler une procédure depuis un

trigger on utilise la commande CALL :

CREATE TRIGGER TEST3 BEFORE INSERT ON EMP CALL AFFICHER /

 Nom des triggers et leurs propriétaires SELECT owner, object_name FROM all_objects WHERE object_type = ‘TRIGGER' ORDER BY owner, object_name;

PL/SQL

-M. HAJJI-

76

Exercices

 Ecrire un trigger qui permet de mettre à jour la table

palmares à chaque nouvelle rencontre jouée.

 Ecrire un trigger qui contrôle les primes des joueurs. Si un joueur participe à un tournoi, il doit obligatoirement avoir une prime. Dans le cas contraire, la prime doit être égale à 10000€.

 Ecrire un trigger qui contrôle le minimum de prime pour

les joueurs perdant dans les tournoi.

 Ecrire un trigger qui contrôle le maximum de prime pour

les joueurs vainqueurs de tournois.

PL/SQL

-M. HAJJI-

77

Exercices

 Ecrire un trigger qui se déclenche avant l'insertion d'un tuple dans la table Gain, et qui transforme la valeur de la prime en euro si la date du tournoi est antérieure à 2001.  Taux de conversion : 1 franc = 0,152 €

 Ecrire un trigger qui se déclenche avant l’insertion d’un tuple dans la table gain, et qui vérifie si au moins une rencontre a été enregistrée pour le joueur en question dans le tournoi et l’année considéré.

PL/SQL

-M. HAJJI-

78

Les Packages

79

PL/SQL

-M. HAJJI-

Introduction  Un package est similaire à la notion de classe dans

l’orienté objet.

 Il regroupe des procédures, des fonctions, des variables,

des constantes, des curseurs et des traitements d’exceptions qui ont un lien logique entre eux, sous une seule entité.

 Les procédures et les fonctions peuvent être:

 Publiques: PUBLIC: c’est à dire appelées depuis l’extérieur

du package.

 Privées: PRIVATE: qui sont invisibles à l’extérieur et accessibles uniquement à des procédures du même package.

PL/SQL

-M. HAJJI-

80

Organisation d’un Package  Un paquetage est organisé en deux parties distinctes  Une partie spécification

 Permet de spécifier à la fois les fonctions et procédures publiques ainsi que les déclarations des types, variables, constantes, exceptions et curseurs utilisés dans le paquetage et visibles par le programme appelant.

 Une partie corps

 Contient les blocs et les spécifications de tous les objets

publics listés dans la partie spécification.

 Cette partie peut inclure des objets qui ne sont pas listés dans

la partie spécification, et sont donc privés.

 Cette partie peut également contenir du code qui sera exécuté

à chaque invocation du paquetage par l'utilisateur

PL/SQL

-M. HAJJI-

81

Organisation d’un Package

 La déclaration de la partie spécification d'un paquetage

s'effectue avec l'instruction

CREATE [OR REPLACE] PACKAGE

 Celle de la partie corps avec l'instruction

CREATE [OR REPLACE] PACKAGE BODY

PL/SQL

-M. HAJJI-

82

Exemple: Spécification d’un package

CREATE OR REPLACE PACKAGE Pkg_Finance IS

-- Variables globales et publiques GN_Salaire EMP.sal%Type ;

-- Fonctions publiques FUNCTION F_Test_Augmentation (

PN_Numemp IN EMP.empno%Type ,PN_Pourcent IN NUMBER

) Return NUMBER ;

-- Procédures publiques PROCEDURE Test_Augmentation (

PN$Numemp IN EMP.empno%Type -- numéro de l'employé ,PN$Pourcent IN OUT NUMBER -- pourcentage d'augmentation

) ;

End Pkg_Finance ; /

PL/SQL

-M. HAJJI-

83

Exemple : Corps d’un package

CREATE OR REPLACE PACKAGE BODY Pkg_Finance IS

-- Variables globales privées GR_Emp EMP%Rowtype ;

-- Procédure privées PROCEDURE Affiche_Salaires IS

CURSOR C_EMP IS select * from EMP ; BEGIN

OPEN C_EMP ; Loop

FETCH C_EMP Into GR_Emp ; Exit when C_EMP%NOTFOUND ;

dbms_output.put_line( 'Employé ' || GR_Emp.ename || ' --> ' ||

Lpad( To_char( GR_Emp.sal ), 10 ) ) ;

End loop ; CLOSE C_EMP ; END Affiche_Salaires ;

PL/SQL

-M. HAJJI-

84

Exemple : Corps d’un package

-- Fonctions publiques FUNCTION F_Test_Augmentation (PN_Numemp IN EMP.empno%Type,

PN_Pourcent IN NUMBER 25 )

Return NUMBER IS

LN_Salaire EMP.sal%Type ; BEGIN

Select sal Into LN_Salaire From EMP Where empno = PN_Numemp ; -- augmentation virtuelle de l'employé 31 LN_Salaire := LN_Salaire *

PN_Pourcent ;

-- Affectation de la variable globale publique GN_Salaire := LN_Salaire ; Return( LN_Salaire ) ; -- retour de la valeur

END F_Test_Augmentation;

PL/SQL

-M. HAJJI-

85

Exemple : Corps d’un package

-- Procédures publiques PROCEDURE Test_Augmentation ( PN_Numemp IN EMP.empno%Type,

PN_Pourcent IN OUT NUMBER)

IS

LN_Salaire EMP.sal%Type ; BEGIN

Select sal Into LN_Salaire From EMP Where empno = PN_Numemp ;

-- augmentation virtuelle de l'employé PN_Pourcent := LN_Salaire * PN_Pourcent ;

-- appel procédure privée Affiche_Salaires ; END Test_Augmentation;

END Pkg_Finance; /

PL/SQL

-M. HAJJI-

86

Organisation d’un Package  La spécification du paquetage est créée avec  une variable globale et publique : GN_Salaire  une procédure publique : PROCEDURE Test_Augmentation  une fonction publique : FUNCTION F_Test_Augmentation qui sont visibles depuis l'extérieur (le programme appelant)

 Le corps du paquetage est créé avec une procédure privée :

 PROCEDURE Affiche_Salaires qui n'est visible que dans le corps du

paquetage

 Le corps définit également une variable globale au corps du

paquetage :  GR_Emp utilisée par la procédure privée

PL/SQL

-M. HAJJI-

87

Accès/Suppression

 L'accès à un objet d'un paquetage est réalisé avec la

syntaxe suivante : nom_paquetage.nom_objet[(liste paramètres)]  L'exécution d'une procédure d'un package s'effectue par

la commande

EXECUTE nom_package.nom_procédure(liste_paramètres effectifs) ;

 Suppression d’un Package

 DROP PACKAGE package_name;  DROP PACKAGE BODY package_name;

PL/SQL

-M. HAJJI-

88

Accès

 Appel de la fonction F_Test_Augmentation du paquetage

Declare

Begin

End ; /

LN_Salaire emp.sal%Type ;

Select sal Into LN_Salaire From EMP Where empno = 7369 ; dbms_output.put_line( 'Salaire de 7369 avant augmentation ‘

|| To_char( LN_Salaire ) ) ;

dbms_output.put_line( 'Salaire de 7369 après augmentation ‘

|| To_char( Pkg_Finance.F_Test_Augmentation( 7369, 1.1 ) ) ) ;

PL/SQL

-M. HAJJI-

89

Accès

 Appel de la procédure Test_Augmentation du paquetage

Declare

Begin

LN_Pourcent NUMBER := 1.1 ;

Pkg_Finance.Test_Augmentation( 7369, LN_Pourcent ) ; dbms_output.put_line( 'Employé 7369 après augmentation : ' || To_char(

LN_Pourcent ) ) ; End ; /

PL/SQL

-M. HAJJI-

90

Accès

 Interrogation de la variable globale publique : GN_Salaire

Begin

End ; /

dbms_output.put_line( 'Valeur salaire du package : '

|| To_char( Pkg_Finance.GN_Salaire ) ) ;

PL/SQL

-M. HAJJI-

91

Merci…

PL/SQL

-M. HAJJI-

92