Oracle PL/SQL

Vous pouvez déclarer dans cette section tous les types, variables et constantes nécessaires à l'exécution du bloc.
Ces variables peuvent être de n'importe quel type SQL ou PL/SQL (voir le chapitre Variables, types et littéraux).
Leur initialisation, facultative, s'effectue avec l'opérateur :=

DECLARE
   LN$Nbre   NUMBER(3) := 0 ;
   LD$Date   DATE := SYSDATE ;
   LC$Nom    VARCHAR2(10) := 'PL/SQL' ;

Une constante est une variable dont l'initialisation est obligatoire et dont la valeur ne pourra pas être modifiée en cours d'exécution
Elle est déclarée avec le mot clé : CONSTANT qui doit précéder le type

DECLARE
   LN$Pi  CONSTANT NUMBER := 3. 1415926535 ;

PL/SQL n'est pas sensible à la casse. Pour lui les expressions suivantes sont équivalentes :

NOM_VARIABLE NUMBER ;
Nom_Variable Number ;
nom_variable number ;

Délimitée par les mots clés BEGIN et END; elle contient les instructions d'exécution du bloc PL/SQL, les instructions de contrôle et d'itération,
l'appel des procédures et fonctions, l'utilisation des fonctions natives, les ordres SQL, etc.

Chaque instruction doit être suivie du terminateur d'instruction ;

Voici la liste des instructions que cette section peut contenir

1-2-1. Assignation▲

Ma_variable := 10 ;
Ma_chaine   := 'Chaîne de caractères' ;

Declare
   LC$Nom_emp  EMP.ENAME%Type ;

   Cursor C_EMP Is
   Select
	ename
   From
	EMP
   Where
	Empno = 1014
   ;

Begin
   Select
	ename
   Into
	LC$Nom_emp
   From
	EMP
   Where
	Empno = 1014
   ;

   Open  C_EMP ;
   Fetch C_EMP Into LC$Nom_emp ;
   Close C_EMP ;
End ;

1-2-2. OPEN▲

Ouverture d'un curseur SQL

nom curseur représente le nom donné au curseur qui permettra de le référencer dans les instructions suivantes
paramètres du curseur représente la liste des paramètres transmis au curseur

le curseur doit avoir été préalablement défini dans la section déclarative

 

Sélectionnez

Declare
   LC$Nom_emp  EMP.ENAME%Type ;

   Cursor C_EMP ( LN$Numemp IN EMP.EMPNO%Type ) Is
   Select
	ename
   From
	EMP
   Where
	Empno = LN$Numemp
   ;

Begin
   Open  C_EMP ( 1024 );
   Fetch C_EMP Into LC$Nom_emp ;
   Close C_EMP ;
End ;

1-2-3. OPEN FOR▲

Ouverture d'un curseur SQL incluant l'ordre select correspondant.
La déclaration préalable du curseur dans la section déclarative n'est pas nécessaire

 

Sélectionnez

Declare
   LC$Nom_emp  EMP.ENAME%Type ;
Begin
   Open  C_EMP For 'Select ename From EMP Where empno = 1024' ;
   Fetch C_EMP Into LC$Nom_emp ;
   Close C_EMP ;
End ;

1-2-4. CLOSE▲

1-2-5. COMMIT▲

Cette instruction permet d'enregistrer en base toutes les modifications effectuées au cours de la transaction

Le mot clé WORK est facultatif et n'a aucun effet particulier
Un commentaire d'un maximum de 50 caractères peut apparaître entre apostrophes derrière le mot clé COMMENT

1-2-6. EXECUTE IMMEDIATE▲

Cette instruction permet d'exécuter du SQL dynamique

-> Pour une explication détaillée de cette instruction, reportez-vous à l'article sur le sql dynamique natif

1-2-7. EXIT▲

Cette instruction permet de quitter une structure itérative

label facultatif permet de nommer précisément la structure dont on veut sortir.
expression booléenne permet de spécifier une condition de sortie

Exit saute à l'instruction suivant le mot clé END LOOP;
Dans le cas de boucles imbriquées, l'indication d'un label permet de quitter tout ou partie des boucles imbriquées

 

Sélectionnez

SQL> Declare
  2    LN$Num pls_integer := 0 ;
  3  Begin
  4    Loop
  5      LN$Num := LN$Num + 1 ;
  6      dbms_output.put_line( to_char( LN$Num ) ) ;
  7      EXIT WHEN LN$Num > 3 ; -- sortie de la boucle lorsque LN$Num est supétieur à 3
  8    End loop ;
  9  End ;
 10  /
1
2
3
4

Procédure PL/SQL terminée avec succès.

Lorsque le test effectué (LN$Num > 3) est vérifié (TRUE), la boucle Loop … End loop est quittée

 

Sélectionnez

SQL> Declare
  2    LN$I pls_integer := 0 ;
  3    LN$J pls_integer := 0 ;  
  4  Begin
  5    <<boucle1>>
  6    Loop
  7      LN$I := LN$I + 1 ;
  8      Loop
  9         LN$J := LN$J + 1 ;
 10         dbms_output.put_line( to_char( LN$I ) || ',' || to_char( LN$J ) ) ;
 11         EXIT boucle1 WHEN LN$J > 3 ;
 12      End loop ;
 13    End loop ;
 14  End ;
 15  /
1,1
1,2
1,3
1,4

Procédure PL/SQL terminée avec succès.

Dans cet exemple, l'instruction EXIT suivie du label boucle1 permet de quitter les deux boucles imbriquées

1-2-8. FETCH▲

Cette instruction permet de ramener une ligne d'un curseur préalablement ouvert avec l'instruction OPEN ou OPEN FOR

nom curseur représente le nom d'un curseur préalablement ouvert avec l'instruction OPEN ou OPEN FOR
nom variable curseur représente le nom d'une variable curseur
nom variable curseur hôte représente le nom d'une variable curseur transmise par un programme tiers (ex : Pro*C, Pro*Cobol, etc.)
variable représente le nom d'une variable préalablement définie dans la section déclarative, qui doit être du même type que la colonne ramenée par l'instruction Select
enregistrement représente le nom d'un enregistrement préalablement défini dans la section déclarative qui doit être du même type que la ligne ramenée par l'instruction Select
nom collection représente le nom d'une collection préalablement définie dans la section déclarative
nom tableau hôte représente le nom du tableau transmis par un programme tiers

Si l'instruction FETCH ne ramène plus de ligne (fin du curseur) l'attribut %NOTFOUND prend la valeur TRUE et l'attribut %FOUND prend la valeur FALSE

-> Pour voir des exemples, reportez-vous à la section Les curseurs explicites (1.2.22)

1-2-9. FORALL▲

Cette instruction permet de générer des ordres SQL de masse basés sur le contenu d'une collection

variable index représente l'indice de la collection sur laquelle porte l'instruction FORALL
borne début représente la valeur d'indice de départ
borne fin représente la valeur d'indice de fin
instruction sql doit être un ordre SQL de type INSERT, UPDATE ou DELETE

(10g)
FORALL i IN INDICES OF nom_collection

permet de ne traiter que les indices valorisés de la collection (non forcément consécutifs comme il était impératif dans les versions précédentes)

Pour voir des exemples, reportez-vous au chapitre 5 Collections et enregistrements

1-2-10. GOTO▲

Cette instruction permet d'exécuter un saut dans le code vers le label precisé

Une instruction valide doit suivre la déclaration du label

 

Sélectionnez

SQL> Declare
  2    LN$I pls_integer := 0 ;
  3    LN$J pls_integer := 0 ;  
  4  Begin
  5    Loop
  6      LN$I := LN$I + 1 ;
  7      Loop
  8         LN$J := LN$J + 1 ;
  9         dbms_output.put_line( to_char( LN$I ) || ',' || to_char( LN$J ) ) ;
 10         If LN$J > 3  Then GOTO sortie ; End if ;
 11      End loop ;
 12    End loop ;
 13    <<sortie>>
 14    null ;
 15  End ;
 16  /
1,1
1,2
1,3
1,4

Procédure PL/SQL terminée avec succès.

1-2-11. IF▲

Cette instruction permet de faire des tests conditionnels

expression booléenne représente un test générant un booléen TRUE ou FALSE

Seuls les mots clés IF et END IF; sont obligatoires. Les clauses ELSIF et ELSE sont facultatives

 

Sélectionnez

SQL> Declare
  2    LN$I pls_integer := 0 ;
  3    LN$J pls_integer := 0 ;  
  4  Begin
  5    Loop
  6      LN$I := LN$I + 1 ;
  7      Loop
  8        LN$J := LN$J + 1 ;
  9        If LN$J = 1 Then
 10           dbms_output.put_line( '1' ) ;
 11        Elsif LN$J = 2 Then
 12           dbms_output.put_line( '2' ) ;
 13        Else
 14           dbms_output.put_line( '3' ) ;
 15           goto sortie ;
 16        End if ;
 17      End loop ;
 18    End loop ;
 19    <<sortie>>
 20    null ;
 21  End ;
 22  /
1
2
3

Procédure PL/SQL terminée avec succès.

1-2-12. CASE▲

SQL> Declare
  2    LN$Num pls_integer := 0 ;
  3  Begin
  4      Loop
  5        LN$Num := LN$Num + 1 ;
  6        CASE LN$Num
  7          WHEN 1 Then dbms_output.put_line( '1' ) ;
  8          WHEN 2 Then dbms_output.put_line( '2' ) ;
  9          WHEN 3 Then dbms_output.put_line( '3' ) ;
 10          ELSE      
 11             EXIT ;
 12        END CASE ;
 13      End loop ;
 14  End ;
 15  /
1
2
3

Procédure PL/SQL terminée avec succès.

SQL> Declare
  2    LN$Num pls_integer := 0 ;
  3  Begin
  4     Loop
  5        LN$Num := LN$Num + 1 ;
  6        CASE
  7          WHEN LN$Num between 1 and 3  Then dbms_output.put_line( To_char( LN$Num ) || ' -> 1-3' ) ;
  8          WHEN LN$Num < 5  Then dbms_output.put_line( To_char( LN$Num ) || ' < 5' ) ;
  9          ELSE dbms_output.put_line( To_char( LN$Num ) || ' >= 5' ) ;
 10        END CASE ;
 11        exit when LN$Num = 5 ;
 12      End loop ;
 13  End ;
 14  /
1 -> 1-3
2 -> 1-3
3 -> 1-3
4 < 5
5 >= 5

Procédure PL/SQL terminée avec succès.

SQL> Declare
  2    LN$Num pls_integer := 0 ;
  3  Begin
  4     Loop
  5        LN$Num := LN$Num + 1 ;
  6        CASE
  7          WHEN LN$Num between 1 and 3  Then dbms_output.put_line( To_char( LN$Num ) || ' -> 1-3' ) ;
  8          WHEN LN$Num < 5  Then dbms_output.put_line( To_char( LN$Num ) || ' -> < 5' ) ;
  9        END CASE ;
 10        exit when LN$Num = 5 ;
 11      End loop ;
 12  End ;
 13  /
1 -> 1-3
2 -> 1-3
3 -> 1-3
4 -> < 5
Declare
*
ERREUR à la ligne 1 :
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 6

1-2-13. FOR (curseur)▲

Cette instruction permet de gérer un curseur sans utiliser les ordres OPEN, FETCH et CLOSE

enregistrement représente un nom de variable de type curseur implicite.
nom curseur représente le nom d'un curseur préalablement défini dans la section déclarative

 

Sélectionnez

SQL> Declare
  2     -- Déclaration du curseur
  3     CURSOR C_EMP IS
  4     Select
  5        *
  6     From
  7        EMP
  8     Where
  9        job = 'CLERK'
 10     ;
 11  
 12  Begin
 13     For Cur IN C_EMP Loop
 14       dbms_output.put_line( To_char( Cur.empno ) || ' - ' || Cur.ename ) ;
 15     End loop ;
 16  End ; 
 17  /
7369 - SMITH
7876 - ADAMS
7900 - JAMES
7934 - MILLER

Procédure PL/SQL terminée avec succès.

La variable de curseur implicite Cur, non définie dans la section déclarative, doit être utilisée pour manipuler dans la boucle,
les objets du curseur (To_char( Cur.empno ),Cur.ename)
Après l'instruction END LOOP; l'utilisation de cette variable génère une erreur

