oracle sql 3

Oracle et le Modle Objet Relationnel

Gabriel MOPOLO-MOKE prof. Associ UNSA 2005/2006

Page

1

Copyright G. Mopolo-Mok Oracle et le modle Objet Relationnel

1. Plan GnralI

1. PLAN GENERAL 2. INTRODUCTION 3. LES TYPES DE BASE (Prdfinis) 4. LES TYPES ABSTRAITS (COMPLEXES) 5. NOTION DIDENTITE 6. LA NOTION DE COLLECTION 7. CREATION ET MANIPULATION DOBJETS 8. LES LIENS DASSOCIATIONS2Copyright G. Mopolo-Mok Oracle et le modle Objet Relationnel

I

I

I

I

I

I

I

Page

1. Plan GnralI

9. LIENS D HERITAGE 10. LES VUES 11. GESTION DES OBJETS VOLUMINEUX 12. PL/SQL OBJET 13. Import/Export 14. CONCLUSION 15. EXERCICES

I

I

I

I

I

I

Page

3


2. INTRODUCTIONI

Pourquoi Oracle Objet? Modlisation des donnes complexes Encapsulation des donnes Type abstraits Collections (varray, nested table) Liens complexes(composition, association, hritage) Dpassement de la premire forme Normale du modle relationnel

Manipulation des objets volumineux LOBs (Large Objects). Va au del des blobs traditionnels

Page

4


2. INTRODUCTIONI

Pourquoi Oracle Objet? (suite) Evolutivit Exploiter de nouveaux gisements de donnes dans les entreprises Donnes une vision objet aux applications relationnelles existantes

Applications internet et intranet SGBD ouvert supportant Java en natif au mme titre que PL/SQL avec une intgration forte dans l environnement ORB Donnes XML directement gres par le SGBD

Page

5


2. INTRODUCTIONI

Approche dintgration des objets dans Oracle Oracle suit lapproche SQL3 Oracle tend son noyau relationnel vers lobjet via la notion de domaine (type): Les colonnes des tables peuvent maintenant tre dfinis sur des types (domaines) simples ou complexes Relation R (D1 X D2 X X Dn) Di pouvant tre un Type scalaire ou un type complexe

Page

6


3. LES TYPES PREDEFINISI

Les types de base (CHAR, VARCHAR2, DATE, NUMBER, .) de la version 7 sont conservs dans les versions objets Les nouveaux types Les associations rfrentielles "Pre/Fils" (REF). Les collections : VARRAY (tableaux) et Nested Tables (Tables imbriques). Les LOBs : BLOB, CLOB, BFILE NOTE : L utilisateur pourra ajouter ses propres types: les types donnes abstraits (ADT).

I

Page

7


4. LES TYPES ABSTRAITS (COMPLEXES)I

PLAN Les Types de donnes Abstraits(ADT) Exemple de cration dun ADT sous Oracle Dfinition des Mthodes dun Type Abstrait Comparaison sur les ADT Complment sur les ADT Oracle Modification dun ADT Suppression dun ADT Visualisatioin des Informations sur les ADT Encapsulation Constructeur Instanciation

Page

8



Les Types de donnees Abstraits(ADT) Un Type abstrait de donnes peut tre vu comme une boite noire accessible uniquement par des oprateurs Les spcifications du type sont indpendantes de leurs implmentations Permet lutilisateur de dfinir lui-mme ses propres types

Page

9



Les Types de donnees Abstraits(ADT)SPECIFICATION DU TYPE

DECLARATION DES ATTRIBUTS

DECLARATION DES METHODES

CORPS DU TYPE

IMPLEMENTATION DES METHODES

Page

10



Les Types de donnees Abstraits(ADT) Syntaxe de cration dun type Forward ou TYPE INCOMPLET permet de rsoudre le problme de rfrences croises en type CREATE [OR REPLACE] TYPE nomType; CREATE OR REPLACE TYPE pilote_t;

Syntaxe de cration dun type objetCREATE [OR REPLACE] TYPE nomType AS OBJECT( { nomAttribut1 typeDeDonne, nomAttributN typeDeDonne, {MAP | ORDER} MEMBER function_specification, {STATIC | MEMBER} {functionSpecification | procedureSpecification } [PRAGMA RESTRICT_REFENCES ({nomFonction|default}, [WNDS, WNPS, RNDS, RNPS, TRUST])] }) Note : les clauses seront dcrites par la suite11

Page



Exemple de cration dun ADT sous Oracle Type abstrait sans champs complexesCREATE TYPE adresse_t as object( Numero NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20));

Type abstrait avec champs complexesCREATE OR REPLACE TYPE Pilote_t AS OBJECT( PL# NUMBER(4), nom VARCHAR2(12), adresse adresse_t,-- type abstrait dfini pralablement Tel VARCHAR2(12), dnaiss DATE, salaire NUMBER(7,2));

ADT avec un champ de type rfrenceCREATE OR REPLACE TYPE REF_Pilote_t AS OBJECT( PL# NUMBER(4), nom VARCHAR2(12), adresse REF adresse_t, -- rfrence sur une adresse Tel VARCHAR2(12), dnaiss DATE, salaire number(7,2) );

Page

12



Dfinition des Mthodes d un Type Abstrait Lutilisateur peut dfinir autant de mthodes quils le souhaite dans un ADT Par contre il ne peut dfinir AUCUN CONSTRUCTEUR. Seul le constructeur par dfaut est utilisable La dclaration des mthodes se fait dans le TYPE SPECIFICATION et limplmentation dans le TYPE BODY Le mot cl MEMBER prcde chaque mthode dinstance Le mot cl STATIC prcde chaque mthode de classe

Page

13



Dfinition des Mthodes dun Type Abstrait Une mthode se caractrise par : Un nom Un ensemble de paramtres : les donnes en entre(IN), les rsultats (OUT), les donnes/rsultats (IN/OUT), les appels par rfrence (CALL-BY-REFERENCE). Un ensemble de rsultats retourns par la mthode. Un ensemble d' exceptions. Un ensemble d' indicateurs prcisant la nature de la mthode (MAP/ORDER MEMBER, MEMBER, STATIC). PRAGMA RESTRICT_REFERENCE :WNDS, WNPS, RNDS, RNPS, TRUST) Un nom spcifique (un alias). Une clause de langage (PL/SQL, C, Java,etc) Une implmentation.

Page

14



Dfinition des Mthodes dun Type Abstrait Notes : MAP function : permet de dfinir une rgle de comparaison s appuyant une des des colonnes du type. ORDER Function: permet de comparer deux objets. STATIC Function : Une mthode statique n' pas a l' objet courant comme paramtre implicite. Elle n a pas besoin d un objet pour tre invoque WNDS : interdit la modification de l tat de la base WNPS : interdit la modification de l tat du package(type) RNDS : interdit la lecture de l tat de la base RNPS : interdit la lecture de l tat du package(type) TRUST: dsactive provosoirement les interdits positionns

Page

15



Dfinition des Mthodes dun ADT Exemple de dfinition de mthodesSpcification du type PILOTE CREATE OR REPLACE TYPE REF_Pilote_t AS OBJECT( PL# NUMBER(4), nom VARCHAR2(12), adresse adresse_t, -- type abstrait dfini pralablement Tel VARCHAR2(12), dnaiss DATE, salaire NUMBER(7,2), MEMBER FUNCTION getPiloteSalaire RETURN NUMBER, MEMBER FUNCTION setPiloteSalaire(SELF IN OUT ref_pilote_t, plNum IN NUMBER, newSalaire IN NUMBER) RETURN BOOLEAN, MEMBER PROCEDURE testSetSalaire, PRAGMA RESTRICT_REFERENCES(getPiloteSalaire, WNDS), PRAGMA RESTRICT_REFERENCES(setPiloteSalaire,RNPS) ); / NOTE : - le paramtre SELF doit tre pass en paramtre si l on souhaite modifier l objet courant dans UNE FONCTION. Ce passage n est pas ncessaire s il s agit d une PROCEDURE

Page

16



Dfinition des Mthodes dun ADT Exemple dimplmentation de mthodesImplmentation du corps des mthodes getPiloteSalaire et setPiloteSalaireCREATE OR REPLACE TYPE BODY ref_pilote_t IS MEMBER FUNCTION getPiloteSalaire RETURN NUMBER IS BEGIN RETURN salaire; END; MEMBER FUNCTION setPiloteSalaire (SELF IN OUT ref_pilote_t, plNum IN NUMBER, newSalaire IN NUMBER) RETURN BOOLEAN IS BEGIN IF pl# = plNum THEN salaire := newSalaire; return true; ELSE return false; END IF; END; MEMBER PROCEDURE testSetSalaire IS retour BOOLEAN; BEGIN retour := Self.setPiloteSalaire(10, 10000); END; END; /

Page

17



Dfinition des Mthodes dun ADT Autre exempleCREATE TYPE Complex AS OBJECT ( rpart REAL, ipart REAL, MEMBER FUNCTION plus (x Complex) RETURN Complex, MEMBER FUNCTION less (x Complex) RETURN Complex, MEMBER FUNCTION times (x Complex) RETURN Complex, MEMBER FUNCTION divby (x Complex) RETURN Complex ); CREATE TYPE BODY Complex AS MEMBER FUNCTION plus (x Complex) RETURN Complex IS BEGIN RETURN Complex(rpart + x.rpart, ipart + x.ipart); END plus; MEMBER FUNCTION less (x Complex) RETURN Complex IS BEGIN RETURN Complex(rpart - x.rpart, ipart - x.ipart); END less; -- A SUIVRE

Page

18



Dfinition des Mthodes dun ADT Autre exemple-- Suite MEMBER FUNCTION times (x Complex) RETURN Complex IS BEGIN RETURN Complex(rpart * x.rpart - ipart * x.ipart, rpart * x.ipart + ipart * x.rpart); END times; MEMBER FUNCTION divby (x Complex) RETURN Complex IS z REAL := x.rpart**2 + x.ipart**2; BEGIN RETURN Complex((rpart * x.rpart + ipart * x.ipart) / z, (ipart * x.rpart - rpart * x.ipart) / z); END divby; END; /

Page

19



Comparaison dADT Soit deux ADT C1 et C2 dun type Type 1. La comparaison de C1 et C2 peut tre faite par dfaut ou en rcrivant les mthodes MAP et ORDER la comparaison par dfaut nest possible que pour lgalit ou la diffrence entre objet. Exemple : C1 = C2, C1 != C2 Par dfaut, pour que deux objets soient gaux, la valeur de chacun de leurs attributs doit tre gale.

Page

20



Comparaison dADT MAP MEMBER fonction : Permet de dfinir une fonction qui rend la position relative dun objet dans lensemble des instances dun type. Lordre des objets est dfini par rapport une valeur scalaire RENDUE PAR LA FONCTION. La valeur scalaire peut tre de type(DATE, NUMBER, VARCHAR2, les types ANSI SQL tels que CHARACTER or REAL) possde un seul paramtre implicite SELF adapt pour le TRI dun grand nombre dobjets et les JOINTURES PAR HACHAGE

Page

21



Comparaison dADT ORDER MEMBER fonction permet de dfinir une mthode qui prend en paramtre un objet du type et le compare avec lobjet courant SELF lordre est dfinie sur une ou plusieurs colonne du type permet de comparer deux Objets Il sagit dune approche de comparaison "PERSONALISEE" Evaluation perptuelle de la fonction POUR CHAQUE OBJET => lenteur

Page

22



Comparaison dADT Notes : il est impossible de dfinir la fois une mthode ORDER MEMBER et une mthode MAP MEMBER dans un mme type pas plus dune mthode ORDER ou MAP par type MAP et ORDER sont appeles automatiquement lors de la comparaison dobjets

Page

23



Comparaison dADT Exemple SANS les mthodes MAP et ORDERCREATE OR REPLACE TYPE adresse2_t as object( Numero NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20), quartier VARCHAR2(20) ); / CREATE TABLE adresse2_table(adr adresse2_t ); INSERT INTO adresse2_table VALUES ( adresse2_t(1, rue du congres, 6000, Nice, Centre)); INSERT INTO adresse2_table VALUES ( adresse2_t(3, rue du congres, 6000, Nice, Centre)); INSERT INTO adresse2_table VALUES( adresse2_t(2, rue des garages, 6100, Nice, Nord)); INSERT INTO adresse2_table VALUES( adresse2_t(2, rue du port, 6300, Nice, Ouest)); SQL> SELECT * FROM adresse2_table ORDER BY adr; SELECT * FROM adresse2_table order by adr * ERROR at line 1: ORA-22950: cannot ORDER objects without MAP or ORDER method

Page

24



Comparaison dADT Exemple avec la mthode MAP MEMBERCREATE OR REPLACE TYPE adresse2_t as object( Numero NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20), quartier VARCHAR2(20), MAP MEMBER FUNCTION compAdresse RETURN VARCHAR2, PRAGMA RESTRICT_REFERENCES (compAdresse, WNDS, WNPS, RNPS, RNDS) ); / CREATE OR REPLACE TYPE BODY adresse2_t IS MAP MEMBER FUNCTION compAdresse RETURN VARCHAR2 IS BEGIN RETURN ville||rue||numero; END; END; / NOTE : La valeur de Comparaison des instance de de adresse2_t sera : ville||rue||numero. Ce qui rduit la comparaison celle d un type scalaire Oracle

