Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

PL/SQL Présentation Structure d’un programme PL/SQL DECLARE -- définition des v

PL/SQL Présentation Structure d’un programme PL/SQL DECLARE -- définition des variables section Facultative BEGIN -- code du programme EXCEPTION section Facultative -- code de gestion des erreurs END; Déclaration, initalisation des variables Déclaration et initialisation Nom_variable type_variable := valeur; Initialisation Nom_variable := valeur; Le type de variables VARCHAR2 Longueur maximale : 32767 octets Syntaxe: Nom_variable VARCHAR2(30); Exemple: name VARCHAR2(30); name VARCHAR2(30) := ‘toto’; NUMBER Nom_variable NUMBER(long,dec); avec Long : longueur maximale Dec : longueur de la partie décimale Exemple: num_tel number(10); toto number(5,2)=142.12; Le type de variables (2) DATE Nom_variable DATE; Par défaut DD-MON-YY (18-DEC-02) Fonction TO_DATE Exemple : start_date := to_date(’29-SEP-2003’,’DD-MON- YYYY’); start_date := to_date(’29-SEP-2003:13:01’,’DD- MON-YYYY:HH24:MI’); BOOLEAN  TRUE, FALSE ou NULL PL/SQL Les principales commandes Test conditionnel IF-THEN IF l_date > ’11-APR-03’ THEN l_salaire := l_salaire * 1.15; END IF; IF-THEN-ELSE IF l_date > ’11-APR-03’ THEN l_salaire := l_salaire * 1.15; ELSE l_salaire := l_salaire * 1.05; END IF; Test conditionnel IF-THEN-ELSIF IF l_nom = ’TATA’ THEN l_salaire := l_salaire * 1.15; ELSIF l_nom = ‘TOTO’ THEN l_salaire := l_salaire * 1.05; END IF; CASE CASE sélecteur WHEN expression1 THEN résultat1 WHEN expression2 THEN résultat2 ELSE résultat3 END; Test conditionnel Exemple : val := CASE city WHEN ‘Fes’ THEN …. WHEN ‘Rabat’ THEN …. ELSE …… END; Les boucles LOOP instructions exécutables; END LOOP; Obligation d’utiliser la commande EXIT WHILE condition LOOP instructions exécutables; END LOOP; Les boucles FOR variable IN val_deb..val_fin LOOP instructions; END LOOP; Affichage Activer le retour écran set serveroutput on size 10000 Affichage dbms_output.put_line(chaîne); Utilise || pour faire une concaténation Exemple n°1 DECLARE i number(2); BEGIN FOR i IN 1..5 LOOP dbms_output.put_line(‘Nombre : ’ || i ); END LOOP; END; / Show errors……..erreurs éventuelles Exemple n°2 DECLARE compteur number(3); i number(3); BEGIN select count(*) into compteur from emp; FOR i IN 1..compteur LOOP dbms_output.put_line('Nombre : ' || i ); END LOOP; END; / Les curseurs Le curseur est une zone de travail de l’environnement utilisateur qui contient des informations permettant l’exécution d’un ordre SQL DECLARE compteur number(3); i number(3); cursor get_nb_clients IS select count(*) from clients; BEGIN OPEN get_nb_clients; FETCH get_nb_clients INTO compteur; FOR i IN 1..compteur LOOP dbms_output.put_line('Nombre : ' || i ); END LOOP; CLOSE get_nb_clients; END; Les curseurs (2) DECLARE nom varchar2(30); CURSOR get_nom_clients IS SELECT nom,adresse FROM clients; BEGIN FOR toto IN get_nom_clients LOOP dbms_output.put_line('Employé : ' || UPPER(toto.nom) ||' Ville : '|| toto.adresse); END LOOP; END; La clause ACCEPT Exemple: ACCEPT dept PROMPT ‘ Fournir le nom du départment: ‘; SELECT * FROM dept WHERE dname = UPPER('&dept') / Exécution : Fournir le nom du départment: Sales Sales DEPTNO DNAME LOC --------- -------------- ------------- 30 SALES CHICAGO ACCEPT permet d'afficher un message d'invite personnalisé Soit la table Pilote(NumP, NomP, Brevet, nbHVol). Ecrire un programme PLSQL qui permet d’afficher le nombre H de vols d’un pilote donné ACCEPT S_brevet PROMPT 'Entrer code Brevet : ' ACCEPT s_duréeVol PROMPT 'Entrer durée du vol : ' DECLARE v_nom Pilote.nom%TYPE; v_nbHVol Pilote.nbHVol%TYPE; BEGIN SELECT nom, nbHVol INTO v_nom, v_nbHVol FROM Pilote WHERE brevet = '&s_brevet’; v_nbHVol := v_nbHVol + &s_duréeVol; DBMS_OUTPUT.PUT_LINE ('Total heures vol : ' || v_nbHVol ||' du pilote ' || v_nom); END; / Entrer code Brevet : PL3 Entrer durée du vol : 27 Total heures vol : 927 du pilote Ali La clause ACCEPT (suite) Procédures et fonctions Les procédures create or replace procedure list_nom_clients IS BEGIN DECLARE nom varchar2(30); CURSOR get_nom_clients IS select nom,adresse from clients; BEGIN FOR C1 IN get_nom_clients LOOP dbms_output.put_line('Employé : ' || UPPER(C1.nom) ||' Ville : '|| C1.adresse); END LOOP; END; END; Les procédures create or replace procedure list_nom_clients (ville IN varchar2, result OUT number) IS BEGIN DECLARE CURSOR get_nb_clients IS select count(*) from clients where adresse=ville; BEGIN open get_nb_clients; fetch get_nb_clients INTO result; end; end; Récupération des résultats Déclarer une variable SQL> variable nb number; Exécuter la fonction SQL> execute list_nom_clients(‘fes',:nb) Visualisation du résultat SQL> print Description des paramètres SQL> desc nom_procedure Les fonctions create or replace function nombre_clients return number IS BEGIN DECLARE i number; CURSOR get_nb_clients IS select count(*) from clients; BEGIN open get_nb_clients; fetch get_nb_clients INTO i; return i; end; end;  Exécution: select nombre_clients() from dual Procédures et fonctions Suppression de procédures ou fonctions DROP PROCEDURE nom_procedure DROP FUNCTION nom_fonction Table système contenant les procédures et fonctions : user_source Exercices Réalisez une procédure list_tables qui donne le nom de toutes vos tables Réalisez une procédure UPDATENOM qui remplit correctement la colonne NOM_PIECE de clients par rapport à la table fournisseurs Réalisez une procédure UPDATEPRIX qui met à jour tous les prix de la table clients Procédure LIST_TABLES create or replace procedure list_tables IS BEGIN DECLARE CURSOR get_nom IS select table_name from user_tables; BEGIN FOR nom IN get_nom LOOP dbms_output.put_line('Nom de la table : ' || toto.table_name); END LOOP; END; END; Procédure UPDATENOM CREATE OR REPLACE PROCEDURE updatenom IS BEGIN DECLARE nompiece varchar2(30); cursor toto IS SELECT distinct fournisseurs.reference, fournisseurs.nom_piece FROM fournisseurs; BEGIN FOR nompiece IN toto LOOP UPDATE clients SET clients.nom_piece=nompiece.nom_piece WHERE clients.reference=nompiece.reference; END LOOP; END; END; CREATE OR REPLACE PROCEDURE updateprix IS BEGIN DECLARE prixunit number(5); CURSOR toto IS SELECT clients.nom, clients.adresse, fournisseurs.prix_piece_unite FROM fournisseurs,clients WHERE fournisseurs.reference=clients.reference; BEGIN FOR prixunit IN toto LOOP UPDATE clients SET clients.prix=clients.quantite*prixunit.prix_piece_unite WHERE prixunit.nom=clients.nom and prixunit.adresse=clients.adresse; END LOOP; END; END; Les déclencheurs (trigger) Automatiser des actions lors de certains événements du type : AFTER ou BEFORE et INSERT, DELETE ou UPDATE Syntaxe : CREATE OR REPLACE TRIGGER nom_trigger Evénement [OF liste colonne] ON nom_table WHEN (condition) [FOR EACH ROW] Instructions PL/SQL ou SQL Accès aux valeurs modifiées Utilisation de new et old Si nous ajoutons un client dont le nom est toto alors nous récupérons ce nom grâce à la variable new.nom Dans le cas de suppression ou modification, les anciennes valeurs sont dans la variable old.nom Exemple Archiver le nom de l’utilisateur, la date et l’action effectuée (toutes les informations) dans une table LOG_CLIENTS lors de l’ajout d’un clients dans la table CLIENTS Créer la table LOG_CLIENTS avec la même structure que CLIENTS Ajouter 3 colonnes USERNAME, DATEMODIF, TYPEMODIF Exemple create or replace trigger logadd after insert on clients for each row begin insert into log_clients values (:new.nom,:new.adresse,:new.reference,:new.nom_ piece, :new.quantite,:new.prix,:new.echeance, USER,SYSDATE,'INSERT'); end; Exercices Ecrire un déclencheur de mise à jour automatique du prix lors de l’ajout d’un clients dans la table CLIENTS Ecrire un déclencheur de mise à jour automatique du nom de la pièce lors de l’ajout d’un clients dans la table CLIENTS Ecrire la mise à jour de la date d’échéance à 2 mois suivant la date courante (utilisation de la fonction add_months(date,nombre) ) Les exceptions et erreurs NO_DATA_FOUND Quand Select into ne retourne aucune ligne TOO_MANY_ROWS Quand Select into retourne plusieurs lignes OTHERS Toutes erreurs non interceptées RAISE_APPLICATION_ERROR Pour retourner une message d’erreur Numéro d’erreur entre -20000 et -20999 uploads/S4/ aaa-cours-plsql-v2.pdf