Des programmes gestionnaires permettent aux utilisateurs et des programmes d’accéder à ces données et fichiers. L’ensemble de ces programmes sont dénommés habituellement « Système de gestion de base de données » (SGBD).

¨ Un SGBD assure :

n l’INTEGRITE,

n la COHERENCE,

n La SECURITE.

Des données de cette base.

¨ Le SGBD permet la gestion des accès concurrents aux données entre plusieurs utilisateurs.

Historiquement, il y a eu au départ des bases de données sans systèmes SGBDL de mise en relation des données.

Puis est apparu ce que l’on appelle les « bases réseau », vers les années 70, où les données étaient mis en relation entre elle par des pointeurs ou index. En général, ces pointeurs ou ces index étaient créées une fois pour toute, à la création de la base. Et ensuite, il n’était pas facile de les modifier.

2 Qu’est-ce qu’une base de données relationnels et un SGBDR ?

C’est une façon de présenter les données sous la forme de tableaux et de vues sur ces tableaux, à l’aide d’un langage relationnel, simple proche du langage naturel, dit de « 4° génération » (en général, le langage SQL).

Avec ce genre de bases relationnel, on peut gérer souplement les relations entre données, voire les modifier en cours de route (bien qu’il faut le faire avec prudence, et que cela ne soit pas souvent recommandé).

¨ Les données sont regroupées en TABLE.

TABLCONC

DEPT	CODE CONCESSION	NOM CONCESSION	MARQUE	CLIENTS 1996
29	001	Automobile Brestoise	RENAULT	520
29	002	Concession Peugeot Quimper	PEUGEOT	325
56	001	La Lorientaise de l’Automobile	FIAT	150
56	002	Vannes Europe Automobile	RENAULT	246
85	001	A.V.A.	FORD	150

TABLEDEP

CODE DEPARTEMENT	NOM DEPARTEMENT	HABITANTS (M)
29	FINISTERE	1,2
22	COTES D’ARMOR	0,5
56	MORBIHAN	0,8
85	VENDEE	0,4
44	LOIRE-ATLANTIQUE	2,1

¨ L’intersection d’une ligne et d’une colonne est un champ.

¨ Un CHAMP non renseigné est NULL.

¨ Ordre quelconque des LIGNES et des COLONNES.

3 Les bases Oracles et Oracle par rapport aux autres bases (DB2, SYBASE …).

¨ Les données d’ORACLE sont manipulables par un langage de haut niveau (SQL), ce qui permet :

n La portabilité des applications,

n Une plus grande durée de vie des applications,

n La communication entre systèmes,

n Une formation des intervenants à un seul interface (SQL …).

Ce langage a été créé par IBM dans les années 1976 et mis dans le domaine public.

ORACLE qui a repris le langage SQL a été créé vers 1979.

Petit schéma de l’historique d’Oracle jusqu’aux années 90.

Plusieurs sociétés concurrentes, à la société ORACLE, ont créé leur propre base de données relationnelle, vers la même époque :

IBM, avec la base DB2, sur gros systèmes IBM.
INGRES,
SYBASE.
…

Parts de marché d’Oracle par rapport à la concurrence en 2004.

Puis des bases relationnelles ont été créées sur micros _comme ACCESS de Microsoft etc … _, et sur serveurs Windows _ comme SQLServer de Microsoft …

Le relationnel avec le langage SQL est devenu une norme universelle largement répandue.

Oracle se situe dans la gamme des bases de données de haut de gamme et haut niveau, très puissante. Son offre logiciel est immense. Et beaucoup d’autres progiciels tourne sur un noyau Oracle (des outils de CRM, de gestions du personnel _ par exemple SAP … _, de gestion de parc informatique …).

ORACLE est disponible sur pratiquement toutes les machines du marché (UNIX, gros système, Windows, MAC OS, Tandem etc …).

Toute application ORACLE est entièrement portable (à ~100 %) d’une machine ou plate-forme matérielle à l’autre, du moment que c’est la même version d’ORACLE installée sur ces machines. La très grande force d’ORACLE est sa portabilité intégrale d’une machine à l’autre (quelque soit le système d’exploitation de cette dernière).

Sa seconde force est la fiabilité d’ORACLE. Sinon, les messages d’erreurs d’ORACLE sont numérotés, parfaitement documentés, expliquées dans le détail (comme pour la base de donnée concurrente gros système DB2).

Sa troisième force est qu’ORACLE peut aussi bien fonctionner avec des petites bases ou des bases gigantesques de l’ordre du Terabytes (du milliard de milliard d’octets, 10¹² octets). Avec un petit nombre d’utilisateurs, ou avec un nombre d’utilisateurs immense (de l’ordre du million d’utilisateurs).

4 Le SQL et SQLPLUS

SQL signifie Structured Query Language (langage d’interrogation structurée).

Pour ce langage SQL, une base est un regroupement logique de TABLES.

Dans le langage SQL, les notions les plus importantes sont celles de tables (TABLE), de colonnes, de lignes dans ces tables, d’index sur ces tables, de vues sur ces tables (VIEW).

Un ordre important dans ce langage, est l’ordre d’interrogation de table, ou de plusieurs tables, de vues, de lignes et de colonnes : SELECT (dont nous verrons la syntaxe au paragraphe suivant).

Le langage SQL est une sorte de « JCL » d’accès aux données de la base.

L’interface la plus simple pour taper ou exécuter en batch, les ordres SQL est le programme SQLPLUS d’ORACLE.

On y tape des commandes sous la forme de lignes de commandes.

Les résultats apparaissent sous la forme d’états (de rapports) sous SQLPLUS, qu’on peut formater, en plus, avec des commandes SQLPLUS.