Page

25



Comparaison dADT Exemple avec la mthode MAP MEMBERCREATE TABLE adresse2_table(adr adresse2_t ); INSERT INTO adresse2_table VALUES ( adresse2_t(1, rue du congres, 6000, Nice, Centre)); INSERT INTO adresse2_table VALUES ( adresse2_t(3, rue du congres, 6000, Nice, Centre)); INSERT INTO adresse2_table VALUES( adresse2_t(2, rue des garages, 6100, Nice, Nord)); INSERT INTO adresse2_table VALUES( adresse2_t(2, rue du port, 6300, Nice, Ouest)); SQL> SELECT * FROM adresse2_table; ADR(NUMERO, RUE, CODE_POSTAL, VILLE,QUARTIER) ADRESSE2_T(1, rue du congres, 6000, Nice, Centre) ADRESSE2_T(3, rue du congres, 6000, Nice, Centre) ADRESSE2_T(2, rue des garages, 6100, Nice, Nord) ADRESSE2_T(2, rue du port, 6300, Nice, Ouest) SQL> SELECT * FROM adresse2_table order by adr; ADR(NUMERO, RUE, CODE_POSTAL, VILLE,QUARTIER) ADRESSE2_T(2, rue des garages, 6100, Nice, Nord) ADRESSE2_T(1, rue du congres, 6000, Nice, Centre) ADRESSE2_T(3, rue du congres, 6000, Nice, Centre) ADRESSE2_T(2, rue du port, 6300, Nice, Ouest)

Page

26



Comparaison dADT Exemple avec la mthode ORDER MEMBER Nous souhaitons classer les adresses selon leur position gographique. Nord, Centre, Sud, Est et enfin Ouest. MAP MEMBER ne suffit plus.

CREATE OR REPLACE TYPE adresse2_t as object( Numero NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20), quartier VARCHAR2(20), ORDER MEMBER FUNCTION compAdresse (adr IN adresse2_t) RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (compAdresse, WNDS, WNPS, RNPS, RNDS) );

Page

27



Comparaison dADT Exemple avec la mthode ORDER MEMBERCREATE OR REPLACE TYPE BODY adresse2_t AS ORDER MEMBER FUNCTION compAdresse (adr IN adresse2_t ) RETURN NUMBER IS position1 NUMBER :=0; position2 NUMBER :=0; concAdr1 VARCHAR2(60) := SELF.ville||SELF.rue||SELF.numero; concAdr2 VARCHAR2(60) := adr.ville|| adr.rue|| adr.numero; BEGIN CASE SELF.quartier WHEN Nord THEN position1 := 1; WHEN Centre THEN position1 := 2; WHEN Sud THEN position1 := 3; WHEN Est THEN position1 := 4; WHEN Ouest THEN position1 := 5; END CASE; CASE adr.quartier WHEN Nord THEN position2 := 1; WHEN Centre THEN position2 := 2; WHEN Sud THEN position2 := 3; WHEN Est THEN position2 := 4; WHEN Ouest THEN position2 := 5; END CASE;

Page

28



Comparaison dADT Exemple avec la mthode ORDER MEMBER concAdr1 := position1||concAdr1; concAdr2 := position2|| concAdr2; IF concAdr1 = ConcAdr2 THEN return 0; ELSIF concAdr1 > ConcAdr2 THEN return 1; ELSIF concAdr1 < ConcAdr2 THEN return -1; END IF; END; END; /

Page

29



Comparaison dADT Exemple avec la mthode ORDER MEMBERCREATE TABLE adresse2_table(adr adresse2_t ); INSERT INTO adresse2_table VALUES ( adresse2_t(1, rue du congres, 6000, Nice, Centre)); INSERT INTO adresse2_table VALUES ( adresse2_t(3, rue du congres, 6000, Nice, Centre)); INSERT INTO adresse2_table VALUES( adresse2_t(2, rue des garages, 6100, Nice, Nord)); INSERT INTO adresse2_table VALUES( adresse2_t(2, rue du port, 6300, Nice, Ouest)); SQL> SELECT * FROM adresse2_table; ADR(NUMERO, RUE, CODE_POSTAL, VILLE) ADRESSE2_T(1, rue du congres, 6000, Nice, Centre) ADRESSE2_T(3, rue du congres, 6000, Nice, Centre) ADRESSE2_T(2, rue des garages, 6100, Nice, Nord) ADRESSE2_T(2, rue du port, 6300, Nice, Ouest) SQL> SELECT * FROM adresse2_table order by adr; ADR(NUMERO, RUE, CODE_POSTAL, VILLE) ADRESSE2_T(2, rue des garages, 6100, Nice, Nord) ADRESSE2_T(1, rue du congres, 6000, Nice, Centre) ADRESSE2_T(3, rue du congres, 6000, Nice, Centre) ADRESSE2_T(2, rue du port, 6300, Nice, Ouest)

Page

30



Complment sur les Types Abstraits Oracle Les privilges sur les types sont : CREATE TYPE (permet de crer, supprimer, modifier, hriter d un type, excuter les mthodes d un type) CREATE ANY TYPE. ALTER ANY TYPE. DROP ANY TYPE. UNDER ANY TYPE (droit d hriter dans tout schma) UNDER ANY VIEW EXECUTE ANY TYPE EXECUTE (privilge objet) UNDER (privilge objet)

