SQL

accept rep prompt 'COMMIT ? o/n : '

select case upper('&rep')

when 'O' then 'commit'

else 'rollback;'||chr(10)||'dbms_output.put_line(''les modifications ont été annulées'')'

end x

from dual;

execute &x

-- Executer des commandes DOS

ho cls

ho echo *** SQLPLUS &x/&y

ho TITLE - SQLPLUS &x/&y

-- Initialisation (LOG et gestion erreur)

SET ECHO OFF

HOST IF NOT EXIST "C:\LOG-SQL" MKDIR "C:\LOG-SQL"

SPOOL "C:\LOG-SQL\monLog.sql"

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

Prompt LANCEMENT DE LA REQUETE

[...]

Commit;

SPOOL OFF;

GESTION DE TEXTE

Concaténer du texte

C1 ||’ ‘ || C2

Décoder une valeur

Decode (champ, X1, ‘C1’, X2, ‘C2’, défaut)

Valeur ASCII

chr(116);

Longueur d'un texte

length( string1 )

Effacer le espaces (TRIM)

TRIM (champ) - Effacer les espaces en début et fin de la valeur

LTRIM (champ) - Effacer les espaces en début de la valeur

RTRIM (champ) - Effacer les espaces à la fin de la valeur

Modifier la casse

LOWER (champ) - Minuscule

UPPER (champ) - Majuscule

Extraire Caractère

Substr (champ, X, Y) - Y caractères à partir de X

Substr (champ, X) - Caractères à partir de X

Remplacer une chaine de caractère

Replace (valeur, TxtRecherché, TxtRemplacement)

GESTION DE DATE

Conversion DATE en TEXTE

to_char( valeur, format)

Conversion TEXTE en DATE

to_date( valeur, format)

Calcul de date

Ajouter Mois

add_months (date, nbMois)

Prochain jour de semaine

next_day(date, weekday) - SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

Dernier jour du mois

last_day (date)

Tronquer une date

Trunc (date, format)

Arrondir une date

Round (date, format)

CONVERSIONS

HEX ==> DEC

TO_NUMBER(valeur,'XXXXXXXXXXXXXXXX') - à partir d'oracle 9i

DEC ==> HEX

To_Char (valeur, 'format')

format = XXXX: 4 digit maxi sans afficher les 0 de poids fort

00XX : 4 digit maxi en affichant les 0 de poids fort

BIN ==> DEX

Bin_To_Num (1,0,1,0,1,1,...)

LOGIQUE

Calcul Binaire

Et binaire

bitand(nombre, masque)

Ou binaire

(nombre + masque) - bitand(nombre , masque)

Test Logique

AND ==> &&

OR ==> ||

XOR ==> ^^

NUMERIQUE

Fonction Mathématique

Puissance ==> Power (base, exposant)

Racine carré ==> Sqrt (valeur)

Modulo ==> MOD (valeur, diviseur)

Valeur Absolu ==> ABS (valeur)

Moyenne ==> AVG (colonne)

Somme ==> SUM (colonne)

Maximum ==> MAX (colonne)

Minimum ==> MIN (colonne)

Compteur ==> COUNT (colonne)

Tronquer/Arrondir un chiffre

trunc (chiffre, nbDécimal)

round (chiffre, nbDécimal)

expl : 125,815 --> trunc(val, 1) = 125,8 / trunc (val,-2) = 100

Structure Oracle

DBLink

Utiliser un DBLink

select * from [Tables]@[NomLienBDDPublic];

Filtre et tri sur caractère générique

Filtre

WHERE NOM LIKE 'nom\_%' escape '\'

TRI DANS REQUETE

select col1 as nomCol1 from TABLE

ORDER BY NLSSORT(nomCol1,'NLS_SORT=BINARY');

TRI DANS PARAMETRE SESSION

ALTER SESSION SET NLS_SORT='BINARY';

ALTER SESSION SET NLS_COMP='BINARY';

NLS_SORT = { BINARY | BINARY_AI | BINARY_CI | linguistic_definition };

BINARY : tri sensible aux accents et à la casse

BINARY_AI : tri NON sensible aux accents et à la casse

BINARY_CI :

linguistic_definition

Afficher la ligne avec la date la plus récente

Select * from ACCOUNTS cl

WHERE {...} AND ROWNUM = 1

ORDER BY DATESTART desc;

Traitements multi-lignes et multi-colonne

Lorsqu'une sous-requête ramène plusieur lignes l'utilisation de l'opérateur '=' dans la clause WHERE provoque une erreur. On utilisera alors des opérateurs spéciaux :

- ALL : compare à toutes les valeurs d'une liste ou d'une requête (doit suivre l'un des opérateurs suivants : =, !=, >, <, <=, >=

- IN : égal à n'importe laquelle des valeurs (équivallent à '=ANY' )

- ANY : compare à au moins une des valeurs de la liste ou du select (opérateurs idem à ALL)

- WHERE EXISTS : vrai si la sous-requête retourne au moins une ligne, n'est pas précédé d'un opérateur...

Exemples

liste de ceux qui gagnent plus qu'un employé du département 10

SQL> select ename,sal from emp

2 where sal > any (select sal from emp where deptno=10);

Expl Init

SQL> set ver off -- evite l'affichage des réécritures de constantes

SQL> set lin 32767 -- taille maxi d'une ligne

SQL> set trimspool on -- enlève les blancs en fin de ligne dans le fichier de sortie

SQL> set pages 0 -- page de taille infinie mais sans titres par défaut

SQL> set emb on -- affiche les titres une foi

SQL> set newp none -- évite les sauts de pages

SQL> set termout off -- évite la sortie sur écran, seul le fichier sera renseigné

SQL> set echo off -- évite l'affichage des requêtes et donc leur écriture

SQL> set feed off -- évite les infos de fin de requêtes (n lignes ...)

SQL> set timi off -- évite le chronométrage en fin de requête

SQL> set autot off -- évite la production d'un plan en fin de requête

SQL> set long 9999999 -- permet l'affichage de longs de 9999999 octets

SQL> set longc 9999999 -- permet l'affichage de longs de 9999999 caractères*

SQL> clear computes -- supprime les agrégations automatiques de colonnes

SQL> clear columns -- supprime les formatages de colonnes

SQL> clear breaks -- supprime les sauts

SQL> TTITLE off -- supprime les titres de haut de requête (ou de pages)

SQL> BTITLE off -- supprime les titres de bas de requête

SQL> REPHEADER off -- supprime les en-tête de rapports

SQL> REPFOOTER off -- supprime les pieds de rapports

SQL> spool <fichier de sortie>

SQL> -- instructions

SQL> spool off

Concaténer résultat sous requête

Ce que l'on a

1, 000001, blabla1

2, 000001, blabla2

3, 000002, blabla3

4, 000002, blabla4

5, 000002, blabla5

...

Ce que l'on veut :

000001, blabla1 blabla2

000002, blabla3 blabla4 blabla5

++

Utilisation de la fonction :

SELECT DISTINCT No, dbo.concat(No) As DescriptionConcaténée FROM Table_X