Mais on peut aussi passer des ordres SQL à travers des écrans de saisies et d’interrogation (l’outils natif Oracle de gestion d’écrans transactionnels étant SQL*Forms) ou de génération de rapports ((l’outils natif Oracle de gestion de rapports / d’états étant SQL*Report). Et bien d’autres interfaces encore (Business Object, VB, PowerBuilder … etc. etc. …).

¨ Langage de définition (DDL : Data Definition Language).

                                           CREATE
                                           DROP
                                           ALTER

¨ Langage de manipulation (DML : Data Manipulation Language).

                                           SELECT
                                           INSERT
                                           UPDATE
                                           DELETE

¨ Langage de contrôle.

GRANT
REVOKE

5 Notions de tables, index, vues.

5.1 La table, description.

¨ L’objet des bases de données relationnelles.

¨ Elle contient les données organisées en LIGNES et COLONNES

CREATE   TABLE   table-name
                  (column-definition)
                  [IN database-name.tablespace-name]
                  ...

5.2 Les types de données

¨ CHAR (n)

¨ VARCHAR (n)

¨ SMALLINT
(Entiers compris entre -32768 et 32767)

¨ INTEGER
(Entiers compris entre -2 147 483 648 et 2 147 483 647)

¨ DECIMAL ( p , s )

(p = nombre total de digits, s = digits après la virgule)

¨ FLOAT (n)

¨ DATE
(Exemple : ‘1997-01-31’)

¨ TIME
(Exemple : ‘18.15.24’)

¨ TIMESTAMP
(Exemple : ‘1997-01-31-18.15.24.456789’)

¨ GRAPHIC (n)

¨ VARGRAPHIC (n)

5.3 La clause NULL

¨ NULL indique un champ non renseigné.

TABLCONC

DEPT	CODE CONCESSION	NOM CONCESSION	MARQUE	CLIENTS 1996
29	001	Automobile Brestoise	RENAULT	520
29	002	Concession Peugeot Quimper	PEUGEOT	325
56	001	La Lorientaise de l’Automobile	FIAT	--
56	002	Vannes Europe Automobile	RENAULT	246
85	001	A.V.A.	FORD	150

¨ Si aucune précision au niveau du champ, alors un champ NULL est accepté.

¨ Si NOT NULL est précisé à la définition du CHAMP, alors le Champ doit être renseigné.

¨ Si NOT NULL WITH DEFAULT est précisé à la définition du CHAMP, alors le Champ prend les valeurs :

0, pour les numériques,

blanc, pour alphanumériques.

¨ NULL est ignoré dans les fonctions COLONNES.

Exemple :

MARQUE	STOCK
RENAULT	12
PEUGEOT	--
FIAT	12

AVG(STOCK) = 12

NOT NULL WITH DEFAULT donnerait :
AVG(STOCK) = 8

5.4 Les noms de TABLE.

¨ Format général

CREATEUR.NOM_TABLE

¨ Format implicite

NOM_TABLE

Exemple :

Þ DBADM.TABLCONC

Þ TABLCONC est accessible par le USER DBADM ou par une personne autorisée par DBADM.

Þ DBADM.TABLCONC et ETUD.TABLCONC sont deux tables différentes et l’appel implicite est identique.

6 Les vues.

TABLCONC

DEPT	CODE CONCESSION	NOM CONCESSION	MARQUE	CLIENTS 1996
29	001	Automobile Brestoise	RENAULT	520
29	002	Concession Peugeot Quimper	PEUGEOT	325
56	001	La Lorientaise de l’Automobile	FIAT	150
56	002	Vannes Europe Automobile	RENAULT	246
85	001	A.V.A.	FORD	150

TABLEDEP

CODE DEPARTEMENT	NOM DEPARTEMENT	HABITANTS (M)
29	FINISTERE	1,2
22	COTES D’ARMOR	0,5
56	MORBIHAN	0,8
85	VENDEE	0,4
44	LOIRE-ATLANTIQUE	2,1

DEPARTEMENT

NOM_DEPT	NOM	HABITANT	NBCLIENT
FINISTERE	Automobile Brestoise	1,2	520
FINISTERE	Concession Peugeot Quimper	1,2	325
MORBIHAN	La Lorientaise de l’Automobile	0,8	150
MORBIHAN	Vannes Europe Automobile	0,8	246
VENDEE	A.V.A.	0,4	150

¨ Restrictions en mise à jour, pas de mise à jour si compaction de l’information.

CREATE   VIEW    view-name
                  (column-name, column-name, ...)
                  AS SELECT ...
                  ...

6.1 Les Index.

¨ En ORACLE, un index ne sert qu’à améliorer la performance des accès.

¨ L’index permet l’accès direct à l’information.

¨ Il est géré par l’administrateur de la base de donnée.

CREATE   INDEX   index-name
                  ON table-name (column-name ASC, ...)
                  ...

¨ L’INDEX UNIQUE est obligatoire si une clé primaire a été définie sur la table.

CREATE UNIQUE INDEX index-name
                        ON table-name(column-name ASC, ...)
                        ...

¨ L’INDEX CLUSTER (un seul par table) assure un rangement physique des lignes dans l’ordre de cet index.

CREATE   INDEX     index-name
                     ON table-name(column-name ASC, ...)
                     CLUSTER
                     ...

6.2 Les Synonymes.

¨ Ils permettent de nommer une TABLE par un autre nom.

CREATE   SYNONYM   synonym
                     FOR table-name

CREATE   SYNONYM   synonym
                     FOR view-name

7 L’ordre SELECT

7.1 Généralités

¨ L’ordre SELECT est structuré par clause.

SELECT Column-name, column-name,...

           FROM ...

           WHERE ...

           GROUP BY ...

           HAVING ...

           ORDER BY ...

¨ Clauses obligatoires.

SELECT ... FROM ...

¨ Dépendances des clauses.

SELECT ... GROUP BY ...

7.2 Définition des colonnes.

SELECT Column-name, column-name,...

           FROM ...

           WHERE ...

           GROUP BY ...

           HAVING ...

           ORDER BY ...

p La clause SELECT est suivie de la liste des colonnes que l’on veut extraire de la table, ces colonnes définissent les éléments de la TABLE résultat.

Exemple :

TABLCONC

DEPT	CODE	NOM	MARQUE	NBCLIENT
29	001	Automobile Brestoise	RENAULT	520
29	002	Concession Peugeot Quimper	PEUGEOT	325
56	001	La Lorientaise de l’Automobile	FIAT	150
56	002	Vannes Europe Automobile	RENAULT	246
85	001	A.V.A.	FORD	150

SELECT NOM,NBCLIENT FROM TABLECONC

Résultat :

NOM	NBCLIENT
Automobile Brestoise	520
Concession Peugeot Quimper	325
La Lorientaise de l’Automobile	150
Vannes Europe Automobile	246
A.V.A.	150

p Un ou plusieurs éléments de cette liste peut être une manipulation de colonnes de la table origine.

p Les manipulations possibles sont :

n l’opérateur de concaténation : ‘||’,

Il sert à regrouper deux chaînes de caractères en une seule.

Requête : SELECT NOM ||’SUR LE DEPARTEMENT ’ || DEPT
FROM TABLCONC

Résultat :
AUTOMOBILE BRESTOISE SUR LE DEPARTEMENT 29
Concession Peugeot Quimper SUR LE DEPARTEMENT 29
....

n les fonctions colonnes,

MIN renvoie le minimum de la colonne

MAX renvoie le maximum de la colonne

SUM renvoie la somme des valeurs

AVG renvoie la moyenne des valeurs de la colonne

COUNT renvoie le nombre de lignes sélectionnées

Requête : SELECT SUM(NBCLIENT)
FROM TABLCONC
WHERE DEPT = 29

Résultat :

SUM(NBCLIENT)

845

n les fonctions scalaires,

Résultat sur chaque champ de la colonne

CHAR transforme une DATE en une chaîne de caractères

DECIMAL transforme un nombre en format décimal

DIGIT transforme un nombre en sa chaîne de caractères

FLOAT transforme un nombre en format flottant

HEX chaîne de caractères représentant le champ en hexadécimal

INTEGER retourne la valeur entière du nombre

LENGTH longueur d’un champ quelconque

SUBSTR extraction de chaîne de caractères

VALUE substitution du champ NULL par deuxième argument

Combinaison possible de plusieurs fonctions.

Exemple : SUBSTR(DIGIT(...),LENGTH(...),7)

n les fonctions de date et d’heure,

DATE
YEAR
MONTH
DAY
DAYS
HOUR
MINUTE
SECOND
MICROSECOND
TIME
TIMESTAMP

Plusieurs types d’argument possibles pour chaque fonction.

Combinaison possible de plusieurs fonctions.

Fonction :

TIME ( TIMESTAMP(’1997-01-31-15.47.25.123456’) )

Résultat :

’15.47.25’

n les fonctions arithmétiques : +, -, *, /

Le résultat dépend des types de données en entrée. Il faut distinguer les calculs effectués avec : les ENTIERS, les DECIMAUX et les FLOTTANTS.

Exemple :
4 / 3 1
4.0 / 3 1.333333333

n les opérations sur les dates et heures : +, -

Exemples :

’1997-02-28’ - ’1996-11-25’ Invalide

(soustraction de caractères)

DATE(’1997-02-28’) - ’1996-11-25’ VALIDE

ORACLE reconnaît une expression arithmétique sur les dates.

n une combinaison des manipulations précédentes.

p Le SELECT * FROM, permet la substitution du nom de toutes les colonnes de la TABLE :

n Diminution de la clarté du programme,

n Affecte les performances,

Exemple :

SELECT * FROM TABLCONC

est équivalent à

SELECT DEPT,CODE,NOM,MARQUE,NBCLIENT FROM TABLCONC

p Le SELECT DISTINCT ... FROM, permet l’élimination des lignes en double de la table.

Exemple :

SELECT DEPT FROM TABLCONC

Résultat :

DEPT

SELECT DISTINCT DEPT FROM TABLCONC

Résultat :

DEPT

7.3 Définition clause FROM.

SELECT Column-name, column-name,...

           FROM ...

           WHERE ...

           GROUP BY ...

           HAVING ...

           ORDER BY ...

p Elle définit la TABLE ou les TABLES dans lesquelles se trouvent les colonnes spécifiées en column-name de la clause SELECT.

p La TABLE peut être une VUE.

p Les noms de TABLES ou de VUES sont séparés par des virgules.

p Si un nom de colonne est commun à deux tables, ce nom de colonne doit être préfixé par le nom de la TABLE ou par une chaîne de caractères associée à la TABLE.

Exemple :
Le Champ DEPT est défini dans la TABLE TABLCONC et dans la TABLE TABLEDEP.

SELECT TABLECONC.DEPT, NOM_DEPT
FROM TABLCONC , TABLEDEP
WHERE ...

SELECT A.DEPT, NOM_DEPT
FROM TABLCONC A, TABLEDEP
WHERE ...

7.4 Définition clause WHERE.

SELECT Column-name, column-name,...

           FROM ...

           WHERE ...

           GROUP BY ...

           HAVING ...

           ORDER BY ...

p Elle définit les critères de sélections sur les lignes des TABLES de la clause FROM.

p Une ligne est sélectionnée si elle vérifie l’ensemble des critères de la clause WHERE.

p Un critère peut être associé avec d’autres critères par l’intermédiaire des opérations logiques AND et OR.

Exemple :

SELECT   DEPT,NOM,MARQUE,NBCLIENT
         FROM TABLCONC
         WHERE NBCLIENT > 350
         AND DEPT = ‘29’

Résultat :

DEPT	NOM	MARQUE	NBCLIENT
29	Automobile Brestoise	RENAULT	520

7.5 Clause WHERE et la structure des critères de sélection.

=	Egal
<>, ^=	Différent
<	Inférieur
<=, ^>	Inférieur ou égal
^<, >=	Supérieur ou égal
>	Supérieur
LIKE	Est similaire à
BETWEEN	Compris entre las valeurs
IN	Appartient à l’ensemble

p Tout critère peut être infirmé par l’opérateur logique NOT.

Exemple :

SELECT   DEPT,NOM,MARQUE,NBCLIENT
         FROM TABLCONC
         WHERE NOT DEPT IN (‘29’,’85’)
         AND NBCLIENT > 200

Résultat :

DEPT	NOM	MARQUE	NBCLIENT
56	Vannes Europe Automobile	RENAULT	246

p LIKE identifie une colonne à une chaîne de caractères, il peut être précédé de l’opérateur NOT.

% : masque de longueur quelconque,

_ : masque de 1 caractère.

Exemple :

SELECT   DEPT,NOM,MARQUE,NBCLIENT
         FROM TABLCONC
         WHERE NOM LIKE ‘%AUTO%’
         AND DEPT LIKE ‘5_’

Résultat :

DEPT	NOM	MARQUE	NBCLIENT
56	La Lorientaise de l’Automobile	FIAT	150
56	Vannes Europe Automobile	RENAULT	246

p .... BETWEEN ... AND ... , vérifie si une expression est dans l’intervalle de deux valeurs calculées.

Exemple :

WHERE NBCLIENT BETWEEN 200 AND 300

est équivalent à

( NBCLIENT >= 200 ) AND ( NBCLIENT <= 300 )

p .... IN ... , vérifie si une expression calculée se trouve dans un ensemble de valeurs. L’ensemble peut-être :

n Une liste finie de valeurs

(...,...,...,...,...)

n Commande SELECT (SUBSELECT)

Exemple :

SELECT   DEPT,NOM,MARQUE,NBCLIENT
         FROM TABLCONC
         WHERE DEPT IN
               SELECT DEPT
                   FROM TABLEDEP
                   WHERE HABITANT > 1

Résultat :

DEPT	NOM	MARQUE	NBCLIENT
29	Automobile Brestoise	RENAULT	520
29	Concession Peugeot Quimper	PEUGEOT	325

p Remarques : Dans un critère, une colonne est définie de la même façon que dans la clause SELECT.

7.6 Définition clause GROUP BY.

SELECT Column-name, column-name,...

           FROM ...

           WHERE ...

           GROUP BY ...

           HAVING ...

           ORDER BY ...

p Elle permet le regroupement des lignes en correspondance avec une fonction colonne (MIN, AVG, ...).

p Cette clause est suivie par une liste de colonnes pour spécifier des ruptures et des sous ruptures.

Exemple :

SELECT   DEPT,SUM(NBCLIENT),AVG(NBCLIENT)
         FROM TABLCONC
         GROUP BY DEPT

Résultat :

DEPT	SUM(NBCLIENT)	AVG(NBCLIENT)
29	845	422,5
56	396	198
85	150	150

7.7 Définition clause HAVING.

SELECT Column-name, column-name,...

           FROM ...

           WHERE ...

           GROUP BY ...

           HAVING ...

           ORDER BY ...

p Le HAVING est à la table résultat ce que le WHERE est à la table initiale.

p L’ordre HAVING intervient après application du GROUP BY.

p Les critères du HAVING s’appliquent sur les colonnes de la clause SELECT.

Exemple :

SELECT   DEPT,SUM(NBCLIENT),AVG(NBCLIENT)
         FROM TABLCONC
         GROUP BY DEPT
         HAVING SUM(NBCLIENT) > 200

Résultat :

DEPT	SUM(NBCLIENT)	AVG(NBCLIENT)
29	845	422,5
56	396	198

7.8 Définition clause ORDER BY.

SELECT Column-name, column-name,...

           FROM ...

           WHERE ...

           GROUP BY ...

           HAVING ...

           ORDER BY ...

p L’ORDER BY permet le tri des lignes de la TABLE sur les colonnes spécifiées.

p La colonne ou les colonnes spécifiées sont celles de la clause SELECT (Si pas de calcul).

p Le tri peut-être ascendant ou descendant.

Exemple :

SELECT   DEPT,NOM,MARQUE,NBCLIENT
         FROM TABLCONC
         ORDER BY NBCLIENT DESC, DEPT ASC

Résultat :

DEPT	NOM	MARQUE	NBCLIENT
29	Automobile Brestoise	RENAULT	520
29	Concession Peugeot Quimper	PEUGEOT	325
56	Vannes Europe Automobile	RENAULT	246
56	La Lorientaise de l’Automobile	FIAT	150
85	A.V.A.	FORD	150

7.9 Définition du SUBSELECT.

p Le SUBSELECT est un ordre SELECT imbriqué dans un autre ordre SELECT.

p Le SUBSELECT se positionne :

n Derrière un opérateur de comparaison,

n Derrière l’opérateur IN.

p Le SUBSELECT s’inclus dans une clause WHERE ou HAVING.

SELECT   ...
         FROM ...
         WHERE ... IN (SELECT ...
                         FROM ...
                         WHERE ... > (SELECT ...
                                       FROM ...
                                       WHERE ...
                                       )
                         )

7.10Définition d’une jointure.

p Les colonnes de la TABLE résultante appartiennent à plusieurs TABLES.

p Mise en place d’une égalité de deux colonnes de tables différentes dans la clause WHERE.

p Le coût n’est pas à négliger à la formulation de la requête.

Exemple :

SELECT   CONC.DEPT,CONC.NOM,DEP.HABITANT,CONC.NBCLIENT
         FROM TABLCONC CONC, TABLEDEP DEP
         WHERE CONC.DEPT = DEP.DEPT
         ORDER BY CONC.DEPT

Résultat :

DEPT	NOM	HABITANT	NBCLIENT
29	Automobile Brestoise	1,2	520
29	Concession Peugeot Quimper	1,2	325
56	La Lorientaise de l’Automobile	0,8	150
56	Vannes Europe Automobile	0,8	246
85	A.V.A.	0,4	150

8 Le langage de manipulation

8.1 L’ordre INSERT

p Commande SQL d’ajout de lignes selon deux formats possibles :

p Insertion d’une ligne

INSERT INTO ...

VALUES ( ... )

p Insertion de plusieurs lignes

INSERT INTO ...

SELECT ...

p La clause INTO spécifie la TABLE ou la VUE.

p La liste des colonnes peut-être spécifiée dans la clause INTO.

p La clause VALUE impose une valeur pour chaque champ.

p La clause SELECT ... doit spécifier le même type de champ que la TABLE de la clause INTO.

p L’INSERT est effectif au COMMIT.

Exemples :

INSERT   INTO TABLECONC
           VALUES (‘44’,
                   ‘010’
                   ‘FORD NANTES AUTOMOBILE’,
                   ‘FORD’,
                   0)

Résultat :

TABLCONC

DEPT	CODE	NOM	MARQUE	NBCLIENT
29	001	Automobile Brestoise	RENAULT	520
29	002	Concession Peugeot Quimper	PEUGEOT	325
56	001	La Lorientaise de l’Automobile	FIAT	150
56	002	Vannes Europe Automobile	RENAULT	246
85	001	A.V.A.	FORD	150
44	010	FORD NANTES AUTOMOBILE	FORD	0

INSERT   INTO TABLEDEP
           SELECT *
               FROM TABLDEP75
               WHERE DEPT = ‘75’)