La fonction IS NULL permet de tester si la rfrence vers un ADT est null ou pas La fontion IS DANGLING permet de vrifier si une rfrence pointe vers un objet dtruit. L indexation d attributs d ADT est possibles s' ils sont accessibles et ne sont ni des VARRAYs, ni des LOBs, ni des NESTED TABLES.31

Page



Modification dun ADT La modification d' type permet : de un Recompiler le type en cas de modifications, d' ajouter de nouvelles mthodes au Type Syntaxe ALTER TYPE nomType COMPILE {SPECIFICATION | BODY} | REPLACE AS OBJECT listeElements | alter_methode_definition | alter_attribute_definitionOPTIONCOMPILE REPLACE

DESCRIPTIONCompilation du corps et/ou de la spcification d' type un Ajout de nouvelles mthodes un type

Page

32



Modification dun ADT L option compileExemple SQL> ALTER TYPE adresse_t COMPILE;

L option REPLACE AS OBJECT listeElements Syntaxe ListeElements ::=nomAttribut1 typeAttribut1, nomAttributN typeAttributN, {MAP|ORDER} MEMBER specDeFonctions, MEMBER specDeFonction1, MEMBER specDeFonctionN, PRAGMA RESTRICT_REFERENCES( {nomMethode, {RNDS,WNDS,RNPS,WNPS}})

Notes Ajout de nouvelles mthodes uniquement La structure entire du type doit tre reprise

Page

33



Modification dun ADT L option REPLACE AS OBJECT listeElements ExempleCREATE OR REPLACE TYPE adresse3_t AS OBJECT( Numero NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20), ORDER MEMBER FUNCTION compAdresse (adr IN adresse3_t) RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (compAdresse, WNDS, WNPS, RNPS, RNDS)); ALTER TYPE adresse3_t REPLACE AS OBJECT( Numero NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20), ORDER MEMBER FUNCTION compAdresse (adr IN adresse3_t) RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (compAdresse, WNDS, WNPS, RNPS, RNDS), Member function setRue(self IN OUT adresse3_t, nom_rue IN varchar2) return boolean );

Page

34



Modification dun ADT L option ajout / suppression de mthodes Syntaxealter_methode_definition::= {ADD | DROP} {map_order_function_spec | subprogram_clauses} Exemple 1 : ajout Alter type adresse3_t add Member function setNumero(self IN OUT adresse3_t, numero1 IN number) return boolean; Exemple 2 : suppression Alter type adresse3_t drop member function setNumero (self IN OUT adresse3_t, numero1 IN number) return boolean;

Page

35



Modification dun ADT Loption ajout / modification / suppression dattributs Syntaxe alter_attribute_definition::= {ADD | MODIFY} ATTRIBUTE attribute datatype [( attribute datatype, ...)] DROP ATTRIBUTE attribute [( attribute, ...)] Exemple 1 : ajout Alter type adresse3_t add attribute state varchar2(20); Alter type adresse3_t add attribute(state2 varchar2(20)); Exemple 2 : Modification Alter type adresse3_t modify attribute state2 varchar2(30); Exemple 3 : Suppression Alter type adresse3_t drop attribute state2;

Page

36



Modification dun ADT Les options INVALIDATE et CASCADE. Sil y a des dpendances, la modification dun type nest possibles quen utilisant les options INVALIDATE et CASCADE. Loption CASCADE permet de propager immdiatement la modification sur les objets dpendants. Loption INVALIDATE permet dinvalider provisoirement tous les objets dpendants. Au prochain accs les objets sont nouveau valid. ATTENTION !!! Il faut tre sr des consquences ExempleCreate table adresse3 of adresse3_t; Insert into adresse3 values(1, rue de la gare, 6560, Valbonne, AM); Alter type adresse3_t modify attribute state varchar2(30) * ERREUR la ligne 1 : ORA-22312: vous devez indiquer l' option CASCADE ou INVALIDATE37

Page



Modification dun ADT Les options INVALIDATE et CASCADE. Sil y a des dpendances, la modification dun type nest possibles quen utilisant les options INVALIDATE et CASCADE. Exemple 1 : Modification avec INVALIDATEAlter type adresse3_t modify attribute state varchar2(30) invalidate; Insert into adresse3 values(2, rue de la gare, 6560, Nice, AM);

Exemple 3 : Modification avec CASCADEAlter type adresse3_t add attribute(state2 varchar2(20)) cascade; Insert into adresse3 values(2, rue de la gare, 6560, Nice, AM,AM);

Loption CASCADE peut tre suivi de INCLUDING DATA ou NOT INCLUDING DATA : avec ou sans conversion de donnes des tables. INCLUDING DATA est loption par dfautPage

38



Modification dun ADT Limites Il nest pas possible de supprimer : Un attribut dun super type dans un sous-type Un attribut dpendant sans loption cascade ou invalidate Un attribut utilis comme cl primaire ou cl de partitionnement

Il nest pas possible de modifier : La taille dun attribut participant un Index bas sur une fonction, un domaine index ou une cl cluster

Page

39



Suppression dun ADT Il est possible de supprimer un type ou le corps dun type. La suppression du corps d' type permet d' un viter de supprimer la spcification du type. Syntaxe pour suppression d' type un DROP TYPE nomType [FORCE | VALIDATE] L' option FORCE permet de supprimer un type en ignorant les dpendances. Les colonnes ou les attributs dfinis sur ce type deviennent invalide. L option VALIDATE permet de vrifier que des instances des super types n ont pas ts construits avec le sous-type Exemple DROP TYPE adresse_t; DROP TYPE adresse_t FORCE; DROP TYPE Employee_t VALIDATE;

Syntaxe pour la suppression du corps d' type un DROP TYPE BODY nomType ; Exemple DROP TYPE BODY adresse_t;

Page

40



Visualisation des informations sur les ADTDESCRIPTION Informations sur les types

NOM DE LA VUE ALL_TYPES DBA_TYPES USER_TYPES ALL_COLL_TYPES DBA_COLL_TYPES USER_COLL_TYPES ALL_TYPE_ATTRS DBA_TYPE_ATTRS USER_TYPE_ATTRS ALL_TYPE_METHODS DBA_TYPE_METHODS USER_TYPE_METHODS

Informations sur les types collections

Informations sur les attributs dun type

Informations sur les mthodes d' un type

Page

41



Visualisation des informations sur les ADTDESCRIPTION Infos sur les index dfinis par le user

NOM DE LA VUE USER_INDEXTYPES USER_INDEXTYPES USER_INDEXTYPES

Commentaire sur les index dfinis (au sens nouveau mcanisme d indexation) par le user USER_INDEXTYPE_COMMENTS USER_INDEXTYPE_COMMENTS USER_INDEXTYPE_COMMENTS Information sur l oprateur d un index dfini par l utilisateur USER_INDEXTYPE_OPERATORS USER_INDEXTYPE_OPERATORS USER_INDEXTYPE_OPERATORS

Page

42



Visualisation des informations sur les ADT SQL> desc user_typesTYPE_NAME TYPE_OID TYPECODE ATTRIBUTES METHODS PREDEFINED INCOMPLETE FINAL INSTANTIABLE SUPERTYPE_OWNER SUPERTYPE_NAME LOCAL_ATTRIBUTES LOCAL_METHODS TYPEID

Page

43



Visualisation des informations sur les ADT

1. Liste des types crs par l' utilisateur courant sql>select type_name, type_oid, attributes, methods, local_attributes from user_types;TYPE_NAME TYPE_OID ATTRIBUTES METHODS ADRESSE2_T A73CC5B2852F418E89136F467DA27C77 4 1 ADRESSE3_T 17E3DBF26849423EB0F6F44CFF2C5EE7 6 2 ADRESSE_T E1F25E99482849E49A9B6AF3A10C2A03 4 0 PILOTE_T 0B930966561E4FF8B8140663F78A9960 6 0 REF_PILOTE_T AB2360EC8BEC4F1EBFE81D0E5F270559 6 3

2. Liste des attributs des types crs par l' utilisateur courantSQL> SELECT type_name, attr_name, attr_type_name FROM user_type_attrs; TYPE_NAME ADRESSE2_T ADRESSE2_T ADRESSE2_T ADRESSE2_T ADRESSE3_T

ATTR_NAME NUMERO RUE CODE_POSTAL VILLE NUMERO

ATTR_TYPE_NAME NUMBER VARCHAR2 NUMBER VARCHAR2 NUMBER

Page

44



Encapsulation L' encapsulation est le principe qui consiste considrer un objet comme une BOITE NOIRE ACCESSIBLE UNIQUEMENT A TRAVERS LES OPERATIONS DEFINIES sur celle -ci L' Encapsulation en C++ par exemple est gre travers trois mots cls : PRIVATE, PROTECTED et PUBLIC Le moteur Oracle ne supporte pas l' encapsulation telle que dfinie. C' au programmeur de se limiter est l' appel de mthodes

DONNEES

METHODES

Page

45



