IdentifiantMot de passe
Mot de passe oublié ?Je m'inscris ! (gratuit)
logo
Sommaire > Développement > SQL
        Comment filtrer les n premières lignes d'une requête avec ROWNUM ?
        Comment sélectionner du m-iéme au n-iéme enregistrements ?
        Comment détecter et supprimer les doublons ?
        Comment détecter les enregistrements empêchant la validation d'une contrainte ?
        Comment supprimer/remplacer des caractères avec la fonction TRANSLATE ?
        Comment manipuler la partie entière et décimale d'un numérique avec la fonction TRUNC ?
        Comment tronquer une date avec la fonction TRUNC ?
        Comment formater les dates ?
        Quelles sont les limitations d'une contrainte CHECK ?
        Quelle est la différence entre les types CHAR et VARCHAR2 ?
        Comment détecter les verrous (locks) ?
        Comment positionner la valeur d'une colonne à sa valeur par défaut ?
        Comment différencier NULL et chaîne vide ?
        Comment insérer ou mettre à jour des données sans savoir si les enregistrements correspondants existent déjà ?
        Comment empêcher l'exécution d'une requête non ré écrite ?
        Comment construire une requête récursive ?
        Quels sont les raccourcis sous SQL*Plus
        Quelles sont les principales variables d'environnement de SQL Plus ?
rechercher
precedent    sommaire    suivant    telechargermiroir

Comment filtrer les n premières lignes d'une requête avec ROWNUM ?
auteur : SheikYerbouti
  • Obtenir les 10 premières lignes d'une requête non triée :

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

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 : 

  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 4 premières lignes : 

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 : 

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.


Comment sélectionner du m-iéme au n-iéme enregistrements ?
auteur : Fred_D
Voici une méthode pour filtrer la liste des enregistrements sur des intervalles consécutifs :
  • les 5 premiers :

SELECT * 
  FROM (SELECT ROWNUM NUM, ename, job 
          FROM EMP ORDER BY sal
       ) 
 WHERE NUM BETWEEN 1 AND 5;
  • les 5 suivants :

SELECT * 
  FROM (SELECT ROWNUM NUM, ename, job 
          FROM EMP ORDER BY sal
       ) 
 WHERE NUM BETWEEN 6 AND 10 ;
  • Élimination des doublons d'une table :

DELETE 
  FROM nom_table a 
 WHERE (col1, col2) IN 
   ( SELECT col1, col2
       FROM nom_table 
      GROUP BY col1, col2 
     HAVING count(*) > 1 
   ) 
   AND ROWID <> 
   ( SELECT rowid 
       FROM nom_table b 
      WHERE a.col1 = b.col1 
        AND a.col2 = b.col2 
        AND ROWNUM <= 1 
   ) ;

Comment détecter et supprimer les doublons ?
auteur : lalystar
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 : 

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 : 

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.


lien : faq Comment sélectionner du m-iéme au n-iéme enregistrements ?

Comment détecter les enregistrements empêchant la validation d'une contrainte ?
auteur : Jaouad
À l'aide de la table des exceptions : elle peut se créer via ce script : 

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 : 

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 : 

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 :
Retrouver toutes les valeurs en doubles

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

Comment supprimer/remplacer des caractères avec la fonction TRANSLATE ?
auteur : SheikYerbouti
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 :

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 :

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 :

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 : 

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 ;

Comment manipuler la partie entière et décimale d'un numérique avec la fonction TRUNC ?
auteur : 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 :

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 :

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 :

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.

Comment tronquer une date avec la fonction TRUNC ?
auteur : 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'] ) 

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

Comment formater les dates ?
auteur : Helyos
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 : Epellation 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 : 

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

Quelles sont les limitations d'une contrainte CHECK ?
auteur : Pomalaix
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 : 

CHECK (UPPER(titre) IN ('M.', 'MME', 'MLLE'))
CHECK ((col1 < col2) OR (col1 = 10))

Quelle est la différence entre les types CHAR et VARCHAR2 ?
auteur : Jaouad
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éree 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 serat compléte par des espaces dans la colonne CHAR(20) : 

create table dvp ( a char(20), b varchar2(20)) ; 
insert into dvp values ('oracle','oracle') ; 
commit ; 


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 : 

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 !


Comment détecter les verrous (locks) ?
auteur : Jaouad
Via la requête suivante : 

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 verouillée : 

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

Comment positionner la valeur d'une colonne à sa valeur par défaut ?
auteur : lalystar
À partir d'Oracle 9i, on peut utiliser le mot-clé default pour spécifier la valeur par défaut d'une colonne : 

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

Comment différencier NULL et chaîne vide ?
auteurs : Jaouad, Laurent Schneider
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.
jeu d'essais

CREATE TABLE dvp ( a VARCHAR2( 10 ) ) ;
INSERT INTO dvp VALUES (NULL) ; 
INSERT INTO dvp VALUES ('DVP') ; 
INSERT INTO dvp VALUES ('') ; 
COMMIT;


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 : 

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.



Comment insérer ou mettre à jour des données sans savoir si les enregistrements correspondants existent déjà ?
auteur : Xavier Vlieghe
Grâce à l'instruction MERGE, à partir de la 9i. Voici sa syntaxe : 

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. 

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 soeur Temp_Article reçoit un fichier de MAJ à jour des prix, sans clé et pouvant contenir de nouveaux articles : 

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 : 

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 :
en http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_9016.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.

lien : en http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_9016.htm#SQLRF01606

Comment empêcher l'exécution d'une requête non ré écrite ?
auteur : Jaouad
Grâce au hint apparue avec la version 10g : 

/*+ REWRITE_OR_ERROR */

Comment construire une requête récursive ?
auteur : Xavier Vlieghe
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) noeud(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)

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 : 

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).
A 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 neod racine : 

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

Quels sont les raccourcis sous SQL*Plus
auteurs : Pomalaix, Jaouad, Fred_D
@ 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



Quelles sont les principales variables d'environnement de SQL Plus ?
auteur : PlaineR
Voici les variables d'environnement les plus communes :

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

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

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

SET numformat 999G999D99
  • numwidth : largeur par défaut des numériques
syntaxe : 

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

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

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

-- utilisation des tabulations
SET tab ON
-- non utilisation des tabulations
SET tab OFF
  • trimspool : suppression des espaces en fin de lignes
syntaxe : 

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

-- liste la commande avant son exécution
SET verify ON
-- la commande n'est pas affichee avant son exécution
SET verify OFF
  • feed : affiche le nombre de lignes affectées par l'ordre SQL
syntaxe : 

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

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

--  affichage de l'entete
SET heading ON 
--  non affichage de l'entete
SET head OFF
  • space : nombre d'espaces entre deux colonnes
syntaxe : 

-- 2 espaces entre 2 colonnes 
SET space 2
  • echo : affichade des commandes d'un script à l'exécution
syntaxe : 

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

rechercher
precedent    sommaire    suivant    telechargermiroir

Consultez les autres F.A.Q's

Valid XHTML 1.1!Valid CSS!

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 © 2006 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site ni 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.