DEPT	NOM_DEPT	HABITANT
29	FINISTERE	1,2
22	COTES D’ARMOR	0,5
56	MORBIHAN	0,8
85	VENDEE	0,4
44	LOIRE-ATLANTIQUE	2,1
75	PARIS	7,8

8.2 L’ordre UPDATE

¨ Commande SQL de modification de lignes.

UPDATE ...

SET ...

WHERE ...

p La clause WHERE est optionnelle, la mise à jour portera sur toute la table si celle-ci est omise.

p La mise à jour de la TABLE est effective au COMMIT.

p La clause UPDATE spécifie la TABLE ou la VUE sur laquelle porte l’ordre de mise à jour.

p La clause SET définit les colonnes mises à jour, la liste est partielle ou totale.

Syntaxe : SET colonne = expression,

colonne = expression,

...

L’expression est identique à l’expression de l’ordre SELECT.

p La clause WHERE définit les lignes.

Utilisation identique à la clause WHERE de l’ordre SELECT.

Exemples :

UPDATE   TABLECONC
           SET NOM = ‘ANJOU VENDEE AUTOMOBILE’
           WHERE DEPT = ‘85’
           AND CODE = ‘001’

Résultat :

TABLCONC

DEPT	CODE	NOM	MARQUE	NBCLIENT
29	001	Automobile Brestoise	RENAULT	520
29	002	Concession Peugeot Quimper	PEUGEOT	325
56	001	La Lorientaise de l’Automobile	FIAT	150
56	002	Vannes Europe Automobile	RENAULT	246
85	001	ANJOU VENDEE AUTOMOBILE	FORD	150
44	001	FORD NANTES AUTOMOBILE	FORD	0

