Les programmes stockés (procédures, fonctions ou packages) sont des programmes codés en PL/SQL, nommés, et stockés dans un schéma de la base.
Nécessite le privilège ‘Create procedure’ ou ‘create any procedure’ suivant qu’on est dans son propre schéma ou dans un autre schéma.
Il existe des outils comme ‘TOAD’ (de chez Quest Software) ou ‘SQL*developper’ (Oracle) qui simplifient la vie du développeur et du DBA pour la gestion de ces objets.
Ces programmes stockés doivent être valides. Dans certains cas notamment quant les objets référencés (tables, vues,etc) dans les programmes ont été modifiés, les procédures peuvent devenir invalides.
Après résolution du problème, les programmes doivent être recompilés :
SQL> ALTER PROCEDURE | PACKAGE | FUNCTION COMPILE
Procedures
Exemple de procédure de crédit d’un compte (qui prend en entrée le no du compte et le montant à créditer)
SQL> create or replace procedure bidon
is
begin
null;
end;
SQL> CREATE PROCEDURE credit (no IN NUMBER, montant IN NUMBER)
AS
BEGIN
UPDATE compte SET solde = solde + montant
WHERE no_compte= no;
END;
Pour executer une procédure :
SQL> execute nom_procedure (ses_parametres);
ou
SQL> BEGIN
nom_proc (ses_parametres)
END;
/
Attention ! pour voir le résultat d’une procédure à l’écran, il fuat que la sortie écran soit valide, avant de lancer son exécution (sinon on ne voit rien!!!) :
SQL> SET SERVEROUTPUT ON
Fonctions
Une fonction est une procédure qui retourne une (ou des) valeur(s).
SQL> CREATE FUNCTION lit_solde (no IN NUMBER)
RETURN NUMBER
IS solde NUMBER (11,2);
BEGIN
SELECT balance INTO solde FROM compte
WHERE no_compte = no;
RETURN (solde);
END;
no est un paramètre d’entrée et solde la valeur du solde du compte fournie en sortie
Packages
Un package est un ensemble encapsulé de procédures et ou de fonctions, ainsi que de données dépendant fonctionnellement.
Il est constitué de 2 parties : la specification qui déclare les objets publics du package et le corps (body) qui définit ces objets.
Pour créer ces 2 entités on utilisera respectivement les ordres :
create package et create package body
Triggers
Un trigger est un morceau de code PL/SQL, stocké dans la base, déclenché lors de l’occurrence d’un événement particulier. Il permet notamment de synchroniser des opérations entre plusieurs tables.
La plupart du temps les triggers sont déclenchés par la modification du contenu d’une table.
La liste des événements déclencheurs apparaît ci-après :
Type d’ordre ordre déclencheur : DELETE, INSERT, UPDATE, CREATE, ALTER, DROP, SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN
exemple
– trigger déclenché lors d’une insertion
– ou d’une modification de la table client
SQL> CREATE OR REPLACE TRIGGER aff_discount BEFORE INSERT OR UPDATE ON clients FOR EACH ROW WHEN (new.no_cli > 0)
DECLARE evol_discount number;
BEGIN evol_discount := :new.discount - :old.discount;
DBMS_OUTPUT.PUT_LINE(’ evolution : ‘ || evol_discount); END;
/ — FOR EACH ROW signale qu’une modification de 4 lignes
– par un seul UPDATE déclenche 4 fois le trigger.
– Si on ne souhaite qu’un seul déclenchement ,
– on omet simplement la clause FOR EACH ROW.
