FAQ OracleConsultez toutes les FAQ

Nombre d'auteurs : 15, nombre de questions : 137, dernière mise à jour : 26 octobre 2006  Ajouter une question

 

Cette F.A.Q. a été réalisée à partir des questions fréquemment posées sur le forum Oracle de www.developpez.com et de l'expérience personnelle des auteurs. Elle pourra traiter de tout type de questions portant sur les technologies Oracle.

Nous espérons que cette F.A.Q. saura répondre à un maximum de vos questions. Nous vous souhaitons une bonne lecture.

L'équipe Oracle de Developpez.


SommaireDéveloppementSQL (18)
précédent sommaire suivant
 

  • Obtenir les 10 premières lignes d'une requête non triée :

Code sql :
1
2
3
SELECT *  
  FROM EMP  
 WHERE ROWNUM <= 10 ;
  • Obtenir les 10 premières lignes d'une requête triée :

Code sql :
1
2
3
4
5
SELECT *  
  FROM (SELECT *  
          FROM EMP  
         ORDER BY ename)  
 WHERE ROWNUM <= 10 ;
Explication de la seconde requête :

Chaque ligne retournée par une requête se voit attribuer un numéro, commençant par 1.
Cela est parfait tant que les données ne sont pas triées avec le mot-clé ORDER BY !

Par contre, si vous souhaitez trier les données, le numéro (ROWNUM) est attribué pour chaque ligne AVANT le tri final !
Prenons un exemple en affichant le contenu de la table EMP trié par salaires :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  1  SELECT ROWNUM, a.*  
  2    FROM emp a  
  3   WHERE sal > 1000  
  4*  ORDER BY sal  
SQL>  
SQL> /  
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO  
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------  
        11       7900 JAMES      CLERK           7698 04/12/81       1045                    30  
        10       7876 ADAMS      CLERK           7788 24/05/87       1210                    20  
        14       9991 Dupontont  CLERK           7698 24/01/82       1210                    10  
        15       9992 Duboudin   CLERK           7698 24/01/82       1215       1250         10  
         2       7521 WARD       SALESMAN        7698 23/02/81       1375        500         30  
         4       7654 MARTIN     SALESMAN        7698 29/09/81       1375       1400         30  
        13       7934 MILLER     CLERK           7782 24/01/82       1430                    10  
         9       7844 TURNER     SALESMAN        7698 09/09/81       1650          0         30  
         1       7499 ALLEN      SALESMAN        7698 21/02/81       1936        300         30  
        16       9994 Schmoll    CLERK           7698 24/05/87       2500                    20  
         6       7782 CLARK      MANAGER         7839 10/06/81       2695       2000         10  
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO  
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------  
         5       7698 BLAKE      MANAGER         7839 02/05/81       3135                    30  
         3       7566 JONES      MANAGER         7839 03/04/81       3273                    20  
         7       7788 SCOTT      ANALYST         7566 20/04/87       3300                    20  
        12       7902 FORD       ANALYST         7566 04/12/81       3300                    20  
         8       7839 KING       PRESIDENT            18/11/81       5500                    10  
  
16 ligne(s) sélectionnée(s).
Nous ne voulons maintenant afficher que les quatre premières lignes :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT ROWNUM, a.*  
  2    FROM emp a  
  3   WHERE sal > 1000  
  4     AND ROWNUM < 5  
  5  ORDER BY sal ;  
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO  
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------  
         2       7521 WARD       SALESMAN        7698 23/02/81       1375        500         30  
         4       7654 MARTIN     SALESMAN        7698 29/09/81       1375       1400         30  
         1       7499 ALLEN      SALESMAN        7698 21/02/81       1936        300         30  
         3       7566 JONES      MANAGER         7839 03/04/81       3273                    20
les CLERK ont disparu !

Simplement parce que le tri a été effectué après que le ROWNUM ait été affecté aux lignes retournées par la requête.

Il faut donc trier les données avant qu'Oracle ne leur affecte un numéro !
Ceci est réalisé par un tri inclus dans une sous-requête :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> SELECT  ROWNUM, a.*  
  2  FROM  (  
  3      SELECT *  
  4      FROM  emp  
  5      ORDER BY sal  
  6     ) a  
  7  WHERE  ROWNUM < 5 ;  
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO  
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------  
         1       7369 SMITH      CLERK           7902 18/12/80        880                    20  
         2       7900 JAMES      CLERK           7698 04/12/81       1045                    30  
         3       7876 ADAMS      CLERK           7788 24/05/87       1210                    20  
         4       9991 Dupontont  CLERK           7698 24/01/82       1210                    10
Voici enfin le résultat attendu.

Mis à jour le 30 novembre 2004 SheikYerbouti

Voici une méthode pour filtrer la liste des enregistrements sur des intervalles consécutifs depuis la version 8i :

  • les cinq premiers :

Code sql :
1
2
3
4
5
SELECT ename, job 
  FROM (SELECT ename, job, ROW_NUMBER() OVER (ORDER BY sal) num 
          FROM emp  
       )  
 WHERE num BETWEEN 1 AND 5;
  • les cinq suivants :

Code sql :
1
2
3
4
5
SELECT ename, job 
  FROM (SELECT ename, job, ROW_NUMBER() OVER (ORDER BY sal) num 
          FROM emp  
       )  
 WHERE num BETWEEN 6 AND 10;
  • de la m-ième à la n-ième ligne :

Code sql :
1
2
3
4
5
SELECT ename, job 
  FROM (SELECT ename, job, ROW_NUMBER() OVER (ORDER BY sal) num 
          FROM emp  
       )  
 WHERE num BETWEEN &m AND &n;
