BEGIN

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
•
répertorier les fonctions des différents modes des
paramètres
créer une procédure
faire la distinction entre les paramètres formels et
les paramètres réels
• 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
2-3
•
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
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;
2-4
•
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
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Développer des procédures
Editeur
Code de création
de procédure
1 file.sql
iSQL*Plus
2 Chargement et exécution du fichier file.sql
Oracle
Code source
Compilation
Pseudo-code
Exécution
2-5
Procédure
créée
Utiliser SHOW
ERRORS pour
visualiser les
erreurs de
compilation
3
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
appelant
Paramètre OUT
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
Mode par défaut
Doit être indiqué
IN OUT
Doit être indiqué
La valeur est transmise au
sous-programme
Est renvoyé à
l'environnement
appelant
Est transmis à un sousprogramme ; est renvoyé
à l'environnement
appelant
Le paramètre formel se
comporte en constante
Variable non
initialisée
Variable initialisée
Le paramètre réel peut être
un littéral, une expression,
une constante ou une
variable initialisée
Doit être une
variable
Doit être une
variable
Peut se voir affecter une
valeur par défaut
Ne peut pas se
voir affecter de
valeur par défaut
Ne peut pas se voir
affecter de valeur par
défaut
2-9
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Exemples de paramètres IN
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;
/
2-10
Copyright © Oracle Corporation, 2001. Tous droits réservés.
p_id
Exemples de paramètres OUT
Environnement appelant Procédure QUERY_EMP
p_id
171
SMITH
2-11
p_name
7400
p_salary
0.15
p_comm
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
VARIABLE g_sal
VARIABLE g_comm
VARCHAR2(25)
NUMBER
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
'8006330575'
Procédure FORMAT_PHONE
'(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
2-17
•
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
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);
COMMIT;
...
END;
2-21
--invoke procedure
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 ...
IS
...
BEGIN
...
EXCEPTION
...
END PROC2;
PROCEDURE
PROC1 ...
IS
...
BEGIN
...
PROC2(arg1);
...
EXCEPTION
La procédure
...
appelante reprend
END PROC1;
Exception déclenchée
Exception traitée
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
PROC2 ...
IS
...
BEGIN
...
EXCEPTION
...
END PROC2;
PROCEDURE
PROC1 ...
IS
...
Exception déclenchée
BEGIN
...
Exception non traitée
PROC2(arg1);
...
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 :
2-32
•
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
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Synthèse
2-33
•
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
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Présentation de l'exercice 2
Dans cet exercice, vous allez :
•
•
•
2-34
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
Copyright © Oracle Corporation, 2001. Tous droits réservés.