insia bases de données oracle – 1 -...

INSIA - BASES DE DONNÉES – ORACLE - 01 - page 1/36 - Bertrand LIAUDET

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 6

Historique, marché et concurrent 6 Historique 6

Marché et concurrent 6

Sites de référence 7 ORACLE XE (express edition) 7

Documentation 7

Environnements graphiques : SQL developper 7

Installation de Oracle 10g express édition 7 Téléchargement 7

Installation sous XP 7

Bilan de l’installation 8

Désinstallation 9

Organisation du serveur ORACLE 10 Le listener ORACLE : TNSLSNR.exe 10

Le serveur ORACLE : oracle.exe 10

La calculette SQL : sqlplus 11 Mise à jour du PATH windows 11

Démarrer SQLPLUS 11

Utilisation de SQLPLUS 11

Les versions graphiques de SQLPLUS 11 Console 10g express edition 11

SQL Developer 11

SQL*Plus graphique 11


SQL*Plus worksheet 11

IQSL*Plus 11

CREATION D’UN UTILISATEUR 12

Interface console 10g Express Edition : http://localhost:8080/apex 12 Chemin 12

Afficher tous les utilisateurs 12

Créer un utilisateur 12

Interface Console 10g express edition 12

Interface SQLPLUS 12 Afficher tous les utilisateurs 12

Création d’un utilisateur 12

Attribution de droits à l'utilisateur 13

SQL*PLUS 14

Calculette SQL ORACLE : sqlplus 14 Documentation 14

Principales commandes sqlplus 14

Connexion 14 Sous SE 14

Sous SQL 14

Afficher le nom de l’utilisateur connecté 15

login.sql 15

Gestion des accents 15 Sous windows 15

Sous linux 15

Variables d’environnement 16 Lister toutes les variables 16

Principales variables d’environnement sqlplus 16

Afficher une variable 16

Modifie une variable 16

Formats d’affichage 16 linesize et pagesize 16

Taille des colonnes 16

Divers 17 Exécuter un script 17

Commentaires 17

Exécuter une commande du système d’exploitation 17

Gestion des transactions 17

Gestion de fichier 17

Gestion de l’affichage des résultats d’un script 17

SQL DEVELOPER 18


Présentation 18 Fonctionnalités 18

Téléchargement 18

Documentation 18

Tutoriel 18

Installation et utilisation. 18 Sqldeveloper.exe 18

Etat du LISTENER 18

Plateforme JAVA 19

Principaux usages 19 Connexion d’un utilisateur 19

Naviguer parmi les objets d’un utilisateur 19

Editeur contextuel SQL et PL-SQL 19

SELECT 20

pseudo-table DUAL 20

Variables de substitution : &var et &&var 20 Exemple 20

Saisie, affectation et affichage des variables de substitution 20

ORDER BY attribut NULLS FIRST et LAST 20

Opérations ensemblistes 21

LIMIT 21