Avec cette syntaxe, l'utilisation des instructions OPEN, FETCH et CLOSE est inutile

Instruction FOR et curseur paramétré

 

Sélectionnez

SQL> Declare
  2     -- Déclaration du curseur
  3     CURSOR C_EMP ( PC$Job IN EMP.job%Type ) IS
  4     Select
  5        *
  6     From
  7        EMP
  8     Where
  9        job = PC$Job
 10     ;
 11  
 12  Begin
 13     For Cur IN C_EMP( 'SALESMAN' ) Loop
 14       dbms_output.put_line( To_char( Cur.empno ) || ' - ' || Cur.ename ) ;
 15     End loop ;
 16  End ;
 17  /
7499 - ALLEN
7521 - WARD
7654 - MARTIN
7844 - TURNER

Procédure PL/SQL terminée avec succès.

Le passage des paramètres s'effectue sur le curseur déclaré (C_EMP) et non sur la variable curseur (Cur)

1-2-14. FOR, LOOP, WHILE▲

Ces instructions déclarent une structure de type itérative (boucle)

Trois syntaxes sont possibles

• LOOP instruction;[instruction;[...]] END LOOP;

Cette syntaxe met en place une boucle simple ou aucune condition de sortie n'est indiquée
Il faut donc une instruction EXIT pour sortir de ce type de boucle

 

Sélectionnez

SQL> Declare
  2    LN$I pls_integer := 0 ;
  3  Begin
  4    Loop
  5      LN$I := LN$I + 1 ;
  6      dbms_output.put_line( to_char( LN$I) ) ;
  7      exit when LN$I > 2 ;
  8    End loop ;
  9  End ;
 10  /
1
2
3

• WHILE expression booléenne LOOP instruction;[instruction;[...]] END LOOP;

Cette syntaxe permet de mettre en place une boucle dont la condition de test est évaluée au début.
Si expression booléenne donne le résultat FALSE, les instructions suivantes jusqu'au mot clé END LOOP; ne seront pas exécutées

 

Sélectionnez

SQL> Declare
  2    LN$I pls_integer := 0 ;
  3  Begin
  4    While LN$I < 3
  5    Loop
  6      LN$I := LN$I + 1 ;
  7      dbms_output.put_line( to_char( LN$I) ) ;
  8    End loop ;
  9  End ;
 10  /
1
2
3

• FOR variable index IN [REVERSE] borne_début..borne_fin LOOP instruction;[instruction;[...]] END LOOP;

Cette syntaxe permet de mettre en place une boucle dont le nombre d'itérations est fixé dès l'entrée
Variable index représente le nom de la variable qui servira d'indice. Cette variable ne nécessite pas de définition préalable dans la section déclarative
Reverse permet de faire varier l'indice dans le sens contraire (décrémentation)
borne début représente l'indice de départ
borne fin représente l'indice de fin

 

Sélectionnez

SQL> Declare
  2    LN$I pls_integer := 0 ;
  3  Begin
  4    For i in 1..3
  5    Loop
  6      dbms_output.put_line( to_char( i ) ) ;
  7    End loop ;
  8  End ;
  9  /
1
2
3

 

Sélectionnez

SQL> Declare
  2    LN$I pls_integer := 0 ;
  3  Begin
  4    For i in reverse 1..3
  5    Loop
  6      dbms_output.put_line( to_char( i ) ) ;
  7    End loop ;
  8  End ;
  9  /
3
2
1

1-2-15. NULL▲

1-2-16. RAISE▲

Cette instruction permet de générer une exception

nom exception représente soit le nom d'une exception prédéfinie, soit une exception utilisateur définie dans la section déclarative

 

Sélectionnez

SQL> Declare
  2    LN$I pls_integer := 0 ;
  3    LE$Fin exception ;
  4  Begin
  5    Loop
  6      LN$I := LN$I + 1 ;
  7      dbms_output.put_line( to_char( LN$I) ) ;
  8      If LN$I > 2 Then
  9         RAISE LE$Fin ;
 10      End if ;
 11    End loop ;
 12  Exception
 13     When LE$Fin Then
 14        Null ;  
 15  End ;
 16  /
1
2
3

Si la variable LN$I est > 2, alors on provoque le saut dans la section EXCEPTION avec l'erreur utilisateur LE$Fin

1-2-17. RETURN▲

Cette instruction permet de sortir d'une procédure ou d'une fonction

expression représente la valeur de retour d'une fonction. Cette valeur doit être compatible avec le type défini dans la clause RETURN de la déclaration de fonction

1-2-18. SAVEPOINT▲

1-2-19. ROLLBACK▲

Cette instruction permet d'annuler en base toutes les modifications effectuées au cours de la transaction

nom savepoint représente le nom d'une étiquette savepoint préalablement définie dans le corps du code avec l'instruction SAVEPOINT
Avec TO SAVEPOINT nom savepoint, l'annulation porte sur toutes les modifications effectuées à partir de l'étiquette nom savepoint

 

Sélectionnez

SQL> Begin
  2     Insert Into EMP( empno, ename, job )
  3       values( 9991, 'Dupontont', 'CLERK' ) ;
  4     Insert Into EMP( empno, ename, job )
  5       values( 9992, 'Duboudin', 'CLERK' ) ;
  6    
  7     SAVEPOINT mise_a_jour ;
  8     
  9     Update EMP Set sal = 2500 Where empno > 9990 ;     
 10     ROLLBACK TO SAVEPOINT mise_a_jour ;
 11     
 12     Commit ;
 13  End ;
 14  /

Procédure PL/SQL terminée avec succès.

SQL> Select * From EMP Where empno > 9990 ;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      9991 Dupontont  CLERK
      9992 Duboudin   CLERK

Dans cet exemple, une étiquette SAVEPOINT est placée après les instructions d'insertion
Un ROLLBACK TO SAVEPOINT est ajouté après l'instruction de mise à jour
puis un COMMIT est effectué

Les insertions sont bien enregistrées en base mais pas la mise à jour

1-2-20. SELECT INTO▲

Sélection d'une ou de plusieurs lignes

Cette instruction permet d'exécuter un ordre Select implicite.
Cet ordre ne doit ramener qu'une ligne sous peine de générer l'exception NO_DATA_FOUND si aucune ligne n'est ramenée
ou TOO_MANY_ROWS si plus d'une ligne est ramenée.

Utilisée avec la clause BULK COLLECT, elle permet de charger une collection avec les lignes ramenées

item représente un littérale ou un nom de colonne
nom variable représente le nom d'une variable d'accueil. Son type doit être identique à celui de item
nom record représente le nom d'un enregistrement composé de champs de même type que les items ramenés
nom collection représente le nom d'une collection d'accueil
nom tableau hôte représente le nom de la variable tableau passée par un programme tiers
référence table représente la liste des tables et/ou vues de l'ordre SQL
sous-requête représente le texte d'une sous-requête SQL conforme
suite instruction représente la suite de l'ordre Select (clauses Where, Group by, Order by, etc.)

 

Sélectionnez

SQL> Declare
  2    LN$Num EMP.empno%Type ;
  3    LC$Nom EMP.ename%Type ;
  4    LC$Job EMP.job%Type ;
  5  Begin  
  6    Select
  7       empno
  8      ,ename
  9      ,job
 10    Into
 11       LN$Num
 12      ,LC$Nom
 13      ,LC$Job   
 14    From 
 15      EMP
 16    Where 
 17      empno = 7369
 18    ;
 19  End ; 
 20  /

Procédure PL/SQL terminée avec succès.

Dans l'exemple suivant, aucun employé ne porte le numéro 1
la requête ne ramène donc aucune ligne et génère l'exception NO_DATA_FOUND

 

Sélectionnez

SQL> Declare
  2    LN$Num EMP.empno%Type ;
  3    LC$Nom EMP.ename%Type ;
  4    LC$Job EMP.job%Type ;
  5  Begin  
  6    Select
  7       empno
  8      ,ename
  9      ,job
 10    Into
 11       LN$Num
 12      ,LC$Nom
 13      ,LC$Job   
 14    From 
 15      EMP
 16    Where
 17      empno = 1
 18    ;
 19  End ;
 20  /
Declare
*
ERREUR à la ligne 1 :
ORA-01403: Aucune donnée trouvée
ORA-06512: à ligne 7

Dans l'exemple suivant, la clause WHERE a été retirée
la requête ramène donc plusieurs lignes et génère l'exception TOO_MANY_ROWS

 

Sélectionnez

SQL> Declare
  2    LN$Num EMP.empno%Type ;
  3    LC$Nom EMP.ename%Type ;
  4    LC$Job EMP.job%Type ;
  5  Begin  
  6    Select
  7       empno
  8      ,ename
  9      ,job
 10    Into
 11       LN$Num
 12      ,LC$Nom
 13      ,LC$Job   
 14    From 
 15      EMP
 16    ;
 17  End ; 
 18  /
Declare
*
ERREUR à la ligne 1 :
ORA-01422: l'extraction exacte ramène plus que le nombre de lignes demandé
ORA-06512: à ligne 7

Dans l'exemple suivant toute une ligne de la table EMP est chargée dans un enregistrement

 

Sélectionnez

SQL> Declare
  2    LR$Emp EMP%Rowtype ;
  3  Begin  
  4    Select
  5       *
  6    Into
  7       LR$Emp
  8    From 
  9      EMP
 10    Where
 11      empno = 7369
 12    ;
 13  End ; 
 14  /

Procédure PL/SQL terminée avec succès.

Dans l'exemple suivant toutes les lignes de la table EMP sont chargées dans une collection

 

Sélectionnez

SQL> Declare
  2    TYPE TYP_TAB_EMP IS TABLE OF EMP%Rowtype ;
  3    Tabemp TYP_TAB_EMP ;
  4  Begin  
  5    Select
  6       *
  7    BULK COLLECT
  8    Into
  9       Tabemp
 10    From 
 11      EMP
 12    ;
 13    For i IN Tabemp.first..Tabemp.last Loop
 14      dbms_output.put_line( To_char( Tabemp(i).empno ) || ' - ' || Tabemp(i).ename ) ;
 15    End loop ;
 16  End ;
 17  /
7369 - SMITH
7499 - ALLEN
7521 - WARD
7566 - JONES
7654 - MARTIN
7698 - BLAKE
7782 - CLARK
7788 - SCOTT
7839 - KING
7844 - TURNER
7876 - ADAMS
7900 - JAMES
7902 - FORD
7934 - MILLER

Procédure PL/SQL terminée avec succès.

1-2-21. Instruction SQL▲

1-2-22. Les curseurs explicites▲

Un curseur est une zone mémoire de taille fixe, utilisée par le moteur SQL pour analyser et interpréter un ordre SQL
Un curseur explicite, contrairement au curseur implicite (SELECT INTO) est géré par l'utilisateur
pour traiter un ordre Select qui ramène plusieurs lignes

Tout curseur explicite géré dans la section exécution doit avoir été déclaré dans la section déclarative

• Déclaration

nom curseur représente le nom du curseur que l'on déclare
déclaration des paramètres(facultatif) représente la liste des paramètres transmis au curseur
instruction select représente l'ordre SQL Select d'alimentation du curseur

 

Sélectionnez

SQL> Declare
  2    -- déclaration du curseur
  3    CURSOR C_EMP IS
  4    Select
  5       empno
  6      ,ename
  7      ,job
  8    From
  9      EMP 
 10    ;
 11    -- variables d'accueil 
 12    LN$Num EMP.empno%Type ;
 13    LC$Nom EMP.ename%Type ;
 14    LC$Job EMP.job%Type ; 
 15  Begin  
 16    Open C_EMP ; -- ouverture du curseur
 17    Loop -- boucle sur les lignes
 18      Fetch C_EMP Into LN$Num, LC$Nom, LC$Job ; -- Lecture d'une ligne
 19      Exit When C_EMP%NOTFOUND ; -- sortie lorsque le curseur ne ramène plus de ligne
 20    End loop ;
 21    Close C_EMP ; -- fermeture du curseur
 22  End ;
 23  /

Procédure PL/SQL terminée avec succès.

Un curseur nommé C_EMP est déclaré avec l'ordre Select correspondant (CURSOR C_EMP IS...)
Il est ouvert avec l'instruction OPEN
lu avec l'instruction FETCH
et fermé avec l'instruction CLOSE

• Paramètres du curseur

Un curseur est paramétrable. On peut donc utiliser le même curseur pour obtenir différents résultats

