stockage des traitements dans la base oracle pl by A5Qz9Hzs

VIEWS: 0 PAGES: 39

									STOCKAGE DES TRAITEMENTS
   DANS LA BASE ORACLE
     PL / SQL STOCKE SOUS LA FORME DE

• PROCEDURES

• FONCTIONS

• PACKAGES


                 ORACLE NIVEAU 2        Page n° 1
              PROCEDURES
• PL / SQL
                         BLOC ANONYME

• PROCEDURE
                         BLOC NOMME

 UNE PROCEDURE PEUT ETRE STOCKEE DANS LA BASE POUR UNE
 UTILISATION ULTERIEURE
 ELLE PEUT ETRE CREE PAR SQLPLUS OU PROCEDURE BUILDER


                     ORACLE NIVEAU 2           Page n° 2
    PROCEDURES / SYNTAXE
CREATE [OR REPLACE] PROCEDURE proc_name
(argument1 [mode1] datatype1,
 argument2 [mode2] datatype2,
…)
IS [AS]
PL / SQL block;
  argument : nom de la variable PL /SQL

  mode : IN , OUT, IN OUT

  datatype : type de données de l ’argument


                         ORACLE NIVEAU 2      Page n° 3
PROCEDURES / SYNTAXE
create_procedure::=




              ORACLE NIVEAU 2   Page n° 4
 PROCEDURES /
CONSTRUCTION
PROCEDURE
BUILDER

SYSTEM                      TEXT
EDITOR                      FILE



                           CODE
                           SOURCE




                           CODE
                           EXECUTABLE
         ORACLE NIVEAU 2            Page n° 5
  PROCEDURES / PARAMETRES
IN                      OUT                   IN OUT

DEFAUT                 A PRECISER             A PRECISER

VALEUR      RETOURNEE A        PASSEE A UN
PASSEE A UN       L ’ENVIRONNEMENT    SOUS PROGRAMME
SOUS PROGRAMME    D ’APPEL            ET RETOURNEE


LITERAL
EXPRESSION
VARIABLE INITIALISEE   VARIABLE               VARIABLE




                            ORACLE NIVEAU 2                Page n° 6
 PROCEDURES / PARAMETRES
• EXEMPLE IN                      7389                    v_id
     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.1
     7        WHERE empno = v_id;
     8 END raise_salary;
     9 /
     Procedure created.

     SQL> EXECUTE raise_salary (7389)
     PL / SQL procedure successfully completed.



                           ORACLE NIVEAU 2          Page n° 7
 PROCEDURES / PARAMETRES
• EXEMPLE OUT
          7389                              v_id
                                   MARTIN v_name
                                   1250   v_salary
                                   1400   v_comm




                 ORACLE NIVEAU 2            Page n° 8
PROCEDURES / PARAMETRES
SQL>CREATE OR REPLACE PROCEDURE query_emp
2 (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 /
Procedure created.




                      ORACLE NIVEAU 2       Page n° 9
 PROCEDURES / PARAMETRES
• EXECUTION ET VISUALISATION SOUS
  SQLPLUS

   – DEFINITION ET IMPRESSION D’UNE
     VARIABLE DE LIAISON
Commande SQLPLUS VARIABLE ET PRINT

VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n)|
VARCHAR2 (n )

PRINT variable
                     ORACLE NIVEAU 2          Page n° 10
PROCEDURES / PARAMETRES
– TEST ET VISUALISATION SOUS SQLPLUS

   SQL> variable g_name      varchar2(15)
   SQL> variable g_salary    number
   SQL> variable g_comm      number

   SQL> EXECUTE query_emp (7389, :g_name,:g_salary,:g_comm)

   SQL> PRINT g_name




                        ORACLE NIVEAU 2                  Page n° 11
 PROCEDURES / PARAMETRES
• EXEMPLE IN OUT
          ‘ 8006330575 ’                      (800)6330575
          (800)6330575
 SQL> CREATE OR REPLACE PROCEDURE format_phone
 2 (v_phone_no IN OUT VARCHAR2(12))
 3 IS
 4 BEGIN
 5 v_phone_no := ‘ (‘ ||substr(v_phone_no,1,3)|| ’) ’||substr(v_phone,4,7)
 6 END format_phone;
 7 /
