INSIA Bases de données ORACLE – 1 – Installation SQL*Plus

INSIA
Bases de données
ORACLE – 1 – Installation
SQL*Plus – SQL Developper
http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm
http://st-curriculum.oracle.com/
http://www.oracle.com/
Bertrand LIAUDET
SOMMAIRE
SOMMAIRE
1
ORACLE
Historique, marché et concurrent
Historique
Marché et concurrent
Sites de référence
ORACLE XE (express edition)
Documentation
Environnements graphiques : SQL developper
Installation de Oracle 10g express édition
Téléchargement
Installation sous XP
Bilan de l’installation
Désinstallation
Organisation du serveur ORACLE
Le listener ORACLE : TNSLSNR.exe
Le serveur ORACLE : oracle.exe
6
6
6
6
7
7
7
7
7
7
7
8
9
10
10
10
La calculette SQL : sqlplus
Mise à jour du PATH windows
Démarrer SQLPLUS
Utilisation de SQLPLUS
Les versions graphiques de SQLPLUS
Console 10g express edition
SQL Developer
SQL*Plus graphique
11
11
11
11
11
11
11
11
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 1/36 - Bertrand LIAUDET
SQL*Plus worksheet
IQSL*Plus
11
11
CREATION D’UN UTILISATEUR
Interface console 10g Express Edition : http://localhost:8080/apex
Chemin
Afficher tous les utilisateurs
Créer un utilisateur
Interface Console 10g express edition
Interface SQLPLUS
Afficher tous les utilisateurs
Création d’un utilisateur
Attribution de droits à l'utilisateur
12
12
12
12
12
12
12
12
12
13
SQL*PLUS
Calculette SQL ORACLE : sqlplus
Documentation
Principales commandes sqlplus
Connexion
Sous SE
Sous SQL
Afficher le nom de l’utilisateur connecté
login.sql
Gestion des accents
Sous windows
Sous linux
Variables d’environnement
Lister toutes les variables
Principales variables d’environnement sqlplus
Afficher une variable
Modifie une variable
Formats d’affichage
linesize et pagesize
Taille des colonnes
Divers
Exécuter un script
Commentaires
Exécuter une commande du système d’exploitation
Gestion des transactions
Gestion de fichier
Gestion de l’affichage des résultats d’un script
14
14
14
14
14
14
14
15
15
15
15
15
16
16
16
16
16
16
16
16
17
17
17
17
17
17
17
SQL DEVELOPER
18
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 2/36 - Bertrand LIAUDET
Présentation
Fonctionnalités
Téléchargement
Documentation
Tutoriel
Installation et utilisation.
Sqldeveloper.exe
Etat du LISTENER
Plateforme JAVA
Principaux usages
Connexion d’un utilisateur
Naviguer parmi les objets d’un utilisateur
Editeur contextuel SQL et PL-SQL
18
18
18
18
18
18
18
18
19
19
19
19
19
SELECT
pseudo-table DUAL
Variables de substitution : &var et &&var
Exemple
Saisie, affectation et affichage des variables de substitution
ORDER BY attribut NULLS FIRST et LAST
Opérations ensemblistes
LIMIT
MAX ( SUM (SALAIRE)
Alias dans un ORDER BY
20
20
20
20
20
20
21
21
21
22
TYPES
Transtypage : cast
Caractères
Les types
Caractères standards
Fonctions utiles
Valeurs numériques
Les types
Fonctions utiles
Date et heure
Les types
Fonctions utiles
Données binaires
Les types
Fonctions utiles
23
23
23
23
23
23
24
24
24
24
24
24
25
25
25
COMPLEMENT DE DDL
CREATE TABLE - rappels
26
26
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 3/36 - Bertrand LIAUDET
CREATE TABLE
DROP TABLE
RENAME
ALTER TABLE
CREATE TABLE… AS SELECT
CREATE SEQUENCE : auto-incrément
Création de la séquence
Utilisation de la séquence
Modification de la séquence
Suppresion de la séquence
CHECK
Commentaires de table et de colonne
ROWID et ROWNUM
ROWID
ROWNUM
Contraintes d’intégrité référentielles
Clé primaire
Clé étrangère
ON DELETE CASCADE, ON DELETE SET NULL
Gestion des contraintes
Désactivation – Réactivation des contraintes nommées
Lister les contraintes
Différer l’application des contraintes
26
26
26
26
26
27
27
27
27
27
27
27
28
28
28
29
29
29
29
30
30
30
30
PREMIERS USAGES DU DICTIONNAIRE DES DONNEES
Tables
Lister toutes les tables
Description des attributs d’une table
Utilisateurs
Tous les utilisateurs
Utilisateur courant
Objets quelconques
Utilisateur courant
Principales vues du dictionnaire des données
La vue des vues : all_views
31
31
31
31
31
31
31
31
31
32
32
TP
Installation
Console 10g express edition : création d’utilisateur
SQLPLUS : création d’utilisateur
SQLPLUS : création et utilisation d’une BD
SQLPLUS : mise en place d’un environnement de travail
33
33
33
33
33
34
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 4/36 - Bertrand LIAUDET
Premiers select
Premières consultations du dictionnaire des données
SQL developer
DDL 36
SCRIPT Commandes
Première édition : avril 2009
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 5/36 - Bertrand LIAUDET
34
35
35
36
ORACLE
Oracle (Petit Robert électronique – 2.1) :
1) Volonté de Dieu annoncée par les prophètes et les apôtres.
2) Réponse qu'une divinité donnait à ceux qui la consultaient en certains lieux sacrés.
3) Décision, opinion exprimée avec autorité et qui jouit d'un grand crédit.
4) Personne qui parle avec autorité ou compétence.
Historique, marché et concurrent
Historique
1979 : Oracle 2. Première version commercial. Premier SGBD basé sur le SQL de CODD.
1983 : Oracle 3. Réécrit en C.
1984 : Oracle 4. Gestion des transactions.
1992 : Oracle 7. Contraintes référentielles. Procédures stockées. Triggers.
1997 : Oracle 8. Objet-relationnel.
1998 : Oracle 8i. i pour internet.
2004 : Oracle 10g. g pour grid computing : calcul distribué et gestion de cluster.
2005 : Oracle 10g express édition. Version gratuite de Oracle 10g mais bridée en nombre de
processeurs, d’enregistrements (4G0) et de mémoire (1G0). L’objectif est d’entrer sur le marché
des PME sensibles aux coût et aux promesses de l’Open Source.
Marché et concurrent
45% du marché pour Oracle en 2006.
21 % pour IBM avec DB2 (DB2 express–C, version gratuite).
18% pour Microsoft avec SQL Server (SQL Server 2008 express, version gratuite).
Total : 84 % du marché pour 3 produits.
La concurrence entre ces produits s’effectuent aussi autour de l’informatique décisionnelle
(datawharehouse et datamart, ETL, analyse et datamining, reporting).
En 2008, SQL Server accroissait ses parts de marché.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 6/36 - Bertrand LIAUDET
Sites de référence
ORACLE XE (express edition)
http://www.oracle.com/technology/products/database/xe/index.html
Documentation
http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm
file:///C:/oraclexe/app/oracle/doc/getting_started.htm
Environnements graphiques : SQL developper
http://www.oracle.com/technology/products/database/sql_developer/index.html
Environnement à installer qui offre un navigateur dans les objets de la BD, un éditeur SQL et
PL-SQL, un débogueur PL-SQL, des modèles de code.
Installation de Oracle 10g express édition
Téléchargement
A partir de http://www.oracle.com : download / database / etc.
On peut arriver à : Oracle Database 10g Express Edition
http://www.oracle.com/technology/products/database/xe/index.html
Pour une installation Windows :
http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html
OracleXEUniv.exe (216 933 372 bytes)
Installation sous XP
Utilisateur et mot de passe
Rien à paramétrer à part le mot de passe de la BD. Ce mot de passe sera utilisé pour les comptes
SYS et SYSTEM.
La fenêtre précise : l’utilisateur SYSTEM permettra de se connecter à la BD après l’installation.
Lancer la page d’accueil de la base de données
Cette option amène sur la « console 10g express edition », c’est-à-dire une version graphique et
en ligne (http://localhost:8080/apex) de la calculette SQL.
On peut se connecter en tant qu’utilisateur SYSTEM avec le mot de passe fourni à l’installation.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 7/36 - Bertrand LIAUDET
Bilan de l’installation
Menu démarrer
Le menu démarre offre 5 types de ressources :
Accès à la calculette sql
•
Exécuter la ligne de commande SQL
Démarrage et arrêt du serveur
•
Démarrer la base de données
•
Arrêter la base de données
Sauvegarde et restauration
•
Sauvegarder la base de données
•
Restaurer la base de données
Console 10g express edition
•
Environnement en ligne, utilisable après l’installation Oracle DBXE permettant d’utiliser et
d’administrer le SGBD.
Ramène à : http://localhost:8080/apex
Aide
•
Accéder à la page d'accueil de la base de données
•
Obtenir de l'aide : forum, documentation en ligne.
Répertoires des exécutables
• C:\oraclexe\app\oracle\product\10.2.0\server\BIN
Le serveur : oracle.exe
Le « listener » : TNSLSNR.exe
Le client calculette : sqlplus.exe
Répertoire des données
• C:\oraclexe\oradata\XE
Les fichiers de données oracle sont des « .DBF ». Ils ne sont interprétables que par ORACLE.
Ils correspondent à des « tablespaces ».
Processus en cours
•
Le listener : TNSLSNR.exe
•
Le serveur : oracle.exe
Programmes installés
•
Oracle Database 10g Express Edition
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 8/36 - Bertrand LIAUDET
•
Oracle Dataprovider for .NET help : http://morpheus.developpez.com/oracledotnet/#LI
Msconfig / service
Sous Windows, on trouve dans : exécuter / msconfig / sevice :
•
OracleServiceXE
•
OracleMTSRecoveryService
•
OracleXEClrAgent
• OracleXETNSListener
La présence du Listener est importante pour le bon fonctionnement de la base.
Désinstallation
Attention, après une désinstallation non finalisée, la réinstallation est impossible !
Pour une désinstallation finalisée, suivre les étapes suivantes :
1. Arrêter le serveur et tous les services ORACLE.
2. Désinstaller à partir du panneau de configuration.
3. Supprimer tout le répertoire d’installation (C:\oraclexe par défaut)
4. Supprimer tous les répertoires liés à ORACLE dans C:\Documents and
settings\utilisateur\local settings\temp
5. Supprimer tous les répertoires liés à ORACLE dans C:\Documents and settings\All
Users\Menu Démarrer\Programmes
6. Si vous avez un logiciel de nettoyage des registres, nettoyer les registres.
7. Dans la base de registre (Démarrer/Exécuter regedit) : rechercher toutes les occurrences de
« oracle » (F3). Supprimer les noms de répertoires qui n’existent plus. Supprimer les
répertoires faisant référence à oracle xe.
8. Redémarrer.
9. Si vous avez un logiciel de nettoyage des registres, nettoyer les registres. Tant qu’il reste des
registres à nettoyer. Redémarrer et recommencer.
10. Réinstaller.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 9/36 - Bertrand LIAUDET
Organisation du serveur ORACLE
Le serveur ORACLE est composé de deux processus :
•
le serveur proprement dit : oracle.exe
•
le « listener » : TNSLSNR.exe.
Le listener ORACLE : TNSLSNR.exe
Présentation du Listener
Le listener est un processus d’écoute qui reçoit les demandes de connexion distantes et les
acheminent au serveur.
Si la connexion est faite sur la machine du serveur, le listener est inutile : il peut donc être
stoppé.
Une fois l’installation terminée, le listener a été démarré : TNSLSNR.exe.
Gestionnaire du Listener
•
LSNRCTL.exe permet d’entrer dans le gestionnaire du listener.
•
Help : liste les commandes disponibles.
Pour connaître l’état du listener
•
Dans le gestionnaire du listener : status.
Pour arrêter le listener
•
Dans le gestionnaire du listener : stop ou stop nomAlias(cf status).
Pour démarrer le listener
•
Dans le gestionnaire du listener : start ou start listener
Le serveur ORACLE : oracle.exe
Une fois l’installation terminée, le serveur a été démarrée : oracle.exe.
Pour arrêter le serveur
• Raccourci « Arrêter la base de donnée ».
L’arrêt du serveur n’arrête pas le listener.
Pour démarrer le serveur
• Raccourci « Démarrer la base de donnée ».
Redémarrer la base de données redémarre le listener si nécessaire.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 10/36 - Bertrand LIAUDET
La calculette SQL : sqlplus
Une fois l’installation terminée, le programme sqlplus.exe se trouve dans le répertoire des
exécutables. C’est la calculette SQL.
Mise à jour du PATH windows
Le PATH est mis à jour par l’installation.
Rappel : Menu démarrer -> Clique droit sur le poste de travail -> Propriété -> Onglet Avancé ->
Bouton variable d'environnement
Démarrer SQLPLUS
•
A partir d’une fenêtre SE : sqlplus. Le path a du être mis à jour pendant l’installation.
•
Raccourcis d’installation : « Exécuter la ligne de commande SQL ». On rentre dans le SQL
sans être connecté : c’est l’équivalent d’un : sqlplus / nolog
Utilisation de SQLPLUS
Cf. chapitre sur SQL*PLUS
Les versions graphiques de SQLPLUS
Console 10g express edition
http://localhost:8080/apex
Environnement en ligne, utilisable après l’installation Oracle DBXE permettant d’utiliser et
d’administrer le SGBD.
SQL Developer
Interface graphique pour gérer en plus les procédures stockées et les triggers.
SQL*Plus graphique
Version « bloc-notes » du SQLPLUS de base. Pour les versions 9i et 10g.
SQL*Plus worksheet
Version “éditeur de développement » du SQLPLUS de base. Pour la version 9i.
IQSL*Plus
Interface graphique accessible sur internet. Pour les versions 9i et 10g.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 11/36 - Bertrand LIAUDET
CREATION D’UN UTILISATEUR
Interface console 10g Express Edition : http://localhost:8080/apex
Chemin
Page d’accueil > Administration > Gérer les utilisateurs.
Afficher tous les utilisateurs
On trouve : SYSTEM, SYS, ANONYMOUS et d’autres utilisateurs créé lors de l’installation
(variables selon les versions installées).
On peut regarder les rôle et droits de chaque utilisateur.
On ne peut se connecter que sous SYSTEM.
Créer un utilisateur
On peut créer un utilisateur. Par défaut, il aura les rôles CONNECT et RESOURCE.
•
CONNECT est un rôle prédéfini qui permet la création de tables et donc leur modification,
suppression, consultation.
•
RESOURCE est un rôle prédéfini qui permet de faire du PL-SQL : création de triggers et de
procédures stockées.
•
DBA donne tous les privilèges.
Interface Console 10g express edition
On peut créer un utilisateur par l’interface console 10g EE
Administration / Gérer les utilisateurs / Créer
Interface SQLPLUS
Afficher tous les utilisateurs
Select * from all_users ;
On trouve : SYSTEM, SYS, ANONYMOUS et d’autres utilisateurs créé lors de l’installation
(variables selon les versions installées).
On peut regarder les rôle et droits de chaque utilisateur.
On ne peut se connecter que sous SYSTEM.
Création d’un utilisateur
CREATE USER bertrand
IDENTIFIED BY "bertrand";
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 12/36 - Bertrand LIAUDET
A ce stade, l’utilisateur n’a aucuns droits : pas même celui de se connecter. Toutefois, il apparaît
dans la liste des utilisateurs.
Attribution de droits à l'utilisateur
GRANT CONNECT, RESOURCE TO Bertrand ;
•
CONNECT est un rôle prédéfini qui permet la création de tables et donc leur modification,
suppression, consultation.
•
RESOURCE est un rôle prédéfini qui permet de faire du PL-SQL : création de triggers et de
procédures stockées.
•
DBA donne tous les privilèges.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 13/36 - Bertrand LIAUDET
SQL*PLUS
Calculette SQL ORACLE : sqlplus
Documentation
C :> sqlplus /?
SQL > help index // toutes les commandes
SQL > help nomCommande // doc de la commande
Principales commandes sqlplus
•
connect, disconnect : pour connecter un utilisateur
•
start, @ : pour exécuter un fichier.
•
show, set : pour voir et affecter des variables d’environnement
•
host : pour exécuter une commande SE
•
spool : pour stocker les résultats d’une requête dans un fichier.
•
desc (describe)
•
rem (remark), --, /* */
•
save, get: pour enregistrer le tampon dans un fichier, pour lire un fichier dans le tampon
Connexion
Sous SE
C:>
C:>
C:>
C:>
C:>
C:>
sqlplus
sqlplus
sqlplus
sqlplus
sqlplus
sqlplus
/nolog
-- pas d’affichage des commentaires
nomUser
nomUser/password
@nomFic
-- le fichier doit commencer par 1 connexion
/nolog @nomFic
Sous SQL
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
connect
connect / as sysdba – connexion comme sysdba
connect nomUser
connect nomUser/password
disconnect
// disconnect et exit valident
exit
// la transaction : commit
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 14/36 - Bertrand LIAUDET
Afficher le nom de l’utilisateur connecté
SQL> show user
SQL> select user from dual; //dual est une pseudo-table
login.sql
Le fichier login.sql s’exécute automatiquement au démarrage de slqplus.
Le fichier doit se trouver dans le répertoire de lancement de sqlplus.
Ce fichier va permettre de paramétrer l’environnement de travail : pagesize, linesize, etc.
Gestion des accents
Sous windows
Ouvrir une fenêtre de commandes windows.
Aller dans le répertoire voulu.
Dans ce répertoire, passer la commande :
C:/monRepertoire> set NLS_LANG=FRENCH_FRANCE.WE8PC850
Dans ce répertoire, lancer SQLPLUS : les accents sont pris en compte.
C:/monRepertoire> sqlplus
Sous linux
SQL> alter session set nls_language=French;
SQL> alter session set nls_territory=France;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 15/36 - Bertrand LIAUDET
Variables d’environnement
Lister toutes les variables
SQL> show all
// Lister toutes les variables
Principales variables d’environnement sqlplus
•
user
•
linesize, pagesize, column
•
echo, termout, feedback, heading, trimspool
•
autocommit
Afficher une variable
SQL> show nomVariable // Affiche la valeur de la var.
SQL> show user
SQL> select user from dual; //dual est une pseudo-table
Modifie une variable
SQL> set linesize 80
Formats d’affichage
linesize et pagesize
linesize et pagesize sont des variable d’environnement SQLPLUS :
Consultation
SQL> show linesize
SQL> show pagesize
Modification
SQL> set linesize 100
SQL> set pagesize 200
// Taille d’une ligne de résultats
// Taille d’une page de résultats
Ces commandes peuvent être placées dans le fichier login.sql
Taille des colonnes
SQL> column mgr format 999 // nombre sur 3 chiffres
SQL> column job format a4 // chaine sur 4 caractères
SQL> column job trunc // job tronqué à la taille max
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 16/36 - Bertrand LIAUDET
Divers
Exécuter un script
SQL> @nomFichier //exécute le fichier, .sql par défaut
SQL> start nomFichier //équivalent à @
PAUSE : pour arrêter l’exécution d’un script
Commentaires
REM
-/*
*/
ligne de commentaire
ligne de commentaire
texte de commentaire
Exécuter une commande du système d’exploitation
SQL> host pwd
//exécute un pwd
SQL> host ls –l //exécute un ls
Gestion des transactions
SQL> show autocommit
// OFF par défaut
SQL> autocommit {ON | OFF | IMMEDIATE} //ON
IMMEDIATE
Gestion de fichier
SQL> save nomFich [create, replace, append]
//enregistre le buffer dans un fichier
SQL> get fichier
// met le contenu du ficher dans un buffer
Gestion de l’affichage des résultats d’un script
SQL> spool fic.txt
SQL> spool off
SQL> set echo on
// Copie l'affichage à l'ecran
// dans 'fic.lst'
// Stoppe la copie dans 'fic.lst'
// affiche la commande en cours
SQL> set feedback on // affiche le nb lignes résultat
SQL> set heading on // affiche l’entête des colonnes
SQL> set trimspool on // supprime les blancs de fin
de ligne
SQL> set termout OFF
// supprime tout affichage
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 17/36 - Bertrand LIAUDET
SQL DEVELOPER
Présentation
Fonctionnalités
ORACLE SQL Developer est un logiciel qui permet de :
Naviguer dans les objets de la base
Editer et exécuter du code SQL et PL-SSL
Deboguer du code PL-SQL
Téléchargement
http://www.oracle.com/technology/products/database/sql_developer/index.html
Documentation
http://download.oracle.com/docs/cd/E12151_01/index.htm
Tutoriel
http://st-curriculum.oracle.com/tutorial/SQLDeveloper/index.htm
Installation et utilisation.
Le package téléchargé fournit le logiciel prêt à l’emploi.
Pour pouvoir utiliser SQL Developer, il faut :
•
Avoir un processus LISTENER qui tourne.
•
Avoir une plateforme JAVA qui tourne.
Sqldeveloper.exe
Dans le répertoire « sqldeveloper », on trouve l’application « sqldevelopper.exe » prête à
l’emploi.
Il faut commencer par exécuter : sqlcli.bat
Etat du LISTENER
Se reporter au chapitre précédent sur le LISTENER
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 18/36 - Bertrand LIAUDET
Plateforme JAVA
On peut lancer le programme : « JDK / JRE / BIN / java.exe » qu’on trouve dans le répertoire
de « sqldeveloper.exe ».
Il suffit parfois aussi de lancer : « sqlcli.bat » qu’on trouve directement dans le répertoire de
« sqldeveloper.exe ».
Principaux usages
Connexion d’un utilisateur
La connexion correspond à un utilisateur.
Connexion / bouton droit / new
Il faut rentrer un nom de connexion (au choix), un nom d’utilisateur avec mot de passe, save
password, connect.
Une fois cette connexion créée, il suffira d’ouvrir le bloc de connexion pour la faire apparaître.
Naviguer parmi les objets d’un utilisateur
Dans un premier temps, on s’intéresse aux tables et aux vues.
Dans public_synonymous, on trouve les tables synonymes auxquelles on a accès (le dictionnaire
des données). Pour chacune de ces tables, on peut récupérer le code de création et donc la table
d’origine. Exemple : select * from all_users.
Editeur contextuel SQL et PL-SQL
Sur l’utilisateur : bouton droit / open SQL work sheet
L’éditeur de requêtes est contextuel : il propose les attributs, les tables, les fonctions, les motsclés possibles au fur et à mesure de l’écriture des requêtes SQL et des instructions PL-SQL.
On peut enregistrer le code avec fichier/enregistrer, ou l’icône d’enregistrement du menu
principal.
On peut ouvrir un code existant avec fichier/ouvrir, ou l’icône d’ouverture du menu principal.
On peut exécuter le script : flèche verte, ou une partie du script en sélectionnant le code à tester,
puis bouton droit / Run
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 19/36 - Bertrand LIAUDET
SELECT
pseudo-table DUAL
SELECT sysdate, 4*3, log(2, 1000000) FROM DUAL;
SELECT sysdate, systimestamp FROM DUAL;
Variables de substitution : &var et &&var
Exemple
SQL> select * from emp where job = &var_job;
Entrez une valeur pour var_job :
SQL> select * from emp where job = &&var_job;
Entrez une valeur pour var_job :
Les variables && garde leur valeur pour toute la session.
Saisie, affectation et affichage des variables de substitution
-- ACCEPT : pour créer et initialiser une variable
prompt, facultatif, permet de préciser le texte associé
SQL> accept var_job num prompt ‘entrez job’
Entrez job :
-- DEFINE : affichage de la valeur d’une variable
SQL> define var_job
// affiche la valeur de var_job
-- DEFINE = affectation d’une variable (toujours char, varchar)
SQL> define var_job=’CLERK’ //donne 1 valeur à var_job
SQL> undefine var_job // rend la variable indéfinie
ORDER BY attribut NULLS FIRST et LAST
ORDER BY attribut NULLS FIRST;
ORDER BY attribut NULLS LAST;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 20/36 - Bertrand LIAUDET
Opérations ensemblistes
INTERSECT
UNION
UNION ALL : garde les doublons
MINUS
LIMIT
Pas de LIMIT sous ORACLE.
On peut le simuler avec les ROWNUM.
Exemple :
SELECT empno, ename FROM emp
WHERE rownum < 5;
Ou
SELECT rownum empno, ename FROM emp
WHERE rownum < 5;
Attention : le order by intervient après le rownum.
Si on fait :
SELECT empno, ename FROM emp
WHERE rownum < 5
ORDER BY empno;
On obtient les mêmes personnes que sans le order by mais triées.
Pour obtenir les 4 premiers par ordre alphabétique, il faudra utiliser une imbrication dans le
from.
MAX ( SUM (SALAIRE)
Moyenne des salaires dans chaque department :
SQL> SELECT deptno, AVG( sal) FROM EMP GROUP BY deptno;
DEPTNO
AVG(SAL)
---------- ---------10
2387,5
20
2175
30 1566,66667
Moyenne des salaires des départements la plus élevée :
SELECT MAX( AVG( sal)) FROM EMP GROUP BY deptno;
Attention on ne peut plus projeter deptno !
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 21/36 - Bertrand LIAUDET
Alias dans un ORDER BY
SELECT empno, ename, sal+NVL(comm, 0) salTot
FROM Emp ORDER BY salTot;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 22/36 - Bertrand LIAUDET
TYPES
Transtypage : cast
Cast (expression AS type)
Exemple:
SELECT empno, cast(ename as varchar(10) from emp;
Caractères
Les types
CHAR( ), NCHAR( ) : chaîne fixe, 2000 caractères max.
VARCHAR2( ), NVARCHAR2 (), chaîne variable, 4000 caractères max.
CLOB, NCLOB. : jusqu’à 4 GO.
Le N correspond à des chaînes Unicode : code unique de caractère plus standard.
Caractères standards
Lettres, chiffres, symboles courants : espace tabulation % ‘ ( ) * - , . / \ : ; < > = ! _ & ~4 + | ^ ?
$#@"[]
Jeu de caractères d’une installation française : WE8ISO8859P1 (Western Europe 8-bit ISO
8859 Part 1)
Fonctions utiles
SELECT ASCII(‘A’) FROM DUAL ; -- code ASCII d’un caractère
SELECT CHR(97) FROM DUAL; -- caractère correspondant au code ASCII
SELECT DUMP(‘Bonjour’) FROM DUAL; -- notation ASCII
CONCAT(ch1, ch2) -- equivalent à l’opérateur ||
SUBSTR(ch, debut, longueur) – sous chaîne
LENGTH(ch) -- longueur de la chaîne.
INITCAP(ch) -- met l’initial en capital
UPPER(ch) -- tout en majuscule
LOWER(ch) -- tout en minuscule
RTRIM(ch) : supprime les espaces à la fin (à droite).
LTRIM(ch) : supprime les espaces au début (à gauche)
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 23/36 - Bertrand LIAUDET
Valeurs numériques
Les types
NUMBER (n, d)
De +ou – 10 –130 à +ou- 10+125. n chiffres dont d décimales. n+d <=38. 21 octets max.
BINARY_FLOAT. Sur 5 octets.
BINARY_DOUBLE. Sur 9 octets.
FLOAT
INTEGER
Fonctions utiles
NVL (attribut, valeur) : substitue une valeur NULL par une autre
REMAINDER, MOD : reste de la division entière
DUMP(valeur, 10) : explique le codage d’une valeur
Date et heure
Les types
DATE : jusqu’à la seconde;
TIMESTAMP: jusqu’à la fraction de seconde.
INTERVAL YEAR TO MONTH : intervale en années et mois.
INTERVAL DAY TO SECOND : intervale en secondes
Fonctions utiles
TO_DATE(date, format)
Formats:
‘MONTH DD, YYYY’,
‘DD MONTH YYYY’,
‘DD MM YYYY’
‘DD-MM-YYYY HH:MM:SS’
‘DD-MM-YYYY HH24:MI’
TO_CHAR(date, format)
SELECT TO_CHAR(sysdate, ‘J’) FROM DUAL; -- nb jours depuis le temps 0
SELECT TO_CHAR(sysdate, ‘DDD’) FROM DUAL; -- nb jours depuis le début de l’année
EXTRACT (partie extraite FROM date)
Partie extraite : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 24/36 - Bertrand LIAUDET
SYSDATE : date du serveur, format DATE
CURRENT_DATE : date de la session, format DATE
SYSTIMESTAMP : date du serveur, format TIMESTAMP
LOCALTIMESTAMP : date de la session, format TIMESTAMP
DBTIMEZONE : fuseau horaire du serveur, format VARCHAR2
SESSIONTIMEZONE : fuseau horaire de la session, format VARCHAR2
ADD_MONTHS : ajoute des mois à une date
ROUND(date, format) : arrondi une date selon un format (year, month, etc)
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL ;
Données binaires
Les types
BLOB : données binaires jusqu’à 4 GO
BFILE : données binaires dans un fichier externe jusqu’à 4 GO.
Fonctions utiles
Fonction BFILENAME(repertoire, fichier) : pour insérer un fichier.
Fonction LOADFROMFILE : pour charger un fichier.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 25/36 - Bertrand LIAUDET
COMPLEMENT DE DDL
CREATE TABLE - rappels
CREATE TABLE
CREATE TABLE nomTable (…) ;
CREATE TABLE user.nomTable (…) ;
Les principaux types sont : CHAR, VARCHAR2, NUMBER, DATE.
DROP TABLE
DROP TABLE nomTable ;
DROP TABLE user.nomTable;
DROP TABLE nomTable CASCADE CONSTRAINTS;
RENAME
RENAME ancienNom TO nouveauNom ;
ALTER TABLE
ALTER
ALTER
ALTER
ALTER
ALTER
ALTER
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
nomTable
nomTable
nomTable
nomTable
nomTable
nomTable
ADD…
MODIFY…
RENAME COLUMN…
DROP COLUMN…
SET UNUSED COLUMN nomCol ; -- effet immédiat
DROP UNUSED COLUMNS;
ALTER TABLE nomTable ADD CONSTRAINT…
ALTER TABLE nomTable DROP CONSTRAINT nomContrainte
[CASCADE [DROP INDEX]] ;
-- le cascade si on supprime une clé primaire
ALTER TABLE nomTable DISABLE CONSTRAINT
nomContrainte [CASCADE [DROP INDEX]] ;
ALTER TABLE nomTable ENABKE CONSTRAINT nom Contrainte ;
CREATE TABLE… AS SELECT
CREATE TABLE nomTable AS SELECT ...;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 26/36 - Bertrand LIAUDET
CREATE SEQUENCE : auto-incrément
Création de la séquence
CREATE SEQUENCE empNo
INCREMENT BY 1
START WITH 7934
NOCACHE;
Le cache permet une préallocation de la mémoire pour les valeurs de la séquence.
Utilisation de la séquence
Valeur suivante de la séquence : nomSeq.nextval
Affichage des currval et nextval en cours : pseudo-table DUAL:
SELECT empNo.nextval FROM DUAL;
Modification de la séquence
ALTER SEQUENCE empNo
Etc.
Suppresion de la séquence
DROP SEQUENCE empNo ;
CHECK
sal
NUMBER(7,2) CHECK(sal BETWEEN 0 AND 100000),
sal
NUMBER(7,2) CHECK(sal > 0),
CONSTRAINT ck_Emp_comm CHECK(comm >=0),
CONSTRAINT nn_Emp_hireDate CHECK(hireDate IS NOT NULL),
Commentaires de table et de colonne
COMMENT ON TABLE Dept IS ‘Table des départements de l’entreprise’ ;
DESC User_Tab_Comment ;
SELECT comments FROM User_Tab_Comments WHERE table_name = ‘DEPT’ ;
SELECT view_name FROM All_Views WHERE view_name like ‘%COMMENT%’;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 27/36 - Bertrand LIAUDET
ROWID et ROWNUM
ROWID
Le ROWID est un attribut qui identifie l’emplacement de chaque enregistrement (adresse).
ORACLE utilise ce ROWID pour accélérer les accès.
Le ROWID est une chaîne de 18 caractères.
Exemple
SELECT rowid, empno, ename
FROM emp
WHERE rowid ='AAADhqAABAAAKaKAAA';
ROWNUM
Le ROWNUM est un attribut qui permet une numérotation des tuples résultant d’une requête,
avant le order by.
Le rownum permet de limiter le nombre de tuples affiché : toutefois, on ne peut que sélectionner
les tuples de 1 à n (et pas de n à m), ou choisir le premier (et pas le n ième).
Pour pallier à cela, il faut imbriquer le select à rownum dans le from en renommant l’attribut
rownum.
De même si on veut numéroter après le tri.
Exemple
SELECT *
FROM
(SELECT rownum r, empno, ename, deptno FROM emp) t
WHERE r>3;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 28/36 - Bertrand LIAUDET
Contraintes d’intégrité référentielles
Clé primaire
CREATE TABLE Dept (
deptNo
NUMBER(2) NOT NULL,
dName
CHAR(14),
CONSTRAINT pk_dept PRIMARY KEY (deptNo)
);
ou
CREATE TABLE Dept (
deptNo
NUMBER(2) NOT NULL,
dName
CHAR(14),
);
ALTER TABLE DEPT ADD CONSTRAINT pk_dept PRIMARY KEY (deptNo)
ou
CREATE TABLE Dept (
deptNo
dName
);
NUMBER(2) PRIMARY KEY NOT NULL,
CHAR(14),
Dans ce cas, la contrainte n’est pas nommée.
Clé étrangère
CREATE TABLE Emp (
empNo
NUMBER(4) NOT NULL,
eName
CHAR(10),
mgr
NUMBER(4),
deptNo
NUMBER(2) NOT NULL,
CONSTRAINT fk_Emp_mgr_Emp FoREIGN KEY (mgr) REFERENCES Emp
(empNo),
CONSTRAINT fk_Emp_deptNo_Dept FOREIGN KEY (deptNo) REFERENCES Dept
(deptNo),
CONSTRAINT pk_Emp PRIMARY KEY (empNo)
);
ON DELETE CASCADE, ON DELETE SET NULL
CONSTRAINT fk_Emp_deptNo_Dept FOREIGN KEY (deptNo) REFERENCES Dept
ON DELETE CASCADE
A noter qu’il n’y a pas de ON UPDATE CASCADE ou SET NULL
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 29/36 - Bertrand LIAUDET
Gestion des contraintes
Désactivation – Réactivation des contraintes nommées
ALTER TABLE Emp DISABLE CONSTRAINT ck_Emp_comm ;
ALTER TABLE Emp ENABLE CONSTRAINT ck_Emp_comm ;
Une fois la contrainte d’intégrité désactivée, on peut violer les contraintes d’intégrité.
On ne pourra réactiver une contrainte que si les contraintes d’intégrité sont bien vérifiées.
Lister les contraintes
DESC all_constraints
DESC user_constraints
SELECT constraint_name, table_name, search_condition, status
FROM user_constraints
WHERE constraint_name LIKE ‘CK%’;
SELECT constraint_name, table_name, search_condition, status FROM
user_constraints WHERE constraint_name LIKE ‘PK%’;
Différer l’application des contraintes
Par défaut les contraintes sont :NOT DEFFERRABLE et INITIALY IMMEDIATE
L’application est IMMEDIATE et ne peut pas être différée.
IMMEDIATE signifie que la vérification de la contrainte est effectuée à chaque instruction du
DML.
On peut aussi choisir DEFERRED à la place de IMMEDIATE. L’application de la contrainte
est différé et ne peut pas être rendue immédiate.
DEFFERED signifie que la vérification sera fait au moment de la validation de la transaction
(COMMIT).
Pour pouvoir modifier le moment de l’application des contraintes, il faut définir la contrainte en
DEFFERABLE.
On peut alors modifier le moment d’application par contrainte :
SET CONSTRAINT nomContrainte IMMEDIATE (ou DEFFERED) ;
Ou pour toutes les contraintes à la fois :
SET CONSTRAINTS ALL DEFFERED ;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 30/36 - Bertrand LIAUDET
PREMIERS USAGES DU DICTIONNAIRE DES DONNEES
Tables
Lister toutes les tables
SQL> select * from cat;
SQL> select * from user_catalog;
SQL> select * from all_catalog;
SQL> desc user_tables ;
SQL> select table_name from user_tables ; // lister les table
Description des attributs d’une table
SQL> desc nomTable
table
// description d’une
Utilisateurs
Tous les utilisateurs
desc all_users ;
select * from all_users
Utilisateur courant
desc user_users ;
select username from user_users
Objets quelconques
Utilisateur courant
desc user_objects
select object_name, object_type from user_objects;
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 31/36 - Bertrand LIAUDET
Principales vues du dictionnaire des données
all_catalog
all_objects
all_views
-- tables, sequence, synonyme, vue, environ 4000
-- 19 types d’objets, environ 5000
-- les vues, environ 1000
cat
user_catalog
user_objects
user_tables
user_constraints
user_indexes
etc.
-------
équivalent à user_catalog
les tables et les séquences
tous les objets
les tables
les contraintes
les index
La vue des vues : all_views
Les tables du dictionnaires des attributs sont des vues.
En général, les vues sont préfixées soit par « all » soit par « user ».
La vue qui contient toutes les vues : « all_views »
Desc all_views
select count(*) from all_views ;
-- pour chercher les vues qui concernent les sequences :
Select view_name from all_views wher view_name like « %SEQ% » ;
-- pour chercher les vues qui concernent les privilèges :
Select view_name from all_views wher view_name like « %PRIV% » ;
Etc.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 32/36 - Bertrand LIAUDET
TP
Installation
1. Installez ORACLE.
2. Trouver le répertoire des données. A quoi correspondent les fichiers « .DBF » ?
3. Trouver et consulter le répertoire des exécutables. Quel est le nom des programmes
correspondant à : la calculette sql, le serveur, le listener.
4. Vérifiez la présence du serveur et du listener dans la liste des processus.
5. Vérifier l’état du listener en utilisant l’outil LSNRCTL.exe
6. Arrêter le serveur (proprement !).
7. Vérifier l’état du listener en utilisant l’outil LSNRCTL.exe
8. Vérifiez l’absence ou la présence du serveur et du listener dans la liste des processus.
9. Arrêter le listener en utilisant l’outil LSNRCTL.exe.
10. Vérifiez l’absence du listener dans la liste des processus.
11. Relancer le serveur.
12. Vérifiez la présence du serveur et du listener dans la liste des processus.
Console 10g express edition : création d’utilisateur
13. Créer un utilisateur à votre nom en passant par la Console 10g express edition (sur
internet) avec les rôles CONNECT et RESOURCE.
14. Afficher la liste de tous les utilisateurs.
15. Regarder le détail de quelques utilisateurs (vous, SYSTEM, SYS, etc.).
SQLPLUS : création d’utilisateur
16. Connectez vous à SQLPLUS par le raccourci. Afficher l’utilisateur courant : show user.
17. Sous SQLPLUS, connectez-vous en tant qu’utilisateur SYSTEM. Quel problème
d’affichage constatez-vous ?
18. Régler le problème des accents.
19. Afficher le nom de l’utilisateur courant.
20. Créer un utilisateur à votre prénom avec les rôles CONNECT et RESOURCE.
21. Afficher la liste de tous les utilisateurs par ordre alphabétique de nom.
22. Ecrire la requête qui répond à la question : combien y a-t-il d’utilisateurs ?
SQLPLUS : création et utilisation d’une BD
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 33/36 - Bertrand LIAUDET
23. Connecter vous en tant qu’utilisateur à votre prénom
24. Charger la base « empdept.sql » sans faire de « copier-coller » mais en exécutant le script.
Quel problème cela pose-t-il ?
25. Afficher le répertoire courant du système d’exploitation : vous devez trouvez le répertoire
des exécutables ORACLE.
26. Afficher les fichiers du répertoire courant du système d’exploitation.
***************************************************************************
27. Charger la base « emdept.sql » en faisant un copier-coller.
28. Afficher tous les employés par ordre croissant de numéro d’employés. Quel problème
d’affichage constatez-vous ?
29. Régler les problèmes d’affichage dans la session : hauteur et largeur et page d’affichage.
SQLPLUS : mise en place d’un environnement de travail
30. Mettre en place un système qui règle définitivement les problèmes d’affichage et de
répertoire courant. Pour cela : on crée un fichier sqlpluss.bat (deux « s » à la fin) dans un
répertoire « TPoracle » que vous placerez sur le bureau (par exemple). Dans ce batch, on
gère le problème des accents et celui du répertoire courant. Dans le répertoire « oracle »,
créer un fichier login.sql qui permet de résoudre les problèmes de hauteur et de largeur de
page d’affichage.
31. Vérifiez que tout fonctionne : lancer le batch. Chargez le script « empdept.sql » que vous
aurez placé dans le répertoire « TPoracle ». Afficher tous les employés.
Premiers select
Sous SQL*PLUS :
1. Chargez le script « empdept.sql »
2. Regarder le contenu et comprendre tout le code : constraint nn, create sequence, create
index, nextval…
3. Afficher la liste des tables.
4. Afficher les attributs des tables.
5. Tous les salaires, commissions et totaux (salaire + commission)
6. Tous les employés qui ne sont pas managers et qui sont embauchés en 2006. On donnera
deux réponses : avec extract et avec substr.
7. Tous les employés n'ayant pas de subordonnées. On donnera deux réponses : avec une
jointure externe et avec une opération ensembliste.
8. Afficher tous les employés du département 30 avec les caractéristique du département en
numérotant les employés de 1 à n.
9. Parmi les salariés triés par somme des salaires et des commissions, afficher ceux qui se
situent entre la 5ème et la 10ème position. On affichera le numéro de leur classement, leur
identifiant, leur nom, leur job, leur département, leur salaire, leur commission et le total.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 34/36 - Bertrand LIAUDET
10. Afficher tous les employés triés par commission puis par salaire pour une même commission,
en présentant d’abord les employés qui n’ont pas de commission.
11. Afficher tous les numéros de départements vides (avec un select imbriqué, avec une jointure
externe, avec un minus).
12. Afficher tous les départements vides, avec le numéro, le nom et la ville (avec un select
imbriqué, avec une jointure externe, avec un minus).
13. Quel est le nombre d’employés du département qui a le plus d’employés ?
14. Quel est le département, avec tous ses attributs, qui a le plus d’employés ?
15. Quel est le département, avec tous ses attributs, dont la moyenne des salaires est la plus
élevé.
Premières consultations du dictionnaire des données
1. Lister les clés de vos tables (vue user_indexes, attribut index_name, table_name,
uniqueness). Expliquer la valeur de « uniqueness ».
2. Lister les toutes les contraintes (vue user_constraints, attributs constraint_name,
table_name, r_constraint_name) par ordre alphabetique de nom. Quel est la signification de
l’attribut « r_constraint_name ».
3. Lister l’état des foreign key (attributs status, deferrable, deferred en plus). Modifier la taille
des colonnes pour que l’affichage soit propre.
4. Lister tous vos objets (vue user_objects). Choisissez trois attributs significatifs. Afficher le
résultat par ordre alphabétique. Modifier la taille des colonne pour que le résultat soit
propre.
5. Afficher les caractéristiques de la vue des tablespaces : desc user_tablespaces
6. Combien y a-t-il de tablespaces ? Lister leurs noms.
7. Afficher toutes les caractéristiques de votre tablespace.
8. Quelle est la taille de votre tablespace ? (max_extents)
9. Afficher toutes les caractéristiques de la vue des privilèges : desc user_role_priv
10. Quels sont vos droits ?
SQL developer
1.
2.
3.
4.
5.
6.
7.
8.
Installez ORACLE SQL Developer
Connectez-vous en tant qu’utilisateur à votre prenom sous SQL Developer
Ajoutez une deuxième connexion en tant qu’utilisateur SYSTEM.
Ajoutez une troisième connexion en tant qu’utilisateur « invité ».
Parcourez les tables de l’utilisateur à votre prénom. Affichez leur contenu.
Regardez le contenu de la séquence.
Modifiez la valeur du prochain numéro de séquence.
Refaites quelques requêtes déjà testées sous SQLPLUS.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 35/36 - Bertrand LIAUDET
DDL
1.
2.
3.
4.
5.
6.
Transformer le script biblio codé en MySQL en script ORACLE.
On ajoutera les séquences nécessaires.
On ajoutera les contraintes d’intégrité suivantes :
La date de retour d’un livre est postérieure à sa date d’emprunt.
La durée maximum d’emprunt est comprise entre 7 et 28 jours.
La date d’emprunt est égale à la date du jour par défaut.
SCRIPT Commandes
1. Ouvrir l’archives « Commandes.rar » : elles contient des script de création de la BD des
Commandes.
2. Pour charger la BD, il faut partir du script : CreerBD.sql
3. Analyser le fonctionnement de l’ensemble des scripts.
4. Faire le graphe des tables.
5. Répondez aux questions suivantes :
•
Quels sont les produits les plus commandés ? On traitera la question de deux façons
différentes : par le nombre d’occurrences d’un produit dans les commandes et par la quantité
commandée.
•
Pour chaque client, afficher le nombre de commandes passées par pays.
INSIA - BASES DE DONNÉES – ORACLE - 01 - page 36/36 - Bertrand LIAUDET