nom paramètre représente le nom de la variable paramètre
datatype représente le type SQL de la variable paramètre (doit correspondre en type avec la colonne visée)
expression représente la valeur par défaut du paramètre (doit correspondre en type avec celui du paramètre)

 

Sélectionnez

SQL> Declare
  2    -- déclaration du curseur
  3    CURSOR C_EMP ( PN$Num IN EMP.empno%Type )IS
  4    Select
  5       empno
  6      ,ename
  7      ,job
  8    From
  9      EMP 
 10    Where
 11      empno = PN$Num
 12    ;
 13    -- variables d'accueil 
 14    LN$Num EMP.empno%Type ;
 15    LC$Nom EMP.ename%Type ;
 16    LC$Job EMP.job%Type ; 
 17  Begin  
 18    Open C_EMP( 7369 ) ; -- ouverture du curseur avec passage du paramètre 7369
 19    Loop
 20      Fetch C_EMP Into LN$Num, LC$Nom, LC$Job ; -- Lecture d'une ligne
 21      Exit When C_EMP%NOTFOUND ; -- sortie lorsque le curseur ne ramène plus de ligne
 22      dbms_output.put_line( 'Employé ' || To_char(LN$Num) || ' ' || LC$Nom ) ; 
 23    End loop ;
 24    Close C_EMP ; -- fermeture du curseur
 25    Open C_EMP( 7521 ) ; -- ouverture du curseur avec passage du paramètre 7521
 26    Loop
 27      Fetch C_EMP Into LN$Num, LC$Nom, LC$Job ; -- Lecture d'une ligne
 28      Exit When C_EMP%NOTFOUND ; -- sortie lorsque le curseur ne ramène plus de ligne
 29      dbms_output.put_line( 'Employé ' || To_char(LN$Num) || ' ' || LC$Nom ) ; 
 30    End loop ;
 31    Close C_EMP ; -- fermeture du curseur  
 32  End ;  
 33  /
Employé 7369 SMITH
Employé 7521 WARD

Procédure PL/SQL terminée avec succès.

• Déclaration d'une variable curseur

nom variable curseur représente le nom de la variable curseur déclarée
nom type représente le nom d'un type curseur

 

Sélectionnez

SQL> Declare
  2     TYPE TYP_REF_CUR IS REF CURSOR ;
  3     -- variable curseur
  4     CEMP TYP_REF_CUR ;
  5     -- variables d'accueil 
  6     LN$Num EMP.empno%Type ;
  7     LC$Nom EMP.ename%Type ;
  8     LC$Job EMP.job%Type ; 
  9  Begin  
 10     Open CEMP For 'Select empno, ename, job From EMP'; -- ouverture du curseur
 11     Loop
 12       Fetch CEMP Into LN$Num, LC$Nom, LC$Job ; -- Lecture d'une ligne
 13       Exit When CEMP%NOTFOUND ; -- sortie lorsque le curseur ne ramène plus de ligne
 14     End loop ;
 15     Close CEMP ; -- fermeture du curseur
 16  End ;
 17  /

Procédure PL/SQL terminée avec succès.

• Spécification d'un curseur

Les attributs de curseur

Chaque curseur dispose de 4 attributs

• %FOUND

Cet attribut prend la valeur TRUE lorsqu'une ligne est ramenée, sinon il prend la valeur FALSE

• %NOTFOUND

Cet attribut prend la valeur FALSE lorsqu'une ligne est ramenée, sinon il prend la valeur TRUE

• %ISOPEN

Cet attribut prend la valeur TRUE lorsque le curseur indiqué est ouvert, sinon il prend la valeur FALSE

• %ROWCOUNT

Cet attribut retourne le nombre de lignes impactées par la dernière instruction SQL

1-2-23. Portée des variables▲

SQL>  Begin
  2  
  3     Declare
  4       LC$Ch1    varchar2(10) := 'Phrase 2';
  5     Begin
  6        dbms_output.put_line( 'LC$Ch1 = ' || LC$Ch1 ) ;
  7     End ;
  8  
  9     dbms_output.put_line( 'LC$Ch1 = ' || LC$Ch1 ) ;
 10     
 11   End ;
 12  /
   dbms_output.put_line( 'LC$Ch1 = ' || LC$Ch1 ) ;
                                        *
ERREUR à la ligne 9 :
ORA-06550: line 9, column 41:
PLS-00201: identifier 'LC$CH1' must be declared
ORA-06550: line 9, column 4:
PL/SQL: Statement ignored

SQL>  Declare
  2     LC$Ch1    varchar2(10) := 'Phrase 1';
  3   Begin
  4  
  5     Declare
  6       LC$Ch1    varchar2(10) := 'Phrase 2';
  7     Begin
  8        dbms_output.put_line( 'LC$Ch1 = ' || LC$Ch1 ) ;
  9     End ;
 10  
 11     dbms_output.put_line( 'LC$Ch1 = ' || LC$Ch1 ) ;
 12     
 13   End ;
 14  /
LC$Ch1 = Phrase 2
LC$Ch1 = Phrase 1

Procédure PL/SQL terminée avec succès.

Débutée par le mot clé EXCEPTION, elle contient le code mis en oeuvre pour la gestion des erreurs générées par la section d'exécution

Une erreur survenue lors de l'exécution du code déclenche ce que l'on nomme une exception. Le code erreur associé est transmis à la section EXCEPTION, pour vous laisser la possibilité de la gérer et donc de ne pas mettre fin prématurément à l'application.

Prenons l'exemple suivant :

Nous souhaitons retrouver la liste des employés dont la date d'entrée est inférieure au premier janvier 1970

SQL> Declare
  2    LC$Nom EMP.ename%Type ;
  3  Begin
  4   Select empno
  5   Into LC$Nom
  6   From EMP
  7   Where hiredate < to_date('01/01/1970','DD/MM/YYYY') ;
  8  End ; 
  9  /
Declare
*
ERREUR à la ligne 1 :
ORA-01403: Aucune donnée trouvée
ORA-06512: à ligne 4

Comme la requête ne ramène aucune ligne, l'exception prédéfinie NO_DATA_FOUND est générée et transmise à la section EXCEPTION qui peut traiter le cas et poursuivre l'exécution de l'application.

L'exception NO_DATA_FOUND (ORA_01403) correspond au code erreur numérique +100.

Il existe des milliers de codes erreur Oracle et il serait vain de tous leur donner un libellé.
Voici la liste des exceptions prédéfinies qui bénéficient de ce traitement de faveur :

Toutes les autres exceptions doivent être interceptées via leur code erreur numérique.

En plus des erreurs Oracle, vous pouvez intercepter vos propres erreurs en déclarant des variables dont le type est exception et en provoquant vous-même le saut dans la section de gestion des erreurs à l'aide de l'instruction RAISE

DECLARE
   LE$Fin  Exception ;
   …
Begin
   …..
   Raise LE$Fin ;
   …
EXCEPTION
   WHEN LE$Fin Then
       ……….
END ;

Il n'est pas possible de déclarer la même exception deux fois dans le même bloc. Toutefois, dans le cas de blocs imbriqués, vous pouvez déclarer la même exception dans la section EXCEPTION de chaque bloc

DECLARE
   LE$Fin  Exception ;
BEGIN
   DECLARE
      LE$Fin  Exception ;
   BEGIN
      ...
   EXCEPTION
      WHEN LE$Fin Then
         ...
   END ;
EXCEPTION
   WHEN LE$Fin Then
       ...
END ;

Le peu d'exceptions prédéfinies vous oblige à traiter tous les autres cas dans la clause WHEN OTHERS en testant le code erreur SQL

EXCEPTION
   WHEN NO_DATA_FOUND Then
       ...
   WHEN OTHERS THEN
     If SQLCODE = … Then …
     Elsif SQLCODE = … Then …
        ...
     End if ;
END;

Vous pouvez associer un code erreur Oracle à vos propres variables exception à l'aide du mot clé PRAGMA EXCEPTION_INIT, dans le cadre de la section déclarative de la façon suivante :

Nom_exception EXCEPTION ;
PRAGMA EXCEPTION_INIT(nom_exception, -code_error_oracle);

Exemple :
Lorsque l'on tente d'insérer plus de caractères dans une variable que sa déclaration ne le permet, Oracle déclenche une erreur -6502. Nous allons "nommer" cette erreur en LE$trop_long et l'intercepter dans la section exception

SQL> Declare
  2    LC$Chaine varchar2(10) ;
  3    LE$trop_long exception ;
  4    pragma exception_init( LE$trop_long, -6502 ) ;
  5  Begin
  6    LC$Chaine := rpad( ' ', 30) ;
  7  Exception
  8    when LE$trop_long then
  9        dbms_output.put_line( 'Chaîne de caractères trop longue') ;
 10  End ; 
 11  /
Chaîne de caractères trop longue

Procédure PL/SQL terminée avec succès.

SQL>

Le code erreur numérique Oracle ayant généré la plus récente erreur est récupérable en interrogeant la fonction SQLCODE.
Le libellé erreur associé est récupérable en interrogeant la fonction SQLERRM

SQL> Declare
  2    LC$Chaine varchar2(10) ;
  3  Begin
  4    LC$Chaine := rpad( ' ', 30) ;
  5  Exception
  6    when others then
  7       dbms_output.put_line( 'Code    erreur : ' || to_char( SQLCODE )) ;
  8       dbms_output.put_line( 'libellé erreur : ' || to_char( SQLERRM )) ; 
  9  End ;
 10  /
Code    erreur : -6502
libellé erreur : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Procédure PL/SQL terminée avec succès.

Poursuite de l'exécution après l'interception d'une exception

Une fois dans la section EXCEPTION, il n'est pas possible de retourner dans la section exécution juste après l'instruction qui a généré l'erreur.

Par contre il est tout à fait possible d'encadrer chaque groupe d'instructions voire même chaque instruction avec les mots clés
BEGIN … EXCEPTION … END;
Cela permet de traiter l'erreur et de continuer l'exécution

  1   Declare
  2     LC$Ch1    varchar2(20) := 'Phrase longue';
  3     LC$Chaine varchar2(10) ;
  4     LE$trop_long exception ;
  5     pragma exception_init( LE$trop_long, -6502 ) ;
  6   Begin
  7     Begin
  8        LC$Chaine := LC$Ch1;
  9     Exception
 10       when LE$trop_long then
 11           LC$Chaine := Substr( LC$Ch1, 1, 10 ) ;
 12     End ;
 13     -- poursuite du traitement --
 14     dbms_output.put_line(LC$Chaine ) ;
 15*  End ;
 16  /
Phrase lon

Procédure PL/SQL terminée avec succès.

Vous pouvez également définir vos propres messages d'erreur avec la procédure RAISE_APPLICATION_ERROR

DBMS_STANDARD.raise_application_error(numero_erreur, message[, {TRUE | FALSE}])

numero_erreur représente un entier négatif compris entre -20000 et -20999
message représente le texte du message d'une longueur maximum de 2048 octets
TRUE indique que l'erreur est ajoutée à la pile des erreurs précédentes
FALSE indique que l'erreur remplace toutes les erreurs précédentes

Du fait que cette procédure appartienne a un paquetage standard, il n'est pas nécessaire de préfixer cette procédure

L'appel de la procédure raise_application_error ne peut être exécuté que depuis une procédure stockée, et déclenche un retour immédiat au programme appelant en lui transmettant le code et le libellé de l'erreur

Propagation des exceptions

Si une exception n'est pas traitée au sein du bloc BEGIN … END; dans lequel elle est générée,
elle remonte de bloc en bloc jusqu'à ce qu'elle soit traitée ou rende la main au programme appelant.

Dans cet exemple, l'exception A est traitée dans le bloc local.
Le traitement se poursuit dans le bloc parent

Dans cet exemple, l'exception B n'est pas traitée dans le bloc local.
Elle se propage dans le bloc parent dans lequel elle est traitée
Puis la main est rendue au programme appelant

Dans cet exemple, l'exception C n'est traitée ni dans le bloc local ni dans les blocs parents
la main est rendue au programme appelant avec le code erreur


Commentaires dans les blocs PL/SQL

Pour mettre une ligne unique en commentaire, il faut la faire précéder d'un double tiret --

-- cette ligne seule est un commentaire

Pour mettre plusieurs lignes en commentaire, il faut les encadrer avec les symboles /* et */

