Get documents about "Les02
Shared by: rachidkita
-
Stats
- views:
- 2
- posted:
- 8/17/2012
- language:
- French
- pages:
- 29
Document Sample
Créer des procédures
Copyright © Oracle Corporation, 2001. Tous droits réservés.
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
2-2 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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
2-3 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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 END procedure_name
2-4 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Développer des procédures
Editeur
Code de création 1 file.sql
de procédure
iSQL*Plus
2 Chargement et exécution du fichier file.sql
Oracle Code source Utiliser SHOW
Compila-
ERRORS pour
tion visualiser les
erreurs de
Pseudo-code Procédure
créée
compilation
Exécution 3
2-5 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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)
2-7 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Modes des paramètres des procédures
Procédure
Paramètre IN
Environnement
Paramètre OUT
appelant
Paramètre IN OUT
(DECLARE)
BEGIN
EXCEPTION
END;
2-8 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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 Est renvoyé à Est transmis à un sous-
sous-programme l'environnement programme ; est renvoyé
appelant à l'environnement
appelant
Le paramètre formel se Variable non Variable initialisée
comporte en constante initialisée
Le paramètre réel peut être Doit être une Doit être une
un littéral, une expression, variable variable
une constante ou une
variable initialisée
Peut se voir affecter une Ne peut pas se Ne peut pas se voir
valeur par défaut voir affecter de affecter de valeur par
valeur par défaut défaut
2-9 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Exemples de paramètres IN
176 p_id
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;
/
2-10 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Exemples de paramètres OUT
Environnement appelant Procédure QUERY_EMP
171 p_id
SMITH p_name
7400 p_salary
0.15 p_comm
2-11 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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;
/
2-12 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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
2-13 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Paramètres IN OUT
Environnement appelant Procédure FORMAT_PHONE
'8006330575' '(800)633-0575' p_phone_no
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;
/
2-15 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Visualiser des paramètres IN OUT
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
2-16 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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
2-17 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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;
/
2-18 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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;
…
2-19 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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;
/
2-20 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Appeler une procédure depuis un bloc
PL/SQL anonyme
DECLARE
v_id NUMBER := 163;
BEGIN
raise_salary(v_id); --invoke procedure
COMMIT;
...
END;
2-21 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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;
/
2-22 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Exceptions traitées
Procédure appelée
Procédure appelante PROCEDURE
PROC2 ...
PROCEDURE IS
PROC1 ... ...
IS BEGIN
... ... Exception déclenchée
BEGIN EXCEPTION
... ... Exception traitée
PROC2(arg1); END PROC2;
...
EXCEPTION La procédure
...
END PROC1; appelante reprend
le contrôle
2-23 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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;
2-24 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Exceptions non traitées
Procédure appelée
Procédure appelante
PROCEDURE
PROCEDURE PROC2 ...
PROC1 ... IS
IS ...
... BEGIN
BEGIN ... Exception déclenchée
... EXCEPTION
... Exception non traitée
PROC2(arg1); END PROC2;
...
EXCEPTION
...
END PROC1; La section de traitement
des exceptions de la
procédure appelante a
repris le contrôle
2-26 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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;
2-27 Copyright © Oracle Corporation, 2001. Tous droits réservés.
Supprimer des procédures
Supprimer une procédure stockée dans la
base de données.
Syntaxe:
DROP PROCEDURE procedure_name
Exemple:
DROP PROCEDURE raise_salary;
2-29 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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
2-30 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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 CREATE PROCEDURE
• 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 IN
OUT
2-32 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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 DROP PROCEDURE
• Les procédures peuvent servir de blocs de
construction pour une application
2-33 Copyright © Oracle Corporation, 2001. Tous droits réservés.
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
2-34 Copyright © Oracle Corporation, 2001. Tous droits réservés.