SQL> variable g_phone_no varchar2(15)             UTILISATION DE
SQL> BEGIN                                        VARIABLE DE LIAISON
SQL> :g_phone_no := ‘ 8006330575 ’;END;
                                                  PREFIXEE PAR :
2 /
SQL>EXECUTE format_phone(:g_phone_no)             DANS UN BLOC PL/SQL
SQL> PRINT g_phone_no

                               ORACLE NIVEAU 2                       Page n° 12
 PROCEDURES / PARAMETRES
• PASSAGE DES PARAMETRES

 – VALEURS PAR DEFAUT
 – POSITIONNEL
 – PAR NOM




              ORACLE NIVEAU 2   Page n° 13
PROCEDURES / PARAMETRES
 EXEMPLE

 SQL> CREATE PROCEDURE add_dept
 2 (v_name IN dept.dname%TYPE DEFAULT ‘ inconnu ’,
 3 v_loc IN dept.loc%TYPE DEFAULT ‘ inconnu ’)
 4 IS
 5 BEGIN
 6 INSERT INTO dept (dept_deptno.NEXTVAL, v_name,v_loc);
 7 END add_dept;
 8 /

 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 /
                         ORACLE NIVEAU 2                      Page n° 14
          PROCEDURES
       APPEL / DESTRUCTION
• APPEL DIRECT SOUS SQLPLUS
         EXECUTE nomproc(param,…)

• DANS UN PROGRAMME PL /SQL OU UNE AUTRE
  PROCEDURE
         nom_proc (param,…);

• DESTRUCTION
        DROP PROCEDURE nomproc;

                   ORACLE NIVEAU 2   Page n° 15
 PROCEDURES / DESCRIPTION
• METABASE


 – USER_DEPENDENCIES
 – USER_ERRORS
   (SHOW ERRORS sous SQLPLUS)
 – USER_SOURCE
   (PERMET DE RETROUVE LE SOURCE PL/SQL)

               ORACLE NIVEAU 2    Page n° 16
           FONCTIONS
• BLOC PL / SQL NOMME QUI RAMENE UNE
  VALEUR
• STOCKE DANS LA BASE EN TANT
  QU ’OBJET
• PEUT ETRE UTILISE COMME EXPRESSION
  DANS SQL OU PL / SQL



              ORACLE NIVEAU 2   Page n° 17
  FONCTIONS / SYNTAXE
CREATE [OR REPLACE] FUNCTION function_name
(argument1 [mode1] datatype1,
…)
RETURN datatype
IS|AS
PL /SQL Block;

Mode1 NECESSAIREMENT IN




                 ORACLE NIVEAU 2        Page n° 18
     FONCTIONS / SYNTAXE
create_function::=




                     ORACLE NIVEAU 2   Page n° 19
   FONCTIONS / CREATION
• SOUS SQLPLUS OU PROCEDURE BUILDER
• 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 /
                   ORACLE NIVEAU 2                Page n° 20
FONCTIONS / EXECUTION
                                               ATTENTION NE PAS PRECISER
SQL> VARIABLE g_salary number                  LA DIMENSION POUR UNE
SQL> EXECUTE :g_salary := get_sal(7934)        VARIABLE DE LIAISON


SQL> PRINT g_salary

SQL> select ename from emp where sal > get_sal(7369)




                        ORACLE NIVEAU 2                     Page n° 21
     FONCTIONS / EXECUTION
• UTILISATION DE LA FONCTION DANS LE LMD

  – DANS UNE PROJECTION, UNE CLAUSE WHERE OU
    UNE CLAUSE HAVING
  – DANS UNE CLAUSE CONNECT BY, START WITH,
    ORDER BY, GROUP BY
  – DANS LA CLAUSE VALUES DE LA COMMANDE
    INSERT
  – DANS LA CLAUSE SET DE LA COMMANDE UPDATE

• UTILISATION DANS PL /SQL ET PACKAGES


                  ORACLE NIVEAU 2      Page n° 22
        FONCTIONS
