c. bonnet / r. chapuis cours pl/sql daprès cours oracle - oai procédures stockées

55
C. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI Procédures Stockées

Upload: bertrand-olivier

Post on 04-Apr-2015

133 views

Category:

Documents


5 download

TRANSCRIPT

Page 1: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI

Procédures StockéesProcédures Stockées

Page 2: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procé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.

Page 3: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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;

Page 4: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 5: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 6: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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.

Page 7: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 8: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 9: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 10: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 11: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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 /

Page 12: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 13: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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'

Page 14: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 15: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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;

Page 16: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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 /

Page 17: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 18: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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 /

Page 19: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 20: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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.

Page 21: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI

Fonctions stockéesFonctions stockées

Page 22: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures 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.

Page 23: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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;

Page 24: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 25: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 26: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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.

Page 27: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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 /

Page 28: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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.

Page 29: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 30: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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.

Page 31: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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.

Page 32: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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.

Page 33: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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;

Page 34: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 35: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 36: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI

PackagesPackages

Page 37: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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.

Page 38: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 39: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 40: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 41: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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..

Page 42: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 43: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 44: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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 /

Page 45: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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)

Page 46: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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;

Page 47: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 48: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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; /

Page 49: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 50: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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;

Page 51: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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);

Page 52: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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

Page 53: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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;

Page 54: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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;

Page 55: C. Bonnet / R. Chapuis Cours PL/SQL daprès cours ORACLE - OAI Procédures Stockées

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