TP SQL-Oracle

Interrogation Catalog

Les informations sur une base de données, nom de tables, colonnes, types, etc., ainsi que les informations utilisateurs sont stockées dans un catalogue, qui est une base de données elle-même (dite métabase).

Des tables catalogue Oracle utiles sont :

• ALL_TABLES : toutes les tables accessibles à un utilisateur Oracle
• ALL_USERS : table de tous les utilisateurs Oracle
• USER_TABLES : les tables Oracle créées par l'utilisateur (leur propriétaire)
• USER_TAB_COLUMNS : la tables Oracle qui contiennent les noms de tables, leurs colonnes avec type et taille, etc.

Exemples :

1) "Le nom de toutes les tables et leur propriétaire"

SQL> SELECT owner, table_name
  2    FROM all_tables;

OWNER                          TABLE_NAME
------------------------------ ---------------
SYS                            DUAL
SYS                            SYSTEM_PRIVILEG
                               E_MAP
....

NAJIB                          EMPLOYEE
NAJIB                          PRODUCT

82 rows selected. 

2) "Idem, mais pour un propriétaire donné  (owner)"

SQL> SELECTtable_name 
  2  FROM all_tables 
  3  WHERE owner='NAJIB';

TABLE_NAME
---------------
EMPLOYEE
DEPARTMENT
PRODUCT
SELL

4 rows selected.

3) Usage de la table :

    USER_TABLES

qui contient les tables d'un propriétaire.

La même requête mais rentrée par l'utilisateur lui-même.  C'est plus simple que 2).

SQL> SELECT table_name FROM user_tables;

TABLE_NAME
---------------
EMPLOYEE
PRODUCT
DEPARTMENT
SELL

4 rows selected. 

4) Chercher les informations sur les colonnes d'une table donnée. 

Usage de la table :

    USER_TAB_COLUMNS

"Nom de colonne, type et taille des attributs de la relation employee"

SQL> SELECT table_name, column_name, data_type, data_length
  2  FROM USER_TAB_COLUMNS
  3  WHERE table_name = 'EMPLOYEE';

TABLE_NAME      COLUMN_NAME     DATA_TYPE  DATA_LENGTH
--------------- --------------- ---------- -----------
EMPLOYEE        ENUM            VARCHAR2             5
EMPLOYEE        ENAME           VARCHAR2            20
EMPLOYEE        SALARY          NUMBER              22
EMPLOYEE        ADDRESS         VARCHAR2            10
EMPLOYEE        DEPT            VARCHAR2             5

5) C'est d'ailleurs le résultat de la commande describe de SQLPlus qui génère un SQL équivalent à la 4).

SQL> describe employee;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENUM                                      NOT NULL VARCHAR2(5)
 ENAME                                              VARCHAR2(20)
 SALARY                                             NUMBER(9,2)
 ADDRESS                                            VARCHAR2(10)
 DEPT                                               VARCHAR2(5)

Autres commandes SQLPlus utiles

1) Save / Start

save : Permet de sauvegarder la requête tapée dans un fichier.
start : Permet de lancer l'exécution d'une requête précédemment sauvegardée.

• SQL> select * from employee;
SQL> save /users/najib/desktop/req.sql
Created file /users/najib/desktop/req.sql
SQL> list
  1* select * from employee
• Mettra le résultat de select dans le fichier spécifié (/users/najib/desktop/req.sql).Il faut donner  le chemin complet, sinon, le fichier est mis dans le répertoire de lancement de SQLPLus.
• SQL> start  /users/najib/desktop/req.sql
ENUM  ENAME                    SALARY ADDRESS    DEPT
----- -------------------- ---------- ---------- -----
E7    Amine                      7500 Fes        D2
E6    Aziz                       8500 Casa       D1
...
• Exécute donc la requête sauvegardée.

2) Spool

spool : Permet de sauvegarder la requête avec son résultat dans un fichier créé pour la circonstance.

• SQL> spool  /users/najib/desktop/resultat.data
  SQL> select * from employee;
  ENUM  ENAME                    SALARY ADDRESS    DEPT
  ----- -------------------- ---------- ---------- -----
  E7    Amine                      7500 Fes        D2
  ...
• SQL> spool  /users/najib/desktop/resultat.data append
  SQL> select * from product;
  PNUM  PNAME          WEIGHT      PRICE ADDRESS
  ----- ---------- ---------- ---------- ----------
  P5    Linux               3          5 Fes
  ...
  SQL> spool off

A noter :

• La commande spool mettra le résultat de select dans le fichier spécifié.
• Avec la clause append cela rallong le fichier.
• spool mettra  tous les outputs SQLPlus dans le fichier spécifié, jusqu'à ce qu'on tape spool off.

(Voir aussi https://www.comp.nus.edu.sg/~ooibc/courses/sql/sqlplus.htm)

3) Column

column : permet de modifier la taille d'affichage des colonnes résultat.

    column nom-de-colonne format motif

Exemple :

SQL> select ename, salary from employee;

ENAME                    SALARY
-------------------- ----------
Amine                      7500
Aziz                       8500
...

SQL> column ename format a8
SQL> column salary format 99999.99
SQL> select ename, salary from employee;

ENAME       SALARY
-------- ---------
Amine      7500.00
Aziz       8500.00
...

On a utilisé a8 pour afficher 8 caractères pour  ename et 99999.99 pour salary sur 7 chiffres dont 2 décimaux.

4) set pagesize

Permet de fixer le nombre de lignes afichées par page.

Il faut choisir le nombre de lignes par page selon la hauteur de sa page.

Il y a aussi set linesize, pour fixer la taille (nombre de caractères) des lignes affichées par SQLPlus sur l'écran.

SQL> set linesize 30
SQL> select * from employee;

ENUM  ENAME       SALARY
----- -------- ---------
ADDRESS    DEPT
---------- -----
E7    Amine      7500.00

Pas utile dans ce cas.

Généralement linesize et pagesize servent en conjonction avec spool pour fixer les tailles des lignes et pages de son fichier output.

5) Autres set ...

DEFINE _EDITOR

Permet de choisir son éditeur pour éditer la requête du buffer. Exemple wordpad, ou sublime :

SQL >  DEFINE _EDITOR = "C:\Program Files (x86)\Windows NT\Accessories"

ou

SQL >  DEFINE _EDITOR = "C:\Program Files\Sublime Text 3\sublime_text.exe"

Mettre les " " pour faire accepter les espaces dans le chemin d'accès au fichier.