MAX ( SUM (SALAIRE) 21

Alias dans un ORDER BY 22

TYPES 23

Transtypage : cast 23

Caractères 23 Les types 23

Caractères standards 23

Fonctions utiles 23

Valeurs numériques 24 Les types 24

Fonctions utiles 24

Date et heure 24 Les types 24

Fonctions utiles 24

Données binaires 25 Les types 25

Fonctions utiles 25

COMPLEMENT DE DDL 26

CREATE TABLE - rappels 26


CREATE TABLE 26

DROP TABLE 26

RENAME 26

ALTER TABLE 26

CREATE TABLE… AS SELECT 26

CREATE SEQUENCE : auto-incrément 27 Création de la séquence 27

Utilisation de la séquence 27

Modification de la séquence 27

Suppresion de la séquence 27

CHECK 27

Commentaires de table et de colonne 27

ROWID et ROWNUM 28 ROWID 28

ROWNUM 28

Contraintes d’intégrité référentielles 29 Clé primaire 29

Clé étrangère 29

ON DELETE CASCADE, ON DELETE SET NULL 29

Gestion des contraintes 30 Désactivation – Réactivation des contraintes nommées 30

Lister les contraintes 30

Différer l’application des contraintes 30

PREMIERS USAGES DU DICTIONNAIRE DES DONNEES 31

Tables 31 Lister toutes les tables 31

Description des attributs d’une table 31

Utilisateurs 31 Tous les utilisateurs 31

Utilisateur courant 31

Objets quelconques 31 Utilisateur courant 31

Principales vues du dictionnaire des données 32

La vue des vues : all_views 32

TP 33

Installation 33

Console 10g express edition : création d’utilisateur 33

SQLPLUS : création d’utilisateur 33

SQLPLUS : création et utilisation d’une BD 33

SQLPLUS : mise en place d’un environnement de travail 34


Premiers select 34

Premières consultations du dictionnaire des données 35

SQL developer 35

DDL 36

SCRIPT Commandes 36

Première édition : avril 2009


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


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.


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


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


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.


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.


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


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.


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:> sqlplus C:> sqlplus /nolog -- pas d’affichage des commentai res C:> sqlplus nomUser C:> sqlplus nomUser/password C:> sqlplus @nomFic -- le fichier doit commencer pa r 1 connexion C:> sqlplus /nolog @nomFic

Sous SQL SQL> connect SQL> connect / as sysdba – connexion comme sysdba SQL> connect nomUser SQL> connect nomUser/password SQL> disconnect // disconnect et exit valident SQL> exit // la transaction : commit


Afficher le nom de l’utilisateur connecté SQL> show user SQL> select user from dual; //dual est une pseudo-t able

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

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;


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

SQL> show user SQL> select user from dual; //dual est une pseudo-t able

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 // Taille d’une ligne de ré sultats SQL> set pagesize 200 // Taille d’une page de rés ultats

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ère s SQL> column job trunc // job tronqué à la taille ma x


Divers

Exécuter un script SQL> @nomFichier //exécute le fichier, .sql par déf aut 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 // Copie l'affichage à l'ecran // dans 'fic.lst' SQL> spool off // Stoppe la copie dans 'fic.lst' SQL> set echo on // affiche la commande en cours SQL> set feedback on // affiche le nb lignes résult at SQL> set heading on // affiche l’entête des colonn es SQL> set trimspool on // supprime les blancs de fin de ligne SQL> set termout OFF // supprime tout affichage


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


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 mots-clé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


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 ch ar, 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;


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 dep tno; 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 !


Alias dans un ORDER BY

SELECT empno, ename, sal+NVL(comm, 0) salTot FROM Emp ORDER BY salTot;


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)


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;


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.


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 TABLE nomTable ADD… ALTER TABLE nomTable MODIFY… ALTER TABLE nomTable RENAME COLUMN… ALTER TABLE nomTable DROP COLUMN… ALTER TABLE nomTable SET UNUSED COLUMN nomCol ; -- effet immédiat ALTER TABLE nomTable DROP UNUSED COLUMNS; ALTER TABLE nomTable ADD CONSTRAINT… ALTER TABLE nomTable DROP CONSTRAINT nomContrainte [CASCADE [DROP INDEX]] ; -- le cascade si on supprime une clé prima ire ALTER TABLE nomTable DISABLE CONSTRAINT nomContrainte [CASCADE [DROP INDEX]] ; ALTER TABLE nomTable ENABKE CONSTRAINT nom Contrain te ;

CREATE TABLE… AS SELECT CREATE TABLE nomTable AS SELECT ...;


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 N ULL),

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 lik e ‘%COMMENT%’;


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;


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 NUMBER(2) PRIMARY KEY NOT NULL , dName 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) REFERE NCES 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


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_conditio n, status FROM user_constraints WHERE constraint_name LIKE ‘CK%’; SELECT constraint_name, table_name, search_conditio n, 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 ;


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 // description d’u ne

table

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;


Principales vues du dictionnaire des données all_catalog -- tables, sequence, synonyme, vue, en viron 4000 all_objects -- 19 types d’objets, environ 5000 all_views -- les vues, environ 1000 cat -- équivalent à user_catalog user_catalog -- les tables et les séquences user_objects -- tous les objets user_tables -- les tables user_constraints -- les contraintes user_indexes -- les index etc.

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 sequen ces : 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.


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’utilisateu r

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


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 trava il

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.


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. Installez ORACLE SQL Developer

2. Connectez-vous en tant qu’utilisateur à votre prenom sous SQL Developer

3. Ajoutez une deuxième connexion en tant qu’utilisateur SYSTEM.

4. Ajoutez une troisième connexion en tant qu’utilisateur « invité ».

5. Parcourez les tables de l’utilisateur à votre prénom. Affichez leur contenu.

6. Regardez le contenu de la séquence.

7. Modifiez la valeur du prochain numéro de séquence.

8. Refaites quelques requêtes déjà testées sous SQLPLUS.


DDL

1. Transformer le script biblio codé en MySQL en script ORACLE.

2. On ajoutera les séquences nécessaires.

3. On ajoutera les contraintes d’intégrité suivantes :

4. La date de retour d’un livre est postérieure à sa date d’emprunt.

5. La durée maximum d’emprunt est comprise entre 7 et 28 jours.

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