Constructeur Un Constructeur est une mthode spciale ayant le mme nom que le type. Le constructeur du type ADRESSE_T sappelle ADRESSE_T. un constructeur sert la cration et la mise jour des objets (dans les ordres SQL : INSERT, UPDATE, dans des fonctions PL/SQL). Un Constructeur en Oracle est appelable partout ou une mthode peut tre appele Un constructeur par dfaut est disponible. Ses paramtres sont lensemble des champs du type L utilisateur peut BRICOLER constructeurs via des Mthodes (jusqu la version 8) ses propres STATIQUES

Page

46



Constructeur Exemple : constructeur par dfaut Soit la table objet Pilote de pilote_t:CREATE TABLE o_pilote OF ref_pilote_t; // Insertion d un lement de la table objet. Pour la colonne, le // Constructeur adresse_t sera appel INSERT INTO o_pilote VALUES ( ref_pilote_t(1, Jean DUPOND, adresse_t(1, rue Arago,6000, NICE), 0493825084, 12-JUN-47, 12000.50) ); NOTE : lappel de ref_PILOTE_T() est facultatif

Modification via un constructeur UPDATE o_pilote op set op.adr = ref_adresse_t(5, ' de la traverse' rue ,6000, ' ) Nice' Note : - tous les champs doivent tre concerns - il est prfrable d' accder directement un champ ou via une une mthode ou un autre constructeur si la MAJ ne concerne pas la ligne entire,

Page

47



Constructeur Exemple : constructeur dfini par lutilisateur (jusqu la V8)CREATE OR REPLACE TYPE adresse4_t AS OBJECT( Numero NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20), STATIC FUNCTION cons_adresse4_t(numero Number , rue VARCHAR2 ) RETURN Adresse4_t , ORDER MEMBER FUNCTION compAdresse (adr IN adresse4_t) RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (compAdresse, WNDS, WNPS, RNPS, RNDS)); /

Page

48



Constructeur Exemple : constructeur dfini par lutilisateur(jusqu la V8)create or replace type body adresse4_t is STATIC FUNCTION cons_adresse4_t(numero Number , rue VARCHAR2 ) RETURN Adresse4_t IS begin return adresse4_t(numero, rue, null, null); end; ORDER MEMBER FUNCTION compAdresse (adr IN adresse4_t) RETURN NUMBER is begin if self.numeroadr.numero then return 1 ; end if; return 0; End; end; /

Page

49



Constructeur Exemple : constructeur lutilisateur(jusqu la V8) dfini par

declare adr1 adresse4_t:=adresse4_t(1, Alufa, 5000, Ifa); adr2 adresse4_t:=adresse4_t.cons_adresse4_t(1, Josoe); begin adr2.ville:= Jerusalem ; Adr2.code_postal :=10000; end; /

Page

50



Instanciation Il nest pas possible dinstancier un type abstrait sinon que dans une table ou dans un programmeType PILOTE_T Attributs ou Champs PL# NOM ADRESSE TEL DNAISS SALAIRE Mthodes getSalaire setSalaire

PL# : 1 Nom : Tintin Milou Adresse : (1, R Arago , 6000 Bruxelles ) Tl : 0493825084 Dnaiss : 12-10-40 Salaire : 12000.0

PL# :2 Nom : Dupond Dupont Adresse : (1, R Gare , 75 Paris ) Tl : 0141825084 Dnaiss : 12-10-45 Salaire : 13200.0

CREATE TABLE r_pilote(pil PILOTE_T); Declare Begin End; /Page

pil_var

PILOTE_T;

51


5. NOTION DIDENTITEI

PLAN Identit sur les tables Identit sur les vues Diffrences entre un OID et ROWID

Page

52



Identit sur les tables Lors de l' insertion d' objet dans la base, un OID ou un Object Identifier lui est affect. Cest identifiant ne peut changer pendant la dure de vie de l' objet Seuls les objets des tables ou des vues objets sont identifis par des OID. Une table Objet est cre avec la commande CREATE TABLE . OF Le type rfrence REF permet de stocker des rfrences vers des objets (OID). Une rfrence permet de dfinir les liens entre objets

Attention! : les OID sont trs diffrents des ROWID!

Page

53



Identit sur les tables

Exemple : Manipulation des rfrences CREATE TABLE O_REF_PILOTE OF REF_PILOTE_T; CREATE TABLE ref_Vol ( Vol# NUMBER(4) PRIMARY KEY, VD VARCHAR2(20), VA VARCHAR2(20), Pilote REF ref_pilote_t); --type abstrait dfini pralablement

// Insertion en exploitant les astuces du langage SQLINSERT INTO o_ref_pilote VALUES ( ref_pilote_t(2, ' Milou TINTIN' , adresse_t(12, ' nord'75000, ' rue , Paris' ' ), 0493825084'' , 11-AUG-63' , 10000.5)); INSERT INTO ref_vol SELECT 300, ' Paris'' , REF(px) , Nice' from o_ref_pilote px where px.nom = ' Milou TINTIN' ;

Page

54



Identit sur les tables

Exemple : Manipulation des rfrences et la clause RETURNING REF( ) INTO ... La clause RETURNING... REF( ) INTO permet, lors de linsertion dune ligne dans une table Objet de capturer la vole la rfrence vers cet Objet. L' intrt est la mise jour des liens. // Insertion d un pilote et d un vol dans un programme PL/SQL declare pil_ref REF ref_pilote_t :=null; Begin INSERT INTO o_ref_pilote op VALUES ( ref_pilote_t(1, ' Dupond' Jean , adresse_t(3, ' Arago'6000, ' rue , NICE' ' ), 0493825084' , ' 11-AUG-62'10000.5)) RETURNING ref(op) INTO pil_ref; , INSERT INTO ref_vol values(400, ' Paris'' , Nice'pil_ref); , end; /

Page

55



Identit sur les vues A l image des tables, deux types de vues sont introduites en Oracle objet : Les vues normales (relationnelles) et les vues dites objets

Les vues objets possdent des identifiants physiques appels Primary key OID (pseudo OID) avec leur pendant la manipulation Primary key REF (pseudo REF) les vues objets servent entre autre crer des OID sur les tables relationnelles classiques !!! Note: voir chap. 10 pour plus de dtails sur les vues

Page

56



Identit sur les vues Syntaxe de cration d' vue object une CREATE OR REPLACE [FORCE] VIEW nomVue OF nomTypeObjet WITH OBJECT IDENTIFIER [DEFAULT | (attribut1, attribut2, )] AS sousRequte

Mots et paramtres OF nomTypeObjet

Description cration d' vue objet sur le type une nomTypeObjet permet de spcifier la ou les colonnes utilises comme cl pour identifier de faon unique les lignes de la vue objet. Cette clause est obligatoire s' il s' d' vue sur une table objet agit une ou une vue objet.

WITH OBJECT IDENTIFIER

Page

57



Identit sur les vuesDescription attributs permettant didentifier de faon unique une vue indique que l' identification d' objets de la vue s' appuie sur les OID de la table objet ou vue objet sousjascente

Mots et paramtres Attribut1, attribut2, ... DEFAULT

NOTES : - les attributs Attribut1, Attribut2, sont la cls primaire de la table sous-jascente

Page

58



Identit sur les vues