Ces exemples utilisent la fonction analytique ROW_NUMBER. Cette fonction est très similaire au ROWNUM utilisé dans le paragraphe précédent mais est appliqué sur le résultat de la requête et pas pendant l'exécution de la requête elle-même.
Avec ROWNUM, la requête précédente aurait pu s'écrire :

Code sql :
1
2
3
4
5
6
7
8
SELECT ename, job 
  FROM ( SELECT ename, job, ROWNUM num  
           FROM ( 
		         SELECT ename, job 
                   FROM emp ORDER BY sal 
				) 
       )  
 WHERE num BETWEEN &m AND &n;
Les fonctions analytiques peuvent sensiblement simplifier les requêtes et éventuellement améliorer leurs performances.

Mis à jour le 30 novembre 2004 orafrance

Supposons qu'on ait une table PERSONNE (nom, prenom, telephone) et qu'on souhaite avoir la liste des personnes ayant le même nom et le même prénom. On peut l'obtenir avec la requête suivante :

Code sql :
1
2
3
4
SELECT nom, prenom 
FROM PERSONNE 
GROUP BY nom, prenom 
HAVING count(*) > 1;
Si on ne veut garder qu'une seule ligne pour chaque ensemble de personnes ayant le même nom et le même prénom, le choix se faisant de manière arbitraire, on peut utiliser la requête suivante :

Code sql :
1
2
3
4
5
6
7
DELETE PERSONNE a 
WHERE rowid > ( 
SELECT min(rowid) 
FROM PERSONNE b 
WHERE b.nom = a.nom AND 
b.prenom = a.prenom 
);
Remarque :
  • cette requête doit être adaptée si l'une des colonnes nom ou prénom peut être nulle.
  • cette requête garde la ligne de rowid minimum parmi l'ensemble des lignes ayant même nom et même prénom.

Mis à jour le 18 septembre 2006 lalystar

Comment sélectionner du m-ième au n-ième enregistrements ?

À l'aide de la table des exceptions : elle peut se créer via ce script :

Code sql :
1
2
3
4
5
CREATE TABLE exceptions( 
row_id rowid,  
owner varchar2(30),  
table_name varchar2(30),  
constraint varchar2(30));
Ou en utilisant le script fournit dans $ORACLE_HOME/RDBMS/admin/UTLEXCPT.SQL.

Voyons son utilisation au travers de ce jeu d'essais :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE test ( 
a varchar2(10), 
b number ); 
  
-- Insertion des valeures 
INSERT INTO test VALUES ('a',1); 
INSERT INTO test VALUES ('b',2); 
INSERT INTO test VALUES ('c',3); 
INSERT INTO test VALUES ('d',4); 
INSERT INTO test VALUES ('a',5); 
INSERT INTO test VALUES ('e',6); 
INSERT INTO test VALUES ('f',7); 
INSERT INTO test VALUES ('b',8); 
INSERT INTO test VALUES ('a',9); 
INSERT INTO test VALUES ('z',10); 
commit;
Essayons maintenant de créer une contrainte d'unicité : nous allons obtenir une erreur :

Code sql :
1
2
3
4
5
6
SQL> ALTER TABLE test ADD constraint TEST_PK UNIQUE  (a)  
  2  exceptions INTO exceptions ; 
ALTER TABLE test ADD constraint TEST_PK UNIQUE  (a) 
                                * 
ERREUR à la ligne 1 : 
ORA-02299: impossible de valider (SCOTT.TEST_PK) - clés en double trouvées
La requête suivante nous permet d'identifier les lignes posant problème :

Code sql :
1
2
3
4
5
6
7
8
9
10
SQL> SELECT * FROM test WHERE rowid IN ( SELECT row_id FROM exceptions WHERE table_name='TEST' AND o 
wner ='SCOTT' ) ; 
  
A                   B 
---------- ---------- 
a                   1 
b                   2 
a                   5 
b                   8 
a                   9

Mis à jour le 18 septembre 2006 Jaouad

Cette fonction permet d'effectuer plusieurs remplacements d'un coup (à l'inverse de REPLACE() qui ne permet le remplacement que d'un caractère ou une chaîne à la fois) :

TRANSLATE( chaîne en entrée, chaîne de recherche, chaîne de substitution ) ;

  • Exemple de suppression de caractères indésirables pour la constitution d'un nom de fichier :

Code sql :
1
2
3
4
5
6
SQL>  SELECT TRANSLATE ( 'Nom/de~fichier;non.conforme', '1/\^~.,;', '1' ) "Fichier"  
  2   FROM DUAL ;  
  
Fichier  
-----------------------  
Nomdefichiernonconforme
Le premier argument de la fonction est la chaîne en entrée,
Le deuxième indique la chaîne de recherche,
Le troisième indique la substitution caractère par caractère,

En clair, le premier caractère de la chaîne de recherche trouvé dans la chaîne en entrée sera remplacé par le premier caractère de la chaîne de substitution et ainsi de suite pour les autres caractères.
La ruse dans l'exemple est le premier caractère '1' dans les deux derniers arguments. On dit qu'il s'agit d'un caractère leurre, pusiqu'il sert à ignorer tous les caractères de la chaîne en entrée qui ne sont pas dans la chaîne de recherche.
La fonction remplace tous les '1' par '1' et tous les autres caractères de la chaîne de recherche trouvés dans la chaîne en entrée par rien !

  • Exemple de remplacement de caractères indésirables pour la constitution d'un nom de fichier :