/* toutes les lignes suivantes
sont en commentaire
elles ne seront ni compilées
ni exécutées
*/

nom variable [CONSTANT] type [ [NOT NULL] := expression ] ;

nom variable représente le nom de la variable composé de lettres, chiffres, $, _ ou #
Le nom de la variable ne peut pas excéder 30 caractères

CONSTANT indique que la valeur ne pourra pas être modifiée dans le code du bloc PL/SQL

NOT NULL indique que la variable ne peut pas être NULL, et dans ce cas expression doit être indiqué.

type représente de type de la variable correspondant à l'un des types suivants :


(dans le premier tableau, les types Oracle sont en gras, les sous-types compatible ANSI/ISO en normal)

Vous pouvez également créer des sous-types :

SUBTYPE nom_sous-type IS type ;

SUBTYPE entier_court IS SMALLINT ;
i entier_court ;

Et utiliser les types dérivés

%TYPE
référence à un type existant qui est soit une colonne d'une table soit un type défini précédemment

nom_variable nom_table.nom_colonne%TYPE ;
nom_variable nom_variable_ref%TYPE ;

%ROWTYPE
référence à une ligne d'une table ou d'un curseur

nom_variable nom_table%ROWTYPE ;
nom_variable nom_curseur%ROWTYPE ;

Declare
  -- variable de même type que la colonne ENAME de la table EMP
  LC$Nom	EMP.ENAME%TYPE ;
  -- variable de même type qu'une ligne de la table EMP
  LR$EMP    EMP%ROWTYPE ;
  LC$Dat1	DATE ;
  -- variable de même type que LC$Dat1 (DATE)
  LC$Dat2   LC$Dat1%TYPE ;
  -- Curseur --
  Cursor C_EMP is
  Select empno, ename, job From EMP ;
  -- variable de type ligne du curseur C_EMP
  LR$C_emp C_EMP%ROWTYPE ;

2-2-1. Types caractères▲

2-2-2. Types numériques▲

2-2-3. Types pour les grands objets▲

2-2-4. Types supplémentaires▲

En plus des types prédéfinis, l'utilisateur peut définir ses propres types avec le mot clé TYPE ou SUBTYPE

• SUBTYPE nom_sous-type IS type_base[(précision)] [NOT NULL]

nom_sous-type représente le nom du sous-type déclaré
type_base représente le nom du type prédéfini
précision représente une longueur pour les caractères et longueur + décimales pour les numériques

SUBTYPE chaine_courte IS VARCHAR2(10);
le sous-type utilisateur chaine_courte définit un VARCHAR2(10)
à la suite de cette définition, toute déclaration de variable de type chaine_courte sera égale à VARCHAR2(10)

SUBTYPE chaine IS VARCHAR2(100);
le sous-type utilisateur chaine définit un VARCHAR2(100)

SUBTYPE chaine_longue IS VARCHAR2(1000);
le sous-type utilisateur chaine_longue définit un VARCHAR2(1000)

SUBTYPE NOM_EMP IS EMP.ename%Type;
le sous-type NOM_EMP définit un type identique à la colonne ename de la table EMP

SUBTYPE REC_EMP IS EMP%ROWTYPE;
le sous-type REC_EMP définit un type identique à une ligne de la table EMP



TYPE tableau_numerique IS TABLE OF NUMBER;
le type tableau_numerique définit un tableau de NUMBER

TYPE TAB_REC_EMP IS TABLE OF REC_EMP;
le type TAB_REC_EMP définit un tableau d'éléments de type REC_EMP
Lorsque vos nouveaux types et sous-types sont déclarés, vous pouvez les utiliser pour typer de nouvelles variables

SQL> Declare
  2    -- définition des types et sous-types
  3    SUBTYPE chaine_courte IS VARCHAR2(10);
  4    SUBTYPE chaine IS VARCHAR2(100);
  5    SUBTYPE chaine_longue IS VARCHAR2(1000);
  6    TYPE tableau_numerique IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  7    SUBTYPE NOM_EMP IS EMP.ename%Type;
  8    SUBTYPE REC_EMP IS EMP%ROWTYPE;
  9    TYPE TAB_REC_EMP IS TABLE OF REC_EMP;
 10    -- définition des variables
 11    LC$cc chaine_courte;
 12    LC$cl chaine_longue;
 13    Tab   tableau_numerique;
 14  Begin
 15    LC$cc := 'Court' ;
 16    LC$cl := 'Chaîne beaucoup plus longue' ;
 17    dbms_output.put_line( 'Chaîne longue = ' || LC$cl ) ;
 18    For i in 1..5 Loop
 19       Tab(i) := i + (.1 * i ) ;
 20    dbms_output.put_line( 'Tab(' || Ltrim( To_char( i ) ) || ') = ' || To_char( Tab(i) ) ) ;
 21    End loop ;
 22  End ;
 23  /
Chaîne longue = Chaîne beaucoup plus longue
Tab(1) = 1,1
Tab(2) = 2,2
Tab(3) = 3,3
Tab(4) = 4,4
Tab(5) = 5,5

Procédure PL/SQL terminée avec succès.

Un littéral ou valeur constante désigne une valeur fixe.
Par exemple 'LUNDI', 'Montpellier', '2012' représentent des valeurs littérales de type caractère.
12.3, 25 représentent des valeurs littérales de type numérique

Ces valeurs peuvent apparaître dans des initialisations de variables, des calculs ou transmises à des procédures ou fonctions.

2-4-1. Littéral de type caractère▲

2-4-2. Littéral de type entier▲

2-4-3. Littéral de type décimal▲

2-4-4. Littéral de type intervalle (9i)▲

• Concaténation de chaînes

2 syntaxes :

chaîne || chaîne

CONCAT( chaîne, chaîne )

• Remplissage à gauche ou à droite d'une chaîne

Ces fonctions formatent une chaîne sur une longueur donnée par ajout de caractères avant (LPAD) ou après (RPAD) la chaîne passée en argument

LPAD( chaîne, longueur [, 'caractères'] )
RPAD( chaîne, longueur [, 'caractères'] )


chaîne représente le nom d'une colonne, d'une variable ou un littéral
longueur représente le nombre total de caractères du résultat
caractères représente le ou les caractères de remplissage

RPAD( 'Total', 20, '.' )
Total...............

LPAD( 'Hello', 20, '.' )
...............Hello

RPAD( LPAD( '2320 euros', 20, '*' ), 30, '*' )
**********2320 euros**********

LPAD( 'Hello', 20, '-*-' )
-*--*--*--*--*-Hello

Si caractères n'est pas spécifié, le caractère par défaut est l'espace (CHR(32))

• Suppression de caractères à gauche (LTRIM) ou à droite (RTRIM) d'une chaîne

LTRIM( chaîne [, 'caractères'] )
RTRIM( chaîne [, 'caractères'] )

LTRIM( '"Libellé', '"' )
Libellé

RTRIM( 'Libellé----', '-' )
Libellé

LTRIM( RTRIM( '"Libellé"', '"' ), '"' )
Libellé

si caractères n'est pas spécifié, le caractère par défaut est l'espace (CHR(32))

RTRIM( '(Libellé     ') || ')'
(Libellé)

TRIM

Cette fonction permet de cumuler les possibilités de LTRIM et RTRIM

chaîne représente la chaîne de caractères en entrée de la fonction
si LEADING est spécifié, tous les premiers caractères identiques à caractère sont supprimés
si TRAILING est spécifié, tous les derniers caractères identiques à caractère sont supprimés
si BOTH ou rien est spécifié, tous les premiers et derniers caractères identiques à caractère sont supprimés
si caractère n'est pas spécifié la valeur par défaut est l'espace
si seule chaîne est spécifiée tous les espaces en début et fin de chaîne sont supprimés
si caractère ou chaîne est NULL, la fonction retourne NULL

SQL> Declare
  2     LC$Ch1 Varchar(20) := '   libellé   ' ;
  3     LC$Ch2 Varchar(20) := '***libellé***' ;    
  4  Begin  
  5     dbms_output.put_line( TRIM( LC$Ch1 ) ) ;
  6     dbms_output.put_line( TRIM( '*' FROM LC$Ch2 ) ) ;
  7  End ;
  8  /
libellé
libellé

Procédure PL/SQL terminée avec succès.

• Modification de la casse d'une chaîne

LOWER( chaîne )
NLS_LOWER( chaîne [, nls_paramètre ] )
UPPER( chaîne )
NLS_UPPER( chaîne [, nls_paramètre ] )
INITCAP( chaîne )
NLS_INITCAP( chaîne [, nls_paramètre ] )

-- Conversion d'une chaîne en minuscules
LOWER( 'ORACLE' )
oracle

-- Conversion d'une chaîne en majuscules
UPPER( 'oracle' )
ORACLE

-- Conversion d'une chaîne avec la première lettre de chaque mot en majuscule
INITCAP( 'le sgbd oracle' )
Le Sgbd Oracle

• Indication de la longueur d'une chaîne en caractères (LENGTH) ou en octets (LENGTHB)

LENGTH( chaîne )

LENGTH( 'le sgbd oracle' )
14

• Extraction d'une sous-chaîne à partir d'une chaîne

SUBSTR( chaîne, début [, 'nombre'] )

chaîne représente le nom d'une colonne, d'une variable ou un littéral
début représente la position de départ de recherche dans la chaîne
nombre représente le nombre de caractères à extraire

-- extraction de 4 caractères à partir du 4e caractère
SUBSTR( 'le sgbd oracle', 4, 4 )
sgbd

si nombre est omis, la fonction ramène tous les caractères à partir de la position début

SUBSTR( 'le sgbd oracle', 4 )
sgbd oracle

si nombre est négatif l'extraction débute à partir de la fin de la chaîne

SUBSTR( 'le sgbd oracle', -6 )
oracle

• Recherche de la position d'une sous-chaîne dans une chaîne

INSTR( chaîne, sous-chaîne [, début [,nombre occurrences] ] )
INSTRB( chaîne, sous-chaîne [, début [,nombre occurrences] ] )

chaîne représente le nom d'une colonne, d'une variable ou un littéral passé en argument
sous-chaîne représente le nom d'une variable ou un littéral dont on cherche la position
début (optionnel) représente la position de départ de la recherche dans chaîne
nombre occurrences (si début renseigné) représente le nombre d'occurrences trouvées à ignorer

Lorsque sous-chaîne représente plusieurs caractères, la fonction retourne la position du premier caractère de la sous-chaîne

recherche de la première position de la sous-chaîne oracle

INSTR( 'le sgbd oracle', 'oracle' )
9

recherche de la deuxième position de la sous-chaîne oracle

INSTR( 'le sgbd oracle d''oracle corporation', 'oracle', 1, 2 )
18

si la sous-chaîne n'est pas trouvée, la fonction retourne 0

INSTR( 'le sgbd oracle d''oracle corporation', 'texte', 1, 2 )
0

• Remplacement des caractères d'une chaîne par une sous-chaîne

REPLACE( chaîne, chaîne source, chaîne cible )

chaîne représente le nom d'une colonne, d'une variable ou un littéral passé en argument
chaîne source représente le nom d'une variable ou un littéral de recherche
chaîne cible représente le nom d'une variable ou un littéral de remplacement

REPLACE( 'banjaur', 'a', 'o' )
bonjour

Elimination de caractères

REPLACE( '"champ1","champ2"', '"', '' )
champ1,champ2

• Remplacement caractère par caractère dans une chaîne

TRANSLATE( chaîne, chaîne source, chaîne cible )

chaîne représente le nom d'une colonne, d'une variable ou un littéral passé en argument
chaîne source représente le nom d'une variable ou un littéral de recherche
chaîne cible représente le nom d'une variable ou un littéral de remplacement


Chaque caractère présent dans chaîne qui est également présent dans chaîne source est remplacé
par le caractère qui occupe la même position dans chaîne cible

TRANSLATE( 'Pas d''accents : éèàùôö', 'éèàùôö', 'eeauoo' )
Pas d'accents : eeauoo

Dans l'exemple suivant, le caractère _ est remplacé par _ et tous les autres (interdit sous Unix)
sont également remplacés par _

TRANSLATE( 'Nom*de[fichier<unix>', '_ /\<>|(){}[]*&"''$;','___________________' )
Nom_de_fichier_unix_

Elimination de caractères indésirables

