jh-net Blog

Plusieurs symboles peuvent être utilisés dans votre code SQL. Ces derniers vous permettent de réaliser certaines actions comme vous demander de saisir la valeur d’un paramètre dans une requête, ou encore déclarer une variable dans cette dernière. Dans ce cas, vous pouvez utiliser le symbole “&” qui vous permettra de créer une variable temporaire de substitution qui vous demandera une valeur à chaque fois qu’elle est référencée.

Voici un exemple d’utilisation :

SQL> SELECT sal FROM emp WHERE ename LIKE '&NAME';
Enter value for name: SCOTT

Old query  1: SELECT sal FROM emp WHERE ename LIKE '&NAME'
New query  1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

Résultat:

SAL
----------
5000

Si vous souhaitez relancer la même requête, vous pouvez saisir :

SQL> /
Enter value for name: SCOTT

Old query 1: SELECT sal FROM emp WHERE ename LIKE '&NAME'
New query 1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

Résultat :

SAL
----------
5000
Nous allons maintenant voir le caractère “&&” qui permet de créer une « substitution permanente ». Une fois que vous aurez saisi la valeur de la variable, sa valeur sera utilisée automatiquement à chaque fois que vous relancerez la requête ou que vous réutiliserez la variable.
Voici l’exemple d’utilisation :

SQL> SELECT sal FROM emp WHERE ename LIKE '&&NAME';
Enter value for name: SCOTT

Old query 1: SELECT sal FROM emp WHERE ename LIKE '&&NAME'
New query 1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

Résultat :

SAL
----------
3000

Relancez la même requête que précédemment :

SQL> /

Old query 1: SELECT sal FROM emp WHERE ename LIKE '&&NAME'
New query 1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

Résultat :

SAL
----------
3000

Il est possible d’utiliser l’instruction DEFINE pour réaliser la même action qu’avec “&&”.

Pour connaitre la version de l’OS du serveur de base de données Oracle depuis SQLPlus, vous pouvez effectuer la commande SQL suivante :

SQL> select dbms_utility.port_string from dual;

PORT_STRING
-----------------------------------------------

Linuxi386/Linux-2.0.34-8.1.0

Par défaut, lorsque vous souhaitez qu’une valeur dans une table soit auto-incrémentée, vous devez créer une séquence. Lors de la création de cette séquence, la valeur d’incrément (le pas) est de 1. Autrement dit, votre séquence s’incrémentera par défaut de 1 en 1. Seulement, vous pouvez ne pas souhaiter cette incrémentation et préférer incrémenter par pas de 10 par exemple, voire par pas de -2 (pourquoi pas ?). Voici donc la façon de procéder pour changer la valeur de l’incrément :

Création de la séquence tout d’abord :

SQL> CREATE SEQUENCE ma_sequence;

Maintenant voyons les valeurs générées :

SQL> SELECT ma_sequence.NEXTVAL FROM DUAL;

1


SQL> SELECT ma_sequence.NEXTVAL FROM DUAL;

2


SQL> SELECT ma_sequence.NEXTVAL FROM DUAL;

3

Ainsi de suite…

Maintenant je souhaite modifier ce pas pour la valeur de l’incrément :

SQL> ALTER SEQUENCE ma_sequence INCREMENT BY 10;

On interroge à nouveau la séquence :

SQL> SELECT ma_sequence.NEXTVAL FROM DUAL;

13


SQL> SELECT ma_sequence.NEXTVAL FROM DUAL;

23

On aperçoit ici que le nouveau pas de 10 a été pris en compte.

Ce serait la même chose si la valeur était négative :

SQL> ALTER SEQUENCE ma_sequence INCREMENT BY -10;


Attention toutefois, lorsque vous utilisez une valeur négative pour la valeur de l’incrément, vous allez obtenir une valeur inférieure à la valeur minimale de la séquence. Vous pouvez toutefois modifier cette valeur grâce au paramètre MINVALUE afin de pouvoir utiliser une valeur négative inférieure à 0. (0 étant la valeur minimale par défaut lors de la création de la séquence).

Modification de la valeur minimum :

SQL> ALTER SEQUENCE ma_sequence MINVALUE -100;

On interroge une nouvelle fois la séquence :

SQL> SELECT ma_sequence.NEXTVAL FROM DUAL;

13


SQL> SELECT ma_sequence.NEXTVAL FROM DUAL;

3

On s’aperçoit que le changement a aussi été pris en compte. Il vous est donc possible si vous en avez besoin, de modifier la valeur de l’incrément de votre séquence très facilement afin de l’adapter aux besoins de votre application.