8.3 L’ordre DELETE

p Commande SQL de suppression de lignes.

DELETE FROM ...

WHERE ...

p FROM spécifie la table concernée

p WHERE indique les lignes à supprimer, si la clause n’est pas spécifiée toutes les lignes de la table seront supprimées.

p La suppression des lignes de la TABLE est effective au COMMIT.

Exemples :

DELETE   FROM TABLECONC
           WHERE DEPT = ‘44’
           AND CODE = ‘001’

Résultat :

TABLCONC

DEPT	CODE	NOM	MARQUE	NBCLIENT
29	001	Automobile Brestoise	RENAULT	520
29	002	Concession Peugeot Quimper	PEUGEOT	325
56	001	La Lorientaise de l’Automobile	FIAT	150
56	002	Vannes Europe Automobile	RENAULT	246
85	001	ANJOU VENDEE AUTOMOBILE	FORD	150

9 Le langage de contrôle

9.1 La commande GRANT.

p Tout accès à un objet ORACLE n’est possible que par un privilège accordé (par la commande GRANT).

p Plusieurs privilèges :

n DATABASE

n PLAN

n SYSTEM

n TABLE ou VUE

n USE privilège

p Privilèges :

n Nominatif

GRANT   SELECT
          ON TABLE TABLCONC
          TO XUSERX

n Public

GRANT   SELECT
          ON TABLE TABLCONC
          TO PUBLIC

n Avec option GRANT

GRANT   SELECT
          ON TABLE TABLCONC
          TO XUSERX
          WITH GRANT OPTION

9.2 La commande REVOKE.

p Tout privilège accordé peut être supprimé par la commande REVOKE.

p Plusieurs privilèges révocables :

n DATABASE

n PLAN

n SYSTEM

n TABLE ou VUE

n USE privilège

p Privilèges :

n Nominatif

REVOKE SELECT
ON TABLE TABLCONC
FROM YUSERY

n Public

REVOKE SELECT
ON TABLE TABLCONC
FROM PUBLIC

p Les privilèges disparaissent hiérarchiquement, la suppression d’un privilège sur un utilisateur qui avait donné lui-même des privilèges à d’autres utilisateurs supprime ces privilèges.

p Les privilèges sont supprimés sur la commande DROP.

10 Le langage de définition

10.1 La commande CREATE.

p Définition des objets ORACLE.

p Une commande par objet : DATABASE, STOGROUP, TABLE, TABLESPACE, INDEX, SYNONYM, ALIAS, VIEW.

p Les informations sont stockées au catalogue.

p Le SQL-id du créateur des objets définit le préfixe du nom des TABLES, VUES, ALIAS.

p La création des objets se fait hiérarchiquement.

1. La DATABASE et le STORAGE GROUP,

2. Les TABLESPACE,

3. Les TABLES,

4. Les INDEX et les VUES.
3.2.1.1. Le Create TABLE.

CREATE TABLE Table name

(Column definition, column definition, ...,
[PRIMARY KEY (Column name, ...)],

[UNIQUE (Column name, ...)],

              [FOREIGN KEY Constraint name (Column name, ...)
              REFERENCES Table name [ON DELETE rules] ])
              IN Database name.tablespace name ...

ü Primary key : Identification des colonnes identifiant une clé primaire sur la table, la définition de la table est incomplète sans la création de l’index associé.

ü Unique : Identification des colonnes identifiant une unicité sur la table, la définition de la table est incomplète sans la création de l’index associé.

ü Foreign key: Identification des colonnes identifiant une clé étrangère, identification d’une relation avec une autre table et avec des contraintes associées.

ü ON DELETE rules : Identification des règles de suppression entre la table parent et les tables dépendantese.

ON DELETE RESTRICT : Erreur sur la suppression d’une ligne de la table parent.

ON DELETE CASCADE : Suppression en cascade des lignes des tables dépendantes.

ON DELETE SET NULL : Mise à la valeur NULL des clés étrangères.