TRANSLATE( 'Nom+de|fichier!unix', 'A+|!', 'A' )
Nomdefichierunix

Le premier caractère A de la chaîne source est un leurre qui indique à la fonction de remplacer
toutes les occurrences de A par A et de remplacer les autres caractères (+|!) par rien.

• Fonctions monolignes

ABS( valeur )
Valeur absolue d'un nombre

ABS( 200 ) = 200
ABS( -200 ) = 200

CEIL( valeur)
Entier supérieur ou égal à valeur

CEIL( 5 ) = 5
CEIL( 5.1 ) = 6
CEIL( -5 ) = -5
CEIL( -5.1 ) = -5
CEIL( -5.9 ) = -5

FLOOR( valeur )
Entier inférieur ou égal à valeur

FLOOR( 5 ) = 5
FLOOR( 5.1 ) = 5
FLOOR( -5 ) = -5
FLOOR( -5.1 ) = -6
FLOOR( -5.9 ) = -6

MOD( valeur, diviseur )
Reste d'une division

MOD( 10, 2 ) = 0
MOD( 10, 3 ) = 1
MOD( 10, .6 ) = 0.4
MOD( -10, 2 ) = 0
MOD( -10, .6 ) = -0.4

Lorsque diviseur est supérieur à valeur, la fonction retourne la valeur
MOD( 5, 12 ) = 5

Si valeur est un entier alors MOD( valeur, 1 ) = 0 (idéal pour tester que valeur est entier)

Si valeur est un entier pair alors MOD( valeur, 2 ) = 0

POWER( valeur, exposant )
Elévation d'un nombre à une puissance

POWER( 8, 2 ) = 64
POWER( 8, -2 ) = 0.015625

SQRT( valeur )
Racine carrée d'un nombre

SQRT( 64 ) = 8

EXP( valeur )
e (2,71828182845905) élevé à une puissance

EXP( 5 ) = 148.413159102577

LN( valeur )
Logarithme naturel, ou base e, d'une valeur

LN(148.413159102577 ) = 5

LOG( base, valeur )
Logarithme d'une valeur

LOG( 10, 2 ) = 0.301029995663981

LN( valeur ) est identique à LOG(2,71828182845905 (EXP( 1 ) ), valeur )

ROUND( valeur, précision )
Arrondi d'une valeur à un certain nombre de chiffres de précision

ROUND( 9.254, 1 )
9,3

ROUND( 9.259, 2 )
9,26

ROUND( 9.258, 0 )
9

ROUND( 9.9, 0 )
10

ROUND( 99.259, -1 )
100

TRUNC( valeur, précision )
Suppression d'une partie des chiffres de précision décimale

TRUNC( 9.259, 2 )
9,25

TRUNC( 9.9, 0 )
9

TRUNC( 99.259, -1 )
90

une précision négative est intéressante pour arrondir des nombres sur des tranches de milliers, millions, milliards, etc.

SIGN( valeur )
Signe d'une valeur

SIGN( 12 )
1

SIGN( -12 )
-1

SIN( valeur ), COS( valeur ), TAN( valeur )
Renvoie la valeur trigonométrique d'un angle exprimé en radians

SINH( valeur ), COSH( valeur ), TANH( valeur )
Renvoie la valeur trigonométrique hyperbolique d'un angle exprimée en radians

ASIN( valeur ), ACOS( valeur ), ATAN( valeur )
Renvoie respectivement l'arc sinus, cosinus et tangente en radians

• Fonctions d'agrégation

Ces fonctions portent sur des groupes de lignes et sont utilisées dans les ordres select
(Bien sûr rien n'empêche de coder : AVG(40), mais cela n'a pas beaucoup de sens)

AVG( colonne )
Valeur moyenne des valeurs de colonne
(les valeurs NULL ne sont pas prises en compte)

select AVG( SAL ) from EMP

AVG(SAL)
----------
2073,21429

COUNT( colonne )
Nombre de valeurs de colonne (les valeurs NULL ne sont pas prises en compte)

select COUNT( ENAME ) from EMP;

COUNT(ENAME)
------------
14

MAX( colonne )
Valeur maximum des valeurs de colonne

select ENAME, SAL from emp where SAL = (select MAX( SAL) From EMP );

ENAME SAL
---------- ----------
KING 5000

MIN( colonne )
Valeur minimum des valeurs de colonne

Select MIN( SAL ) From EMP

SUM( colonne )
Somme des valeurs de colonne

Select SUM( SAL ) From EMP

• Fonctions de listes

Ces fonctions portent sur un ensemble de colonnes d'une seule ligne
Elles peuvent être utilisées avec de nombreuses valeurs qui peuvent apparaître sous forme de colonnes, de littéraux, de calculs ou de combinaison d'autres colonnes.

GREATEST( valeur1, valeur2, valeur3,… )
Valeur la plus grande de la liste

GREATEST( 1, 5, 15, 8 )
15

GREATEST( 'Elmer', 'Achille','Richard', 'Josianne' )
Richard

GREATEST( Length('Elmer'), Length('Achille'),Length('Richard'), Length('Josianne') )
8

LEAST( valeur1, valeur2, valeur3,… )
Valeur la plus petite de la liste

LEAST( 1, 5, 15, 8 )
1

LEAST( 3, 5+5, 8, 12-6 )
3

BIN_TO_NUM( bit [,bit[...]] )

Conversion d'une suite de bits en nombre

SQL> SELECT BIN_TO_NUM(1,0,0,0,0,0,0,0,0,0,0,0,0) FROM DUAL ;

BIN_TO_NUM(1,0,0,0,0,0,0,0,0,0,0,0,0)
-------------------------------------
                                 4096

SQL>

BITAND( arg1, arg2 )

Applique un ET logique sur les deux arguments

SELECT order_id, customer_id,
  DECODE(BITAND(order_status, 1), 1, 'Warehouse', 'PostOffice') Location,
  DECODE(BITAND(order_status, 2), 2, 'Ground', 'Air') Method,
  DECODE(BITAND(order_status, 4), 4, 'Insured', 'Certified') Receipt
  FROM orders
  WHERE order_status < 8;

  ORDER_ID CUSTOMER_ID LOCATION   MET RECEIPT
---------- ----------- ---------- --- ---------
      2458         101 Postoffice Air Certified
      2397         102 Warehouse  Air Certified
      2454         103 Warehouse  Air Certified
      2354         104 Postoffice Air Certified
      2358         105 Postoffice G   Certified
      2440         107 Warehouse  G   Certified
      2357         108 Warehouse  Air Insured
      2435         144 Postoffice G   Insured
...

CHARTOROWID( char )
Conversion d'une chaîne en ROWID

CONVERT( chaîne, jeu caract source, jeu caract cible )
Conversion d'une chaîne d'un jeu de caractères à un autre

select CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') from dual ;