RESTRICTIONS D ’UTILISATION
     POUR L’UTILISATION DANS UNE
     REQUÊTE OU SOUS SQLPLUS

• LES DATATYPES DOIVENT ETRE CEUX DU NOYAU
  (DATE,VARCHAR2 etc ... ET NON CEUX SPECIFIQUES A
  PL /SQL)
• PAS DE FONCTIONS DANS UNE CLAUSE CHECK
• PAS D ’INSERT, UPDATE OU DELETE
• PAS D ’APPELS A DES SOUS PROGRAMMES NON
  CONFORMES
• PAS D’ATTRIBUT OUT / IN OUT


                     ORACLE NIVEAU 2        Page n° 23
        COMPARAISON
    FONCTIONS / PROCEDURES

PROCEDURE                                FONCTION

EXECUTE UN BLOC                          INVOQUE COMME
PL / SQL                                  EXPRESSION

PAS DE DONNEES                           DOIT CONTENIR AU
DE TYPE RETURN                           MOINS UNE DONNEE
                                         DE TYPE RETURN

PEUT RETOURNER UNE                       DOIT RETOURNER
OU PLUSIEURS VALEURS                     UNE VALEUR


                       ORACLE NIVEAU 2              Page n° 24
             PACKAGES
• REGROUPENT DES VARIABLES,
  FONCTIONS, PROCEDURES QUI
  POSSEDENT UN LIEN LOGIQUE
• CONSTRUITS EN DEUX PARTIES
  – SPECIFICATION
  – BODY
• UTILISATION DE LA NOTATION POINTEE
• CHARGEMENT EN MEMOIRE DE
  PLUSIEURS OBJETS

                ORACLE NIVEAU 2   Page n° 25
  PACKAGES / AVANTAGES
• MODULARITE
• CONCEPTION FACILITE DE
  L ’APPLICATION
• CACHE DE L ’INFORMATION
 – CONSTRUCTIONS PUBLICS
 – CONSTRUCTIONS PRIVES
• AMELIORATION DES FONCTIONALITES
 – PERSISTANCE DES DONNEES SUR LA
   SESSION
• PERFORMANCES
              ORACLE NIVEAU 2       Page n° 26
         PACKAGES / STRUCTURE
                   DECLARATION
SPECIFICATIONS     PROCEDURE A




                    PROCEDURE B
 PACKAGE            DEFINITION
 BODY



                 DEFINITION
                 PROCEDURE A




                        ORACLE NIVEAU 2   Page n° 27
         PACKAGES / STRUCTURE
 PORTEE       UTILISATION                     DECLARATION
                                              DEFINITION

 PUBLIQUE     DANS TOUS LES                   DECLARE DANS SPECIFIC.
              ENVIRONNEMENTS ORACLE           DEFINI DANS P.B.


 PRIVE        PEUT SEULEMENT ETRE             DECLARE ET DEFINI
              REFERENCE PAR DES               UNIQUEMENT
              ELEMENTS DU PACKAGE             DANS P. B.

VISIBILITE

LOCALE       DEFINIE A L ’INTERIEUR D ’UN SOUS PROGRAMME
             PAS DE REFERENCE EN DEHORS DU SOUS PROGRAMME

GLOBALE      PEUT ETRE REFERENCEE ET CHANGEE EN DEHORS DU SOUS
                                                   PROGRAMME
                            ORACLE NIVEAU 2               Page n° 28
      PACKAGES / STRUCTURE
                                           VARIABLE PUBLIQUE
SPECIFICATIONS
                   DECLARATION
                   PROCEDURE A            PROCEDURE PUBLIQUE



                                            VARIABLE GLOBALE

                    PROCEDURE B               PROCEDURE PRIVEE
PACKAGE             DEFINITION
BODY
                                            PROCEDURE PUBLIQUE

                 DEFINITION
                 PROCEDURE A

                                           VARIABLE LOCALE


                        ORACLE NIVEAU 2             Page n° 29
     PACKAGES / SYNTAXE
• SYNTAXE
  CREATE [OR REPLACE] PACKAGE package_name IS|AS
       element_package;
       …
  END package_name;
