Créer des procédures

1. Créer des procédures

2. Objectifs A la fin de ce chapitre, vous pourrez : • décrire une procédure • créer une procédure • faire la distinction entre les paramètres formels et les paramètres réels • répertorier les fonctions des différents modes des paramètres • créer des procédures avec des paramètres • appeler une procédure • traiter des exceptions dans les procédures • supprimer une procédure

3. Définition d'une procédure • Une procédure est un type de sous-programme qui exécute une action • Une procédure peut être stockée en tant qu'objet de schéma dans la base de données en vue d'exécutions répétées

4. Syntaxe pour la création de procédures CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS|AS PL/SQL Block; • L'option REPLACE indique que, si la procédure existe, elle sera supprimée et remplacée par la nouvelle version créée avec l'instruction • Le bloc PL/SQL commence par BEGIN ou par la déclaration de variables locales et se termine par END ou par ENDprocedure_name

5. 1 Oracle Exécution 3 Développer des procédures Editeur file.sql Code de création de procédure iSQL*Plus Chargement et exécution du fichierfile.sql 2 Code source Utiliser SHOW ERRORS pour visualiser les erreurs de compilation Compila-tion Pseudo-code Procédure créée

6. Paramètres formels/réels • Les paramètres formels sont des variables déclarées dans la liste de paramètres d'une spécification de sous-programme Exemple: CREATE PROCEDURE raise_sal(p_id NUMBER, p_amount NUMBER) ... END raise_sal; • Les paramètres réels sont des variables ou des expressions référencées dans la liste de paramètres d'un appel de sous-programme Exemple: raise_sal(v_id, 2000)

7. Modes des paramètres des procédures Procédure Paramètre IN Paramètre OUT Paramètre IN OUT Environnement appelant (DECLARE) BEGIN EXCEPTION END;

8. Créer des procédures avec des paramètres IN OUT IN OUT Mode par défaut Doit être indiqué Doit être indiqué La valeur est transmise au sous-programme Est renvoyé à l'environnement appelant Est transmis à un sous-programme ; est renvoyé à l'environnement appelant Le paramètre formel se comporte en constante Variable non initialisée Variable initialisée Doit être une variable Doit être une variable Le paramètre réel peut être un littéral, une expression, une constante ou une variable initialisée Ne peut pas se voir affecter de valeur par défaut Peut se voir affecter une valeur par défaut Ne peut pas se voir affecter de valeur par défaut

9. Exemples de paramètres IN p_id 176 CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%TYPE) IS BEGIN UPDATE employees SET salary = salary * 1.10 WHERE employee_id = p_id; END raise_salary; /

10. Exemples de paramètres OUT Environnement appelant ProcédureQUERY_EMP p_id 171 p_name SMITH p_salary 7400 p_comm 0.15

11. Exemples de paramètres OUT emp_query.sql CREATE OR REPLACE PROCEDURE query_emp (p_id IN employees.employee_id%TYPE, p_name OUT employees.last_name%TYPE, p_salary OUT employees.salary%TYPE, p_comm OUT employees.commission_pct%TYPE) IS BEGIN SELECT last_name, salary, commission_pct INTO p_name, p_salary, p_comm FROM employees WHERE employee_id = p_id; END query_emp; /

12. Visualiser des paramètres OUT • Charger et exécuter le fichier script emp_query.sql pour créer la procédure QUERY_EMP • Déclarer les variables hôte, exécuter la procédure QUERY_EMP, puis imprimer la valeur de la variable globale G_NAME VARIABLE g_name VARCHAR2(25) VARIABLE g_sal NUMBER VARIABLE g_comm NUMBER EXECUTE query_emp(171, :g_name, :g_sal, :g_comm) PRINT g_name

13. Paramètres INOUT Environnement appelant ProcédureFORMAT_PHONE p_phone_no '8006330575' '(800)633-0575' CREATE OR REPLACE PROCEDURE format_phone (p_phone_no IN OUT VARCHAR2) IS BEGIN p_phone_no := '(' || SUBSTR(p_phone_no,1,3) || ')' || SUBSTR(p_phone_no,4,3) || '-' || SUBSTR(p_phone_no,7); END format_phone; /

14. Visualiser des paramètres INOUT VARIABLE g_phone_no VARCHAR2(15) BEGIN :g_phone_no := '8006330575'; END; / PRINT g_phone_no EXECUTE format_phone (:g_phone_no) PRINT g_phone_no

15. Méthodes de transmission des paramètres • Méthode positionnelle : répertorie les paramètres réels dans le même ordre que les paramètres formels • Méthode de transmission de paramètres par association de noms : répertorie les paramètres réels dans un ordre arbitraire en associant chacun d'eux au paramètre formel correspondant • Méthode par combinaison : répertorie certains paramètres réels en tant que paramètres positionnels et d'autres en tant que paramètres nommés

16. Option DEFAULT des paramètres CREATE OR REPLACE PROCEDURE add_dept (p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 1700) IS BEGIN INSERT INTO departments(department_id, department_name, location_id) VALUES (departments_seq.NEXTVAL, p_name, p_loc); END add_dept; /

17. Exemples de transmission de paramètres BEGIN add_dept; add_dept ('TRAINING', 2500); add_dept ( p_loc => 2400, p_name =>'EDUCATION'); add_dept ( p_loc => 1200) ; END; / SELECT department_id, department_name, location_id FROM departments; …