Exemple :CREATE table Pilote1 ( PL# NUMBER(4) CONSTRAINT pk_pilote1 PRIMARY KEY, nom VARCHAR2(12), adresse adresse_t,-- type abstrait dfini pralablement Tel VARCHAR2(12), dnaiss DATE, salaire NUMBER(7,2)); // cration du type Ville_pil_t CREATE OR REPLACE TYPE Ville_pil_t AS OBJECT( plnum NUMBER(4), nom CHAR(20), ville CHAR(20)); CREATE OR REPLACE VIEW Ville_pil_v OF Ville_pil_t WITH OBJECT IDENTIFIER(plnum) AS -- liste de colonnes assurant l unicit de la cl select p.Pl#, p.nom, p.adresse.ville from Pilote1 p;

Page

59



Identit sur les vues

Exemple : INSERT INTO pilote1 VALUES (5, Milou TINTIN, adresse_t(15, Av Mars, 75000, CAP), 0493825084, 11/08/64, 15000.5);

SQL> SELECT * FROM ville_pil_v; PLNUM 4 5 NOM Milou TINTIN Milou TINTIN VILLE Cap Cap

SQL> SELECT REF(v) FROM ville_pil_v v; REF(V) 000050030A004CAA966C70AF1111D2A64700A0245FEFFD00000 01426010001000100290000000000090604002A0007000000 200200000000000000001602C105000000000000000000000000000 00000000000 000050030A004CAA966C70AF1111D2A64700A0245FEFFD00000 01426010001000100290000000000090604002A0007000000 200200000000000000001602C106000000000000000000000000000 00000000000

Page

60



Diffrence entre un OID et un ROWIDCritre Taille ROWID 10 OID 16 OUI NON OUI

identifiant immuable NON Sert indexer partage d' objets OUI NON

SQL> SELECT REF(op), ROWID FROM o_ref_pilote op; REF(OP) ROWID 000028020985B3AD5BB8C04D39BF81C8DB5573EFE53AFC0C48 6CA3447283858183F28672A6020001 B20000 AAAIFzAAIAAAAGyAAA 0000280209A36782FB22EE44409D6560943CFD7A433AFC0C486 CA3447283858183F28672A6020001 B20001 AAAIFzAAIAAAAGyAABPage

61


6. NOTION DE COLLECTIONI

PLAN Gnralits Les tableaux variables (VARRAY) Les tables imbriques (Nested Table) Indexation dune Nested Table Stockage dune Nested Table Mthodes sur les Collections Comparaison entre VARRAY et Nested Table

Page

62



Gnralits Deux types de collections :- Les tableaux de longueur variable (VARRAY) ANSI SQL. - Ensemble Multiple ou Bag (Nested Table) ANSI standard.

Une collection peut contenir :- Des types de donnes lmentaires. - Des types de donnes abstraits. - Des rfrences vers des types de donnes abstraits.

Page

63



Les tableaux variables (VARRAY) Dfinition dun VARRAY Un varying ARRAY est un ensemble dlments ordonns de mme type. Chaque lment a une valeur et occupe une position unique.

SyntaxeCREATE [OR REPLACE ] TYPE nomType AS {VARRAY | VARYING ARRAY} (nombreMaxDlments) OF typeDeDonnes

Page

64



Les tableaux variables (VARRAY)

Exemple de Cration d' VARRAY unCREATE TYPE avion_t as object ( Av# NUMBER(4), AvType VARCHAR2(45), Loc VARCHAR2(20), Cap NUMBER(4)); / CREATE TYPE avions_t AS VARRAY (5) OF avion_t ; -- Cration d' table contenant un VARRAY une -- Chaque colonne avions est un tableau de 5 lments de avions_t CREATE TABLE hangar ( hangar# NUMBER(2) , avions AVIONS_T);

Page

65



Les tableaux variables (VARRAY)

Exemple dinsertion dans une table avec un VARRAYINSERT INTO hangar VALUES (1, avions_t (avion_t ( 1, AIRBUS, NICE,300) )) ; INSERT INTO hangar VALUES (2, avions_t (avion_t ( 2, AIRBUS, PARIS,320))) ; INSERT INTO hangar VALUES (3, avions_t (avion_t ( 3, AIRBUS, PARIS,320), avion_t ( 5, AIRBUS, PARIS,320))) ;

Exemple de consultation dune table avec un VARRAYSQL>SELECT * FROM hangar;

HANGAR# AVIONS(AV#, AVTYPE, LOC, CAP) 1 AVIONS_T(AVION_T(1, AIRBUS, NICE, 300)) 2 AVIONS_T(AVION_T(2, AIRBUS, NICE, 300)) 3 AVIONS_T(AVION_T(3, AIRBUS, NICE, 300), avion_t ( 5, AIRBUS, PARIS,320))Mise jour d' VARRAY. un UPDATE hangar SET avions =avions_t(avion_t ( 4, CARAVELLE,PARIS,320)) WHERE hangar#=2 ;Page

66



Les tableaux variables (VARRAY) Quelques contraintes et informations :- Le stockage d un VARRAY se fait l intrieur de la table ou il a t dclar - si un VARRAY est trop grand, il peut tre stock en dehors du segment de la table - Un constructeur d objet existe aussi sur un type VARRAY - dans les Ordres SQL (UPDATE, INSERT, DELETE), un VARRAY est manipulable comme un TOUT - La mise jour d' lment particulier est possible un dans PL/SQL

Page

67



Les tables imbriques (Nested Table) Dfinition dune nested table (bag ou set) Les nested tables sont des tables imbriques efficaces pour la gestion des relations matre-dtail.

Caractristiques Une Nested Table peut tre un champ, un attribut, une variable ou un paramtre dont le type de donne est un type table . Les Store Tables sont des segments physiques o sont stocks les instances des Nested Tables

Page

68



Les tables imbriques (Nested Table) Reprsentation schmatiquePL# PLNOM ADR Tlphone Salaire 1 Tintin @1 012222111 10000.0 2 Zembla @2 5551111 23000.0 3 Bleck @3 222222 12500.0 4 Dragon bol @... 233399 15000.0

@1 Nested_table_id Numro Rue Code Postal Ville @1 60 r. du Congrs 06100 Nice

@2 @2 15 av. Gare 075002 Paris

@1

@3

@1 @3 47 25 BD Dessaline Square des iles 75014 Port au Prince Paris

STORE TABLE : segment associ la table imbrique

Page

69



Les tables imbriques (Nested Table) Cration d' type Table un CREATE OR REPLACE TYPE nomType AS TABLE OF typeDesElements Exemple-- Cration d un type table sur le type vol_t CREATE OR REPLACE TYPE ref_Vol2_t AS OBJECT ( Vol# NUMBER(4), VD VARCHAR2(20), VA VARCHAR2(20), Pilote REF ref_pilote_t); --type abstrait dfini pralablement CREATE OR REPLACE TYPE REF vol2_set_t AS TABLE OF REF ref_vol2_t ; .

Page

70



Les tables imbriques (Nested Table) Utilisation d' type TABLE un un CHAMP d' type ou une COLONNE d' table un une peuvent tre instance d' type TABLE un

-- Utilisation du type table VOL2_SET_T CREATE OR REPLACE TYPE Avion2_ns_t AS OBJECT( Av_num NUMBER(3), Av_type VARCHAR2(12), Av_loc VARCHAR2(20), Av_cap NUMBER(3), Av_rem CLOB, Av_liste_vol Vol2_set_t) ;

NOTE : dans ce modle un VOL n' existe que parce qu' ya un il PILOTE.

Page

71



Les tables imbriques (Nested Table) Cration d' STORE TABLE pour stocker les une objets dune table imbrique. Une STORE TABLE est une table dont l' accs aux LIGNES se fait uniquement travers la table qui la contient. Exemple-- Table non Objet avec Table imbrique CREATE TABLE Avion2_ns ( Av_num NUMBER(3), Av_type VARCHAR2(12), Av_loc VARCHAR2(20), Av_cap NUMBER(3), Av_rem CLOB, Av_liste_vol Vol2_set_t) NESTED TABLE av_liste_vol STORE AS storeVols2Set; -- Table objet avec Table imbrique. CREATE TABLE avion2_ns OF Avion2_ns_t NESTED TABLE Av_liste_vol STORE AS vols2_set ;

Page

72



Les tables imbriques (Nested Table) : CARACTERISTIQUES ces tables sont accessibles indirectement que via un SELECT, UPDATE, INSERT Oracle tend le relationnel au niveau domaine (EF Codd) Un TYPE TABLE est ncessaire pour crer une NESTED TABLE Il peut tre utilis comme type de donnes dans les dclarations suivantes : colonne SQL, attribut d' ADT, variable, paramtre ou rsultat en PL/SQL.

Page

73



Les tables imbriques (Nested Table) Un constructeur par dfaut est fourni pour construire les objets d' NESTED TABLE, il doit une tre du mme type :- du mme nom que le type de la table. - retourne une table dont les lments sont une liste de valeurs (la valeur NULL est permise). - appel sans argument pour construire une table vide.Exemple CREATE OR REPLACE TYPE Avion3_t AS OBJECT ( Av_num NUMBER(3), Av_type VARCHAR2(12), Av_loc VARCHAR2(20), Av_cap NUMBER(3), Av_rem CLOB); CREATE OR REPLACE TYPE avion3_set_t AS TABLE OF avion3_t ;

Page

74



Les tables imbriques (Nested Table)Exemple (suite) -- Cration d' table avec une colonne de type table une CREATE TABLE hangar3 ( hangar# number(2), avions avion3_set_t) NESTED TABLE avions STORE AS store_avion3_set ; -- insertion dans la table hangar3 INSERT INTO hangar3 VALUES (1,avion3_set_t()) ; -- modification de la table hangar3 UPDATE hangar3 h SET -- hangar est la table dfinie pralablement. h.avions = avion3_set_t (avion3_t (1, ' AIRBUS'' , NICE' ,300, null) ) WHERE h.hangar# = 1 ; -- consultation de la table hangar3 SELECT t1.av_num, t1.av_type, t1.av_loc, t1.av_cap FROM table(SELECT avions FROM hangar3 WHERE hangar#=1) t1; AV_NUM AV_TYPE 1 AIRBUS AV_LOC NICE AV_CAP 300

Page

75



Les tables imbriques (Nested Table) Afin d' viter les duplications, il est ncessaire de stocker les rfrences dans une Nested Table. Ce qui n' pas le cas ici car il ya duplication des AVIONS. estEXEMPLE CREATE TABLE avion3s OF avion3_t; INSERT INTO AVION3S VALUES (avion3_t (1, AIRBUS, NICE,300, null) ); INSERT INTO hangar3 VALUES (2, avion3_set_t( avion3_t (2, CARAVELLE, NICE,300, null) )) ; -- consultation de la table Avion3s. SELECT * FROM avion3s; AV_NUM AV_TYPE 1 AIRBUS AV_LOC NICE AV_CAP AV_REM 300

SELECT avions FROM hangar3; AVIONS(AV_NUM, AV_TYPE, AV_LOC, AV_CAP, AV_REM) AVION3_SET_T(AVION3_T(1, AIRBUS, NICE, 300, NULL)) AVION3_SET_T(AVION3_T(2, CARAVELLE, NICE, 300, NULL))Page

76



Indexation dune Nested Table Les lments associs un objet par exemple 2000 AND Salaire nom du rpertoire dans la base

Page

172


11. 3 Les LOB externesI

Voici les tapes pour installer des objets de type FILE et des objets DIRECTORY : 1. Crer le rpertoire sous l OS (en tant qu utilisateur oracle) et lui donner les accs. 2. Copier les fichiers l intrieur de ce rpertoire. 3. Crer la table oracle contenant le type BFILE. 4. Crer l objet DIRECTORY et lui donner les droits. 5. Insrer les rows de la table en associant le fichier OS.

Page

173



Exemple : 1. Cration du rpertoire sous L' OS mkdir C:\ORA8DATA\DBCOUR\LOB\FILES\; 2. Copier le fichier dans ce rpertoire : exemple ORACLEGAL3.DOC

3. Crer la table contenant le BFILE CREATE TABLE PROJET( PROJ# ProjNom ProjFile suivre NUMBER(4), VARCHAR2(20), BFILE);

Page

174



Exemple : 4. Crer un objet DIRECTORY CREATE OR REPLACE DIRECTORY bfile_dir AS ' C:\ORA8DATA\DBCOUR\LOB\FILES\' ; 5. Initialiser un BFILE grce la mthode BFILENAME(' nomDirectory'' , nomFichier' ); INSERT INTO projet VALUES(102, ' Objectif lune ' , BFILENAME(' BFILE_DIR' , ' ORACLEGAL3.DOC' )); NOTE : Pas de vrification de la prsence du fichier en insertion

Page

175



Rgles d' utilisation des directory Une directory ne doit pas pointer sur les Rpertoires des Fichiers Oracle(fichiers de donnes, de controles our Redo Log) : RISQUE d' ACCIDENTS Contrler l' affectation du privilge CREATE ANY DIRECTORY Ne pas utiliser les commandes DROP ou REPLACE sur les objets qui pointent vers un BFILE en cours de manipulation. Risque de perte de pointeurs Le retrait des privilges un utilisateur sur une Directory provoque l' chec de ses actions sur un BFILE

Page

176


11. 4 Les LOBs LocatorsI

On distingue deux types de Lobs Locators : les LOBs Locators internes les LOBs Locators externes

I

Un Lob Locator est un pointeur contenu dans un enregistrement vers les donnes (DATA) d' un LOB externe ou interne. Sil sagit dun BFILE le Locator est un nom de fichier Sil sagit dun CLOB, BLOB ou NCLOB cest la position de la valeur du LOB dans son segment de donnes

Page

177


11. 4 Les LOBs LocatorsI

Oprations sur les LOBs Locators Initialisation des colonnes devant contenir des LOCATORS. Via INSERT et UPDATE en utilisant les Builtins : EMPTY_CLOB() RETURN CLOB; EMPTY_BLOB() RETURN BLOB; BFILENAME(directory_alias, filename) RETURN BFILE; Rcupration des LOBs dans des Variables HOTEs SELECt nomLob INTO variableLob Manipulations de LOBs partir du package DBMS_LOB

Page

178


11.5 Le Package DBMS_LOBI

LOB PL/SQL Package (DBMS_LOB) La plupart des oprations utilises sur les objets BFILEs, CLOB, BLOB et NCLOB sont contenues dans la boite outils DBMS_LOB. Ces outils utilisent les valeurs de deux constantes : DBMS_LOB.MAX_BLOB_SIZE = 4 Go. DBMS_LOB.MAX_CLOB_SIZE = 4 Go.

Les fonctions et procdures de ce package peuvent tre classes en deux groupes : Mutators : Write, fdelete, copy, erase..... (interviennent sur la mise jour de fichiers externes). Observers : Compare, read, exist, ... (interviennent en consultation).

Page

179



Classification des mthodesMthodes de MAJ des BLOB, CLOB et NCLOB Append() Ajoute le contenu du source vers Destination copie un LOB source vers un autre Efface toute les parties dun LOB Charge les donnes d' Fichier vers un un BLOB interne Rduit la valeur d' LOB la taille un spcifie Ecriture des donnes dans un LOB partir d' OFFSET un

Copy() ERASE() LOADFROMFILE()

TRIM()

WRITE

Page

180



Classification des mthodesMthodes de Lecture et de contrle des LOB internes et Externes compare compare deux LOBs ou deux parties de LOBs rend la longueur dun LOB rend la position de la Nime occurrence d' modle dans le LOB un lecture des donnes dans un LOB partir de la position OFFSET rend une "partie" du LOB partir de la position OFFSET

getLength() instr()

read()

substr()

Page

181



Classification des mthodesMthodes spcifiques aux BFILE fileClose() fileClose() fileExist() rend la longueur dun LOB fermeture de tous les fichiers ouverts teste si le fichier existe bien dans sa directory rend le nom Oracle de la directory et le nom du fichier test si un fichier est ouvert ouverture file

fileGetName()

fileIsOpen() fileOpen()

Page

182



Classification des mthodes Les types de donnes en entre ou en sortie des mthodes sur les BLOB BLOB : source ou destination des Binary LOB RAW : source "raw buffer" et destination "raw buffer" (BLOB) CLOB : source ou destination des Character LOB VARCHAR2 :source/destination des buffers de caractres (utile pour manipuler les CLOB et NCLOB) INTEGER : spcifie la taille du buffer ou du LOB, l' offset du LOB

Page

183



La mthode READ Cette mthode permet de lire des donnes dans les LOB internes ou externes Trois prototypes sont disponibles selon le type de LOB : BLOB, CLOB/NCLOB, BFILE1. procedure READ(lob_loc IN BLOB, amount IN OUT BINARY_INTEGER, offset IN TEGER, buffer OUT RAW) 2. procedure READ(lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN OUT BINARY_INTEGER, offset IN TEGER, buffer OUT VARCHAR2 CHARACTER SET LOB_LOC%CHARSET) 3. procedure READ(lob_loc IN BFILE, amount IN OUT BINARY_INTEGER, offset IN TEGER, buffer OUT RAW)

Page

184



La mthode READ les paramtres nom lob_loc amount offset buffer description Locator du LOB lire Quantit des donnes lire Position partir du dbut du CLOB ou BLOB Buffer dans lequel est stock le rsultat

les exceptions pouvant survenir VALUE_ERROR INVALID_ARGVAL NO_DATA_FOUND Pour les BFILEs seuls : UNOPENED_FILE, NO_EXIST_DIRECTORY, NOPRIV_DIRECTORY, INVALID_OPERATIONCopyright G. Mopolo-Mok Oracle et le modle Objet Relationnel

Page

185


La mthode READ Exemple de lecture d' BLOB unCREATE OR REPLACE PROCEDURE readCvAnime IS lobCvAnime BLOB; buffer RAW(32767); qte BINARY_INTEGER := 32767; position INTEGER := 1; BEGIN SELECT cv_anime INTO lobCvAnime FROM lpilote WHERE pl#=1; LOOP DBMS_LOB.READ(lobCvAnime, qte, position, buffer); /* Insrer les Manipulations ICI*/ position := position+qte; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' More Data' No ); END; /

Page

186



La mthode READ Exemple de lecture dans un BFILECREATE OR REPLACE PROCEDURE readProjetFile IS bfProjetFile BFILE; buffer RAW(32767); qte BINARY_INTEGER := 32767; position INTEGER := 1; BEGIN SELECT projFile INTO bfProjetFile FROM projet WHERE proj#=102; -- ouverture du fichier (Lecture seule sinon erreur) IF DBMS_LOB.FILEEXISTS(bfProjetFile)=1 THEN DBMS_OUTPUT.PUT_LINE(Fichier Ouvert ! ); DBMS_LOB.FILEOPEN(bfProjetFile, DBMS_LOB.FILE_READONLY); ELSE DBMS_OUTPUT.PUT_LINE(Fichier inexistant); END IF; --position := position+qte; -- suivre

Page

187



La mthode READ Exemple de lecture d' BFILE un LOOPDBMS_LOB.READ(bfProjetFile, qte, position, buffer); position := position+qte; /* Insrer les Manipulations ICI*/ END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' du LOB' Fin ); DBMS_LOB.FILECLOSE(bfProjetFile); END; /

Page

188



La mthode WRITE Cette mthode permet d' crire des donnes dans un LOB interne partir d' OFFSET un Deux prototypes sont disponibles selon le type de LOB : BLOB, CLOB/NCLOB, BFILE1. procedure WRITE(lob_loc IN OUT BLOB, amount IN IN BINARY_INTEGER, offset IN TEGER, buffer IN RAW) 2. procedure WRITE(lob_loc IN OUT CLOB CHARACTER SET ANY_CS, amount IN BINARY_INTEGER, offset IN TEGER, buffer IN VARCHAR2 CHARACTER SET LOB_LOC%CHARSET)

Page

189



La mthode WRITE les paramtres nom lob_loc amount offset buffer description Locator du LOB interne ou crire Nombre de bytes crire ou crits Position ( partir du dbut du CLOB ou BLOB) partir de laquelle on va crire Buffer contenant les donnes crire

les exceptions pouvant survenir VALUE_ERROR INVALID_ARGVAL

Page

190



La mthode WRITE Exemple d' criture dans un BLOBCREATE OR REPLACE PROCEDURE writeCvTexte IS lobCvtexte CLOB; buffer VARCHAR2(32767); qte BINARY_INTEGER := 32767; position INTEGER :=2147483647; BEGIN SELECT cv_texte INTO lobCvTexte FROM lpilote WHERE pl#=12; LOOP -- prparer des donnes dans buffer puis crire -- buffer := ' ya un debut a tout' Il ; DBMS_LOB.WRITE(lobCvTexte, qte, position, buffer); position := position+qte; END LOOP; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(' Erreur de Valeurs' ); END; /

Page

191


11.6 Informations sur les LOBsI

Les vues du dictionnaire Oracle Les vues sur les LOBS : ALL_LOBS, DBA_LOBS, USER_LOBS Les vues sur les directories : ALL_DIRECTORIES, DBA_DIRECTORIES

I

DESCRIBE DBA_LOBSNull? NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL Type VARCHAR2(30) VARCHAR2(30) VARCHAR2(400 VARCHAR2(30) VARCHAR2(30) NUMBER NUMBER VARCHAR2(3) VARCHAR2(3) VARCHAR2(3)

Name OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME CHUNK PCTVERSION CACHE LOGGING IN_ROW

Page

192



Les vues du dictionnaire Oracle DESCRIBE DBA_LOBSName CHUNK Description Unit de transfert dans un LOB : Multiple d' Bloc un Zone de lecture consistante Cache mmoire pour le LOB Vrai si stockage dans le segment contenant le LOB

PCTVERSION CACHE IN_ROW

DESCRIBE DBA_DIRECTORIESName OWNER DIRECTORY_NAME DIRECTORY_PATHI

Null? NOT NULL NOT NULL

Type VARCHAR2(30) VARCHAR2(30) VARCHAR2(4000)

Page

193



Les vues du dictionnaire Oracle Lister les informations sur les directoriesSELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME, INDEX_NAME, CHUNK, PCTVERSION, CACHE, IN_ROW

FROM dba_lobs WHERE table_name IN (' LPILOTE'' , PROJET' );TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME CHUNK PCTVERSION CACHE IN_ROW LPILOTE CV_TEXTE SYS_LOB0000003236C00 SYS_IL0000003236C000 2048 007$ 07$ 20 YES NO SYS_LOB0000003236C00 LPILOTE CV_ANIME SYS_IL0000003236C000 2048 008$ 08$ 10 NO NO

Page

194



Les vues du dictionnaire Oracle Lister les informations sur toutes les Directories de votre base. SELECT OWNER, DIRECTORY_NAME, DIRECTORY_PATH FROM dba_directories;OWNER DIRECTORY_NAME DIRECTORY_PATH SYS BFILE_DIR2 SYS BFILE_DIR F:\ora8data\DBCOUR\LOB\files\ C:\ORA8DATA\DBCOUR\LOB\FILES

Page

195


11.7 Quelques Considrations sur les LOBsI

Lecture Consistante et LOB : Oracle assure que deux processus peuvent agir sur un mme CHUNCK d un LOB. L un en lecture l autre en criture.

I

Le nombre maximum de fichiers BFILE ouverts est contrl via le paramtre d' initialisation SESSION_MAX_OPEN_FILES=valeur Le dveloppeur doit, s' a ouvert un fichier le il fermer. SINON RISQUE DE DEPASSEMENT du PARAMETRE SESSION_MAX_OPEN_FILES

Page

196


11.8 Contraintes sur les LOBsI

Pas de LOB distribu Pas de chargements de LOB avec SQLLOADER en V8. Utiliser dbms_lob.loadfromfile() Les LOBs ne peuvent apparatre : dans des requtes avec Group by, Order by, select distinct, join.

I

I

Page

197


11.8 Contraintes sur les LOBsI

Pas dappel des mthodes du package DBMS_LOB dans le code PL/SQL du poste client Les NCLOBs ne sont pas autoriss dans les types objets

I

Page

198


12. PL/SQLI

PLAN Gnralits Les types Les instances Les collections en PL/SQL Les call out de PL/SQL

Page

199


12. PL/SQLI

Gnralits Version disponible du cot du moteur Oracle. les outils de dveloppement ne sont pas encore toujours jour supporte la programmation Orient Objet amliore les performances grce la centralisation du code sur le moteur base de donnes augmente la portabilit

Page

200


12. PL/SQL OBJETI

Les types Oracle Objet distingue deux sortes de types : .Les types dfinis en SQL (types permanents): Les ADT dfinis ce niveau peuvent avoir des mthodes Les types dfinis en PL/SQL (types temporaires) : leur dure de vie est limite l excution du programme.

Dfinition d' type permanent en SQL unSQL>CREATE TYPE pAdresse_t as object( Numro NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20)); Ce type pourra tre utilis dans la dclaration de variables PL/SQL

Page

201


12. PL/SQL OBJETI

Les types Dfinition d' type temporaire en PL/SQL un Les types temporaires sont dfinis grce la commande TYPE. Il sera possible de dfinir des "Types Tables", des Types RECORD, des Types VARRAY et des Types REF CURSOR Les diffrentes Syntaxes TYPE nomType IS VARRAY(limit) OF typeDesElements; TYPE nomType IS TABLE OF typeDesElements ; TYPE nomType IS RECORD (dclarationDesChamps, ); TYPE nomType REF CURSOR OF typeDesElements ; Les lments d' VARRAY ou d' Type table peuvent un un pas tre : BOOLEAN, NCHAR, NVARCHAR2, types objets contenant des types tables ou des VARRAY, des REF CURSOR, des TYPES TABLES ou des VARRAY

Page

202


12. PL/SQL OBJETI

Les types Dfinition d' type temporaire en PL/SQL un Exemple declare TYPE numList_t IS TABLE OF NUMBER; TYPE jourSeamine_t IS VARRAY(7) OF VARCHAR2(13); TYPE tabAdresse_t IS VARRAY(2) OF Adresse_t; TYPE pAdresse_t IS RECORD( Numero NUMBER(4), Rue VARCHAR2(20), Code_Postal NUMBER(5), Ville VARCHAR2(20)); p padresse_t; begin p.numero :=10; end; / NOTE : adresse_t est est type permanent

Page

203


12. PL/SQL OBJETI

Les instances

1) Instantiation permanente dun ADT -- cration de la table object Adresse CREATE TABLE Adresse OF Adress_t ; -- type abstrait dfini pralablement INSERT INTO Adresse VALUES (1, rue Arago ,06000, NICE ) ; 2) Instantiation temporaire dun ADT DECLARE addr1 adress_t := adress_t (1, rue Arago ,06000, NICE ) ; --avec constructeur addr2 adress_t ; -- a pour valeur NULL BEGIN IF addr2 IS NULL -- VRAI ; addr2.Rue := 5th Avenue ; -- Exception ! END ;

Page

204


12. PL/SQL OBJETI

Les collections en PL/SQL A la diffrence de PL/SQL V2, les tables dfinies partir d Oracle 8 peuvent contenir des ADTs comme lments. Exemple de manipulation d' collection dfinies une dans la base ou dans PL/SQL SQL> CREATE PR REPLACE TYPE avion9_t as object ( Av# AvType Loc CapAS TABLE OF avion9_t;

NUMBER(4), VARCHAR2(45), VARCHAR2(20), NUMBER(4));

SQL>CREATE OR REPLACE TYPE avion9_set_t

Page

205


12. PL/SQL OBJETI

Les collections en PL/SQL Exemple de manipulation d' collection dfinie dans une la base ou dans PL/SQL

Cration d une table avec une colonne de type table CREATE TABLE hangar9 ( hangar# avions9 NUMBER(2), avion9_set_t)

NESTED TABLE avions9 STORE AS storeAvions9;

Page

206


12. PL/SQL OBJETI

Les collections en PL/SQL OBJET Exemple de manipulation d' collection dfinie dans une la base ou dans PL/SQLDECLARE tabav9 avion9_set_t; BEGIN tabav9 := avion9_set_t( avion9_t(1, ' Concorde'' , Paris'100), , avion9_t(1, ' A300'' , Berlin'300), , avion9_t(1, ' A320'' , Paris'200)); ,

INSERT INTO hangar9 values (1, avion9_set_t(avion9_t(1, ' Caravelle' , ' Paris'250), avion9_t(2, ' , A300'' , 300))); , Nice' INSERT INTO hangar9 VALUES(2, tabav9); END; /Page

207


12. PL/SQL OBJETI

Les CALL OUT de PL/SQL OBJET Il est possible d' excuter un code d' L3G un (gnralement C ou parfois PASCAL) partir d' un code PL/SQL. Ce code L3G s' excute dans un espace d' adressage spar. Il a un accs contrl grce aux objets LIBRARY.

Page

208


13. Import/ExportI

Plan Gnralits Import/Export et performances Le dictionnaire des donnes Les Exports Les Imports Import/Export de BLOB Divers

Page

209


13.1 GnralitsI

Les utilitaires Import/Export prennent en compte tous les objets dORACLE OBJET. Si lors dun import/export, il apparat des types objets dans une table, elle sera considre comme oriente objet. Les objets manipuls lors des import/export sont les suivants : Les ADTs et rfrences d ADT. Les Librairies et fonctions spcifiques. Les LOBs. Les DIRECTORIES. Les Tableaux (ARRAY et VARRAY) . Les Nested Tables.

I

I

Page

210


13.1 Import/Export et performanceI

Les performances dimport/export dcroissent si export/import : De donnes volumineuses de donnes objets de tables objets des tables n' ayant aucune rfrence vers les User Defined Type

Page

211


13.2 Le Dictionnaire de donnesI

La table SYS.INCEP, dfinie dans sql.bsq Elle contient toutes les informations propos des dfinitions des types exports. Pour les types, la valeur du type OID (TOID), au moment de lExport, est enregistre. Elle sera utilise pour diffrencier les types modifis des types qui ont t remplacs depuis le dernier Export.

Page

212


13.3 Les ExportsI

Les dfinitions de types utilises par une table sont incluses dans l export, uniquement si elles appartiennent au mme schma.

Page

213


13.4 Les ImportsI

OID et Import Limport prserve les OID des Types , des tables objets et des lignes exportes La valeur de lOID exporte ne doit pas tre diffrente en cas d' export un COMMIT est effectu aprs chaque table exporte En cas de CREATE OR REPLACE limport dtermine le dgr d' utilisation du type et supprime toute les tables qui l' utilesent

Page

214


13.5 Import/export des BLOBsI

Ladministrateur doit manuellement se proccuper de la sauvegarde des fichiers binaires externaliss Oracle assure la sauvegarde des Localisations (Locators) des fichiers Binaires

I

Page

215


13.6 DiversI

En version objet, il ny a pas de diffrence ou dimpact sur la commande EXPLAIN PLAN. La commande ANALYZE supporte dsormais les extensions lobjet. De nouvelles syntaxes apparaissent (ANALYZE COLUMN VALIDATE REF).

I

Page

216


14. CONCLUSIONI

Le modle Objet Relationnel est un compromis entre le modle objet pur et le modle relationnel. Il permet une approche volutive vers l' objet Il est ncessaire ds prsent denvisager la mise en place d' applications tests ou une migration L' option Objet permet d' tendre le noyau Oracle (cartouche : son, image, vido, texte, )

I

I

Page

217


15. ExercicesI

PLAN Objectif du TP Dfinition du schma d' objets PL/SQL et Procdures stockes Insertion des objets Consultation des objets Mise jour des objets Les vues

Page

218


15. ExercicesI

Objectif du TP Manipuler une base de donnes objet relationnelle Les points que nous visons sont : concevoir et crer le schma d' base de donnes objet une relationnelle consulter les objets du schma en exploitant entre autre les nouveaux oprateurs tels que TABLE, VALUE, ... effectuer des insertions, mises jour et suppressions dans ce schma en exit Manipuler des objets dans PL/SQL Manipuler les vues objets sur les tables rlationnelles manipuler des objets volumineux

Page

219


15. ExercicesI

Dfinition du schma d' objets Schma conceptuelEMPLOYE

# * * * * *

EMPNO ENAME PRENOMS {de 0 4 prnoms} JOB SAL CV DATE_NAISS DATE_EMB

Est employ dans emploie DEPT # * * DEPTNO DNAME LOC

# *

: indique un identifiant : attribut obligatoire : attribut facultatif

Page

220


15. ExercicesI

Dfinition du schma d' objets Description des entitsNom entitEMPLOYE

Nom des champsEMPNO ENAME PRENOMS JOB SAL CV DATE_NAISSS DATE_EMB

typeNUMBER(4) varchar2(15) varray Varchar2(20) number(7,2) CLOB date date number(4) varchar2(30)

LibellNumro Employ Nom EMPLOYE : En lettre capitales tableau de 4 prnom max Mtier de l employ {Ingnieur, Secrtaire, Directeur, Planton, PDG} Salaire entre 1500 et 15000 CV Date de Naissance Date embauche : doit tre suprieure Date_Naiss Nr. de dpartement doit tre entre 1000 et 9999 Nom du dpartement {Recherche, Resources, Humaines, Marketing, Ventes, Finance} Localisation du dpartement (voir

DEPT

DEPTNO DNAME

LOC

varchar2(30)

NOTE : prendre en compte les contraintes attributs UNIQUE ou OBLIGATOIRE schma page prcdente)

Page

221


15. ExercicesI

Dfinition du schma d' Objets Suggestions Toutes les tables doivent tre des tables objets les liens 1:N ou N:M sont systmatiquement modliss via le type REF si 1 ou NESTED TABLE si N les dclarations des types FORWARD ou INCOMPLETS (EMPLOYE_t, DEPT_t) sont faire les lments des Types Tables (listes) sont des rfrences vers les types incomplets prcits les mthodes sont dclarer ds la cration des types l' action de chaque mthode doit tre limite RESTRICT_REFERENCES chaque "Type objet" doit contenir une mthode de comparaison MAP ou ORDER les index seront localiss dans le Tablespace TS_INDEX_RES et les tables dans le Tablespace TS_TABLE_RES les index et les donnes des LOB internes sont localiser dans le tablespace TS_LOB_RES. NOTE : LIRE L' ENSEMBLE DU TP PUIS CHAQUE SECTION ENTIEREMENT

Page

222


15. ExercicesI

Cration des types Les tapes1. Crer les types FORWARD ou INCOMPLETS appropris. Il n' pas ncessaire de dfinir tous les types en FORWARD est 2. Crer le type ARRAY tab_prenoms_t ncessaire pour grer la liste des PRENOMs 3. Crer le Type Table (liste) ncessaire : ces lments seront des rfrences vers des EMPLOYE_t 4. Dfinir maintenant vos types EMPLOYE_T, DEPT_T, Vous devez y dclarer les champs ou attributs des types, les mthodes et les liens ou Nested Tables Note : pour les mthodes et les liens, voir plus loin.

Page

223


15. ExercicesI

Cration des types Les mthodes1. Les mthodes de consultation introduire une mthode de classe qui affiche les informations (, ...) sur un DEPT connaissant son numro

static function getDept (deptno) return boolean; crire une mthode de classe qui affiche pour un DEPT donn, le nom et les prnoms de chaque Employ qui y travaillent. Static function getInfoEmp(deptno) return boolean

2. Les mthodes pour ordonner les lments des types crire pour chaque type une mthode ORDER ou MAP. Il est ncessaire d' utiliser au moins une fois chacune de ces mthodes.

Page

224


15. ExercicesI

Cration des types Les mthodes3. Les mthodes de gestion des LiensIntroduire pour chaque Lien Multivalu les procdures suivantes : - addLinkNomColonneNested(RefType1 REF type); -- pour ajouter dans la liste - deleteLinkNomColonneNested (RefType1 REF type); -- suppression dans un lien - updateLinkNomColonneNested (RefType1 REF type, RefType2 REF type); -- modification du lien

4. Notes ces mthodes DOIVENT tre en Lecture seule (PRAGMA RESTRICT_REFERENCES) sauf celles dcrites en 3. dans cette phase, aucune mthode n' encore t a implmente. Il ne s' que des dclarations. agit

Page

225


15. ExercicesI

Cration des tables Crer les tables EMPLOYE_O, DEPT_O comme tant des tables objets Les contraintes dintgrits Dfinir les contraintes d' intgrits d' entits et de rfrences sur chacune des tables Dfinir les contraintes d' intgrits de domaine (cf. la description des champs page 4 et page 5)

Les Nested Tables Donner les noms de segments toutes vos Nested Tables Format du nom : storeNomColNested

Les LOB internes PCTVERSION doit tre 30

Page

226


15. ExercicesI

Cration des tables Localisation des objets Les tuples des tables objets sont localiser dans le Tablespace TS_TABLE_RES les index des tables objets y compris ceux crs implicitement sont localiser dans TS_INDEX_RES les donnes et les index des LOB internes sont localiser dans le Tablespace TS_LOB_RES

I

Cration index Crer un index unique sur la colonne dname de DEPT_o crer un index sur la colonne implicite Nested_table_id de la Nested Table. Peut - il tre unique ?

Page

227


15. ExercicesI

Insertion des objets Insrer 2 ou 3 objets dans chacune des tables cres prcdemment. L' intgrit des objets doit tre assure

I

Mise jour des objets Modifier la localit d' dpartement connaissant son un nom Modifier la date d' embauche d' Employ un connaissant son nom sachant qu il doit travailler dans l un des dpartements suivants : Recherche , Finance ou Ressources Humaines Supprimer un DEPT

Page

228


15. ExercicesI

Consultation des objets Faire un Listing des DEPTs tris par nom Pour un DEPT donn, lister tous les EMPLOYEs qui y travaillent

I

PL/SQL et Procdures stockes Implmenter les mthodes dcrites plus haut au niveau des types DEPT et EMPLOYE tester les mthodes

Page

229


15. ExercicesI

Les vues crer une table relationnelle (non objet) DEPT_r. La structure de cette table est celle du type DEPT_t sans les liens Insrer 3 ou 4 objets dans cette table Crer une vue objet DEPT_v masquant toutes les colonnes de cette table. Le type objet associ doit s' appeler old_DEPT_t. Y insrer 2 lignes Crer un trigger INSTEAD OF qui permet d' insrer travers la vue dans la table DEPT_r et DEPT_o Manipuler (insert, update, delete, select) via la vue

Page

230


oracle sql 3

Documents