CONVERT('ÄÊÍÕØABCDE'
--------------------
A E I ? ? A B C D E

DECODE( valeur, si1, alors1, si2, alors2,….., sinon )
Substitution valeur par valeur

select SAL, DECODE( TRUNC( SAL, -3 ), 0, 'Bof', 1000, 'Mieux', 2000, 'Pas mal', 'Super' ) from emp ;

       SAL DECODE(
---------- -------
       800 Bof
      1600 Mieux
      1250 Mieux
      2975 Pas mal
      1250 Mieux
      2850 Pas mal
      2450 Pas mal
      3000 Super
      5000 Super
      1500 Mieux
      1100 Mieux
       950 Bof
      3000 Super
      1300 Mieux

BIN_TO_NUM( bit [,bit[...]] )

Conversion d'une suite de bits en nombre

SQL> SELECT BIN_TO_NUM(1,0,0,0,0,0,0,0,0,0,0,0,0) FROM DUAL ;

BIN_TO_NUM(1,0,0,0,0,0,0,0,0,0,0,0,0)
-------------------------------------
                                 4096

SQL>

DUMP( expr [,format_retour [,début [,longueur]]] )

Retourne le type interne, longueur en octets de l'expression passée en argument

format_retour peut prendre l'une des quatre valeurs suivantes :

• 8 octal
• 10 décimal
• 16 hexadécimal
• 17 caractères

On peut ajouter 1000 à l'argument format_retour pour forcer DUMP à afficher le jeu de caractères en cours

SQL> -- DUMP --
SQL> SELECT DUMP('Hello', 8) "Octal",
  2  DUMP('Hello', 10) "Décimal",
  3  DUMP('Hello', 16) "Hexadécimal",
  4  DUMP('Hello', 17) "Caractère" 
  5  FROM DUAL ; 

Octal                             Décimal                          Hexadécimal                  Caractère
--------------------------------- -------------------------------- ---------------------------- -----------------------
Typ=96 Len=5: 110,145,154,154,157 Typ=96 Len=5: 72,101,108,108,111 Typ=96 Len=5: 48,65,6c,6c,6f Typ=96 Len=5: H,e,l,l,o

SQL>

SELECT DUMP('abc', 1016)
   FROM DUAL;

DUMP('ABC',1016)                          
------------------------------------------ 
Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63 

SQL>

HEXTORAW( char )
Conversion d'un nombre hexadécimal en un nombre binaire

RAWTOHEX( raw )
Conversion d'un nombre binaire en nombre hexadécimal

ROWIDTOCHAR( rowid )
Conversion d'un ROWID en chaîne

select ROWIDTOCHAR( ROWID ) from emp ;

ROWIDTOCHAR(ROWID)
------------------
AAAHW7AABAAAMUiAAA
AAAHW7AABAAAMUiAAB
AAAHW7AABAAAMUiAAC
AAAHW7AABAAAMUiAAD
AAAHW7AABAAAMUiAAE
AAAHW7AABAAAMUiAAF

TO_CHAR( date [, 'format' ] )
TO_CHAR( nombre [, 'format' ] )
Transformation d'un type DATE ou NUMBER en chaîne

select TO_CHAR( SYSDATE, 'DD/MM/YYYY HH24:MI:SS' ) from dual ;

TO_CHAR(SYSDATE,'DD
-------------------
31/01/2004 19:30:56

select TO_CHAR( 1256.35, '999G999D000' ) from dual ;

TO_CHAR(1256
------------
   1.256,350

TO_DATE( nombre [, 'format' ] )
TO_DATE( chaîne [, 'format' ] )
Transformation d'un type NUMBER ou CHAR ou VARCHAR2 en date

select TO_DATE( '01/01/04', 'DD/MM/RR' ) from dual ;

TO_DATE('01/01/04',
-------------------
01/01/2004 00:00:00

Select TO_DATE( 2453036, 'J' ) from dual ;

TO_DATE(2453036,'J'
-------------------
31/01/2004 00:00:00

TO_DSINTERVAL( chaîne )(9i)
Transformation d'un type CHAR ou VARCHAR2 en INTERVAL DAY TO SECOND

TO_LOB( LONG )
TO_LOB( LONG RAW )
Conversion d'une valeur de type LONG ou LONG RAW en valeur de type LOB

TO_CLOB( char )
TO_CLOB( colonne lob )
Conversion d'une valeur de type CHAR ou LOB en valeur de type CLOB

TO_MULTI_BYTE
Conversion d'une chaîne de caractères mono-octet en chaîne de caractères multi-octets
Cette fonction n'est nécessaire que si votre jeu de caractères contient à la fois des caractères mono-octets et des caractères multi-octets

TO_NUMBER( chaîne [, 'format' ] )
Transformation d'un type CHAR ou VARCHAR2 en nombre

select TO_NUMBER( '25,8', '9G999D00' ) from dual ;

TO_NUMBER('25,8','9G999D00')
----------------------------
                        25,8

TO_SINGLE_BYTE
Conversion d'une chaîne de caractères multi-octets en chaîne de caractères mono-octet
Cette fonction n'est nécessaire que si votre jeu de caractères contient à la fois des caractères mono-octets et des caractères multi-octets

TO_TIMESTAMP( chaîne )(9i)
Transformation d'un type CHAR ou VARCHAR2 en TIMESTAMP

select to_timestamp( '01/01/2004 01:00:00' ,'DD/MM/YYYY HH24:MI:SS') from dual ;

TO_TIMESTAMP('01/01/200401:00:00','DD/MM/YYYYHH24:MI:SS')
---------------------------------------------------------------------------
01/01/04 01:00:00,000000000

TO_TIMESTAMP_TZ( chaîne )(9i)
Transformation d'un type CHAR ou VARCHAR2 en TIMESTAMP + TIME ZONE

TO_YMINTERVAL( chaîne )(9i)
Transformation d'un type CHAR ou VARCHAR2 en INTERVAL YEAR TO MONTH
Chaîne représente un couple année-mois 'AA-MM'

select ename "Salarié", hiredate "Date entrée" , hiredate + TO_YMINTERVAL( '05-01' ) "Ancienneté" from emp ;

Salarié    Date entrée         Ancienneté
---------- ------------------- -------------------
SMITH      17/12/1980 00:00:00 17/01/1986 00:00:00
ALLEN      20/02/1981 00:00:00 20/03/1986 00:00:00
WARD       22/02/1981 00:00:00 22/03/1986 00:00:00

VSIZE( argument )

Retourne le nombre d'octets nécessaires au stockage de l'argument

SQL> -- Nombre d'octets de stockage --
SQL> SELECT VSIZE(10),VSIZE(123.9658), VSIZE(123.9658123456789), VSIZE(123456789456) FROM DUAL ;

 VSIZE(10) VSIZE(123.9658) VSIZE(123.9658123456789) VSIZE(123456789456)
---------- --------------- ------------------------ -------------------
         2               5                       10                   7

SQL> 
SQL> SELECT VSIZE(SYSDATE), VSIZE(CURRENT_TIMESTAMP) FROM DUAL ;

VSIZE(SYSDATE) VSIZE(CURRENT_TIMESTAMP)
-------------- ------------------------
             7                       13

SQL>

ADD_MONTHS( date, nombre de mois )
Ajoute ou soustrait un nombre de mois à une date

select SYSDATE, ADD_MONTHS( SYSDATE, 1 ) from dual ;

SYSDATE    ADD_MONTHS
---------- ----------
31/01/2004 29/02/2004

select SYSDATE, ADD_MONTHS( SYSDATE, -12 ) from dual ;

SYSDATE    ADD_MONTHS
---------- ----------
31/01/2004 31/01/2003

EXTRACT (datetemps FROM valeur)(9i)
Extraction d'un segment d'une valeur de type date ou d'un littéral de type intervalle

(Pour une raison inconnue, cette fonction ne permet pas d'extraire l'heure d'une date...)

datetemps peut être l'un des arguments suivants

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
• TIMEZONE_HOUR
• TIMEZONE_MINUTE
• TIMEZONE_REGION
• TIMEZONE_ABBR

valeur est l'un des arguments suivants

• un littéral de type DATE
• un littéral de type INTERVAL

Extraction de la partie année d'une date :

SQL> SELECT EXTRACT(YEAR FROM TO_DATE('10/01/2004','DD/MM/YYYY')) FROM DUAL;

EXTRACT(YEARFROMTO_DATE('10/01/2004','DD/MM/YYYY'))
---------------------------------------------------
                                               2004

Affichage des employés dont la date d'entée est supérieure à 1981

SQL>  SELECT empno "Numéro", ename "Nom", to_char(hiredate,'DD/MM/YYYY') "Entré le"
  2      FROM EMP
  3      WHERE EXTRACT(YEAR FROM
  4      TO_DATE(hiredate, 'DD/MM/RR')) > 1981
  5     ORDER BY hiredate
  6  /

    Numéro Nom        Entré le
---------- ---------- ----------
      7934 MILLER     24/01/1982
      7788 SCOTT      20/04/1987
      7876 ADAMS      24/05/1987

LAST_DAY( date )
Dernier jour du mois de la date passée en argument

select LAST_DAY( '01/01/2004' ) from dual ;

LAST_DAY('
----------
31/01/2004

MONTHS_BETWEEN( date2, date1 )
Nombre de mois qui séparent date2 de date1

select MONTHS_BETWEEN( '01/10/2004', '01/01/2004' ) from dual ;

MONTHS_BETWEEN('01/10/2004','01/01/2004')
-----------------------------------------
                                        9

NEXT_DAY( date, 'jour' )
Date du prochain jour après date ou jour est un jour de la semaine

select NEXT_DAY( '31/01/2004', 'Lundi' ) from dual ;

NEXT_DAY('
----------
02/02/2004

NEW_TIME( date, fuseau1, fuseau2 )
Date et heure de date en time zone fuseau2 lorsque date est en time zone fuseau1

select sysdate, NEW_TIME( sysdate, 'AST', 'PST' ) from dual ;

SYSDATE             NEW_TIME(SYSDATE,'A
------------------- -------------------
31/01/2004 18:15:20 31/01/2004 14:15:20

NUMTODSINTERVAL( nombre, 'type' )(9i)
Conversion d'un nombre en intervalle de type DAY TO SECOND

'type' peut prendre l'une des quatre valeurs suivantes :

• 'DAY' précise que nombre indique un nombre de jours
• 'HOUR' précise que nombre indique un nombre d'heures
• 'MINUTE' précise que nombre indique un nombre de minutes
• 'SECOND' précise que nombre indique un nombre de secondes

SQL> -- Ajout de 8 minutes à une date --
SQL> SELECT SYSDATE, SYSDATE + NUMTODSINTERVAL(8,'MINUTE') FROM DUAL ;

SYSDATE             SYSDATE+NUMTODSINTE
------------------- -------------------
25/11/2004 12:18:23 25/11/2004 12:26:23

SQL> 
SQL> -- Ajout de 8 heures à une date --
SQL> SELECT SYSDATE, SYSDATE + NUMTODSINTERVAL(8,'HOUR') FROM DUAL ;

SYSDATE             SYSDATE+NUMTODSINTE
------------------- -------------------
25/11/2004 12:18:23 25/11/2004 20:18:23

SQL> 
SQL> -- ajout de 30 secondes à une date --
SQL> SELECT SYSDATE, SYSDATE + NUMTODSINTERVAL(30,'SECOND') FROM DUAL ;

SYSDATE             SYSDATE+NUMTODSINTE
------------------- -------------------
25/11/2004 12:18:23 25/11/2004 12:18:53

SQL>

NUMTOYMINTERVAL( nombre, 'type' )(9i)
Conversion d'un nombre en intervalle de type YEAR TO MONTH

'type' peut prendre l'une des deux valeurs suivantes :

• 'YEAR' précise que nombre indique un nombre d'années
• 'MONTH' précise que nombre indique un nombre de mois

SQL> -- Ajout de 6 mois à une date --
SQL> SELECT SYSDATE + NUMTOYMINTERVAL(6,'MONTH') FROM DUAL ;

SYSDATE+NUMTOYMINTE
-------------------
25/05/2005 12:18:23

SQL> 
SQL> -- Ajout de 8 ans à une date --
SQL> SELECT SYSDATE + NUMTOYMINTERVAL(8,'YEAR') FROM DUAL ;

SYSDATE+NUMTOYMINTE
-------------------
25/11/2012 12:18:23

SQL>

ROUND( date [, format] )

select ROUND( to_date('01/01/2004 10:25','DD/MM/YYYY HH24:MI') ) from dual
SQL> /

ROUND(TO_DATE('01/0
-------------------
01/01/2004 00:00:00

select ROUND( to_date('01/01/2004 18:25','DD/MM/YYYY HH24:MI') ) from dual
SQL> /

ROUND(TO_DATE('01/0
-------------------
02/01/2004 00:00:00

sans l'argument format, la date est arrondie au matin 00:00:00 si l'heure transmise dans date est située avant 12:00, sinon elle est arrondie au lendemain 00:00:00

TRUNC( date [, format] )

select TRUNC( to_date('01/01/2004 18:25','DD/MM/YYYY HH24:MI') ) from dual ;

TRUNC(TO_DATE('01/0
-------------------
01/01/2004 00:00:00

select TRUNC( to_date('01/01/2004 10:25','DD/MM/YYYY HH24:MI') ) from dual ;

TRUNC(TO_DATE('01/0
-------------------
01/01/2004 00:00:00

sans l'argument format, la date est arrondie au matin 00:00:00

SYS_EXTRACT_UTC( timestamp )(9i)
Conversion d'une date au fuseau Greenwich

SQL> -- Ramener une date/heure au format Greenwich --
SQL> SELECT SYSDATE, SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL ;

SYSDATE             SYS_EXTRACT_UTC(SYSTIMESTAMP)
------------------- -----------------------------------------------------
25/11/2004 12:18:26 25/11/04 11:18:26,399272

SQL>

Une procédure est un ensemble de code PL/SQL nommé, défini par l'utilisateur et généralement stocké dans la BDD

Une procédure est paramétrable afin d'en faciliter la réutilisation

CREATE indique que l'on veut créer une procédure stockée dans la base
La clause facultative OR REPLACE permet d'écraser une procédure existante portant le même nom
nom procédure est le nom donné par l'utilisateur à la procédure
AUTHID indique sur quel schéma la procédure s'applique :

• CURRENT_USER

Indique que la procédure utilise les objets du schéma de l'utilisateur qui appelle la procédure

• DEFINER(défaut)

Indique que la procédure utilise les objets du schéma de création de la procédure

nom paramètre est le nom donné par l'utilisateur au paramètre transmis
IN(valeur par défaut) indique que le paramètre transmis par le programme appelant n'est pas modifiable par la procédure
OUT indique que le paramètre est modifiable par la procédure
IN OUT indique que le paramètre est transmis par le programme appelant et renseigné par la procédure
NOCOPY indique que le paramètre est transmis par référence (pointeur) et non par copie de la valeur
Par défaut, les paramètres sont transmis par copie, c'est à dire qu'un espace mémoire est créé pour recevoir une copie de la valeur
avec la clause NOCOPY, aucun espace mémoire supplémentaire n'est créé, c'est donc l'adresse de l'espace mémoire initial qui est transmise, permettant d'une part de ne pas gaspiller la mémoire disponible (surtout lorsqu'il s'agit de grands objets (LOB) et également d'éviter le temps nécessaire à la gestion de ces nouveaux espaces mémoire (empilement, dépilement, etc.)
datatype représente le type SQL ou PL/SQL du paramètre
:= représente le symbole d'assignation d'une valeur par défaut
DEFAULT identique à :=
expression représente la valeur par défaut du paramètre (doit être conforme au type du paramètre)

Créons une procédure permettant d'augmenter le salaire d'un employé

SQL> CREATE OR REPLACE PROCEDURE Augmentation
  2   (
  3     PN$Numemp IN EMP.empno%Type -- numéro de l'employé
  4    ,PN$Pourcent IN NUMBER       -- pourcentage d'augmentation
  5   ) IS
  6  BEGIN
  7    -- augmentation de l'employé
  8    Update EMP Set sal = sal * PN$Pourcent
  9    Where empno = PN$Numemp ; 
 10  END;
 11  /

Procédure créée.

La procédure Augmentation reçoit deux paramètres

PN$Numemp en entrée (IN) de même type que la colonne empno de la table EMP qui reçoit le numéro d'employé
PN$Pourcent en entrée (IN) de type NUMBER qui reçoit le pourcentage d'augmentation

Faisons maintenant appel à cette procédure dans un bloc PL/SQL anonyme

SQL> Declare
  2    LR$Emp EMP%Rowtype ;
  3  Begin   
  4    Select * Into LR$Emp From EMP Where empno = 7369 ; -- lecture ligne avant mise à jour
  5    dbms_output.put_line( 'Avant augmentation ' || To_char( LR$Emp.empno )
  6      || ' ' || LR$EMp.ename || ' --> ' || To_char( LR$Emp.sal ) ) ;
  7    Augmentation( 7369, 1.1 ) ; -- appel de la procédure
  8    Select * Into LR$Emp From EMP Where empno = 7369 ; -- lecture ligne après mise à jour 
  9    dbms_output.put_line( 'Après augmentation ' || To_char( LR$Emp.empno )
 10      || ' ' || LR$EMp.ename || ' --> ' || To_char( LR$Emp.sal ) ) ;
 11  End ;
 12  /
Avant augmentation 7369 SMITH --> 880
Après augmentation 7369 SMITH --> 968

Procédure PL/SQL terminée avec succès.

Les paramètres sont passés lors de l'appel de la fonction

D'une façon générale, les procédures ne devraient pas exécuter d'instruction de fin de transaction (COMMIT, ROLLBACK, Ordre DDL)
La décision d'enregistrer ou annuler la transaction en cours relève du programme appelant

Si toutefois, le traitement impose un enregistrement en base, la procédure peut être déclarée Autonome,
via la directive de compilation PRAGMA AUTONOMOUS_TRANSACTION

Imaginons que vous ayez besoin d'une procédure de trace qui utilise l'instruction INSERT pour enregistrer vos messages dans une table d'erreurs

Afin de dépister correctement la trace désirée, cette procédure doit enregistrer chaque insertion avec l'instruction COMMIT

Cependant, nous voulons que cet enregistrement ne valide que les instructions de notre procédure de trace

Pour atteindre cet objectif, nous allons donc créer une procédure autonome

Nous avons besoin d'une table de trace

SQL> CREATE TABLE TRACE(
  2      UTI Varchar2(30) DEFAULT USER
  3     ,DDATE Date DEFAULT SYSDATE
  4     ,LIGNE Varchar2(4000) ) ;

Table créée.

Cette table permettra de stocker l'utilisateur, la date et la ligne de trace

Nous allons créer maintenant notre procédure

Celle-ci pourra utiliser au choix la sortie du message sur écran avec la fonction DBMS_OUTPUT.PUT_LINE,
ou bien l'insertion dans la table de trace

Au passage nous allons augmenter les possibilités natives
En effet la fonction DBMS_OUTPUT.PUT_LINE est limitée à 255 caractères, et une colonne VARCHAR2 à 4000

Qu'à cela ne tienne, nous allons contourner le problème en découpant le message en tranches, permettant d'afficher quelle que soit la méthode jusqu'à 32767 caractères

SQL> CREATE OR REPLACE procedure DEBUG ( PC$Message in VARCHAR2, PC$Output in VARCHAR2 DEFAULT 'E' )
  2  Is
  3  PRAGMA AUTONOMOUS_TRANSACTION ;
  4    LC$Chaine Varchar2(4000) ;
  5    LN$Tranches PLS_INTEGER ;
  6    LN$Reste  PLS_INTEGER ;
  7    LN$Pos PLS_INTEGER := 1 ;
  8    LN$Inc PLS_INTEGER ;
  9  Begin
 10    
 11    If PC$Output = 'E' Then
 12       -- Sortie sur ecran (DBMS_OUTPUT) --
 13       LN$Inc := 255 ;
 14       LN$Tranches := Length( PC$Message ) / LN$Inc ;
 15       LN$Reste    := MOD( Length( PC$Message ), LN$Inc ) ;
 16       If LN$Reste > 0 Then LN$Tranches := LN$Tranches + 1 ; End if ;
 17  
 18       -- Sortie --
 19       For i in 1..LN$Tranches Loop
 20          LC$Chaine := Substr( PC$Message, LN$Pos, LN$Inc ) ;
 21          DBMS_OUTPUT.PUT_LINE( LC$Chaine ) ;
 22          LN$Pos := LN$Pos + LN$Inc ;  
 23       End loop ;
 24    
 25    Else
 26       -- Sortie sur table (INSERT) --
 27       LN$Inc := 4000 ;
 28       LN$Tranches := Length( PC$Message ) / LN$Inc ;
 29       LN$Reste    := MOD( Length( PC$Message ), LN$Inc ) ;
 30       If LN$Reste > 0 Then LN$Tranches := LN$Tranches + 1 ; End if ;
 31    
 32       -- Sortie --
 33       For i in 1..LN$Tranches Loop
 34          LC$Chaine := Substr( PC$Message, LN$Pos, LN$Inc ) ;
 35          Insert into TRACE (LIGNE) Values ( LC$Chaine ) ;
 36          Commit ; -- enregistrement de la ligne
 37          LN$Pos := LN$Pos + LN$Inc ;  
 38       End loop ;    
 39    End if ;
 40    
 41  End;
 42  /

Procédure créée.

Cette procédure accepte en premier paramètre la chaîne de caractères de trace (max 32767 caractères)
et en deuxième paramètre (facultatif) le type de sortie désiré E pour écran (défaut), différent de E pour table

La directive de compilation PRAGMA AUTONOMOUS_TRANSACTION en entête de la section déclarative indique
que cette procédure s'exécute dans sa propre transaction.

Utilisons maintenant cette procédure pour notre système de trace sur écran

SQL> set linesize 100
SQL> Declare
  2    LC$Ch Varchar2(32767) ;
  3  Begin
  4    LC$Ch := Rpad( '1', 300, '1' ) || Rpad( '2', 300, '2' ) ; -- trace de 600 caractères
  5    Debug( LC$Ch ) ;
  6  End ;     
  7  /
1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
1111111111111111111111111111111111111111111111111111111
1111111111111111111111111111111111111111111112222222222222222222222222222222222222222222222222222222
2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
2222222222222222222222222222222222222222222222222222222
222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222

Procédure PL/SQL terminée avec succès.

Puis sur table

SQL> Declare
  2    LC$Ch Varchar2(32767) ;
  3  Begin
  4    LC$Ch := Rpad( '1', 2100, '1' ) || Rpad( '2', 2100, '2' ) ; -- trace de 4200 caractères
  5    Debug( LC$Ch, 'T' ) ;
  6  End ;
  7  /

Procédure PL/SQL terminée avec succès.
SQL> select * from trace ;

UTI                            DDATE
------------------------------ --------
LIGNE
-----------------------------------------------------------------------------------------------------------
SCOTT                          13/03/04
11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112222222

SCOTT                          13/03/04
22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
etc...

Lorsqu'un paramètre est passé en mode OUT, la procédure peut le modifier.
Le programme appelant doit avoir défini une variable correspondante dans sa section déclarative

SQL> CREATE OR REPLACE PROCEDURE Test_Augmentation
  2     (
  3       PN$Numemp IN EMP.empno%Type
  4      ,PN$Pourcent IN OUT NUMBER
  5     ) IS
  6     LN$Salaire EMP.sal%Type ;
  7  BEGIN
  8      Select sal Into LN$Salaire From EMP Where empno = PN$Numemp ;
  9      -- augmentation virtuelle de l'employé
 10      PN$Pourcent := LN$Salaire * PN$Pourcent ;
 11  END;
 12  /

Procédure créée.
SQL> select empno, sal from emp where empno = 7369 ;

     EMPNO        SAL
---------- ----------
      7369        880

SQL> Declare
  2    LN$Pourcent NUMBER := 1.1 ;
  3  Begin
  4    Test_Augmentation( 7369, LN$Pourcent ) ;
  5    dbms_output.put_line( 'Employé 7369 après augmentation : ' || To_char( LN$Pourcent ) ) ;
  6  End ; 
  7  /
Employé 7369 après augmentation : 968

Une fonction est identique à une procédure à la différence qu'elle retourne obligatoirement une valeur
d'où le mot clé obligatoire RETURN

CREATE indique que l'on veut créer une fonction stockée dans la base
La clause facultative OR REPLACE permet d'écraser une fonction existante portant le même nom
nom fonction est le nom donné par l'utilisateur à la fonction
AUTHID indique sur quel schéma la fonction s'applique :

• CURRENT_USER

Indique que la fonction utilise les objets du schéma de l'utilisateur qui appelle la fonction

• DEFINER(défaut)

Indique que la fonction utilise les objets du schéma de création de la fonction


nom paramètre est le nom donné par l'utilisateur au paramètre transmis
IN(valeur par défaut) indique que le paramètre transmis par le programme appelant n'est pas modifiable par la fonction
OUT indique que le paramètre est modifiable par la procédure
IN OUT indique que le paramètre est transmis par le programme appelant et renseigné par la fonction
NOCOPY indique que le paramètre est transmis par référence (pointeur) et non par copie de la valeur
datatype représente le type SQL ou PL/SQL du paramètre
:= représente le symbole d'assignation d'une valeur par défaut
DEFAULT identique à :=
expression représente la valeur par défaut du paramètre (doit être conforme au type du paramètre)
PRAGMA AUTONOMOUS_TRANSACTION indique que la fonction sera exécutée dans une transaction autonome

Au chapitre sur les procédures, nous avons vu la procédure permettant de simuler une augmentation
en retournant le nouveau salaire théorique dans une variable IN OUT

Transformons cette procédure en fonction

SQL> CREATE OR REPLACE FUNCTION F_Test_Augmentation
  2      (
  3        PN$Numemp IN EMP.empno%Type
  4       ,PN$Pourcent IN NUMBER
  5      ) Return NUMBER
  6  IS
  7      LN$Salaire EMP.sal%Type ;
  8  BEGIN
  9       Select sal Into LN$Salaire From EMP Where empno = PN$Numemp ;
 10       -- augmentation virtuelle de l'employé
 11       LN$Salaire := LN$Salaire * PN$Pourcent ;
 12    Return( LN$Salaire ) ; -- retour de la valeur
 13  END;
 14  /

Fonction créée.

La valeur de retour de la fonction est directement utilisable, même sans déclaration d'une variable d'accueil

SQL> Declare
  2    LN$Salaire emp.sal%Type ; 
  3  Begin
  4    Select sal Into LN$Salaire From EMP Where empno = 7369 ;
  5    dbms_output.put_line( 'Salaire de 7369 avant augmentation ' || To_char( LN$Salaire ) ) ;
  6    dbms_output.put_line( 'Salaire de 7369 après augmentation ' || To_char( F_Test_Augmentation( 7369, 1.1 ) ) ) ;
  7  End ;
  8  /
Salaire de 7369 avant augmentation 880
Salaire de 7369 après augmentation 968

Procédure PL/SQL terminée avec succès.

Un paquetage est un ensemble de procédures et fonctions regroupées dans un objet nommé

Par exemple le paquetage Oracle DBMS_LOB regroupe toutes les fonctions et procédures manipulant les grands objets (LOBs)
Le paquetage UTL_FILE regroupe les procédures et fonctions permettant de lire et écrire des fichiers du système d'exploitation

Un paquetage est organisé en deux parties distinctes

• Une partie spécification
  qui permet de spécifier à la fois les fonctions et procédures publiques ainsi que les déclarations des types, variables, constantes, exceptions et curseurs utilisés dans le paquetage et visibles par le programme appelant
• Une partie corps
  qui contient les blocs et les spécifications de tous les objets publics listés dans la partie spécification
  Cette partie peut inclure des objets qui ne sont pas listés dans la partie spécification, et sont donc privés
  Cette partie peut également contenir du code qui sera exécuté à chaque invocation du paquetage par l'utilisateur

La déclaration de la partie spécification d'un paquetage s'effectue avec l'instruction CREATE [OR REPLACE] PACKAGE

Celle de la partie corps avec l'instruction CREATE [OR REPLACE] PACKAGE BODY

SQL> CREATE OR REPLACE PACKAGE Pkg_Finance
  2  IS
  3    -- Variables globales et publiques
  4    GN$Salaire EMP.sal%Type ;
  5  
  6    -- Fonctions publiques
  7    FUNCTION F_Test_Augmentation
  8          (
  9            PN$Numemp IN EMP.empno%Type
 10           ,PN$Pourcent IN NUMBER
 11          ) Return NUMBER ;
 12  
 13    -- Procédures publiques
 14    PROCEDURE Test_Augmentation
 15       (
 16         PN$Numemp IN EMP.empno%Type -- numéro de l'employé
 17        ,PN$Pourcent IN OUT NUMBER       -- pourcentage d'augmentation
 18       ) ;
 19  
 20  End Pkg_Finance ;
 21  /

Package créé.

SQL> CREATE OR REPLACE PACKAGE BODY Pkg_Finance IS
  2  
  3     -- Variables globales privées
  4     GR$Emp EMP%Rowtype ;
  5     
  6     -- Procédure privées 
  7     PROCEDURE Affiche_Salaires
  8     IS
  9       CURSOR C_EMP IS select * from EMP ;
 10     BEGIN
 11       OPEN C_EMP ;
 12       Loop
 13         FETCH C_EMP Into GR$Emp ;
 14         Exit when C_EMP%NOTFOUND ;
 15         dbms_output.put_line( 'Employé ' || GR$Emp.ename || ' --> ' || Lpad( To_char( GR$Emp.sal ), 10 ) ) ;
 16       End loop ;
 17       CLOSE C_EMP ;
 18     END Affiche_Salaires ;   
 19     
 20     -- Fonctions publiques
 21     FUNCTION F_Test_Augmentation
 22         (
 23           PN$Numemp IN EMP.empno%Type
 24          ,PN$Pourcent IN NUMBER
 25         ) Return NUMBER
 26     IS
 27         LN$Salaire EMP.sal%Type ;
 28     BEGIN
 29          Select sal Into LN$Salaire From EMP Where empno = PN$Numemp ;
 30          -- augmentation virtuelle de l'employé
 31          LN$Salaire := LN$Salaire * PN$Pourcent ;
 32          
 33          -- Affectation de la variable globale publique
 34          GN$Salaire := LN$Salaire ;
 35          
 36       Return( LN$Salaire ) ; -- retour de la valeur
 37     END F_Test_Augmentation;   
 38    
 39     -- Procédures publiques
 40     PROCEDURE Test_Augmentation
 41        (
 42          PN$Numemp IN EMP.empno%Type
 43         ,PN$Pourcent IN OUT NUMBER
 44        ) IS
 45        LN$Salaire EMP.sal%Type ;
 46     BEGIN
 47         Select sal Into LN$Salaire From EMP Where empno = PN$Numemp ;
 48         -- augmentation virtuelle de l'employé
 49        PN$Pourcent := LN$Salaire * PN$Pourcent ;
 50        
 51        -- appel procédure privée
 52        Affiche_Salaires ;
 53        
 54     END Test_Augmentation;
 55     
 56      
 57  END Pkg_Finance;
 58  /

Corps de package créé.

La spécification du paquetage est créée avec une variable globale et publique : GN$Salaire
une procédure publique : PROCEDURE Test_Augmentation
une fonction publique : FUNCTION F_Test_Augmentation

qui sont visibles depuis l'extérieur (le programme appelant)

Le corps du paquetage est créé avec une procédure privée : PROCEDURE Afiche_Salaires qui n'est visible que dans le corps du paquetage

Le corps définit également une variable globale au corps du paquetage : GR$Emp utilisée par la procédure privée

L'accès à un objet d'un paquetage est réalisé avec la syntaxe suivante :

nom_paquetage.nom_objet[(liste paramètres)]

Appel de la fonction F_Test_Augmentation du paquetage

SQL> Declare
  2      LN$Salaire emp.sal%Type ; 
  3  Begin
  4      Select sal Into LN$Salaire From EMP Where empno = 7369 ;
  5      dbms_output.put_line( 'Salaire de 7369 avant augmentation ' 
  6          || To_char( LN$Salaire ) ) ;
  7      dbms_output.put_line( 'Salaire de 7369 après augmentation ' 
  8          || To_char( Pkg_Finance.F_Test_Augmentation( 7369, 1.1 ) ) ) ;
  9  End ;
 10  /
Salaire de 7369 avant augmentation 880
Salaire de 7369 après augmentation 968

Procédure PL/SQL terminée avec succès.

Appel de la procédure Test_Augmentation du paquetage

SQL> Declare
  2       LN$Pourcent NUMBER := 1.1 ;
  3  Begin
  4       Pkg_Finance.Test_Augmentation( 7369, LN$Pourcent ) ;
  5       dbms_output.put_line( 'Employé 7369 après augmentation : ' || To_char( LN$Pourcent ) ) ;
  6  End ; 
  7  /
Employé SMITH -->        880
Employé ALLEN -->       1936
Employé WARD -->       1375
Employé JONES -->       3273
Employé MARTIN -->       1375
Employé BLAKE -->       3135
Employé CLARK -->       2695
Employé SCOTT -->       3300
Employé KING -->       5500
Employé TURNER -->       1650
Employé ADAMS -->       1210
Employé JAMES -->       1045
Employé FORD -->       3300
Employé MILLER -->       1430
Employé Dupontont -->
Employé Duboudin -->
Employé 7369 après augmentation : 968

Procédure PL/SQL terminée avec succès.

Interrogation de la variable globale publique : GN$Salaire

SQL> Begin
  2       dbms_output.put_line( 'Valeur salaire du package : ' || To_char( Pkg_Finance.GN$Salaire ) ) ;
  3  End ;
  4  /
Valeur salaire du package : 968

Procédure PL/SQL terminée avec succès.

Oracle 9i
Depuis la version 9i apparaît une nouvelle possibilité de PL/SQL de pouvoir définir des fonctions qui acceptent en argument des collections ou des références à un curseur, et qui retournent les données au fur et à mesure de l'exécution de la fonction.

Lors de la déclaration de la fonction, le mot clé PIPELINED est ajouté dans l'entête et les informations sont retournées à l'aide de la commande PIPE ROW.

Soit l'exemple suivant

SQL> create type MtSal as object (numemp number(4), salaire number(7,2) ) ;
  2  /

Type créé.

SQL> create type MtSalTab as table of MtSal ;
  2  /

Type créé.

SQL> CREATE OR REPLACE function LigneSalaire (cur_lig in SYS_REFCURSOR)
  2    return MtSalTab PIPELINED
  3  IS
  4   LSal  MtSal := MtSal (NULL,NULL) ;
  5   Remp  emp%rowtype ;
  6  Begin
  7   Loop
  8     Fetch cur_lig into Remp ;
  9     Exit When cur_lig%NOTFOUND ;
 10       -- Manipulation des données --
 11       LSal.numemp := Remp.empno ;
 12       LSal.salaire := Remp.sal * 1.1 ;
 13       -- Retour des valeurs --
 14       PIPE ROW( LSal ) ;
 15   End loop ;
 16   Return ;
 17  End ;
 18  /

Fonction créée.

SQL> Select numemp, salaire from
  2  table(LigneSalaire(CURSOR(Select * from EMP)))
  3  /

    NUMEMP    SALAIRE
---------- ----------
      7369        968
      7499       1936
      7521     1512,5
      7566     3600,3
      7654     1512,5
      7698     3448,5
      7782     2964,5
      7788       3630
      7839       6050
      7844       1815
      7876       1331
      7900     1149,5
      7902       3630
      7934       1573

14 ligne(s) sélectionnée(s).

L'instruction table(LigneSalaire(CURSOR(Select * from EMP))) indique que la fonction LigneSalaire retourne un ensemble de données.

Cet exemple est bien évidemment simpliste, mais on peut imaginer toutes sortes de traitements sur les lignes du curseur avant de renvoyer le résultat.


Pour faire une estimation de la masse salariale totale après une augmentation de salaire de 10% pour l'ensemble du personnel, il suffit d'interroger la fonction de la façon suivante :

SQL> Select sum(salaire) from
  2  table(LigneSalaire(CURSOR(Select * from EMP)))
  3  /

SUM(SALAIRE)
------------
     35120,8

Bien entendu un résultat équivalent pourrait être obtenu avec la requête :

select sum(sal * 1.1) from emp;

mais, répétons-le le traitement effectué dans la fonction peut être beaucoup plus élaboré.

Visualisation du code source

Le code source des objets procéduraux est visible par l'intermédiaire des vues du dictionnaire de données

USER_SOURCE pour les objets appartenant au schéma
ALL_SOURCE pour les objets appartenant aux schémas accessibles
DBA_SOURCE pour les objets appartenant à tous les schémas

SQL> desc user_source
 Nom                    
 ---------------------
 NAME                   
 TYPE                   
 LINE                   
 TEXT

Pour dissocier le code des procédures, fonctions et paquetages, la restriction sur la colonne TYPE doit être la suivante :

• Procédure : TYPE = 'PROCEDURE'
• Fonction : TYPE = 'FUNCTION'
• Spécification de paquetage : TYPE = 'PACKAGE'
• Corps de paquetage : TYPE = 'PACKAGE BODY'

SQL> Select text from user_source
  2  Where name = 'AUGMENTATION'
  3  And   type = 'PROCEDURE'
  4  /

TEXT
----------------------------------------
PROCEDURE Augmentation
 (
   PN$Numemp IN EMP.empno%Type
  ,PN$Pourcent IN NUMBER
 ) IS
BEGIN
  -- augmentation de l'employé
  Update EMP Set sal = sal * PN$Pourcent
  Where empno = PN$Numemp ;
END;

10 ligne(s) sélectionnée(s).

Compilation des objets

• Procédure : ALTER PROCEDURE nom_procédure COMPILE
• Fonction : ALTER FUNCTION nom_fonction COMPILE
• Spécification de paquetage : ALTER PACKAGE nom_package COMPILE PACKAGE
• Corps de paquetage : ALTER PACKAGE nom_package COMPILE BODY
• Spécification + Corps de paquetage : ALTER PACKAGE nom_package COMPILE

Suppression des objets

• Procédure : DROP PROCEDURE nom_procédure
• Fonction : DROP FUNCTION nom_fonction
• Paquetage entier : DROP PACKAGE nom_package
• Corps de paquetage : DROP PACKAGE BODY nom_package

• Les collections de type NESTED TABLE et INDEX-BY TABLES

Elles sont de taille dynamique et il n'existe pas forcément de valeur pour toutes les positions

Déclaration d'une collection de type nested table
TYPE nom type IS TABLE OF type élément [NOT NULL] ;

Déclaration d'une collection de type index by
TYPE nom type IS TABLE OF type élément [NOT NULL] INDEX BY index_by_type ;

index_by_type représente l'un des types suivants :

• BINARY_INTEGER
• PLS_INTEGER(9i)
• VARCHAR2(taille)
• LONG

SQL> declare
  2    -- collection de type nested table
  3    TYPE TYP_NES_TAB is table of varchar2(100) ;
  4    -- collection de type index by  
  5    TYPE TYP_IND_TAB is table of number index by binary_integer ;
  6    tab1 TYP_NES_TAB ;
  7    tab2 TYP_IND_TAB ;
  8  Begin
  9    tab1 := TYP_NES_TAB('Lundi','Mardi','Mercredi','Jeudi' ) ;
 10    for i in 1..10 loop
 11       tab2(i):= i ;
 12    end loop ;
 13  End;
 14  /

Procédure PL/SQL terminée avec succès.

• Les collections de type VARRAY

Ce type de collection possède une dimension maximale qui doit être précisée lors de sa déclaration
Elle possède une longueur fixe et donc la suppression d'éléments ne permet pas de gagner de place en mémoire
Ses éléments sont numérotés à partir de la valeur 1

Déclaration d'une collection de type VARRAY
TYPE nom type IS VARRAY (taille maximum) OF type élément [NOT NULL] ;

SQL> declare
  2    -- collection de type VARRAY
  3    TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ;
  4    tab1 TYP_VAR_TAB := TYP_VAR_TAB('','','','','','','','','','');
  5  Begin
  6    for i in 1..10 loop
  7       tab1(i):= to_char(i) ;
  8    end loop ;
  9  End;
 10  /

Procédure PL/SQL terminée avec succès.

Déclaration d'un tableau VARRAY de 30 éléments de type varchar2(100)

• Les enregistrements

TYPE nom type IS RECORD ( nom_champ type_élément [[ NOT NULL] := expression ] [, ….] ) ;
Nom_variable nom_type ;

Comme pour la déclaration des variables, il est possible d'initialiser les champs lors de leur déclaration

SQL> declare
  2    -- Record --
  3    TYPE T_REC_EMP IS RECORD (
  4       Num emp.empno%TYPE,
  5       Nom emp.ename%TYPE,
  6       Job emp.job%TYPE );    
  7    
  8    R_EMP T_REC_EMP ; -- variable enregistrement de type T_REC_EMP
  9  Begin
 10    R_EMP.Num := 1 ;
 11    R_EMP.Nom := 'Scott' ;
 12    R_EMP.job := 'GASMAN' ; 
 13  End; 
 14  /

Procédure PL/SQL terminée avec succès.

Bien sûr il est possible de gérer des tableaux d'enregistrements

SQL> declare
  2    -- Record --
  3    TYPE T_REC_EMP IS RECORD (
  4       Num emp.empno%TYPE,
  5       Nom emp.ename%TYPE,
  6       Job emp.job%TYPE );    
  7    -- Table de records --
  8    TYPE TAB_T_REC_EMP IS TABLE OF T_REC_EMP index by binary_integer ;
  9    t_rec TAB_T_REC_EMP ; -- variable tableau d'enregistrements
 10  Begin
 11    t_rec(1).Num := 1 ;
 12    t_rec(1).Nom := 'Scott' ;
 13    t_rec(1).job := 'GASMAN' ;
 14    t_rec(2).Num := 2 ;
 15    t_rec(2).Nom := 'Smith' ;
 16    t_rec(2).job := 'CLERK' ;    
 17  End; 
 18  /

Procédure PL/SQL terminée avec succès.