Exemple : CREATE TABLE ETU.TW0DEPT

(DEPT CHAR(2) NOT NULL,

NOMDEPT CHAR(20) NOT NULL,

NBHABIT DECIMAL(5,2) NOT NULL,

PRIMARY KEY (DEPT));

CREATE TABLE ETU.TW0CONC

(DEPT CHAR(2) NOT NULL,

CODECONC CHAR(3) NOT NULL,

NOMCONC CHAR(20) NOT NULL,

ILCONC CHAR(20) NOT NULL,

NBCLIENT INTEGER,

PRIMARY KEY (DEPT, CODECONC),

FOREIGN KEY CONT1 (DEPT) REFERENCES (ETU.TW0DEPT) );

10.2 Le Create VIEW.

CREATE VIEW View name

              (Column name, column name, ...)
              AS Subselect

Exemple : CREATE VIEW WEMP1
AS SELECT * FROM DSN8230.EMP WHERE SALARY < 35000.00;

10.3Le Create INDEX.

CREATE [UNIQUE] INDEX Index name

              ON Table name (Column name ASC/DESC, ...)
              [USING STOGROUP Stogroup name
                             PRIQTY integer
                             SECQTY integer]

[CLUSTER [PART integer VALUES (constant, ...)]]

Exemple : CREATE UNIQUE INDEX DSN8310.XDEPT1

                                                         ON DSN8310.DEPT
                                                         (DEPTNO ASC)
                                                         USING STOGROUP DSN8G310 ;

10.4La commande DROP.

p Suppression des objets ORACLE.

p Une commande par objet : DATABASE, STOGROUP, TABLE, TABLESPACE, INDEX, SYNONYM, ALIAS, VIEW.

p Un Drop TABLE

DROP TABLE Table name

p Un Drop INDEX

DROP INDEX Index name

Exemple : DROP TABLE DSN8230.DEPT;

DROP TABLESPACE DSN8D23A.DSN8S23D;

DROP VIEW DSN8230.VPROJRE1;

p Les informations sont enlevées du catalogue.

p La disparition des OBJETS se fait hiérarchiquement.

10.5La commande ALTER.

p Modification des objets ORACLE.

p Une commande par objet : STOGROUP, TABLE, TABLESPACE, INDEX.

p Les informations sont stockées au catalogue. Cette modification peut nécessité l’exécution d’utilitaires pour être pris en compte

(ALTER ... PRIQTY ...)

p Un ALTER TABLE.

ALTER TABLE Table name

[ADD Column definition]

[ADD PRIMARY KEY (Column name, column name, ...)]

[DROP PRIMARY KEY]

[DROP FOREIGN KEY Constraint name ]

11 Annexe : notions d’appel de SQL dans un autre programme ProC, ProCobol …

11.1 Structure du programme appelant

11.2La zone de déclaration SQLCA.

p Zone de communication entre le programme et ORACLE.

p Apporte au programme des indications sur le déroulement de la requête SQL. La zone SQLCODE contient le code retour à tester après un ordre EXEC SQL.

p L’insertion dans le programme se fait par un ordre :

EXEC SQL INCLUDE SQLCA END-EXEC.

11.3La déclaration des objets.

p TABLE, VUE ou CURSEUR

...
   EXEC   SQL
         DECLARE TABLE ...
   END-EXEC.
...

11.4La gestion des erreurs.

p Le SQLCODE indique l’état de la requête SQL.

p L’ordre WHENEVER permet de tester :

n NOT FOUND (SQLCODE = 100)

n SQLERROR (SQLCODE < 0)

n SQLWARNING (SQLCODE > 0)

...
   EXEC SQL WHENEVER NOTFOUND GOTO NONTROUVE     END-EXEC.
...
   EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.
...
   EXEC SQL WHENEVER SQLERROR GO TO ERREUR END-EXEC.
...

11.5La gestion des HOST VARIABLES.

p Une HOST VARIABLE est une zone mémoire qui contient la donnée récupérée par un ordre SELECT ou qui produit la valeur traitée par UPDATE, INSERT, DELETE.

p Une HOST VARIABLE est générée par le DCLGEN.

p Une HOST VARIABLE correspond à la définition de la colonne.

Exemple :

...
01 WPAYS    PIC X(30) VALUE ‘France’.
...
   EXEC SQL UPDATE TABLEDEP
            SET PAYS = :WPAYS
            WHERE PAYS = ‘b’
   END-EXEC.
...

ORACLE	COBOL
CHAR(N)	PIC X(N)
DATE	PIC X(10)
TIME	PIC X(8)
TIMESTAMP	PIC X(26)
SMALLINT	PIC S9(4) COMP
INTEGER	PIC S9(8) COMP
DECIMAL(N,M)	PIC S9(N-M)V9(M) COMP-3
FLOAT simple	COMP-1
FLOAT double	COMP-2
VARCHAR(N) LONG VARCHAR	01 49 PIC S9(4) COMP 49 PIC X(N)

11.6Le SELECT INTO.

p Le SELECT INTO permet la recherche d’une ligne de la TABLE (ou VUE). La sélection de plusieurs lignes doit se faire par un CURSOR.

Si le résultat du SELECT aboutit à renvoyer plusieurs lignes alors

SQLCODE = -811

Aucune lignes de disponible pour le traitement.

...
   EXEC SQL SELECT ...
            INTO    <Host variables>
            FROM    ...
            WHERE   ...
   END-EXEC.
...

Exemple :

...
77 W-DEPT PIC X(2).
77 W-CODE PIC X(3).

01 LIG-TABCONC.
   02 DEPT     PIC X(2).
   02 CODE     PIC X(3).
   02 NOM      PIC X(40).
   02 MARQUE   PIC X(10).
   02 NBCLIENT PIC S9(8) COMP.
...
MOVE ‘85’      TO W-DEPT.
MOVE ‘001’     TO W-CODE.
EXEC SQL
        SELECT DEPT,CODE,NOM,MARQUE,NBCLIENT
               INTO :LIG-TABCONC
               FROM TABLCONC
               WHERE DEPT = :W-DEPT
               AND CODE = :W-CODE
   END-EXEC.
IF SQLCODE < 0 ...
...

p Un INDICATEUR de HOST VARIABLES permet de tester la valeur NULL.

EXEC SQL
        SELECT DEPT,CODE,NOM,MARQUE,NBCLIENT
               INTO :DEPT, :CODE, :NOM:NOMIND,
                    :MARQUE, :NBCLIENT
               FROM TABLCONC
               WHERE DEPT = :W-DEPT
               AND CODE = :W-CODE
   END-EXEC.
IF SQLCODE < 0 ...
...

Si NOMIND < 0 alors NOM est NULL.

Si NOMIND > 0 alors NOM est tronqué.

Si NOMIND = 0 alors NOM est correct.

11.7L’ordre INSERT.

...
   EXEC SQL INSERT INTO table-name
            (column-name,column-name,...)
            VALUES (host-variable,host-variable,...)
   END-EXEC.
...

p Une host-variable correspond à une valeur à affecter à la column-name correspondante. Toutes les colonnes doivent être affectées d’une valeur. Si une colonne restante n’est pas affectée d’une valeur elle prendra une valeur par défaut, attention si la colonne est définie NOT NULL.

p INSERT de plusieurs lignes (MASSINSERT)

p INDICATEUR VARIABLE pour spécifier NULL sur une colonne spécifiée.

p Effectif au COMMIT

Exemple :

...
   EXEC SQL INSERT INTO TABLCONC
        (DEPT,CODE,NOM,MARQUE,NBCLIENT)
        VALUES(:DEPT,:CODE,:NOM,:MARQUE,:NBCLIENT)
   END-EXEC.