18. Déclarer des sous-programmes leave_emp2.sql CREATE OR REPLACE PROCEDURE leave_emp2 (p_id IN employees.employee_id%TYPE) IS PROCEDURE log_exec IS BEGIN INSERT INTO log_table (user_id, log_date) VALUES (USER, SYSDATE); END log_exec; BEGIN DELETE FROM employees WHERE employee_id = p_id; log_exec; END leave_emp2; /

19. Appeler une procédure depuis un bloc PL/SQL anonyme DECLARE v_id NUMBER := 163; BEGIN raise_salary(v_id); --invoke procedure COMMIT; ... END;

20. Appeler une procédure depuis une autre procédure process_emps.sql CREATE OR REPLACE PROCEDURE process_emps IS CURSOR emp_cursor IS SELECT employee_id FROM employees; BEGIN FOR emp_rec IN emp_cursor LOOP raise_salary(emp_rec.employee_id); END LOOP; COMMIT; END process_emps; /

21. Procédure appelée PROCEDURE PROC2 ... IS ... BEGIN ... EXCEPTION ... END PROC2; Exception traitée La procédure appelante reprend le contrôle Exceptions traitées Procédure appelante PROCEDURE PROC1 ... IS ... BEGIN ... PROC2(arg1); ... EXCEPTION ... END PROC1; Exception déclenchée

22. Exceptions traitées CREATE PROCEDURE p2_ins_dept(p_locid NUMBER) IS v_did NUMBER(4); BEGIN DBMS_OUTPUT.PUT_LINE('Procedure p2_ins_dept started'); INSERT INTO departments VALUES (5, 'Dept 5', 145, p_locid); SELECT department_id INTO v_did FROM employees WHERE employee_id = 999; END; CREATE PROCEDURE p1_ins_loc(p_lid NUMBER, p_city VARCHAR2) IS v_city VARCHAR2(30); v_dname VARCHAR2(30); BEGIN DBMS_OUTPUT.PUT_LINE('Main Procedure p1_ins_loc'); INSERT INTO locations (location_id, city) VALUES (p_lid, p_city); SELECT city INTO v_city FROM locations WHERE location_id = p_lid; DBMS_OUTPUT.PUT_LINE('Inserted city '||v_city); DBMS_OUTPUT.PUT_LINE('Invoking the procedure p2_ins_dept ...'); p2_ins_dept(p_lid); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such dept/loc for any employee'); END;

23. Procédure appelée PROCEDURE PROC2 ... IS ... BEGIN ... EXCEPTION ... END PROC2; Exception non traitée Exceptions non traitées Procédure appelante PROCEDURE PROC1 ... IS ... BEGIN ... PROC2(arg1); ... EXCEPTION ... END PROC1; Exception déclenchée La section de traitement des exceptions de la procédure appelante a repris le contrôle

24. Exceptions non traitées CREATE PROCEDURE p2_noexcep(p_locid NUMBER) IS v_did NUMBER(4); BEGIN DBMS_OUTPUT.PUT_LINE('Procedure p2_noexcep started'); INSERT INTO departments VALUES (6, 'Dept 6', 145, p_locid); SELECT department_id INTO v_did FROM employees WHERE employee_id = 999; END; CREATE PROCEDURE p1_noexcep(p_lid NUMBER, p_city VARCHAR2) IS v_city VARCHAR2(30); v_dname VARCHAR2(30); BEGIN DBMS_OUTPUT.PUT_LINE(' Main Procedure p1_noexcep'); INSERT INTO locations (location_id, city) VALUES (p_lid, p_city); SELECT city INTO v_city FROM locations WHERE location_id = p_lid; DBMS_OUTPUT.PUT_LINE('Inserted new city '||v_city); DBMS_OUTPUT.PUT_LINE('Invoking the procedure p2_noexcep ...'); p2_noexcep(p_lid); END;

25. Supprimer des procédures Supprimer une procédure stockée dans la base de données. • Syntaxe: • Exemple: DROP PROCEDURE procedure_name DROP PROCEDURE raise_salary;

26. Avantages liés aux sous-programmes • Facilité de maintenance • Sécurité et intégrité accrues des données • Performances améliorées • Clarté améliorée du code

27. Synthèse Ce chapitre vous a permis d'apprendre : • qu'une procédure est un sous-programme qui exécute une action • que vous pouvez créer des procédures en utilisant la commande CREATEPROCEDURE • que vous pouvez compiler et enregistrer une procédure dans la base de données • que des paramètres sont utilisés pour transmettre les données de l'environnement appelant vers la procédure • qu'il existe trois modes de paramètre : IN, OUT et INOUT

28. Synthèse • Les sous-programmes locaux sont des programmes définis dans la section déclarative d'un autre programme • Les procédures peuvent être appelées à partir de n'importe quel outil ou langage prenant en charge le langage PL/SQL • Vous devez être conscient de l'impact des exceptions traitées et non traitées sur les transactions et les procédures appelantes • Vous pouvez supprimer des procédures de la base de données en utilisant la commande DROPPROCEDURE • Les procédures peuvent servir de blocs de construction pour une application

29. Présentation de l'exercice 2 Dans cet exercice, vous allez : • créer des procédures stockées pour : • insérer des lignes dans une table, en utilisant les valeurs de paramètres fournies • mettre à jour les données d'une table pour les lignes correspondant aux valeurs de paramètres fournies • supprimer d'une table les lignes correspondant aux valeurs de paramètres fournies • interroger une table et extraire les données en fonction des valeurs de paramètres fournies • traiter les exceptions dans les procédures • compiler et appeler les procédures