• CREATE [OR REPLACE ] PACKAGE BODY
  package_name
   IS|AS
  declaration de type ou de variables globales;
  specifications package;
  END package_name;


                  ORACLE NIVEAU 2        Page n° 30
          PACKAGES / EXEMPLE
• PACKAGE comm_p
   – variable publique g_comm
   – procédure publique reset_comm
   – fonction privée validate_comm


PACKAGE               g_comm
SPECIFICATIONS
                      reset_comm


PACKAGE
                 validate_comm
BODY
                 reset_comm


                               ORACLE NIVEAU 2   Page n° 31
     PACKAGES / EXEMPLE
 SQL> CREATE OR REPLACE PACKAGE comm_p IS
 2 g_comm NUMBER (10);
 3 PROCEDURE reset_comm
 4 (v_comm IN NUMBER);
 5 END comm_p;
 6 /

SQL> EXECUTE comm_p.g_comm : = 15;

SQL> EXECUTE comm_p.reset_comm(10);
                                      ERREUR
                                      LE P.B. DOIT EXISTER
                    ORACLE NIVEAU 2                Page n° 32
      PACKAGES / EXEMPLE
SQL> CREATE OR REPLACE PACKAGE BODY comm_p 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) INTO v_max_comm FROM emp;
8 IF v_comm> v_max_comm THEN RETURN (FALSE);
9 ELSE RETURN (TRUE);
10 END IF;
11 END validate_comm;



                   ORACLE NIVEAU 2       Page n° 33
        PACKAGES / EXEMPLE
12 PROCEDURE reset_comm
13 (v_comm IN NUMBER)
14 IS
14 v_valid BOOLEAN;
15 BEGIN
16 v_valid := validate_comm (v_comm);
17 IF v_valid = TRUE THEN
18 g_comm := v_comm;
19 ELSE
20 RAISE_APPLICATION_ERROR (-20200, ’commission invalide ’);
21 END IF;
22 END reset_comm;
23 END comm_p;
24 /
                       ORACLE NIVEAU 2           Page n° 34
               PACKAGES
                 APPEL
• A L’ INTERIEUR DU PACKAGE

  – ON NE PRECISE QUE LE NOM DE L ’ELEMENT

• A PARTIR D ’UNE AUTRE PROCEDURE
  – package_name.element(…);

• SOUS SQLPLUS
  – SQL> EXECUTE package_name.element(…);
                   ORACLE NIVEAU 2     Page n° 35
PACKAGES / EXEMPLE

SQL> EXECUTE comm_p.reset_comm(1500);

BEGIN
…
comm_p.reset_comm(1500);
IF comm_p.g_comm = xxx THEN …
…

END;



             ORACLE NIVEAU 2            Page n° 36
        PACKAGES
 PERSISTANCE DES DONNEES
• VARIABLES GLOBALES (PUBLIQUES OU PRIVEES)
   – PERSISTANCE POUR LA SESSION ET POUR
     L ’UTILISATEUR
   – REINITIALISATION AU PREMIER APPEL POUR LA
     SESSION
• CURSEUR GLOBAL(PUBLIC OU PRIVE)
   – PERSISTANCE DE L ’ETAT DU CURSEUR DANS LA
     SESSION DE L ’UTILISATEUR
    (LE FETCH SE POURSUIT LA OU IL S ’EST ARRETE)
• ACCES AUX VALEURS DANS LA SESSION

                         ORACLE NIVEAU 2            Page n° 37
    PACKAGES STANDARDS
• DBMS_ALERT
• DBMS_JOB
• DBMS_PIPE
• DBMS_SESSION
  ...
• DBMS_OUTPUT


                 ORACLE NIVEAU 2   Page n° 38
       PACKAGE DBMS_OUTPUT
PUT           AJOUT D ’UN TEXTE AU BUFFER DE SORTIE

NEW_LINE SAUT DE LIGNE DANS BUFFER DE SORTIE

PUT_LINE      NEW_LINE + PUT

...
      SQL> SET SERVEROUTPUT ON
      ...
      BEGIN
      LOOP FETCH c1 into v_empno;
            DBMS_OUTPUT.PUT_LINE(v_empno);
      ...
                       ORACLE NIVEAU 2       Page n° 39

								
To top