Sous Oracle comme sur certains autres SGBDR (la plupart) lorsque vous souhaitez effectuer des sélections sur plusieurs tables et obtenir les résultats dans une seule colonne, vous pouvez utiliser la clause UNION. C’est une clause ensembliste à l’instar des clauses INTERSECT et EXCEPT par exemple. L’opérateur UNION permet de rassembler les tuples provenant de deux ou plusieurs requêtes SELECT.

Voici un exemple d’utilisation :

SELECT employeeName, employeeFirstname FROM t_employees;

employeeName employeeFirstname
---------------------------------
Dubois Michel
Norris Jean
Eddie Louis
Dupont Daniel
Durand Laurent


SELECT userName, userFirstname FROM t_users;

userName userFirstname
--------------------------
Norris Jean
Eddie Louis
Dargaud Bernard
LaMalice Denis
Dubois Michel

Imaginez maintenant que vous souhaitez les mêmes résultats mais dans un seul résultat de d’une seule requête. Vous pouvez dans ce cas utiliser la clause UNION ou la clause UNION ALL.

Avec la clause UNION :

SELECT employeeName AS Name, employeeFirstname AS Firstname FROM t_employees
UNION
SELECT userName AS Name, userFirstname AS Firstname FROM t_users;

Vous obtenez :

Name Firstname
-----------------------
Dubois Michel
Norris Jean
Eddie Louis
Dupont Daniel
Durand Laurent
Dargaud Bernard
LaMalice Denis

Remarquez que les doublons ont été automatiquement éliminés. Si vous souhaitez utiliser tous les résultats et par conséquent obtenir même les doublons, vous devrez utiliser la clause UNION ALL.

Exemple avec UNION ALL :

SELECT employeeName AS Name, employeeFirstname AS Firstname FROM t_employees
UNION ALL
SELECT userName AS Name, userFirstname AS Firstname FROM t_users;

Name Firstname
-----------------------
Dubois Michel
Norris Jean
Eddie Louis
Dupont Daniel
Durand Laurent
Norris Jean
Eddie Louis
Dargaud Bernard
LaMalice Denis
Dubois Michel

Vous obtenez donc tous les résultats y compris les doublons. Vous avez sans doute remarqué que j’utilise ici des alias sur les noms de colonnes. La raison est juste que je souhaitais pour l’exemple que les noms de collone soient unifiés.

Cet article sera par la suite complété.

Oracle propose différentes fonctions SQL, dont l’une notamment permet de retourner le code ASCII du caractère passé en paramètre. La fonction ASCII retourne donc la représentation décimale, dans le “character set” défini de la BDD, du premier caractère passé en paramètre.

Le paramètre peut être de type CHAR, VARCHAR2, NCHAR ou encore NVARCHAR2. La fonction retourne une valeur de type NUMBER. Attention toutefois, si le “character set” de votre base de données est 7-bit ASCII, alors la fonction retournera une valeur ASCII. Si par contre votre character set est défini comme code EBCDIC, la fonction retournera une valeur de type EBCDIC.

Voici un exemple d’utilisation de la fonction :

SELECT ASCII('Q') FROM DUAL;

ASCII('Q')
----------
81

L’éditeur de San José ne s’arrête plus dans la course au rachat de différents éditeurs de progiciels. Après avoir proposé l’an passé de racheter l’éditeur de logiciels BEA (concepteur du célébrissime Weblogic entre autres) pour 17 USD par action, et face au refus de BEA qui ne souhaitait pas descendre en dessous de 21 USD par action, Oracle avait jusque là mis de côté ce rachat, estimant que BEA demandait trop cher. Aucune autre entreprise ne s’étant proposée pour acheter BEA a ce tarif, Oracle en a profité pour proposer une négociation. C’est finalement à 19,375 dollars par action que l’éditeur de San José a finalement acheté BEA. La somme représente tout de même la bagatelle de 8,5 milliard de dollars.
“Nous estimons que cette opération sera positive sur le bénéfice net par action d’Oracle d’au moins 1 à 2 cents dès la première année“, a commenté Safra Catz, directeur financier du groupe.
Fort de ce rachat, la firme de Larry Ellisson devient numéro deux du marché du middleware derrière IBM. Pour information, sur les dernières années, Oracle a dépensé plus de 20 milliards de dollars et racheté plus d’une trentaine d’éditeurs de logiciels.

Sources ITRManager

Vous aurez sans doute déjà été confronté au problème de chercher à compter le nombre d’enregistrements dans une table en fonction de certaines conditions. La solution la plus appropriée à première vue serait d’effectuer la requête suivante :

SELECT count(*)
FROM [nom_table]
WHERE [la_condition] ;

Maintenant, imaginez que vous souhaitez effectuer plusieurs comptages sur la même table, et que chaque comptage doit répondre à une certaine condition. Vous seriez obligé avec la première solution d’écrire autant de requêtes que de conditions sur chaque champ.
Il existe une solution pour palier à ce genre de problème :