Code sql :
1
2
3
4
5
6
SQL> SELECT TRANSLATE ( 'Nom/de~fichier;non.conforme', '1/\^~.,;', '1_______' ) "Fichier"  
  2  FROM DUAL ;  
  
Fichier  
---------------------------  
Nom_de_fichier_non_conforme
La fonction remplace tous les '1' par '1' et tous les autres caractères de la chaîne de recherche par le caractère '_'.

  • Autre exemple de remplacement de caractères indésirables :

Code sql :
1
2
3
4
5
6
7
8
SQL> SELECT TRANSLATE ( 'àâäéèëêùûüô', 'àâäéèëêùûüô', 'aaaeeeeuuuo' ) "Chaine"  
  2  FROM DUAL ;  
  
Chaine  
-----------  
aaaeeeeuuuo  
  
SQL>
La fonction remplace toutes les minuscules accentuées par leurs équivalents non accentués.

Bien évidement, les arguments de la fonction peuvent être des variables :

Code sql :
1
2
3
4
FUNCTION Remplace ( PC$Chaine IN VARCHAR2, PC$Rech IN VARCHAR2 , PC$Subst IN VARCHAR2 ) RETURN VARCHAR2 IS  
BEGIN  
   RETURN TRANSLATE( PC$Chaine, PC$Rech, PC$Subst ) ;  
END ;

Mis à jour le 30 novembre 2004 SheikYerbouti

TRUNC( valeur, précision ) ;
Cette fonction permet d'effectuer deux types d'action (sur un numérique) :

  • Conserver le nombre de décimales souhaitées (précision >= 0)
  • Cadrer la partie entière d'un nombre dans une tranche (précision < 0)

  • Exemple de conservation des décimales voulues :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT  
  2   trunc(12.98764, 5) "+5 dec."  
  3  ,trunc(12.98764, 4) "+4 dec."  
  4  ,trunc(12.98764, 3) "+3 dec."  
  5  ,trunc(12.98764, 2) "+2 dec."  
  6  ,trunc(12.98764, 1) "+1 dec."  
  7  ,trunc(12.98764, 0) "+0 dec."  
  8   FROM dual ;  
  
   +5 dec.    +4 dec.    +3 dec.    +2 dec.    +1 dec.    +0 dec.  
---------- ---------- ---------- ---------- ---------- ----------  
  12,98764    12,9876     12,987      12,98       12,9         12
  • Exemple de cadrage par tranches :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT  
  2   trunc(1234567,-6) "million"  
  3  ,trunc(1234567,-5) "cent-mille"  
  4  ,trunc(1234567,-4) "dix-mille"  
  5  ,trunc(1234567,-3) "mille"  
  6  ,trunc(1234567,-2) "cent"  
  7  ,trunc(1234567,-1) "dix"  
  8  FROM dual ;  
  
   million cent-mille  dix-mille      mille       cent        dix  
---------- ---------- ---------- ---------- ---------- ----------  
   1000000    1200000    1230000    1234000    1234500    1234560
  • Extraction des parties entière et décimale d'un nombre :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
SQL> DECLARE  
  2    LN$Num number := 100.95 ;  
  3  BEGIN  
  4    DBMS_OUTPUT.PUT_LINE( 'Partie entière  : ' || To_char( TRUNC ( LN$Num ) )) ;  
  5    DBMS_OUTPUT.PUT_LINE( 'Partie décimale : ' || To_char( LN$Num - TRUNC (LN$Num ) ) ) ;  
  6  END ;  
  7  /  
Partie entière  : 100  
Partie décimale : ,95  
  
Procédure PL/SQL terminée avec succès.

Mis à jour le 30 novembre 2004 SheikYerbouti

La fonction TRUNC() appliquée à une date ne permet pas seulement l'ablation de la partie horaire.

Nous connaissons tous cette fonction qui, sans spécification de son deuxième argument, retire la partie horaire.
Mais nous connaissons souvent moins l'autre particularité de cette fonction appliquée aux dates, par l'intermédiaire du second argument (format).

TRUNC( date [,'format'] )

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
SQL> -- Format étendu d'affichage des dates  --  
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;  
  
Session modifiée.  
  
SQL>  
SQL> -- Affichage de la date du jour --  
SQL> SELECT SYSDATE "Date du jour" FROM DUAL ;  
  
Date du jour                                                                     
-------------------                                                               
08/10/2004 14:08:48                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour de l'année --  
SQL> SELECT TRUNC(SYSDATE, 'YEAR') "'YEAR'" FROM DUAL;  
  
'YEAR'                                                                           
-------------------                                                               
01/01/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour de l'année ISO--  
SQL> SELECT TRUNC(SYSDATE, 'IYYY') "'IYYY'" FROM DUAL;  
  
'IYYY'                                                                           
-------------------                                                               
29/12/2003 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour du trimestre --  
SQL> SELECT TRUNC(SYSDATE, 'Q') "'Q'" FROM DUAL;  
  
'Q'                                                                               
-------------------                                                               
01/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour du mois --  
SQL> SELECT TRUNC(SYSDATE, 'MONTH') "'MONTH'" FROM DUAL;  
  
'MONTH'                                                                           
-------------------                                                               
01/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour de la semaine --  
SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;  
  
'DAY'                                                                             
-------------------                                                               
04/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- jour de la semaine correspondant au jour du 1er jour du mois --  
SQL> SELECT TRUNC(SYSDATE, 'W')  "'W'" FROM DUAL;  
  