...

L’ordre UPDATE.

...
EXEC SQL UPDATE table-name
SET column-name = host-variable,

column-name = host-variable
...

WHERE ...
END-EXEC.
...

p Mise à jour des lignes de la TABLE sélectionnées par la clause WHERE.

p Effectif au COMMIT.

11.8L’ordre DELETE.

...
   EXEC SQL DELETE
            FROM table-name
            WHERE ...
   END-EXEC.
...

p Suppression des lignes de la TABLE sélectionnées par la clause WHERE.

p Effectif au COMMIT

11.9Le SELECT de plusieurs lignes (CURSEURS).

p Le Curseur permet de traiter une sélection de plusieurs lignes en séquentielle avec les ordres suivants :

n DECLARE CURSOR

n OPEN

n FETCH

n CLOSE

11.10 La déclaration du CURSOR

p Dans un programme on pourra trouver plusieurs CURSOR et donc une déclaration pour chacun.

...
   EXEC SQL DECLARE cursor-name CURSOR
            [WITH HOLD]
            FOR SELECT ...
            [FOR FETCH ONLY]
            [FOR UPDATE OF column-name,column-name,...

END-EXEC.
...

p WITH HOLD : permet de conserver la position du curseur après un COMMIT.

p FOR UPDATE OF ... : indique une intention de mise à jour ou de suppression par un ordre SQL.

p FOR FETCH ONLY : Pas d’ordre UPDATE ou DELETE référençant le cursor.

p Lecture uniquement si dans le SELECT du DECLARE CURSOR apparaissent les ordres suivants :

n DISTINCT

n GROUP BY

n HAVING

n FONCTIONS ou EXPRESSIONS

n JOINTURE

n UNION de SELECT

n SUBSELECT

n ORDER BY

(FOR FETCH ONLY est implicite dans ce cas)

11.11 Le traitement (OPEN, FETCH, CLOSE)

...
   EXEC SQL DECLARE cursor-name CURSOR
...
   EXEC SQL OPEN    cursor-name
   EXEC SQL FETCH ... INTO ...
   TANTQUE SQLCODE = 0
                   Traiter la ligne
        ...
        EXEC SQL FETCH ... INTO ...
        ...
   FINTANTQUE
   EXEC SQL CLOSE   cursor-name
...

p C’est lors de l’OPEN CURSOR que le SELECT est exécuté.

...
EXEC SQL OPEN cursor-name END-EXEC.
...

p l’ordre FETCH permet d’accéder à une ligne

...
   EXEC SQL FETCH cursor-name
            INTO host-variables
   END-EXEC.
...

p Par défaut le COMMIT implique le CLOSE du curseur.

...
EXEC SQL CLOSE cursor-name END-EXEC.
...

Exemple :

01 LIG_TABLCONC
   02 DEPT PIC X(2).
   02 NOM   PIC X(40).
   02 MARQUE PIC X(15).
...
   EXEC SQL DECLARE BRETAGNE CURSOR
            FOR SELECT DEPT,NOM,MARQUE
                    FROM TABLCONC
                    WHERE DEPT IN (’29’,’56’,...)

   END-EXEC.
...
   EXEC SQL OPEN   BRETAGNE
   END-EXEC.
...
   EXEC SQL FETCH BRETAGNE
            INTO :DEPT,:NOM,:MARQUE
   END-EXEC.

   TANTQUE SQLCODE = 0
                   Traiter la ligne
        ...
        EXEC SQL    FETCH BRETAGNE
            INTO :DEPT,:NOM,:MARQUE
        END-EXEC.
...
   FINTANTQUE

   EXEC SQL CLOSE BRETAGNE
...

11.12 La mise à jour avec CURSOR.

p la déclaration du curseur (FOR UPDATE OF ...).

...
   EXEC SQL DECLARE cursor-name CURSOR
            FOR SELECT ...
            FOR UPDATE OF column-name,column-name,...

END-EXEC.
...

p la mise à jour par UPDATE.

...
   EXEC SQL UPDATE table-name
            SET ...
            WHERE CURRENT OF cursor-name

END-EXEC.
...

p la suppression par DELETE.

...
EXEC SQL DELETE FROM table-name
WHERE CURRENT OF cursor-name

END-EXEC.
...

12 Notion de PL/SQL

Le PL/SQL est une extension structurée L3G du SQL (proche d’un L4G), pour des traitements batch sans passer par un programme C, COBOL etc. … (Il évite aussi les ordres OPEN ou CLOSE de fichier).

Ce langage est interprété, sauf dans le cas où les programmes PL/SQL sont pré-compilés sous la forme de procédures stockées dans la base.

Les règles de déclaration de variables, de curseurs, d’appels de curseurs, de programmation etc … sont les même qu’au chapitre 11 précédent.

12.1Structure d’un programme PL/SQL

DECLARE

Déclarations de variables et/ou de curseurs ;

BEGIN

Instructions SQL ou PL/SQL ;

EXCEPTION

Instructions SQL ou PL/SQL à exécuter en cas d’erreur ;

END

12.2Exemples de programmes PL/SQL

DECLARE

dummy char(1);

BEGIN

select 'X' into dummy

from vcv.vcv_coloris

where cod_coloris = :block.cod_coloris;

EXCEPTION

when no_data_found then

message('............');

raise form_trigger_failure;

when others then

message('Error '||to_char(sqlcode)||' <OVF> Contact DBA');

raise form_trigger_failure;

END;

/* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */

PROCEDURE insert_tempovalcol IS

BEGIN

DECLARE

tmp_cod_col CHAR(30);

tmp_COD_TYPE CHAR(1) ;

tmp_STR_FORMAT CHAR(30);

/*-----------------------------------------------------------------*

* Le curseur c_val_col selectionne le nom de toutes les colonnes *

* de la table en cours, sauf ceux qui correspondent … un FOREIGN *

* KEY dans une autre table *

* Les foreign keys sont trouves par la table VCV_TEMPFKEY, qui *

* est rempli par un procedure SQL separee (VCVDDPREP.DBA). *

* Cette table contient les tables qui ont un Foreign Key *

* (DETAIL_TABLE) plus le noms de la contrainte *

* *

* Le colonnes qui constituent une contrainte sont retrouvees *

* dans la table VCV_CONS_COLUMNS qui n'est qu'une copie du VIEW *

* du dictionnaire des donnees ALL_CONS_COLUMNS pour le OWNER VCV *

* La table VCV_CONS_COLUMNS est egalement mis … jours par la *

* proc‚dure VCV_LDPREP.DBA qui ne doit etre relance‚ qu'au moment *

* que le modele de donnees de VCV changerait *

* *

* En plus il met a jours le data_type de cettes colonnes *

* et le format model. Celui ci est UPDATED apres pour les *

* colonnes numeriques et date *

*-----------------------------------------------------------------*/

CURSOR c_val_col IS

SELECT A.column_name,

DECODE(A.DATA_TYPE,'CHAR' ,'C',

'NUMBER','N',

'LONG' ,'L',

'DATE' ,'D'),

A.DATA_LENGTH

FROM all_tab_columns A

WHERE A.table_name = :tablenamebl.cod_table

AND NOT EXISTS

(SELECT *

FROM VCV.VCV_TEMPOFKEY B,

VCV.VCV_TEMPOCONSCOL C

WHERE B.COD_DETAIL_TABLE = 'VCV.'||A.table_name

AND B.COD_FOREIGN_KEY = C.cod_constraint

AND C.cod_col = A.column_name);

BEGIN

OPEN c_val_col;

LOOP

FETCH c_val_col INTO tmp_cod_col,

tmp_COD_TYPE,

tmp_STR_FORMAT;

EXIT WHEN c_val_col%NOTFOUND;

INSERT INTO vcv.vcv_tempovalcol

(cod_table,cod_col,lib_col,COD_TYPE,STR_FORMAT,chx_sel)

VALUES

(:tablenamebl.cod_table,tmp_cod_col,

tmp_cod_col,tmp_COD_TYPE,tmp_STR_FORMAT,'N');

END LOOP;

CLOSE c_val_col;

/*---------------------------------------------------------------*

* UPDATE de la colonne STR_FORMAT si c'est une colonne numerique*

* Si pas de SCALE -> format '9999' correspondant … la precision *

* Si SCALE -> 0 -> format '999.9999' dans lequel la partie *

* avant le point decimal a une longueur de *

* DATA_PRECISION-DATA_SCALE et la partie *

* aprŠs le point d‚cimal a une longueur de *

* DATA_SCALE *

* R‚alis‚ avec un DECODE bas‚ sur la valeur de DATA_SCALE *

*---------------------------------------------------------------*/

UPDATE VCV.VCV_TEMPOVALCOL A

SET A.STR_FORMAT =

(SELECT DECODE(B.data_scale,

0,RPAD('9',B.data_precision,'9'),

RPAD('9',B.data_precision-B.data_scale,'9')

||'.'||

RPAD('9',B.data_scale,'9'))

FROM ALL_TAB_COLUMNS B

WHERE A.cod_table = B.table_name

AND A.cod_col = B.column_name

AND B.owner = 'VCV')

WHERE A.COD_TYPE = 'N'

AND A.cod_table = :tablenamebl.cod_table;

/*---------------------------------------------------------------*

* UPDATE de la colonne STR_FORMAT si c'est une colonne date *

* Toujours le format A8, parce que la fonction *

* TO_CHAR(---,'DD/MM/YY') sera mise dans le SELECT-Statement *

*---------------------------------------------------------------*/

UPDATE VCV.VCV_TEMPOVALCOL A

SET A.STR_FORMAT = 'A8'

WHERE A.COD_TYPE = 'D'

AND A.cod_table = :tablenamebl.cod_table;

/*---------------------------------------------------------------*

* Les colonnes de type 'LONG' seront format‚ par la commande *

* SET LONG = 80 de SQL*Plus *

* *

* Les colonnes de type 'CHAR' seront format‚ avec le format *

* A##, selon la valeur se trouvant dans la colonne FORMAT-MODEL *

* de la table VCV_TEMPOSELCOL *

*---------------------------------------------------------------*/

UPDATE VCV.VCV_TEMPOVALCOL

SET STR_FORMAT = 'A'||STR_FORMAT

WHERE COD_TYPE = 'C'

AND cod_table = :tablenamebl.cod_table;

END;

13 Annexe : Exemple de script SQL de création de table

rem ============================================================

rem Table : T_ACT_R

rem ============================================================

DROP TABLE T_ACT_R;

CREATE TABLE T_ACT_R

(

CO_APE CHAR(4) NOT NULL,

LI_APE CHAR(40) NOT NULL,

PRIMARY KEY (CO_APE) CONSTRAINT PK_T_ACT_R

);

COMMENT ON TABLE T_ACT_R IS 'Activite des entreprises (ACT)';

COMMENT ON COLUMN T_ACT_R.CO_APE IS 'Code A.P.E';

COMMENT ON COLUMN T_ACT_R.LI_APE IS 'Libelle';

rem ============================================================

rem Index : T_ACT_R_PK

rem ============================================================

DROP INDEX T_ACT_R_PK;

CREATE UNIQUE INDEX T_ACT_R_PK ON T_ACT_R (CO_APE ASC);

14 Annexe : Exemple de script SQL de création de synonymes

SET DOC OFF

*------------------------------------------------------------------------------

* Module...............: dropsyn.sql

* Domaine..............: DBA

* Auteur...............: ATT-DATAID

* Description g‚n‚rale.: suppress des synonymes des tables pour tous les usagers

* Date de cr‚ation.....: <19/01/94>

* Version..............: <01>

* R‚visions............:

* Date.............: <>

* Auteur...........: <ffe>

* Descriptif.......:

* R‚f‚rence........: SYO/PRO/FFA/CPT/SFD/008 du 06/01/94

*------------------------------------------------------------------------------

REM Note : script … lancer par le DBA ("SYSTEM") de l'application FFA

DEFINE nom = OPS$FFA ;

SET HEAD OFF ;

SET VER OFF ;

SET PAGES 9999;

SET FEED OFF ;

SPOOL drop_syn2.sql

SELECT 'DROP PUBLIC SYNONYM OPS$FFA..'||TABLE_NAME||' ;'

FROM ACCESSIBLE_TABLES

WHERE OWNER = 'OPS$FFA';

SPOOL OFF

HOST vi drop_syn2.sql

START drop_syn2.sql

HOST rm drop_syn2.sql

SET DOC OFF

*------------------------------------------------------------------------------

* Module...............: cresyn.sql

* Domaine..............: DBA

* Auteur...............: ATT-DATAID

* Description g‚n‚rale.: cr‚ation des synonymes des tables pour tous les usagers

* Date de cr‚ation.....: <19/01/94>

* Version..............: <01>

* R‚visions............:

* Date.............: <>

* Auteur...........:

* Descriptif.......:

* R‚f‚rence........: SYO/PRO/FFA/CPT/SFD/008 du 06/01/94

* Note : script … lancer par le DBA ("SYSTEM") de l'application

*------------------------------------------------------------------------------

DEFINE nom = OPS$FFA ;

SET HEAD OFF

SET VER OFF

SET PAGES 9999

SET FEED OFF

SET TERM OFF

SPOOL grant_syn2.sql

SELECT 'CREATE PUBLIC SYNONYM '||TABLE_NAME||' FOR &nom..'||TABLE_NAME||' ;'

FROM ACCESSIBLE_TABLES

WHERE OWNER = '&nom';

SPOOL OFF

SET TERM ON

ACCEPT ok CHAR PROMPT 'visualisation sous "vi" pour verification, taper "Return"'

PROMPT =========================================================

HOST vi grant_syn2.sql

PROMPT creation des synonymes des tables pour tous les usagers

PROMPT =======================================================

SET ECHO ON

START grant_syn2.sql

SET ECHO OFF

HOST rm grant_syn2.sql

15 Annexe : Exemple de scripts ou d’ordres SQL d’insertions de données dans une table

INSERT INTO T_MESS VALUES ('M001','Ce numero de DAEDO n'existe pas');

INSERT INTO T_MESS VALUES ('M002','Ce numero de DAP n'existe pas');

INSERT INTO T_MESS VALUES ('M003','Ce numero de CIB n'existe pas');

INSERT INTO T_MESS VALUES ('M004','Ce code programme n'existe pas');

INSERT INTO T_MESS VALUES ('M005','Ce code sous programme n'existe pas');

INSERT INTO T_MESS VALUES ('M006','Ce numero de SDAEDO n'existe pas');

INSERT INTO T_MESS VALUES ('M007','Ce numero de SDAP n'existe pas');

/*----------------------------------------------------------------------------*

| Modifications : |

| Auteur : B.LISAN Date : 21/12/92 |

| Raison : remplisage de la table VCV_TEMPOVALCOL. |

| But : pour rendre plus convial l'utilisation de l'‚cran de selection des |

| colonnes propos‚es(VCVTVFM.inp) |

*-----------------------------------------------------------------------------*

|* 3) PRE-INITIALISATION DE LA TABLE VCV_TEMPOVALCOL : |

| |

| On inserre les noms des tables et des colonnes |

| de VCV dans la table VCV_TEMPOVALCOL. |

| On n'inserre ces colonnes que si elles ne sont pas encore |

| pr‚sents dans cette derniŠre table. |

| si elles ne sont pas utilis‚es dans une Foreign key |

| le |

| AND EXISTS (SELECT 'x' |

| FROM ALL_OBJECTS |

| WHERE object_name = table_name |

| AND object_type = 'TABLE') |

| est utilis‚ pour ‚viter de s‚lectionner des nom des vues dans la liste des |

| noms de tables s‚lectionn‚es par all_tab_columns |

*----------------------------------------------------------------------------*/

INSERT INTO vcv.vcv_tempovalcol

(cod_table,cod_col,lib_col,COD_TYPE,STR_FORMAT,chx_sel)

SELECT table_name,

column_name,

/*--------------------------------------------------------------*

| OBTENTION DU LIBELLE : |

| remplacement des 3 1er caractŠres du nom de la colonne et |

| du 4iŠme caractŠre '_' par un blanc pour obtenir le libell‚ |

| (et mise en majuscule du 1er caractŠre de chaque mot du lib.) |

| On remplace NUM_ par '' |

| " COD_ par 'Code ' |

| " ... par ... |

| " BEX par Bon d'Examen |

*--------------------------------------------------------------*/

REPLACE(

INITCAP(

DECODE(SUBSTR(column_name,1,4),'NUM_','',

'COD_','Code ' ,

'LIB_','Libell‚ de ',

'TPS_','Date de ' ,

'SUB_','Sous-' ,

'CHX_','Choix ' ,

'STR_','' ,

substr(column_name,1,3) || ' ')

|| LOWER(REPLACE(REPLACE(SUBSTR(column_name,5),

'COMMENT','COMMENTAIRE '),'BEX','BON D''EXAMEN '))

),' De ',' de '

/*------------------------------------------------------------*

| TRADUCTION DU TYPE (de data_type en cod_type) |

*------------------------------------------------------------*/

DECODE(data_type,'CHAR' ,'C',

'NUMBER','N',

'LONG' ,'L',

'DATE' ,'D'),

/*--------------------------------------------------------------*

| CALCUL DU FORMAT : |

| But : donne les formats par defaut des ordres SQPLUS "COL" |

| de formatage des colonnes du listing "LISTE DYNAMIQUE" |

| Si DATE :-> FORMAT = 'A8' |

| Si CHAR ou LONG :-> FORMAT = 'A' || data_length |

| Si NUMBER : |

| Si SCALE = 0 -> FORMAT = '9...9' avec |

| nbre de '9' = data_precision |

| Si SCALE > 0 -> FORMAT = '9"""9.9~~~9' avec |

| '9"""9' = partie entiŠre dont le nbre de |

| '9' = data_precision - data_scale |

| '.' = point d‚cimal, |

| '9~~~9' = partie d‚cimale dont nbre de |

| '9' = data_scale |

| Exemple: |

| Si NUM_SPECIMEN d‚clar‚ NUMBER(5,0) alors |

| data_precision = 5, data_scale = 0 et FORMAT = '99999' |

*--------------------------------------------------------------*/

DECODE(data_type,'CHAR' , 'A' || data_length,

'LONG' , 'A' || data_length,

'NUMBER', DECODE(data_scale,

0,RPAD('9',data_precision,'9')

,RPAD('9',data_precision -

data_scale ,'9')

||'.'||

RPAD('9',data_scale ,'9')),

'DATE' , 'A8'),

'O'

FROM all_tab_columns

WHERE owner = 'VCV'

AND table_name NOT LIKE 'VCV_TEMPO%'

AND NOT EXISTS (SELECT 'x'

FROM vcv.vcv_tempofkey,

vcv.vcv_tempoconscol

WHERE cod_detail_table = 'VCV.' || table_name

AND cod_col = column_name

AND cod_foreign_key = cod_constraint)

AND EXISTS (SELECT 'x'

FROM ALL_OBJECTS

WHERE object_name = table_name

AND object_type = 'TABLE')

;

16 Annexe : Exemple de scripts Sql de gestion de droits

SET DOC OFF

*------------------------------------------------------------------------------

* Module...............: grant_tabs.sql

* Domaine..............: DBA

* Auteur...............: ATT-DATAID

* Description generale.: creation droits d'acces DML,connection … ORACLE pour

* .....................: pour les usagers

* Date de creation.....: <19/01/94>

* Version..............: <01>

* Revisions............:

* Date.............: <>

* Auteur...........: <ffe>

* Descriptif.......:

* Reference........: SYO/PRO/FFA/CPT/SFD/008 du 06/01/94

*------------------------------------------------------------------------------

GRANT RESOURCE TO OPS$FFA IDENTIFIED BY OPS$FFA ;

GRANT RESOURCE TO OPS$FFE IDENTIFIED BY OPS$FFE ;

GRANT RESOURCE TO OPS$GLP IDENTIFIED BY OPS$GLP ;

GRANT RESOURCE TO OPS$DBO IDENTIFIED BY OPS$DBO ;

GRANT RESOURCE TO OPS$CME IDENTIFIED BY OPS$CME ;

GRANT RESOURCE TO OPS$SBH IDENTIFIED BY OPS$SBH ;

GRANT RESOURCE TO OPS$BLI IDENTIFIED BY OPS$BLI ;

REM >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET DOC OFF

*------------------------------------------------------------------------------

* Module...............: grant_all_usr_tabs.sql

* Domaine..............: DBA

* Auteur...............: ATT-DATAID

* Description generale.: creation droits d'acces aux tables

* .....................: pour tous les usagers

* Date de creation.....: <19/01/94>

* Version..............: <01>

* Revisions............:

* Date.............: <>

* Auteur...........: <ffe>

* Descriptif.......:

* R‚f‚rence........: SYO/PRO/FFA/CPT/SFD/008 du 06/01/94

* Programme appele.....: grant_tables

*------------------------------------------------------------------------------

PROMPT creation droits d'acces aux tables

PROMPT ==================================

REM @grant_tables FFA

@grant_tables FFE

@grant_tables GLP

@grant_tables DBO

@grant_tables CME

@grant_tables SBH

@grant_tables BLI

/* creation grant pour user SYSTEM … laisser :

* utile pour creation PUBLIC SYNONYM

@grant_tables SYSTEM

REM >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET DOC OFF;

*------------------------------------------------------------------------------

* Module...............: grant_tables.sql

* Domaine..............: DBA

* Auteur...............: ATT-DATAID

* Description generale.: creation droits d'acces aux tables pour les usagers

* Date de creation.....: <19/01/94>

* Version..............: <01>

* R‚visions............:

* Date.............: <>

* Auteur...........: <ffe>

* Descriptif.......:

* Reference........: SYO/PRO/FFA/CPT/SFD/008 du 06/01/94

*------------------------------------------------------------------------------

REM Note : script … lancer par le OWNER (Proprietaire) de l'application FFA

REM (OPS$FFA)

SET HEADING OFF;

SET VERIFY OFF;

SET PAGESIZE 9999;

SET FEEDBACK OFF;

ACCEPT nom CHAR PROMPT 'Usager dont on accorde des droit sur les tables FFA : '

SPOOL grant_tab2.sql

SELECT 'GRANT SELECT,DELETE,INSERT,UPDATE ON '||TABLE_NAME||' TO OPS$&nom ;'

FROM USER_TABLES;

REM WHERE OWNER = 'OPS$FFA';

SPOOL OFF;

HOST vi grant_tab2.sql

START grant_tab2.sql

HOST rm grant_tab2.sql