| | 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.
|
| | auteur : Fred_D |
Voici une méthode pour filtrer la liste des enregistrements sur des intervalles consécutifs depuis la version 8i :
SELECT ename, job
FROM (SELECT ename, job, ROW_NUMBER() OVER (ORDER BY sal) num
FROM emp
)
WHERE num BETWEEN 1 AND 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 :
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 :
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.
|
| | 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 :  Comment sélectionner du m-iéme au n-iéme enregistrements ?
|
| | 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 );
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
|
|
| | 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 ;
|
|
| | 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.
|
|
| | 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>
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;
Session modifiée.
SQL>
SQL>
SQL> SELECT SYSDATE "Date du jour" FROM DUAL ;
Date du jour
08/10/2004 14:08:48
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'YEAR') "'YEAR'" FROM DUAL;
'YEAR'
01/01/2004 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'IYYY') "'IYYY'" FROM DUAL;
'IYYY'
29/12/2003 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'Q') "'Q'" FROM DUAL;
'Q'
01/10/2004 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'MONTH') "'MONTH'" FROM DUAL;
'MONTH'
01/10/2004 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;
'DAY'
04/10/2004 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'W') "'W'" FROM DUAL;
'W'
08/10/2004 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'WW') "'WW'" FROM DUAL;
'WW'
07/10/2004 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'IW') "'IW'" FROM DUAL;
'IW'
04/10/2004 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL;
'DD'
08/10/2004 00:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'HH') "'HH'" FROM DUAL;
'HH'
08/10/2004 14:00:00
SQL>
SQL>
SQL> SELECT TRUNC(SYSDATE, 'MI') "'MI'" FROM DUAL;
'MI'
08/10/2004 14:08:00
|
|
| | 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
|
|
| | 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))
|
|
| | 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 !
|
| | auteur : Jaouad |
Via la requête suivante :
SELECT
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';
|
|
| | 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
|
|
| | 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.
|
| | 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 )
WHEN MATCHED THEN
UPDATE SET T1.Meschamps = T2.Meschamps
WHEN NOT MATCHED THEN
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 :
 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 : http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_9016.htm#SQLRF01606
|
| | auteur : Jaouad |
Grâce au hint apparue avec la version 10g :
|
| | 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));
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).
|
|
| | 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
|
| | auteur : PlaineR |
Voici les variables d'environnement les plus communes :
- define : caractère utilisé pour la substitution des variables (par défaut "&")
syntaxe :
SET define ON
SET define off
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 :
- numformat : format par défaut des numériques à l'affichage.
syntaxe :
- numwidth : largeur par défaut des numériques
syntaxe :
- pagesize : nombre de lignes de détail (entête nom compris)
syntaxe :
- scan : contrôle la présence de variables de substitution
syntaxe :
- tab : utilisation des tabulations
syntaxe :
- trimspool : suppression des espaces en fin de lignes
syntaxe :
SET trimspool ON
SET trimspool OFF
|
- verify : liste la commande avant son exécution
syntaxe :
SET verify ON
SET verify OFF
|
- feed : affiche le nombre de lignes affectées par l'ordre SQL
syntaxe :
- term : affiche le résultat des commandes d'un script
syntaxe :
- heading : affichage de l'entête du résultat
syntaxe :
SET heading ON
SET head OFF
|
- space : nombre d'espaces entre deux colonnes
syntaxe :
- echo : affichade des commandes d'un script à l'exécution
syntaxe :
NB : Ce sont les principales, il en existe d'autres (SQL*Plus : Menu Option-> Environment) dont vous trouverez l'explication dans la documentation Oracle.
|
Consultez les autres F.A.Q's
 
|
|