'W'                                                                               
-------------------                                                               
08/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- jour de la semaine correspondant au jour du 1er jour de l'année --  
SQL> SELECT TRUNC(SYSDATE, 'WW')  "'WW'" FROM DUAL;  
  
'WW'                                                                             
-------------------                                                               
07/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- jour de la semaine correspondant au jour du 1er jour de l'année ISO --  
SQL> SELECT TRUNC(SYSDATE, 'IW')  "'IW'" FROM DUAL;  
  
'IW'                                                                             
-------------------                                                               
04/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au jour (retire les heures) --  
SQL> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL;  
  
'DD'                                                                             
-------------------                                                               
08/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée à l'heure (retire les minutes) --  
SQL> SELECT TRUNC(SYSDATE, 'HH') "'HH'" FROM DUAL;  
  
'HH'                                                                             
-------------------                                                               
08/10/2004 14:00:00                                                               
  
SQL>  
SQL> -- date tronquée à la minute (retire les secondes) --  
SQL> SELECT TRUNC(SYSDATE, 'MI') "'MI'" FROM DUAL;  
  
'MI'                                                                             
-------------------                                                               
08/10/2004 14:08:00

Mis à jour le 30 novembre 2004 SheikYerbouti

Il existe beaucoup de formats de date disponibles avec Oracle.