Création de la table :

DROP TABLE t_users ;

CREATE TABLE t_users (t_userID NUMBER,
t_userName VARCHAR2(20),
t_userAge NUMBER,
t_userGender VARCHAR2(1),
t_userSalary NUMBER) ;

INSERT INTO t_users VALUES ( 1, 'Abib', 22, 'M', 38000);
INSERT INTO t_users VALUES ( 2, 'Emilien', 20, 'M', 35000);
INSERT INTO t_users VALUES ( 3, 'Julien', 38, 'M', 30000);
INSERT INTO t_users VALUES ( 4, 'Jerome', 26, 'M', 32000);
INSERT INTO t_users VALUES ( 5, 'Clement', 15, 'M', 22000);
INSERT INTO t_users VALUES ( 6, 'Sophie', 20, 'F', 26000);
INSERT INTO t_users VALUES ( 7, 'Jessica', 21, 'F', 20000);
INSERT INTO t_users VALUES ( 8, 'Ingrid', 25, 'F', 10000);

Et la fameuse requête avec les conditions sur chaque comptage :

SELECT COUNT( CASE WHEN t_userAge >= 25 THEN 1 END) AS count_age_25,
COUNT( CASE WHEN t_userSex = 'M' THEN 1 END) AS count_gender_male,
COUNT( CASE WHEN t_ userSex = 'F' THEN 1 END) AS count_gender_female,
COUNT( CASE WHEN t_userSalary >= 22000 THEN 1 END) AS count_sal_22000
FROM t_users;

Résultat de la requête :


Count_age_25 count_gender_male count_gender_female count_sal_22000
-------------------------------------------------------------------------------

3 5 3 6

Un grand merci à l’excellent site web dba-village qui regorge d’informations.

Voici donc une requête basée sur le package PL/SQL “dbms.xmlgen” qui permet de retourner le nom et le nombre d’enregistrements de chaque table du schéma. Elle peut être très interressante à utiliser pour avoir un aperçu global du nombre d’enregistrements dans chaque table dans la base de données.


SELECT table_name,
to_number(extractvalue
(xmltype
(dbms_xmlgen.getxml
('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) nbr_lignes
FROM user_tables
ORDER BY 1;

Résultat de la requète :

TABLE_NAME NBR_LIGNES
------------------------
COUNTRIES 25
DEPARTMENTS 27
DEPT 29
DEPT2 27
EMP2 0
EMPLOYEES 107
EMPLOYEES2 107
EMPLOYES 4
JOBS 19
JOB_GRADES 6
JOB_HISTORY 10
LOCATIONS 23
MY_EMPLOYEE 4
REGIONS 4

14 rows selected.

En cas d’erreur pensez à verifier si vous avez la permission d’exécution sur le package “dbms_xmlgen”. Ce doit être activé par défaut mais vérifiez tout de même que cette modification soit effective.

Lors d’un ordre SELECT, il peut etre nécessaire de passer outre certains caractères spéciaux. Par exemple, le caractère “_” (underscore) qui est un caractère joker dans une ordre SQL. Pour ce faire on peut utiliser l’instruction ESCAPE.

Exemple :

Voici la structure de la table d’exemple nommée employes :

DESCRIBE employes;

NAME TYPE
--------------------------
EMP_ID NUMBER(38)
EMP_NOM VARCHAR2(20)
EMP_PRENOM VARCHAR2(20)
EMP_STATUT VARCHAR2(20)

Et voici son contenu :

SELECT * FROM employes;


EMP_ID EMP_NOM EMP_PRENOM EMP_STATUT
-------------------------------------------------
1 Jerome Hontarrede Cadre_superieur
2 Jean Dupont Cadre superieur
3 Miche Durand Cadre_direction
4 Pierre Martinez Cadre direction

Vous souhaitez récupérer uniquement les statuts contenant le caractère “_” (underscore) :

En toute logique vous écrivez :


SELECT *
FROM emp_statut
WHERE emp_statut LIKE '%_%';


EMP_STATUT
---------------
Cadre_superieur
Cadre superieur
Cadre_direction
Cadre direction

Ce résultat est dû au fait que le caractère underscore est un wildchar qui signifie n’importe quel caractère. Pour ne récupérer que les enregistements contenant ce caractère (ou tout du moins un caractère reconnu par Oracle comme un wildchar) vous pouvez ajouter l’instruction ESCAPE à la clause LIKE.


SELECT *
FROM emp_statut
WHERE emp_statut LIKE '%#_%' ESCAPE '#';


EMP_STATUT
---------------
Cadre_superieur
Cadre_direction

Notez toutefois que le caractère “#” n’est utilisé qu’à titre d’exmple, et peut bien évidement être remplacé par celui de votre choix.