c. bonnet / r. chapuis cours pl/sql daprès cours oracle - oai procédures stockées
TRANSCRIPT
C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI
Procédures StockéesProcédures Stockées
Cours PL/SQL d’après cours ORACLE - OAI / 2 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
GénéralitésGénéralités
• Une procédure est un bloc PL/SQL nommé qui exécute une action.
• Une procédure est stockée dans la base données
• Une procédure est un objet réutilisable.
• Une procédure est un bloc PL/SQL nommé qui exécute une action.
• Une procédure est stockée dans la base données
• Une procédure est un objet réutilisable.
Cours PL/SQL d’après cours ORACLE - OAI / 3 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Structure d’une procédure PL/SQLStructure d’une procédure PL/SQL
Entête Entête
ISIS
Section déclarativeSection déclarative
BEGINBEGIN
Section exécutionSection exécution
[[EXCEPTIONEXCEPTION
Section ExceptionSection Exception]]
END;END;
Entête Entête
ISIS
Section déclarativeSection déclarative
BEGINBEGIN
Section exécutionSection exécution
[[EXCEPTIONEXCEPTION
Section ExceptionSection Exception]]
END;END;
Cours PL/SQL d’après cours ORACLE - OAI / 4 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Syntaxe pour créer une procédure
Syntaxe pour créer une procédure
CREATE [OR REPLACE] PROCEDURE nom_procédure (paramètre1 [mode1] type1, paramètre2 [mode2] type2, . . .IS [AS]PL/SQL Block; -- corps de la procédure
CREATE [OR REPLACE] PROCEDURE nom_procédure (paramètre1 [mode1] type1, paramètre2 [mode2] type2, . . .IS [AS]PL/SQL Block; -- corps de la procédure
Cours PL/SQL d’après cours ORACLE - OAI / 5 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Développement d’une procédureDéveloppement d’une procédure
FichierFichiersourcesource
Code Code SourceSource
p-codep-code
EditEdit
Stocké dans la base
Compilation
Exécution
1
2
EditeurEditeur
OracleOracleProcedure Procedure
BuilderBuilder
Cours PL/SQL d’après cours ORACLE - OAI / 6 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Développement d ’une procédure avec SQL*Plus
Développement d ’une procédure avec SQL*Plus
1.1. SaSa isir le texte de l’ordre CREATE isir le texte de l’ordre CREATE PROCEDURE avec un éditeur et le sauver PROCEDURE avec un éditeur et le sauver sous forme de fichier source (extension sous forme de fichier source (extension .sql.sql ). ).
1.1. SaSa isir le texte de l’ordre CREATE isir le texte de l’ordre CREATE PROCEDURE avec un éditeur et le sauver PROCEDURE avec un éditeur et le sauver sous forme de fichier source (extension sous forme de fichier source (extension .sql.sql ). ).
2.2. Sous SQL*Plus, exécuter le script par Sous SQL*Plus, exécuter le script par START.START.
2.2. Sous SQL*Plus, exécuter le script par Sous SQL*Plus, exécuter le script par START.START.
3.3. Appeler la procédure pour l’exécuter.Appeler la procédure pour l’exécuter.3.3. Appeler la procédure pour l’exécuter.Appeler la procédure pour l’exécuter.
Cours PL/SQL d’après cours ORACLE - OAI / 7 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Paramètre de procédureParamètre de procédure
Environnement Environnement d’appeld’appel
ProcédureProcédure
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
IN paramètreIN paramètre
OUT paramètre OUT paramètre
IN OUT paramètreIN OUT paramètre
Cours PL/SQL d’après cours ORACLE - OAI / 8 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
IN OUT
Doit être spécifié
Valeur transmise à la procédure et renvoyée à l’environnement
variable initialisée
variable
OUT
Doit être spécifié
Valeur transmise à l’environnement
variable non initialisée
variable
Mode d’un paramètreMode d’un paramètreIN
Par défaut
Valeur transmise à la procédure
paramètre formel variable ou constante
paramètre effectifvariable, constante ou expression
Cours PL/SQL d’après cours ORACLE - OAI / 9 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Mode IN : ExempleMode IN : Exemple
SQL> CREATE OR REPLACE PROCEDURE raise_salary 2 (v_id in emp.empno%TYPE) 3 IS 4 BEGIN 5 UPDATE emp 6 SET sal = sal * 1.10 7 WHERE empno = v_id; 8 END raise_salary; 9 /Procedure created.Procedure created.
SQL> EXECUTE raise_salary (7369)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE raise_salary 2 (v_id in emp.empno%TYPE) 3 IS 4 BEGIN 5 UPDATE emp 6 SET sal = sal * 1.10 7 WHERE empno = v_id; 8 END raise_salary; 9 /Procedure created.Procedure created.
SQL> EXECUTE raise_salary (7369)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
v_idv_id73697369
Cours PL/SQL d’après cours ORACLE - OAI / 10 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Mode OUT : ExempleMode OUT : Exemple
EnvironnementEnvironnement Procédure QUERY_EMPProcédure QUERY_EMP
76547654 v_idv_id
v_namev_name
v_salaryv_salary
v_ commv_ comm
MARTINMARTIN
12501250
14001400
Cours PL/SQL d’après cours ORACLE - OAI / 11 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Mode OUT : ExempleMode OUT : Exemple
SQL> CREATE OR REPLACE PROCEDURE query_emp 1 (v_id IN emp.empno%TYPE, 2 v_name OUT emp.ename%TYPE, 3 v_salary OUT emp.sal%TYPE, 4 v_comm OUT emp.comm%TYPE) 5 IS 6 BEGIN 7 SELECT ename, sal, comm 8 INTO v_name, v_salary, v_comm 9 FROM emp 10 WHERE empno = v_id; 11 END query_emp; 12 /
SQL> CREATE OR REPLACE PROCEDURE query_emp 1 (v_id IN emp.empno%TYPE, 2 v_name OUT emp.ename%TYPE, 3 v_salary OUT emp.sal%TYPE, 4 v_comm OUT emp.comm%TYPE) 5 IS 6 BEGIN 7 SELECT ename, sal, comm 8 INTO v_name, v_salary, v_comm 9 FROM emp 10 WHERE empno = v_id; 11 END query_emp; 12 /
Cours PL/SQL d’après cours ORACLE - OAI / 12 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Mode OUT et SQL*PlusMode OUT et SQL*Plus
SQL> START emp_query.sqlProcedure created.Procedure created.
SQL> START emp_query.sqlProcedure created.Procedure created.
SQL> VARIABLE g_name varchar2(15)SQL> VARIABLE g_salary numberSQL> VARIABLE g_comm number
SQL> VARIABLE g_name varchar2(15)SQL> VARIABLE g_salary numberSQL> VARIABLE g_comm number
SQL> EXECUTE query_emp (7654, :g_name, :g_salary, 2 :g_comm)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE query_emp (7654, :g_name, :g_salary, 2 :g_comm)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_nameG_NAME---------------MARTIN
SQL> PRINT g_nameG_NAME---------------MARTIN
Cours PL/SQL d’après cours ORACLE - OAI / 13 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Mode IN OUT : ExempleMode IN OUT : Exemple
SQL> CREATE OR REPLACE PROCEDURE format_phone 2 (v_phone_no IN OUT VARCHAR2) 3 IS 4 BEGIN 5 v_phone_no := '(' || SUBSTR(v_phone_no,1,3) || 6 ')' || SUBSTR(v_phone_no,4,3) || 7 '-' || SUBSTR(v_phone_no,7); 8 END format_phone; 9 /
EnvironnementEnvironnement Procédure FORMAT_PHONEProcédure FORMAT_PHONE
v_phone_nov_phone_no'(800)633-0575' '(800)633-0575' '8006330575' '8006330575'
Cours PL/SQL d’après cours ORACLE - OAI / 14 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Appel d’une procéduredans SQL*Plus
Appel d’une procéduredans SQL*Plus
SQL>VARIABLE g_phone_no varchar2(15)
SQL> BEGIN :g_phone_no := '8006330575'; END; 2 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE format_phone (:g_phone_no)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_phone_no
SQL>VARIABLE g_phone_no varchar2(15)
SQL> BEGIN :g_phone_no := '8006330575'; END; 2 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE format_phone (:g_phone_no)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_phone_no
G_PHONE_NO---------------(800)633-0575
G_PHONE_NO---------------(800)633-0575
Cours PL/SQL d’après cours ORACLE - OAI / 15 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Appel d’une procéduredans un bloc PL/SQL
Appel d’une procéduredans un bloc PL/SQL
DECLARE v_id NUMBER := 7900;BEGIN raise_salary(v_id); --appel procédure par son nomCOMMIT;...
END;
DECLARE v_id NUMBER := 7900;BEGIN raise_salary(v_id); --appel procédure par son nomCOMMIT;...
END;
Cours PL/SQL d’après cours ORACLE - OAI / 16 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Appel d’une procéduredans une autre procédure
Appel d’une procéduredans une autre procédure
SQL> CREATE OR REPLACE PROCEDURE process_emps 2 IS 3 CURSOR emp_cursor IS 4 SELECT empno 5 FROM emp; 6 BEGIN 7 FOR emp_rec IN emp_cursor LOOP 8 raise_salary(emp_rec.empno); --appel procédure
par son nom 9 END LOOP; 10 COMMIT; 11 END process_emps; 12 /
SQL> CREATE OR REPLACE PROCEDURE process_emps 2 IS 3 CURSOR emp_cursor IS 4 SELECT empno 5 FROM emp; 6 BEGIN 7 FOR emp_rec IN emp_cursor LOOP 8 raise_salary(emp_rec.empno); --appel procédure
par son nom 9 END LOOP; 10 COMMIT; 11 END process_emps; 12 /
Cours PL/SQL d’après cours ORACLE - OAI / 17 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Transmission des valeurs de paramètres
Transmission des valeurs de paramètres
• Par position
• Par Nom (=>)
• Combinaison
• Par position
• Par Nom (=>)
• Combinaison
Cours PL/SQL d’après cours ORACLE - OAI / 18 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Transmission des valeurs de paramètres - exemple
Transmission des valeurs de paramètres - exemple
SQL> CREATE OR REPLACE PROCEDURE add_dept 1 (v_name IN dept.dname%TYPE DEFAULT 'unknown', 2 v_loc IN dept.loc%TYPE DEFAULT 'unknown') 3 IS 4 BEGIN 5 INSERT INTO dept 6 VALUES (dept_deptno.NEXTVAL, v_name, v_loc); 7 END add_dept; 8 /
SQL> CREATE OR REPLACE PROCEDURE add_dept 1 (v_name IN dept.dname%TYPE DEFAULT 'unknown', 2 v_loc IN dept.loc%TYPE DEFAULT 'unknown') 3 IS 4 BEGIN 5 INSERT INTO dept 6 VALUES (dept_deptno.NEXTVAL, v_name, v_loc); 7 END add_dept; 8 /
Cours PL/SQL d’après cours ORACLE - OAI / 19 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Transmission des valeurs de paramètres - exemple
Transmission des valeurs de paramètres - exemple
SQL> begin 2 add_dept; 3 add_dept ( 'TRAINING', 'NEW YORK'); 4 add_dept ( v_loc => 'DALLAS', v_name =>
'EDUCATION') ; 5 add_dept ( v_loc => 'BOSTON') ; 6 end; 7 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> begin 2 add_dept; 3 add_dept ( 'TRAINING', 'NEW YORK'); 4 add_dept ( v_loc => 'DALLAS', v_name =>
'EDUCATION') ; 5 add_dept ( v_loc => 'BOSTON') ; 6 end; 7 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC------ -------------- ------------- ... ... ... 41 unknown unknown 42 TRAINING NEW YORK 43 EDUCATION DALLAS 44 unknown BOSTON
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC------ -------------- ------------- ... ... ... 41 unknown unknown 42 TRAINING NEW YORK 43 EDUCATION DALLAS 44 unknown BOSTON
Cours PL/SQL d’après cours ORACLE - OAI / 20 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Suppression d’une ProcédureSuppression d’une Procédure
• Par SQL*Plus:
Ordre Drop procédure
• Syntaxe
• Exemple
• Par SQL*Plus:
Ordre Drop procédure
• Syntaxe
• Exemple
DROP PROCEDURE nom_procédure;DROP PROCEDURE nom_procédure;
SQL> DROP PROCEDURE raise_salary;Procedure dropped.
SQL> DROP PROCEDURE raise_salary;Procedure dropped.
C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI
Fonctions stockéesFonctions stockées
Cours PL/SQL d’après cours ORACLE - OAI / 22 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
GénéralitésGénéralités• Une fonction est un bloc PL/SQL
nommé qui exprime une action.
• Une fonction renvoie une et une seule valeur.
• Une fonction est stockée dans la base données
• Une fonction est un objet réutilisable..
• Une fonction stockée (fonction utilisateur) peut être utilisée dans une expression.
• Une fonction est un bloc PL/SQL nommé qui exprime une action.
• Une fonction renvoie une et une seule valeur.
• Une fonction est stockée dans la base données
• Une fonction est un objet réutilisable..
• Une fonction stockée (fonction utilisateur) peut être utilisée dans une expression.
Cours PL/SQL d’après cours ORACLE - OAI / 23 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Structure d’une fonction PL/SQLStructure d’une fonction PL/SQL
Entête Entête
ISIS
Section déclarativeSection déclarative
BEGINBEGIN
Section exécutionSection exécution
[[EXCEPTIONEXCEPTION
Section ExceptionSection Exception]]
END;END;
Entête Entête
ISIS
Section déclarativeSection déclarative
BEGINBEGIN
Section exécutionSection exécution
[[EXCEPTIONEXCEPTION
Section ExceptionSection Exception]]
END;END;
Cours PL/SQL d’après cours ORACLE - OAI / 24 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Syntaxe pour créer une Fonction
Syntaxe pour créer une Fonction
CREATE [OR REPLACE] FUNCTION nom_fonction (paramètre1 [mode1] type1, paramètre2 [mode2] type2, . . .RETURN type -- type de la valeur résultatIS|ASPL/SQL Block; -- corps de la fonction
CREATE [OR REPLACE] FUNCTION nom_fonction (paramètre1 [mode1] type1, paramètre2 [mode2] type2, . . .RETURN type -- type de la valeur résultatIS|ASPL/SQL Block; -- corps de la fonction
Cours PL/SQL d’après cours ORACLE - OAI / 25 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Développement d’une fonctionDéveloppement d’une fonction
FichierFichiersourcesource
Code Code SourceSource
p-codep-code
EditEdit
Stocké dans la base
Compilation
Exécution
1
2
EditeurEditeur
OracleOracleProcedure Procedure
BuilderBuilder
Cours PL/SQL d’après cours ORACLE - OAI / 26 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Développement d’une fonction avec SQL*Plus
Développement d’une fonction avec SQL*Plus
1.1. SaSa isir le texte de l’ordre CREATE isir le texte de l’ordre CREATE FUNCTION avec un éditeur et le sauver FUNCTION avec un éditeur et le sauver sous forme de fichier source (extension sous forme de fichier source (extension .sql.sql ). ).
1.1. SaSa isir le texte de l’ordre CREATE isir le texte de l’ordre CREATE FUNCTION avec un éditeur et le sauver FUNCTION avec un éditeur et le sauver sous forme de fichier source (extension sous forme de fichier source (extension .sql.sql ). ).
2.2. Sous SQL*Plus, exécuter le script par Sous SQL*Plus, exécuter le script par START.START.
2.2. Sous SQL*Plus, exécuter le script par Sous SQL*Plus, exécuter le script par START.START.
3.3. Appeler la fonction pour l’exécuter.Appeler la fonction pour l’exécuter.3.3. Appeler la fonction pour l’exécuter.Appeler la fonction pour l’exécuter.
Cours PL/SQL d’après cours ORACLE - OAI / 27 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Création d’une fonction avec SQL*Plus : Exemple
Création d’une fonction avec SQL*Plus : Exemple
SQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_id IN emp.empno%TYPE) 3 RETURN NUMBER 4 IS 5 v_salary emp.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salary 9 FROM emp 10 WHERE empno = v_id; 11 RETURN (v_salary); 12 END get_sal; 13 /
SQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_id IN emp.empno%TYPE) 3 RETURN NUMBER 4 IS 5 v_salary emp.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salary 9 FROM emp 10 WHERE empno = v_id; 11 RETURN (v_salary); 12 END get_sal; 13 /
Cours PL/SQL d’après cours ORACLE - OAI / 28 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Exécution d’une FonctionExécution d’une Fonction
• Utilisation dans une expression PL/SQL de façon identique à une fonction prédéfinie.
• Allocation une variable de lien pour recevoir le résultat de la fonction et exécution de la fonction.
• Utilisation dans une expression PL/SQL de façon identique à une fonction prédéfinie.
• Allocation une variable de lien pour recevoir le résultat de la fonction et exécution de la fonction.
Cours PL/SQL d’après cours ORACLE - OAI / 29 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Exécution d ’une Fonction avec SQL*Plus : Exemple
Exécution d ’une Fonction avec SQL*Plus : Exemple
Environnement d’appelEnvironnement d’appel Fonction GET_SALFonction GET_SAL
v_idv_id79347934
RETURN v_salaryRETURN v_salary
SQL> START get_salary.sqlSTART get_salary.sqlProcedure createdProcedure created.
SQL> VARIABLE g_salary numberSQL> VARIABLE g_salary number
SQL> EXECUTE :g_salary := get_sal(7934)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE :g_salary := get_sal(7934)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_salary G_SALARY
------------------ 1300
SQL> PRINT g_salary G_SALARY
------------------ 1300
Cours PL/SQL d’après cours ORACLE - OAI / 30 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Utilisation d’une fonction Utilisation d’une fonction
• Dans la liste de projection d’un ordre SELECT.
• Dans un prédicat d’une clause WHERE ou HAVING.
• Dans les clauses CONNECT BY, START WITH, ORDER BY, et GROUP BY.
• Dans la clause VALUES d’un ordre INSERT.
• Dans la clause SET d’un ordre UPDATE.
• Dans la liste de projection d’un ordre SELECT.
• Dans un prédicat d’une clause WHERE ou HAVING.
• Dans les clauses CONNECT BY, START WITH, ORDER BY, et GROUP BY.
• Dans la clause VALUES d’un ordre INSERT.
• Dans la clause SET d’un ordre UPDATE.
Cours PL/SQL d’après cours ORACLE - OAI / 31 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Fonction utilisateur : Restrictions
Fonction utilisateur : Restrictions
• Obligatoirement une fonction stockée.
• Doit être une fonction « ligne » et non une fonction « de groupe ».
• N’utilise que des paramètres de mode IN.
• Le type d ’un paramètre est limité à CHAR, DATE, ou NUMBER .
• Le type de la valeur résultat peut être tout type supporté par le SGBDR.
• Obligatoirement une fonction stockée.
• Doit être une fonction « ligne » et non une fonction « de groupe ».
• N’utilise que des paramètres de mode IN.
• Le type d ’un paramètre est limité à CHAR, DATE, ou NUMBER .
• Le type de la valeur résultat peut être tout type supporté par le SGBDR.
Cours PL/SQL d’après cours ORACLE - OAI / 32 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Fonction utilisateur : Restrictions
Fonction utilisateur : Restrictions
• Les ordres INSERT, UPDATE, ou DELETE ne sont pas autorisés dans le corps de la fonction.
• Les procédures / fonctions appelées dans le corps de la fonction doivent respecter les restrictions précédentes.
• Les ordres INSERT, UPDATE, ou DELETE ne sont pas autorisés dans le corps de la fonction.
• Les procédures / fonctions appelées dans le corps de la fonction doivent respecter les restrictions précédentes.
Cours PL/SQL d’après cours ORACLE - OAI / 33 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Suppression d’une FonctionSuppression d’une Fonction
En utilisant SQL*PlusEn utilisant SQL*Plus
• Par l’ordre DROP FUNCTION
• Syntaxe
• Exemple
En utilisant SQL*PlusEn utilisant SQL*Plus
• Par l’ordre DROP FUNCTION
• Syntaxe
• Exemple
SQL> DROP FUNCTION get_salary;Function dropped.
SQL> DROP FUNCTION get_salary;Function dropped.
DROP FUNCTION nom_fonction;DROP FUNCTION nom_fonction;
Cours PL/SQL d’après cours ORACLE - OAI / 34 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Procédure ou Fonction?Procédure ou Fonction?
ProcédureProcédure
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
IN paramètreIN paramètre
OUT paramètreOUT paramètre
IN OUT paramètreIN OUT paramètre
Environnement Environnement d ’appeld ’appel
Environnement Environnement d ’appeld ’appel
FonctionFonction
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
IN paramètreIN paramètre
Cours PL/SQL d’après cours ORACLE - OAI / 35 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Procédure ou Fonction?Procédure ou Fonction?
Procédure
Exécutée comme une instruction PL/SQL
Pas de type de retour
Peut renvoyer plusieurs valeurs résultats
Fonction
Utilisée dans une expression
Type de RETOUR défini
Une seule valeur résultat
C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI
PackagesPackages
Cours PL/SQL d’après cours ORACLE - OAI / 37 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
GénéralitésGénéralités
• Regroupement logique de types PL/SQL, variables, procédures et fonctions.
• Deux parties:
– Spécification
– Corps
• Ne peut pas être appelé.
• Permet à Oracle 8 de charger plusieurs objets simultanément en mémoire.
• Regroupement logique de types PL/SQL, variables, procédures et fonctions.
• Deux parties:
– Spécification
– Corps
• Ne peut pas être appelé.
• Permet à Oracle 8 de charger plusieurs objets simultanément en mémoire.
Cours PL/SQL d’après cours ORACLE - OAI / 38 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Avantages des PackagesAvantages des Packages
• Modularité
• Meilleur développement des applications
• Informations cachées
• Ajout de fonctionnalités
• Meilleurs performances
• Surcharge
• Modularité
• Meilleur développement des applications
• Informations cachées
• Ajout de fonctionnalités
• Meilleurs performances
• Surcharge
Cours PL/SQL d’après cours ORACLE - OAI / 39 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Structure d’un Package Structure d’un Package Procédure AProcédure Adéclarationdéclaration
Procédure BProcédure Bdéfinitiondéfinition
SpécificationSpécification
Corps duCorps du Package Package
Procédure AProcédure Adéfinitiondéfinition
Fonction CFonction Cdéclarationdéclaration
Fonction CFonction Cdéfinitiondéfinition
Cours PL/SQL d’après cours ORACLE - OAI / 40 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Développement d’un packageDéveloppement d’un package
FichierFichiersourcesource
Code Code SourceSource
p-codep-code
EditEdit
Stocké dans la base
Compilation
Exécution
1
2
EditeurEditeur
OracleOracleProcedure Procedure
BuilderBuilder
Cours PL/SQL d’après cours ORACLE - OAI / 41 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Développement d’un PackageDéveloppement d’un Package
• Cataloguer dans deux fichiers différents les parties spécification et corps facilite les modifications ultérieures.
• La partie spécification peut exister sans la partie corps.
• La partie corps ne peut pas exister sans la partie spécification..
• Cataloguer dans deux fichiers différents les parties spécification et corps facilite les modifications ultérieures.
• La partie spécification peut exister sans la partie corps.
• La partie corps ne peut pas exister sans la partie spécification..
Cours PL/SQL d’après cours ORACLE - OAI / 42 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
CREATE [OR REPLACE] PACKAGE nom_packageIS | AS type public et déclarations de variables spécifications de procédures et/ou fonctionsEND nom_package;
CREATE [OR REPLACE] PACKAGE nom_packageIS | AS type public et déclarations de variables spécifications de procédures et/ou fonctionsEND nom_package;
Création de la Spécification du Package
Création de la Spécification du Package
SyntaxeSyntaxeSyntaxeSyntaxe
Cours PL/SQL d’après cours ORACLE - OAI / 43 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
package COMM_PACKAGEpackage COMM_PACKAGE
G_COMMG_COMM
RESET_COMMRESET_COMM déclaration de déclaration de procédureprocédure
spécificationspécification
Déclaration Éléments PUBLICSDéclaration Éléments PUBLICS
Variable
Globale
Variable
Globale
Procédure PubliqueProcédure Publique
Cours PL/SQL d’après cours ORACLE - OAI / 44 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Déclaration Éléments Publiques Exemple
Déclaration Éléments Publiques Exemple
SQL>CREATE OR REPLACE PACKAGE comm_package IS
2 g_comm NUMBER := 10; --initialisé à 10
3 PROCEDURE reset_comm
4 (v_comm IN NUMBER);
5 END comm_package;
6 /
Cours PL/SQL d’après cours ORACLE - OAI / 45 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Utilisation Variable Globale ou Procédure Publique
Utilisation Variable Globale ou Procédure Publique
SQL>EXECUTE comm_package.g_comm := 5
SQL>EXECUTE comm_package.reset_comm(8)
Cours PL/SQL d’après cours ORACLE - OAI / 46 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Création d’un Corps de Package
Création d’un Corps de Package
SyntaxeSyntaxeSyntaxeSyntaxe
CREATE [OR REPLACE] PACKAGE BODY nom_packageIS | AS type privé et déclarations de variables corps de procédures et/ou fonctionsEND nom_package;
CREATE [OR REPLACE] PACKAGE BODY nom_packageIS | AS type privé et déclarations de variables corps de procédures et/ou fonctionsEND nom_package;
Cours PL/SQL d’après cours ORACLE - OAI / 47 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
G_COMMG_COMM
RESET_COMMRESET_COMMprocédure declarationprocédure declaration
VALIDATE_COMMVALIDATE_COMM définition fonction définition fonction
SpécificationSpécification
Corps de Corps de PackagePackage
RESET_COMMRESET_COMM définition procédure définition procédure
COMM_PACKAGE packageCOMM_PACKAGE package
Éléments publics et privésÉléments publics et privés
PrivéePrivée
PublicPublic
GlobaleGlobale
Cours PL/SQL d’après cours ORACLE - OAI / 48 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Création Corps Package Exemple
Création Corps Package Exemple
SQL>CREATE OR REPLACE PACKAGE BODY comm_package IS 2 FUNCTION validate_comm 3 (v_comm IN NUMBER) RETURN BOOLEAN 4 IS 5 v_max_comm NUMBER; 6 BEGIN 7 SELECT MAX(comm) 8 INTO v_max_comm 9 FROM emp; 10 IF v_comm > v_max_comm THEN RETURN(FALSE); 11 ELSE RETURN(TRUE); 12 END IF; 13 END validate_comm; … END comm_package; /
Cours PL/SQL d’après cours ORACLE - OAI / 49 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
14 PROCEDURE reset_comm 15 (v_comm IN NUMBER) 16 IS 17 v_valid BOOLEAN; 18 BEGIN 19 v_valid := validate_comm(v_comm); 20 IF v_valid = TRUE THEN 21 g_comm := v_comm; 22 ELSE 23 RAISE_APPLICATION_ERROR 24 (-20210,'Invalid commission'); 25 END IF; 26 END reset_comm; 27 END comm_package; 28 /
Création Corps Package Exemple - suite
Création Corps Package Exemple - suite
Cours PL/SQL d’après cours ORACLE - OAI / 50 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Appel composant d’un packageAppel composant d’un packageExemple 1: appel d’une fonction à partir Exemple 1: appel d’une fonction à partir d’une procédure du même package.d’une procédure du même package.Exemple 1: appel d’une fonction à partir Exemple 1: appel d’une fonction à partir d’une procédure du même package.d’une procédure du même package.
CREATE OR REPLACE PACKAGE BODY comm_package IS. . .
PROCEDURE reset_comm(v_comm IN NUMBER)IS v_valid BOOLEAN;BEGIN v_valid := validate_comm(v_comm); -- nom fonction IF v_valid = TRUE THEN
g_comm := v_comm; ELSE
RAISE_APPLICATION_ERROR (-20210, 'Invalid comm'); END IF;END reset_comm;
END comm_package;
Cours PL/SQL d’après cours ORACLE - OAI / 51 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Exemple 2: Appel à une procédure d’un Exemple 2: Appel à une procédure d’un package avec SQL*Plus :package avec SQL*Plus :
Nom_package.nom_procédureNom_package.nom_procédure
Exemple 3: Appel à une procédure d’un Exemple 3: Appel à une procédure d’un package d ’un schéma différent :package d ’un schéma différent :
Nom_schéma.nom_package.nom_procédureNom_schéma.nom_package.nom_procédure
..
Exemple 2: Appel à une procédure d’un Exemple 2: Appel à une procédure d’un package avec SQL*Plus :package avec SQL*Plus :
Nom_package.nom_procédureNom_package.nom_procédure
Exemple 3: Appel à une procédure d’un Exemple 3: Appel à une procédure d’un package d ’un schéma différent :package d ’un schéma différent :
Nom_schéma.nom_package.nom_procédureNom_schéma.nom_package.nom_procédure
..
Appel composant d’un packageAppel composant d’un package
SQL> EXECUTE comm_package.reset_comm(1500);
SQL> EXECUTE scott.comm_package.reset_comm(1500);
Cours PL/SQL d’après cours ORACLE - OAI / 52 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Appel composant d’un packageAppel composant d’un package
SQL> EXECUTE comm_package.reset_comm@ny (1500);
Exemple 4: Appel à une procédure d’un Exemple 4: Appel à une procédure d’un package d’une base distantepackage d’une base distante
Nom_package.nom_procédure@lienBDNom_package.nom_procédure@lienBD
Exemple 4: Appel à une procédure d’un Exemple 4: Appel à une procédure d’un package d’une base distantepackage d’une base distante
Nom_package.nom_procédure@lienBDNom_package.nom_procédure@lienBD
Cours PL/SQL d’après cours ORACLE - OAI / 53 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Référence à une variable globale à l’intérieur d’un package
Référence à une variable globale à l’intérieur d’un package
ExempleExempleExempleExempleCREATE OR REPLACE PACKAGE BODY comm_package IS
. . .PROCEDURE reset_comm(v_comm IN NUMBER)IS
v_valid BOOLEAN;BEGIN
v_valid := validate_comm(v_comm);IF v_valid = TRUE THEN
g_comm := v_comm; -- nom variableELSE
RAISE_APPLICATION_ERROR (-20210,'Invalid comm');END IF;
END reset_comm;END comm_package;
Cours PL/SQL d’après cours ORACLE - OAI / 54 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
Référence à une variable globale par une procédure externe
Référence à une variable globale par une procédure externe
Exemple Exemple Exemple Exemple CREATE OR REPLACE PROCEDURE hire_emp (v_ename IN emp.ename%TYPE, v_mgr IN emp.mgr%TYPE, v_job IN emp.job%TYPE, v_sal IN emp.sal%TYPE)IS v_comm emp.comm%TYPE;. . .BEGIN. . .
v_comm := comm_package.g_comm;-- nom_package.nom_variable
. . .END hire_emp;
Cours PL/SQL d’après cours ORACLE - OAI / 55 C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis
DROP PACKAGE package_nameDROP PACKAGE package_name
Suppression d’un PackageSuppression d’un Package
Suppression de la spécification et du Suppression de la spécification et du corps :corps :Suppression de la spécification et du Suppression de la spécification et du corps :corps :
Suppression du corps seul :Suppression du corps seul :
DROP PACKAGE BODY package_name DROP PACKAGE BODY package_name