Voici une liste non exhaustive des différents formats de date disponible.

  • - / , . ; : "text" : Ponctuation ou texte à insérer dans la chaîne finale.
  • AD A.D. : Pour ajouter les sigles après Jésus Christ
  • AM A.M. : Symbole Am
  • BC B.C. : Pour ajouter les sigles avant Jésus Christ
  • CC SCC : Pour obtenir le n° du siècle en cours
  • D : N° du jour dans la semaine (par exemple Lundi =1, Mardi =2, etc.)
    Attention la notation peut changer en fonction de vos paramètres NLS.
  • DAY : Nom du jour (compensé avec des espaces jusqu'à 9 caractères)
  • DD : Le numéro du jour dans le mois (de 1 à 31)
  • DDD : Le numéro du jour dans l'année (de 1 à 366)
  • DY: Abréviation du nom du jour
  • E : Abréviation du nom de l'ère en cours (pour les calendriers Japonais, ROC et Thai).
  • EE : Nom complet de l'ère en cours
  • FM : Permet de supprimer les espaces inutiles
  • HH: Heure (de 1 à 12)
  • HH12: Heure (de 1 à 12)
  • HH24: Heure (de 1 à 23)
  • IW: Numéro de la semaine dans l'année (de 1 à 52 ou 53 en fonction de l'année)
  • IYY IY I : Les 3, 2 ou 1 derniers chiffres de l'année
  • IYYY : Les 4 chiffres de l'année
  • J: Nombre de jours depuis la date January 1, 4712 BC
  • MI: Minutes (de 1 à 59)
  • MM: Numéro du mois dans l'année
  • MON : Abréviation du nom du mois
  • MONTH : Nom du mois (compensé avec des espaces jusqu'à 9 caractères)
  • PM P.M. : Symbole Pm
  • Q: Numéro du trimestre
  • RM: Numéro du mois au format romain
  • RR: Les deux derniers chiffres de l'année (avec prise en compte du siècle en cours)
  • RRRR: Les 4 derniers chiffres de l'année (avec prise en compte du siècle en cours)
  • SS: Nombre de secondes (de 0 à 59)
  • SSSSS: Nombre de secondes écoulées depuis minuit (de 0 à 86399)
  • W: Numéro de la semaine dans le mois (de 1 à 5)
  • Y,YYY: Numéro de l'année avec une virgule pour délimiter le millénaire (par exemple 2,004)
  • YEAR : Épellation du numéro de l'année en cours
  • YYYY: Numéro de l'année en cours (sans prise en compte du siècle en cours)
  • YYY YY Y : Les 3, 2 ou 1 derniers chiffres de l'année

Exemple :

Code sql :
1
2
3
4
5
6
SELECT to_char(sysdate,'DD/MM/RRRR Day D Month Year') 
FROM dual; 
  
TO_CHAR(SYSDATE,'DD/MM/RRRRDAYDMONTHYEAR')   
------------------------------------------------------------------------------------ 
15/10/2004 Vendredi 5 Octobre Two Thousand Four

Mis à jour le 30 novembre 2004 helyos

Les contraintes CHECK sont pratiques pour mettre en place un simple contrôle statique de validité des données, mais elles sont très limitées. Il faudra alors recourir à des déclencheurs pour des validations plus complexes.

Une contrainte CHECK ne peut faire référence qu'aux colonnes de la ligne courante.
Elle ne peut donc pas impliquer d'autres lignes de la table, ni impliquer d'autres tables.
En particulier, on ne peut pas y utiliser un SELECT.

Dans une contrainte CHECK, on ne peut pas faire appel à une fonction personnalisée.
Les fonctions standard (comme LENGTH ou UPPER par exemple) sont autorisées, à condition qu'elles soient déterministes.
C'est pourquoi on ne peut pas utiliser les fonctions SYSDATE ou USER, qui provoquent l'une comme l'autre l'erreur "ORA-02436: variable de date ou système mal indiquée dans contrainte CHECK".

Pour résumer, une contrainte CHECK ne peut mettre en jeu que les colonnes de la ligne courante, des constantes éventuellement sous forme de listes, et des fonctions standard déterministes.

Exemples :

Code sql :
1
2
CHECK (UPPER(titre) IN ('M.', 'MME', 'MLLE')) 
CHECK ((col1 < col2) OR (col1 = 10))

Mis à jour le 30 novembre 2004 Pomalaix

Il existe essentiellement une différence de place entre ces types de données . En effet, VARCHAR2 a une longueur variable qui dépend directement de la taille de la donnée insérée alors que CHAR à une longueur fixe (défini à la création de la table).

Prenons l'exemple d'un VARCHAR2 (20) et CHAR(20), si on insère 'Oracle' dans les deux colonnes, Oracle sera complété par des espaces dans la colonne CHAR(20) :

Code sql :
1
2
3
CREATE TABLE dvp ( a char(20), b varchar2(20)) ;  
INSERT INTO dvp VALUES ('oracle','oracle') ;  
commit ;
Code sql :
1
2
3
4
5
6
7
8
9
10
11
SQL> SELECT '-'||a||'-', '-'||b||'-' FROM dvp ;  
  
'-'||A||'-'            '-'||B||'-' 
---------------------- ---------------------- 
-oracle              - -oracle-  
  
SQL> SELECT length(a) , length(b) FROM dvp ;  
  
 LENGTH(A)  LENGTH(B)  
---------- ----------  
        20          6
La Longueur maximale de Char (2000) et Varchar2 (4000) sur une 8i :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> CREATE TABLE dvp ( a varchar2(4001) ;  
CREATE TABLE dvp ( a varchar2(4001)  
                              *  
ERREUR à la ligne 1 :  
ORA-00910: specified length too long FOR its datatype  
  
  
SQL> CREATE TABLE dvp ( a varchar2(4000)) ;  
  
TABLE créée.  
  
SQL>  DROP TABLE dvp ;  
  
TABLE supprimée.  
  
SQL>  CREATE TABLE dvp ( a char(2001)) ;  
 CREATE TABLE dvp ( a char(2001))  
                           *  
ERREUR à la ligne 1 :  
ORA-00910: specified length too long FOR its datatype  
  
  
SQL> CREATE TABLE dvp ( a char(2000)) ;  
  
TABLE créée.
Oracle recommande quand même l'utilisation de VARCHAR2 au lieu de CHAR pour une raison de compatibilité dans les versions supérieures.

Outre l'utilisation supplémentaire de place, l'utilisation de CHAR en lieu et place de VARCHAR2 augmentera également la taille de vos index et peut parfois diminuer les performances de recherches de ces derniers !

Mis à jour le 18 septembre 2006 Jaouad

Via la requête suivante :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
SELECT   /*+ choose */  
         bs.username "Blocking User",  
         bs.username "DB User",  
         ws.username "Waiting User",  
         bs.sid "SID",  
         ws.sid "WSID",  
         bs.sql_address "address",  
         bs.sql_hash_value "Sql hash",  
         bs.program "Blocking App",  
         ws.program "Waiting App",  
         bs.machine "Blocking Machine",  
         ws.machine "Waiting Machine",  
         bs.osuser "Blocking OS User",  
         ws.osuser "Waiting OS User",  
         bs.serial# "Serial#",  
         DECODE (  
            wk.TYPE,  
            'MR', 'Media Recovery',  
            'RT', 'Redo Thread',  
            'UN', 'USER Name',  
            'TX', 'Transaction',  
            'TM', 'DML',  
            'UL', 'PL/SQL USER LOCK',  
            'DX', 'Distributed Xaction',  
            'CF', 'Control FILE',  
            'IS', 'Instance State',  
            'FS', 'FILE SET',  
            'IR', 'Instance Recovery',  
            'ST', 'Disk SPACE Transaction',  
            'TS', 'Temp Segment',  
            'IV', 'Library Cache Invalidation',  
            'LS', 'LOG START OR Switch',  
            'RW', 'ROW Wait',  
            'SQ', 'Sequence Number',  
            'TE', 'Extend TABLE',  
            'TT', 'Temp TABLE',  
            wk.TYPE  
         ) lock_type,  
         DECODE (  
            hk.lmode,  
            0, 'None',  
            1, 'NULL',  
            2, 'ROW-S (SS)',  
            3, 'ROW-X (SX)',  
            4, 'SHARE',  
            5, 'S/ROW-X (SSX)',  
            6, 'EXCLUSIVE',  
            TO_CHAR (hk.lmode)  
         ) mode_held,  
         DECODE (  
            wk.request,  
            0, 'None',  
            1, 'NULL',  
            2, 'ROW-S (SS)',  
            3, 'ROW-X (SX)',  
            4, 'SHARE',  
            5, 'S/ROW-X (SSX)',  
            6, 'EXCLUSIVE',  
            TO_CHAR (wk.request)  
         ) mode_requested,  
       object_name ,  
         TO_CHAR (hk.id1) lock_id1,  
         TO_CHAR (hk.id2) lock_id2  
FROM     v$lock hk, v$session bs, v$lock wk, v$session ws ,  V$LOCKED_OBJECT a ,  
dba_objects b  
WHERE    hk.BLOCK = 1  
AND      hk.lmode != 0  
AND      hk.lmode != 1  
AND      wk.request != 0  
AND      wk.TYPE(+) = hk.TYPE  
AND      wk.id1(+) = hk.id1  
AND      wk.id2(+) = hk.id2  
AND      hk.sid = bs.sid(+)  
AND      wk.sid = ws.sid(+)  
AND      a.object_id=b.object_id  
AND      hk.sid=a.session_id  
ORDER BY 1;
Plus simplement, comment détecter si une table est verrouillée :

Code sql :
1
2
3
4
SELECT session_id , oracle_username , os_user_name 
  FROM V$LOCKED_OBJECT VLO INNER JOIN dba_objects DO ON VLO.object_id = DO.object_id 
 WHERE object_name = 'object_name' 
   AND owner = 'owner';

Mis à jour le 18 septembre 2006 Jaouad

À partir d'Oracle 9i, on peut utiliser le mot-clé DEFAULT pour spécifier la valeur par défaut d'une colonne :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SQL> CREATE TABLE TEST ( 
  2     x number DEFAULT 5, 
  3     y number DEFAULT -3 
  4  ); 
  
TABLE created. 
  
SQL> INSERT INTO TEST 
  2  VALUES (DEFAULT, DEFAULT); 
  
1 row created. 
  
SQL> SELECT * FROM TEST; 
  
         X          Y 
---------- ---------- 
         5         -3 
  
SQL> UPDATE TEST 
  2  SET    x = -5, 
  3         y = 0; 
  
1 row updated. 
  
SQL> SELECT * FROM TEST; 
  
         X          Y 
---------- ---------- 
        -5          0 
  
SQL> UPDATE TEST 
  2  SET    x = DEFAULT, 
  3         y = DEFAULT; 
  
1 row updated. 
  
SQL> SELECT * FROM TEST; 
  
         X          Y 
---------- ---------- 
         5         -3 
  
SQL> ALTER TABLE TEST MODIFY ( 
  2     x DEFAULT 0, 
  3     y DEFAULT NULL 
  4  ); 
  
TABLE altered. 
  
SQL> UPDATE TEST 
  2  SET    x = DEFAULT, 
  3         y = DEFAULT; 
  
1 row updated. 
  
SQL> SELECT * FROM TEST; 
  
         X          Y 
---------- ---------- 
         0

Mis à jour le 30 novembre 2004 lalystar

Comme le montre l'exemple suivant, lors de vos INSERT/UPDATE, Oracle retransforme les chaînes vide en valeur nulles, ce qui peut être gênant fonctionnellement, en particulier pour des applications multi-SGBD.

Code sql :
1
2
3
4
5
CREATE TABLE dvp ( a VARCHAR2( 10 ) ) ; 
INSERT INTO dvp VALUES (NULL) ;  
INSERT INTO dvp VALUES ('DVP') ;  
INSERT INTO dvp VALUES ('') ;  
COMMIT;
Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SQL> SELECT '-' ||a FROM dvp ;  
  
'-'||A 
----------- 
- 
-DVP 
- 
  
SQL> SELECT COUNT(*) FROM dvp ;  
  
  COUNT(*) 
---------- 
         3 
  
SQL> SELECT COUNT(*) FROM dvp WHERE a IS NULL;  
  
  COUNT(*) 
---------- 
         2 
  
SQL> SELECT COUNT(*) FROM dvp WHERE a = '' ;  
  
  COUNT(*) 
---------- 
         0 
  
SQL> SELECT COUNT(*) FROM dvp WHERE a IS NOT NULL ;  
  
  COUNT(*) 
---------- 
         1 
  
SQL> SELECT COUNT(*) FROM dvp WHERE a != '' ;  
  
  COUNT(*) 
---------- 
         0
Une solution consiste à utiliser un caractère ou une chaîne de susbsitution, afin que votre applicatif puisse gérer ces informations de manière distincte. Le plus simple, si c'est possible, est d'utiliser le caractère espace, ce qui donne, si l'on continue sur l'exemple précédent :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> INSERT INTO dvp VALUES (' ') ; 
  
1 ligne créée. 
  
SQL> commit; 
  
Validation effectuée. 
  
SQL> SELECT count (*) FROM dvp ; 
  
COUNT(*) 
---------- 
4 
  
SQL> SELECT count(*) FROM dvp WHERE a IS NULL ;
Attention tout de même :

Oracle® Database SQL Reference 10g Release 2 (10.2) :
Note: Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

Mis à jour le 18 septembre 2006 Jaouad laurentschneider

Grâce à l'instruction MERGE, à partir de la 9i. Voici sa syntaxe :

Code sql :
1
2
3
4
5
6
7
MERGE INTO Table1 T1 
  USING (SELECT Id, Meschamps FROM Table2) T2 
    ON ( T1.Id = T2.Id ) -- Condition de correspondance 
WHEN MATCHED THEN -- Si Vraie 
  UPDATE SET T1.Meschamps = T2.Meschamps 
WHEN NOT MATCHED THEN -- Si faux 
  INSERT (T1.ID, T1.MesChamps) VALUES ( T2.ID, T2.MesChamps);
Prenons l'exemple d'une table "Article" tout ce qu'il y a de plus classique :

  • Une clé (PK)
  • une référence (UQ)
  • un libellé
  • un prix

La clé primaire est renseignée à l'aide d'une séquence.

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE Article ( 
Id Number (10), 
Refe VARCHAR2 (16), 
Libe VARCHAR2 (64), 
Prix NUMBER (12,2)); 
  
CREATE SEQUENCE Seq_Id_Article START WITH 1 INCREMENT BY 1; 
  
INSERT INTO Article VALUES (Seq_Id_Article.NEXTVAL, '001', 'Marteau',   7.46); 
INSERT INTO Article VALUES (Seq_Id_Article.NEXTVAL, '002', 'Tournevis', 3.83); 
INSERT INTO Article VALUES (Seq_Id_Article.NEXTVAL, '004', 'Lime',      5.09); 
  
COMMIT;
Sa sœur Temp_Article reçoit un fichier de MAJ à jour des prix, sans clé et pouvant contenir de nouveaux articles :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Temp_Article ( 
Refe VARCHAR2 (16), 
Libe VARCHAR2 (64), 
Prix NUMBER (12,2)); 
  
INSERT INTO Temp_Article VALUES ('001', 'Marteau',   7.27); 
INSERT INTO Temp_Article VALUES ('002', 'Tournevis', 3.81); 
INSERT INTO Temp_Article VALUES ('003', 'Pince',     2.67); 
INSERT INTO Temp_Article VALUES ('004', 'Lime',      5.35); 
INSERT INTO Temp_Article VALUES ('005', 'Clé',       4.91); 
COMMIT;
La commande MERGE va nous servir à faire la MAJ des prix et l'intégration des nouveaux articles en une seule commande :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> MERGE INTO Article A 
  2    USING (SELECT Refe, Libe, prix FROM Temp_Article) T 
  3       ON (A.Refe = T.Refe) 
  4  WHEN MATCHED THEN  
  5    UPDATE SET A.Prix = T.Prix, A.Libe = T.Libe 
  6  WHEN NOT MATCHED THEN  
  7    INSERT (A.Id, A.Refe, A.Libe, A.Prix) VALUES (Seq_Id_Article.NEXTVAL, T.Refe, T.Libe, T.Prix); 
  
5 lignes fusionnées. 
  
SQL> SELECT * FROM Article; 
  
        ID REFE             LIBE             PRIX 
---------- ---------------- ---------------- ------- 
         1 001              Marteau          7,27 
         2 002              Tournevis        3,81 
         3 004              Lime             5,35 
         7 003              Pince            2,67 
         8 005              Clé              4,91
Voici la page Oracle sur laquelle vous trouverez toutes les informations utiles à propos de cette commande :
http://download-west.oracle.com/docs...htm#SQLRF01606

Use the MERGE statement to select rows from one or more sources for update or insertion into one or more tables. You can specify conditions to determine whether to update or insert into the target tables.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.

Mis à jour le 18 septembre 2006 Xo

http://download-west.oracle.com/docs...htm#SQLRF01606

Grâce au hint apparue avec la version 10g :

Code sql :
/*+ REWRITE_OR_ERROR */

Mis à jour le 18 septembre 2006 Jaouad

Oracle permet de gérer le concept de requêtes récursives, à l'aide des clauses suivantes :

  • START WITH Champ = {Valeur} : Cette clause permet de préciser le(s) nœud(s) de l'arborescence à partir duquel ou desquels on souhaite lister les éléments,
  • CONNECT BY PRIOR Id = Id_Parent : Cette clause indique sur quels champs se baser pour parcourir l'arborescence,

Depuis la version 9i, vous pouvez utiliser un pseudo champ LEVEL afin de connaître le niveau de chaque enregistrement.

En voici un exemple, grâce à une table Catégorie comprenant les champs suivants :

  • Une Clé (Id)
  • Un Libellé (Libe)
  • La clé de la catégorie parent (Id_Parent)

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE Categorie ( 
Id NUMBER (4), 
Libe VARCHAR2 (16), 
Id_Parent NUMBER (4)); 
  
-- Les clés sont attribuées de manière hiérarchique pour plus de clarté, ce qui est bien entendu rarement le cas dans la réalité ... 
INSERT INTO Categorie VALUES (   1, 'Librairie'     , NULL); 
INSERT INTO Categorie VALUES (  11, 'Revue'         , 1); 
INSERT INTO Categorie VALUES ( 111, 'Quotidien'     , 11); 
INSERT INTO Categorie VALUES ( 112, 'Hebdomadaire'  , 11); 
INSERT INTO Categorie VALUES ( 111, 'Mensuel'       , 11); 
INSERT INTO Categorie VALUES (  12, 'Roman'         , 1); 
INSERT INTO Categorie VALUES (  13, 'Bande Dessinée', 1); 
  
INSERT INTO Categorie VALUES (   2, 'Alimentaire'   , NULL); 
INSERT INTO Categorie VALUES (  21, 'Boisson'       , 2); 
INSERT INTO Categorie VALUES ( 211, 'Alcool'        , 21); 
INSERT INTO Categorie VALUES ( 212, 'Jus de fruits' , 21); 
INSERT INTO Categorie VALUES ( 213, 'Alcool'        , 21); 
INSERT INTO Categorie VALUES (2131, 'Vins'          , 213); 
INSERT INTO Categorie VALUES (2132, 'Bière'         , 213); 
INSERT INTO Categorie VALUES (2133, 'Apéritif'      , 213); 
INSERT INTO Categorie VALUES (  22, 'Conserve'      , 2); 
INSERT INTO Categorie VALUES (  23, 'Frais'         , 2); 
  
COMMIT;
Voici la liste des catégories dépendant de la catégorie 1 : Librairie :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SELECT Id, Libe, LEVEL, Id_Parent 
  2    FROM Categorie 
  3   START WITH Id = 1 
  4  CONNECT BY PRIOR Id = Id_Parent; 
  
        ID LIBE                  LEVEL  ID_PARENT 
---------- ---------------- ---------- ---------- 
         1 Librairie                 1 
        11 Revue                     2          1 
       111 Quotidien                 3         11 
       112 Hebdomadaire              3         11 
       111 Mensuel                   3         11 
        12 Roman                     2          1 
        13 Bande Dessinée            2          1 
  
7 ligne(s) sélectionnée(s).
À partir de la 9i, vous pouvez également utilisez la fonction SYS_CONNECT_BY_PATH (Champ, séparateur), qui permet de concaténer les différents valeurs du champ spécifié pour connaître le chemin entre l'élément concerné et le nœud racine :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> SELECT SYS_CONNECT_BY_PATH(Libe, '-') 
  2    FROM Categorie 
  3   START WITH Id = 2 
  4  CONNECT BY PRIOR Id = Id_Parent; 
  
SYS_CONNECT_BY_PATH(LIBE,'-') 
------------------------------------------ 
-Alimentaire 
-Alimentaire-Boisson 
-Alimentaire-Boisson-Alcool 
-Alimentaire-Boisson-Jus de fruits 
-Alimentaire-Boisson-Alcool 
-Alimentaire-Boisson-Alcool-Vins 
-Alimentaire-Boisson-Alcool-Bière 
-Alimentaire-Boisson-Alcool-Apéritif 
-Alimentaire-Conserve 
-Alimentaire-Frais 
  
10 ligne(s) sélectionnée(s).

Mis à jour le 18 septembre 2006 Xo

@ désigne la variable ORACLE_SID courante. Est valable dans les scripts SQL, dans divers paramètres de l'INIT.ORA, dans des scripts RMAN notamment.

? désigne la variable ORACLE_HOME courante. Est valable dans les scripts SQL, dans divers paramètres de l'INIT.ORA, dans des scripts RMAN notamment.

! équivalent de la commande HOST de SQL*Plus, permet d'appeler le shell, sous Unix uniquement

$ équivalent de la commande HOST de SQL*Plus, permet d'appeler le shell, sous Windows uniquement

& permet de désigner une variable SQL*Plus

start et @ permet de lancer un script

define permet de définir une variable sous Sql*Plus , sans argument elle donne toutes les variables déja défini

& permet de définir une variable, && permet de la définir une fois pour toute et pour toute la session.

ed permet de lancer le buffer

l permet de voir sans l'executer le dernier ordre SQL

r permet de voir et executer le dernier ordre SQL

c:mot1:mot2 permet de changer toutes les occurences du mot1 par mot2 dans le dernier ordre SQL

Mis à jour le 15 octobre 2006 Jaouad orafrance Pomalaix

Voici les variables d'environnement les plus communes :

  • define : caractère utilisé pour la substitution des variables (par défaut "&")

syntaxe :

Code sql :
1
2
3
4
5
6
-- gère les caractères de substitution 
SET define ON 
-- les caractères de substitutions ne sont pas gérés 
SET define off 
-- le caractère de substition est @ 
SET define @
- linesize : nombre de caractères par lignes
NB : la ligne est complétée par des espaces pour atteindre ce nombre de caractères.
syntaxe :

Code sql :
1
2
-- mets le nom de caractère par ligne à 130  
SET linesize 130
- numformat : format par défaut des numériques à l'affichage.
syntaxe :

Code sql :
SET numformat 999G999D99
- numwidth : largeur par défaut des numériques
syntaxe :

Code sql :
SET numwidth 8
- pagesize : nombre de lignes de détail (entête nom compris)
syntaxe :

Code sql :
SET pagesize 60
- scan : contrôle la présence de variables de substitution
syntaxe :

Code sql :
1
2
3
4
-- contrôle la présence de variables de substitions 
SET scan ON 
-- ne contrôle pas la présence de variables de substitions 
SET scan OFF
- tab : utilisation des tabulations
syntaxe :

Code sql :
1
2
3
4
-- utilisation des tabulations 
SET tab ON 
-- non utilisation des tabulations 
SET tab OFF
- trimspool : suppression des espaces en fin de lignes
syntaxe :

Code sql :
1
2
3
4
-- supprime les espaces en fin de lignes 
SET trimspool ON 
-- laisse les espaces en fin de lignes 
SET trimspool OFF
- verify : liste la commande avant son exécution
syntaxe :

Code sql :
1
2
3
4
-- liste la commande avant son exécution 
SET verify ON 
-- la commande n'est pas affichée avant son exécution 
SET verify OFF
- feed : affiche le nombre de lignes affectées par l'ordre SQL
syntaxe :

Code sql :
1
2
3
4
-- affichage du nombre de lignes affectées 
SET feed ON 
-- le nombre de lignes affectées n'est pas affiché 
SET feed OFF
- term : affiche le résultat des commandes d'un script
syntaxe :

Code sql :
1
2
3
4
--  affichage du résultat 
SET term ON  
--  non affichage du résultat 
SET term OFF
- heading : affichage de l'entête du résultat
syntaxe :

Code sql :
1
2
3
4
--  affichage de l’entête 
SET heading ON  
--  non affichage de l’entête 
SET head OFF
- space : nombre d'espaces entre deux colonnes
syntaxe :

Code sql :
1
2
-- 2 espaces entre 2 colonnes  
SET space 2
- echo : affichage des commandes d'un script à l'exécution
syntaxe :

Code sql :
1
2
3
4
--  affichage de la commande 
SET echo ON  
--  non affichage de la commande 
SET echo OFF
NB : Ce sont les principales, il en existe d'autres (SQL*Plus : Menu Option-> Environment) dont vous trouverez l'explication dans la documentation Oracle.

Mis à jour le 15 octobre 2006 plaineR

Proposer une nouvelle réponse sur la FAQ

Ce n'est pas l'endroit pour poser des questions, allez plutôt sur le forum de la rubrique pour ça


Réponse à la question

Liens sous la question
précédent sommaire suivant
 

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2014 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

 
 
 
 
Partenaires

PlanetHoster
Ikoula