SGBD Prof Part2

7/25/2019 SGBD Prof Part2

1/22


2/22

LCD:Dfinition(2)

GRANT et REVOKE sont utilises pour exercer un contrle

sur l'accs des donnes. COMMIT et ROLLBACK sontutilises pour prserver l'intgrit des donnes. Pour

utiliser une base de donnes, l'utilisateur doit passer par

une procdure de connexion. Lors de cette procdure, il

doit saisir un login et un mot de passe. Ce login permet

d'identifier chaque utilisateur et sert de repre pour lui

accorder (ou lui enlever) des droits de manipulation de labase.

5

LCD:GRANT(1)

La commande GRANT permet d'autoriser un accs aux

, .

Ainsi, il est possible, par exemple, d'autoriser la

modifier.

Syntaxe:

GRANT ALL PRIVILEGES | accs_spcifique

_

ON nom_table | nom_vue

nom_autoris | [WITH GRANT OPTION];

6

LCD:GRANT(2)

Avec la convention suivante :

d'accs(consultation,modification,suppression,)'

spcifiques(accs_spcifique).

'_ lorsdesaconnexionlabasededonnes(crparl'administrateur)

PUBLIC:toutlemondereoitleprivilgeaccord(accs s ci i ue)outousles rivil es(ALL_

PRIVILEGES).

WITHGRANTOPTION:celuiquireoitleprivilge

peutluimmel'accorderunautre.

7

LCD:GRANT(3)

Exemple:

GRANT ALL PRIVILEGES

ON VOITURE

ar n

WITH GRANT OPTION;

'

de donner Martin tous les droits sur la table VOITURE, il'

privilges.8


3/22

LCD:GRANT(3)

Les droits d'accs:

a gest on es ro ts acc s aux ta es est centra s e :

n'existe pas d'administrateur global attribuant des droits.'des droits sur celleci. Les principaux droits d'accs

slection(SELECT)

suppression(DELETE)

m se our

indexation(INDEX)

rfrencerlatabledansunecontrainte(REFERENCES)

9

LCD:GRANT(4)

Il peut ensuite passer ses droits slectivement d'autres.

tre pass avec le droit de le transmettre (WITH GRANTOPTION) ou non. L'ensemble des droits d'accs (ALL

PRIVILEGES) inclut les droits d'administration(changement de schma et destruction de la relation).

GRANT SELECT, UPDATE

ON VOITURE ACHAT

TO Smith;

consultation et de mise jour de la table VOITURE et de' .

10

LCD:GRANT(5)

GRANT ALL PRIVILEGES (Immatriculation, Prix)

TO Smith, Vandenbrouck, Dubois;

'd'accs aux utilisateurs Smith, Vandenbrouck et Duboisuniquement sur les colonnes Immatriculation et Prix de lata e .

GRANT INSERT

ON PERSONNE

TO PUBLIC;

Cette commande attribue le droit d'insrer de nouveauxenregistrements dans la table PERSONNE tous ceux qui,',

connecter sur la BD.

11

LCD:REVOKE(1)

La commande REVOKE permet de retirer l'accs, c'est la

.

Syntaxe:

_

ON nom_table | nom_vue

FROM nom_utilisateur | PUBLIC;

12


4/22

LCD:REVOKE(2)

Exemples :

,

ON VOITURE, ACHATFROM Smith;

Cette commande supprime les droits de consultation et demise jour de la tableVOITURE et de la table ACHAT qui avaitt accords l'utilisateur Smith.

REVOKE ALL PRIVILEGES

ON VOITUREFROM Martin;

Cette commande retire tous les rivil es accords sur latableVOITURE Martin.

13

LCD:Lecontrledintgrit

Les commandes COMMIT et ROLLBACK constituent des

'

base. Des systmes multiutilisateurs existants emploient des

contrles supplmentaires comme par exemple la

commande LOCK pour empcher les valeurs de changer

pendant qu'un utilisateur examine ou travaille sur ces

valeurs.

14

LCD:COMMIT(1)

La commande COMMIT permet l'utilisateur de fixer le

base de donnes. Dans ce cadre, on utilise le concept de

. La transaction est une suite d'oprations telle que

chaque opration de cette suite est ncessaire pour

atteindre un rsultat unitaire.

C'est la raison pour laquelle SQL propose l'utilisateur

de nenre istrer les modifications dans la base u'au

moment o la transaction est acheve grce lacommandeCOMMIT.

15

LCD:COMMIT(2)

Une transaction (ou une partie de transaction) qui n'a'

que pour l'utilisateur qui l'introduit. Elle n'affecte pas labase tant ue l'instruction n'est as excute.

Avant l'excution de l'instruction COMMIT, il est possiblede restaurer la base ar ROLLBACK c'estdired'liminer les modifications rcentes. Aprsl'enregistrement dfinitif d'une transaction par COMMIT,il n'est plus possible de restaurer l'tat antrieur parROLLBACK. S'il apparat aprs coup qu'une transactiondoive tre modifie ou corrige, on ne pourra effecteurcette modification qu'au moyen d'une autre instruction

.

16


5/22

LCD:ROLLBACK

La commande ROLLBACK permet l'utilisateur de ne pasvalider les dernires modifications en cours dans la base dedonnes.

Par exem le si au cours du droulement d'une transactionl'utilisateur fait une erreur ou si, pour une certaine raison,une transaction ne peut pas tre acheve, l'utilisateur peutsupprimer les modifications afin d'viter des incohrencesdans la base grce la commande ROLLBACK. Cette

validation.

' ',base peut tre prserve par une option ROLLBACKautomatique qui limine les transactions inacheves etempche donc qu'elles soient introduites dans la base.

17

LCD:SAVEPOINT

Une transaction tant une squence de squence de

, ,

il peut tre ncessaire de pouvoir revenir en arrire, .

Pour cela, il suffit de raliser des points de sauvegarde

grce la commande SAVEPOINT lintrieur

dune transaction pour situer un point ventuel de retour

vers ltat de la base de donnes. Le retour en arrire

seffectue grce la commande ROLLBACK WORK TOSAVEPOINT .

18

LCD:Terminaisond'unetransaction

19

Vueset

squences


6/22

Vues:Dfinition(1)

Dfinition :

Une vue est une relation virtuelle au sens o ses

instances n'existent pas physiquement mais sontcalcules chaque invocation de la vue. Une vue est

dfinie par une requte qui utilise des relations ou des

vues existantes.

S ntaxe :

CREATE VIEW nom_de_la_vue

AS ;

21

Vues:Exemples(1)

Exemples :

_

AS SELECT *FROM VOITURE

WHERE Prix > 15 000;

La vue VOITURES_CHERES a le mme schma que la relationVOITURE.

SELECT *FROM VOITURES_CHERES;

Prix est suprieur 15 000.

22

Vues:Exemples(2)

CREATE VIEW VOITURES_PUISSANTES (immat_voiture,

_

SELECT Immatriulation, Marque

FROM VOITURE

WHERE Puissance > 6;

La vue Voiturespuissantes comporte les attributs Immatriculation et

Marque renomms en immat_voiture et marque_voiture. Elle

permet d'accder toutes les voitures dont la puissance est

suprieure 6 chevaux.

En interrogation, une vue est utilise comme toute autre relation. Laseule diffrence rside dans le fait que ses tuples ne sont pas stocks

ma s s son e r su a e va ua on e a requ e e n on

23

Vues:Exemples(3)

En mise jour, toute modification des relations ayant servi la

'

valuation de la requte de dfinition de la vue). Par contre, la

mise jour d'une base de donnes " travers" une vue n'a pas

trouv de solution gnrale : le problme vient du fait qu'il estparfois impossible de rpercuter la mise jour de la vue sur les

re a ons sur esque es e e es n e. epen an , quan es

contraintes logiques ne viennent pas l'empcher la mise jour au

travers des vues est ossible.

A titre d'exemple, si une vue comporte une colonne obtenue par

' ,

supposerait que l'on soit capable de mettre jour les tuplesconcerns par la fonction d'agrgation, tant donne la valeur

introduite, ce qui est logiquement impossible.

24


7/22

Vues:Supression

Enfin la suppression d'une vue est faite explicitement par DROP

25

Squences:Dfinition

Dfinir une squence quivaut dfinir une suite de nombres

'.

de paramtres. L'utilisation d'une squence permet donc d'avoir

disposition une suite de valeurs. Ceci peut permettre de :

gnrerdesclsuniquesdansdestables

avoiruncompteurtitreinformatif,quel'onincrmentequand

onveut

etc...

26

Squences:Cration(1)

Syntaxe:

_ _ _

[START WITH valeur_initiale]

[INCREMENT BY ] incrment[NOMAXVALUE NOMINVALUE | MAXVALUE maximum | MINVALUEm n mum

[NOCYCLE | CYCLE]

[CACHE nombre_de_valeurs];

START

WITH

permet

de

fixer

la

valeur

initiale

de

la

squence.

INCREMENTBYpermetdefixerlepasdincrmentation.Sice uc estpos t , as quenceestcro ssante,s none e cro t.

27

Squences:Cration(2)

NOMAXVALUEetNOMMINVALUEpermetdenepasfixerde

.

dpasser(pourunesquenceascendante),onutilise

MAXVALUE.Silondsirefixerunplancher(pourunesquence

descendante),on

utilise

MINVALUE.

LoptionCYCLEpermetunefoislalimiteducompteuratteint,

dereprendrelecomptagelavaleurMINVALUE(pourune

squenceascendante)ouMAXVALUE(pourunesuite

.

limiteducompteuratteint,ilsuffitdutiliserloptionNOCYCLE.

_ _

dansla

mmoire

cache

afin

doptimiser

lutilisation

des

squencesetavoiruneffetsignificatifsurlesperformances,

surtoutlorsquenombre_de_valeurs estlev.

28


8/22

Squences:Exemples(1)

_

INCREMENT BY 3;

SEQUENCE_VOITURE commenant la valeur 5 avec un pas

dincrmentation de 3. La squence obtenue est alors : 5, 8, 11, 14,

17, 20,

29


_

START WITH 5

INCREMENT BY 3

Cette commande SQL permet de crer une squence


, .obtenue est alors : 5, 8, 11, 14, 17, 20, 23, 26, 29.

30


CREATE SEQUENCE SEQUENCE_VOITURE

START WITH 1

MAXVALUE 10

MINVALUE10

CYCLE;

Cette commande SQL permet de crer une squence


dincrmentation de 1, avec pour valeur maximale 10 et pour valeur

. ,

: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,10,9,8,7,6, 31

Squences:Interrogation

L'interrogation d'une squence se fait par l'utilisation des "pseudo

" .

cela se manipule un peu comme une colonne de table, mais ce

n'est pas une colonne de table.

Lapseudo

colonne

CURRVAL

retourne

la

valeur

courante

de

la

squence.

LapseudocolonneNEXTVALincrmentelasquenceet

retournelanouvellevaleur.

SELECT SEQUENCE_VOITURE.NEXTVAL FROM DUAL;

SELECT SEQUENCE VOITURE.CURRVAL FROM DUAL_

Lors de la premire utilisation dun squence, il faut utiliserNEXTVAL our linitialiser. Ensuite CURRVAL ermet dobtenir la

valeur courante de la squence.

32


9/22

Squences:Modification

Syntaxe:

_ _ _

[INCREMENT BY incrment][NOMAXVALUE NOMINVALUE | MAXVALUE maximum | MINVALUEminimum]

[CACHE nombre_de_valeurs];

Exemple :

ALTER SEQUENCE SEQUENCE_VOITUREINCREMENT BY 3;

Cette commande S L ermet de modifier le as dincrmentation(valeur : 3) de la squenceSEQUENCE_VOITURE.

33

Squences:Colonneautoincrmente

Le principe est le suivant :

crerunesquencequipermettradegnrerdesvaleursentiresuniques

crerunTRIGGERquisedclencherachaqueINSERT,pouralimenter

le

champ

voulu

avec

une

valeur

unique.

CREATE TRIGGER PK_VOITURE

BEFORE INSERT ON VOITURE FOR EACH ROW

BEGIN

SELECT SEQUENCE_VOITURE.NEXTVAL INTO :VOITURE.cle_primaire FROMDUAL;

END;

et exemp e ne g re pas e contr e un c t e a va eur que on vainsrer, mais si le champ n'est aliment QUE par l'utilisation de lasquence qui lui est ddie, et si cette squence n'est pas paramtre

' ',surgisse...

34

PL/SQL:Introduction(1)

PL/SQL est un langage qui intgre SQL et permet de

. ,

PL/SQL on aura notre disposition un vrai langage de

que le SQL. Les 2 langages sont minemment

.

35


Les principaux avantages / inconvnients sont les

nedispensepasdeconnatreleSQL;

letraficrseau;

' etstructurdynamique(%TYPE,%ROWTYPE,);

,gestiondescasparticuliersetdeserreurs(traitement

unparamtrage

et

la

cration

d'ordres

SQL

.

36


10/22


Le PL/SQL peut tre utilis sous 3 formes :

un oc eco e,ex cut commeunecomman eSQL,

viaun

interprteur

standard

unfichierdecommandePL/SQL

un ro rammestock( rocdure,fonction, acka e

outrigger)

37

PL/SQL:OrdresSQLsupports

Les instructions du langage de manipulation de donnes

transaction, savoir :INSERT,UPDATE,DELETE,SELECT;

COMMIT,ROLLBACK,SAVEPOINT.

38

PL/SQL:Blocsetsections(1)

Les blocs de code s'appellent galement des blocs' '.

ou un 'DECLARE'. Ils sont composs de 1 3 sections :

La section 'EXCEPTION' uand elle est rsente estincluse dans la section et NON PAS la suite de celle ci.

NULL;

;

39


Le bloc prcdent est le plus petit bloc PL/SQL au

.

moins une instruction

Syntaxe de bloc en PL/SQL (avec des exceptions)DECLAREmes dclarations de variables

BEGINdbut de la section excutable

mes or res e

EXCEPTION

mon traitement des exceptions

END;la in du bloc excutable

40


11/22


Afin de pouvoir utiliser une exception, il faut tout dabord dclarer unevariable dexception dans la partie DECLARE (ex : DECLARE erreurEXCEPTION;).

Ensuite, dans la partie entre le BEGIN et le END, pour appeler uneexcep on e s opper e oc , on u se a comman enom_exception (ex : RAISE erreur;). Cette commande stoppe le blocPL/SQL et va dans la partie EXCEPTION.

Dans cette partie, pour afficher un message derreur, on utilise lacommande suivante :

WHEN nom_exception THEN

RAISE_APPLICATION_ERROR ( numero_erreur , message );

numero_erreur :repr sente enum ro e erreuruti isateur.Cenumrodoittrecomprisentre20000et20999.

messa e:chanedecaractresdunelon ueurmaximalede2048octetsquicontientlemessageassocilerreur.

41


Les exceptions prdfinies sont :

_ _

dunSELECT.

_ _

dunSELECT.

VALUE ERROR : cas o lon a une erreur de valeur._

ZERO_DIVIDE:casdunedivisionparzro.

INVALIDE NUMBER : cas dun nombre invalide._

42

PL/SQL:Procduresstockes(1)

Une procdure est simplement un programme PL/SQL nomm,

.

Syntaxe de procdure en PL/SQL (avec des exceptions)

CREATE [OR REPLACE] PROCEDURE nom_de_procdure ( par1 type1,par2 type2, )

IS

BEGIN

mes ordres SQL et PL/SQL


END;

43

PL/SQL:Procduresstockes(2)

Ce code est une commande SQL, qui cre la procdure PL/SQL, et donccompile et stocke dans la base le bloc PL/SQL compris entre le BEGIN et

e , en e r renan par nom_ e_proc ure . pour ex cu er ce eprocdure de manire autonome, on utilise la commande : EXECUTEnom_de_procdure. Les paramtres dentre doivent tre dclars (nom

.

,VARCHAR2)

IS

BEGIN

INSERT INTO CLIENTS numclient nomclient villeclient

VALUES (SEQUENCE_NUM_CLIENT.NEXTVAL, nom, ville);COMMIT

END;

44


12/22

PL/SQL:Fonctionsstockes(1)

Une fonction est une procdure retournant une valeur.

nom_ e_ onct on par type ,

par2 type2, )

RETURN type_de_sortie IS variable_de_sortie type_de_sortie

IS

BEGIN

EXCEPTION


END;

45


Ce code est une commande SQL, qui cre la fonction PL/SQL, et donccompile et stocke dans la base le bloc PL/SQL compris entre le BEGIN et

e , en e r renan par nom_ e_ onc on. a va eur re ourn e ensortie correspond la variable_de_sortie dont le type est type_de_sortie.

Les paramtres dentre doivent tre dclars (nom et type).

CREATE OR REPLACE FUNCTION solde (numero NUMBER)

RETURN REAL IS le_solde REAL

BEGIN

SELECT solde INTO le_solde

FROM clients

WHERE numc ient = numero;

RETURN le_solde;

END;

46


Cette comman e permet e cr er une onct on nomm e so e,

dont le paramtre dentre est le numro de client. La fonction

, _ ,

solde du compte du client identifi par numero. Pour appeler cette

fonction et utiliser son rsultat au sein dun ordre SQL, il suffit

dexcuter la commande suivante : SELECT solde(1000) FROM

DUAL.

47

PL/SQL:Triggers(1)

Les dclencheurs (Triggers) sont des procdures stockes

'

lorsqu'une action spcifique se produit sur la table

'.s'effectue subsquemment une instruction de

,

ou UPDATE. Il existe donc trois types de dclencheurs :

, .

48


13/22

PL/SQL:Triggers(2)

Une table peut comporter plusieurs dclencheurs d'un

,

diffrent. Cependant, un dclencheur donn ne peut'

s'appliquant la fois, l'insertion, la mise jour et la

' .

Une table ne peut possder qu'un seul dclencheur

' .

49

PL/SQL:Triggers(3)

Les dclencheurs se produisent soit aprs (AFTER), soit avant (BEFORE)une instruction DML:

Un dclencheur sur INSERTs'excute chaque opration d'insertionlance ar l'utilisateur ou ar un ro ramme. Lors d'une insertionl'enregistrement est insr la fois dans la table cible est dans unetable temporaire dnomme inserted. Une telle table peut

ermettre de vrifier la cohrence des enre istrements.

Un dclencheur sur DELETE s'excute chaque opration desuppression lance par l'utilisateur ou un programme. Lors d'unesu ression, l'enre istrement est su rim h si uement de la tablecible et l'insre dans une table temporaire dnomme deleted. Celapeut permettre de rcuprer l'enregistrement supprim.

Un dclencheur surUPDATEs'excute cha ue o ration de mise our lance par l'utilisateur ou par un programme. Lors d'une mise jour, l'ancien enregistrement est supprim et insr dans la tabletemporaire deleted, tandis que le nouveau est insr la fois dans latable cible et dans la table inserted.

50

PL/SQL:Triggers(4) La suppression des dclencheurs s'effectue par l'intermdiaire de l'instruction

DROP.

DROP TRIGGER nom_trigger1 [,, nom_triggerN ];

La modification des dclencheurs s'effectue par l'intermdiaire de l'instructionALTER. La s ntaxe com lte de la commande ALTER TRIGGER est en fait

identique celle de CREATE TRIGGER.ALTER TRIGGER nom_trigger

ON nom_ta e

FOR INSERT | UPDATE | DELETE

_

Tous les dclencheurs (ALL) ou certains peuvent tre activs (ENABLE) oudsactivs (DISABLE) au moyen de l'instruction ALTER TABLE.

ALTER TABLE

nom_table { ENABLE | DISABLE } TRIGGER

{ ALL | nom_trigger1 [,,nom_triggerN]};

51

PL/SQL:Triggers(5)

52


14/22

PL/SQL:Triggers(6)

Notation (les lments entre [ ] sont optionnels) :

' .

LaclauseBEFOREindiquequeledclencheurdoittrelancavant

l'excutionde

l'vnement.

l'excutiondel'vnement.

LesinstructionsINSERTetDELETEindiquentaudclencheurdes'excuter' ' .

LaclauseUPDATEOFindiquequeledclencheurdoittrelanclorsdechaquemisejourd'unedescolonnesspcifies.Sielleestomise,n'im orte uellecolonnedelatablemodifie rovo ueledclenchementduTrigger.

LaclauseONdsignelenomdelatableassocisonschmapourlequelle

dclencheur

a

t

spcifiquement

cr.

LaclauseFOREACHROWPrcisesilaprocduredudclencheurdoittrelancepourchaqueligneaffecteparl'vnementousimplementpourchaqueinstructionSQL. FOREACHSTATEMENT estlavaleurpardfaut..

53

PL/SQL:Triggers(7)

Cette commande SQL permet de crer deux tables puis cre un

dclencheur qui insre un champ Log lintrieur de DELETE_LOG,

pu s c aque gne suppr m e ans a ta e _ .

54

PL/SQL:Triggers(8)

Cette commande SQL permet de crer deux tables TABLE_1 et TABLE_2 puis _

lorsquune opration dinsertion sest accomplie dans Table_2. Ledclencheur vrifie si le nouvel enregistrement possde un premiercom osant infrieur ou al 10 et si c'est le cas inverse lesenregistrements l'intrieur de TABLE_2. Les variables spciales NEW et

OLD sont disponibles pour se rfrer respectivement des nouveaux oud'anciens enregistrements. Les deux points (:) prcdent NEW et OLD dansVALUES sont dans ce cas obligatoires, par contre dans la clauseconditionnelle WHERE , ils doivent tre omis

55

PL/SQL:Triggers(9)

56


15/22

PL/SQL:Curseurs(1)

Les curseurs sont des pointeurs sur une zone mmoire.

curseurs implicites et explicites. Oracle ouvre toujours un

curseur im licite our traiter une instruction SQL, celuici

ne se rapporte qu la dernire instruction SQL excuteet il se nomme SQL . Le curseur contient des attributs(%NOTFOUND, %FOUND, %ROWCOUNT) qui fournissentdes informations sur lexcution des instructions INSERT,

, , . n curseur mp c e pourune instruction SELECT INTO ne peut grer quune seule

.dune requte multilignes dans un tampon mmoire etlibre les li nes les unes a rs les autres lors dutraitement.

57

PL/SQL:Curseurs(2)

Le curseur se dfinit dans la partie dclarative du bloc PL/SQL (larequte nest pas excute ce momentl). Dans cette

c arat on, est poss e e onner une c ause FOR UPDATE OFnom_colonne(s) qui permet de verrouiller les lignes slectionnes

(aucun autre utilisateur ne peut mettre jour tant que le verrounest pas retir). La commande OPEN nom_curseur excute larequte et place le curseur en mmoire, elle ne retourne aucunrsultat. Linstruction FETCH nom_curseur INTO variable extrait laligne courante du curseur, la place dans une variable et faitavancer le curseur la ligne suivante. Pour parcourir toutes leslignes du curseur, il faut utiliser une boucle LOOP. La clauseCURRENT OF nom_curseur est utilise dans le WHERE dunecommande UPDATE pour modifier la ligne courante (si un FOR

UPDATE a t utilis ralablement . Larrt de la boucle estobtenu grce nom_curseur%NOTFOUND qui retourne false silne reste plus de lignes. Pour librer lespace mmoire, il fautfermer ex licitement le curseur en utilisant CLOSE nom curseur._

58

PL/SQL:Curseurs(3)

Un curseur peut accepter des paramtres en entre, ils servent

dans un WHERE pour limiter la requte. Les paramtres ont un

type associ qui ne peut pas avoir dindication de longueur. Ils sont

passs lors de la commande OPEN.

59

PL/SQL:Curseurs(4)

Syntaxe de la commande DECLARE CURSOR en SQL

Il est galement possible de passer des paramtres un curseur.

,

curseur en les dfinissant par nomtable.attribut%TYPE.

60


16/22

PL/SQL:Curseurs(5)

salaire dun employ. La structure de slection permet de rcuprer la liste desemploys (numro, nom, mtier) et de leur salaire. Dans la structure BEGINEND,on retrouve lopration douverture du curseur suivi de la commande FETCHINTO

qu perme ex ra re es ren es gnes es up es renvoy par a s ruc ure eslection afin de les sauvegarder dans les variables employe et salaire dfinies dansla dclaration du curseur. L'attribut Oracle %NOTFOUND retourne FALSE si ladernire instruction FETCH renvoie un enregistrement ou TRUE en cas d'chec. Suite la fin de son utilisation, le curseur peut tre ferm afin de ne plus consommer deressources .

61

PL/SQL:Langage(1)

Dclaration, initialisation des variables

Identificateurs Oracle :

30caractres

au

plus

commenceparunelettre

, ,_,

passensiblelacasse

Portehabituelledeslangagesblocs

Doivent

tre

dclares

avant

dtre

utilises

62

PL/SQL:Langage(2)

Dclaration, initialisation des variables

c ara on e n a sa on

Nom_variable type_variable := valeur;

InitialisationNom variable := valeur;_

Dclaration multiple interdite

age integer;

nomvarc ar ;

dateNaissance date;

o oo ean := rue;

63

PL/SQL:Langage(3)

Initialisation de variables

Plusieurs faons de donner une valeur une variable

Oprateurdaffectation

n :=

rec ve e arequ e

Exemples :

dateNaissance :=

to date 10 10 2004DD MM YYYY _

SELECTnom

INTO

v_nom FROM

emp WHERE

matr =

64


17/22

PL/SQL:Langage(4)

SELECT INTO

SELECT expr1,expr2, INTO var1, var2,

Met des valeurs de la BD dans une ou lusieurs variablesvar1, var2,

e se ec ne o re ournerqu une seu e gne

Avec Oracle il nest pas possible dinclure un select sans

into dans une procdure

,

les curseurs.

65

PL/SQL:Langage(5)

Le type de variables

VARCHAR2

Longueurmaximale

:32767

octets

Syntaxe:Nom_variable VARCHAR2(30);

:=toto;

NUMBER(long,dec)

Long

:

longueur

maximale

Dec :longueurdelapartiedcimale

_

number(5,2)=142.12;66

PL/SQL:Langage(6)


VARCHAR2

Longueurmaximale:32767octets

Syntaxe:Nom_variable VARCHAR2(30);

:=toto;

NUMBER(long,dec)

Long:longueurmaximale

Dec :longueur

de

la

partie

dcimale

_

number(5,2)=142.12;67

PL/SQL:Langage(7)


DATE

PardfautDDMONYY(18DEC02)

FonctionTO_DATE

_ _ ,

MONYYYY);start_date :=to_date(29SEP

,

BOOLEAN

TRUE

FALSE

NULL68


18/22

PL/SQL:Langage(8)

Dclaration %TYPE et %ROWTYPE

On peut dclarer quune variable est du mme type

quune colonne dune table ou (ou quune autre variable)Exemple:v_nom emp.nom.%TYPE;

ligne dune table

xemp e:v_emp oye emp ;

dclarequelavariablev_employe contiendraune

lignede

la

table

emp

69

PL/SQL:Langage(9)

Exemple dutilisation

DECLARE

v_employe emp%ROWTYPE;v_nom emp.nom%TYPE;

BEGIN

SELECT * INTO v_employe FROM emp WHERE matr = 900;

=_ _ .

v_employe.dept := 20;

INSERT into emp VALUES v_employe;

END;

70

PL/SQL:Langage(10)

Test conditionnel

IF v_date > 11-APR-03 THEN

v_salaire := v_salaire * 1.15;END IF;

IFTHENELSE

_ - -

v_salaire := v_salaire * 1.15;


END IF;

71

PL/SQL:Langage(11)

Test conditionnel

IFTHENELSIF

IF v_nom = PAKER THEN


= _


END IF;

72


19/22

PL/SQL:Langage(12)

Test conditionnel

CASE slecteur

WHEN expression2 THEN rsultat2

r su a

END;

xemp e

val := CASE city

WHEN LOS ANGELES THEN LAKERS

END;73

PL/SQL:Langage(13)

Les boucles

instructions excutables;


Obligation dutiliser la commande EXIT pour viter uneboucle infinie, facultativement quand une condition estvraie.

WHILE condition LOOP


END LOOP;

74

PL/SQL:Langage(14)

Les boucles

FOR variable IN debut..fin

LOOP

instructions;

END LOOP;

dbut, debut+1, debut+2, , jusqu la valeur fin.

n pourra ga emen u ser un curseur ans a c ause .

75

PL/SQL:Langage(15)

Affichage

Activer le retour cran sous sqlplus

Affichage

dbms_output.put_line(chane);

ser pour a reuneconca na on

76


20/22

PL/SQL:Langage(16)

Exemple

i number(2);

BEGIN

FOR i IN 1..5 LOOP

dbms_output.put_line(Nombre : || i );

END LOOP;END;

77

PL/SQL:Langage(17)

Exemple

DECLARE

nb integer;

BEGIN

delete from emp where matr in (600, 610);

n := sq rowcoun ;

dbms_output.put_line('nb = ' || nb);

END;

78

PL/SQL:Langage(18)

Exemple

DECLARE

compteur number(3);

BEGIN

select count(*) nto compteur from cl ents;

FOR i IN 1..compteur LOOP

dbms_output.put_line('Nombre : ' || i );

END LOOP;

END;

79

PL/SQL:Langage(19)

Exemple

DECLARE

compteur number(3);

BEGIN

select count(*) nto compteur from cl ents;

FOR i IN 1..compteur LOOP

dbms_output.put_line('Nombre : ' || i );

END LOOP;

END;

80


21/22

PL/SQL:Applications(1)

Soit le schma suivant:

v um, v om, apac e, oca sa on

PILOTE PlNum PlNom PlPrenom Ville Salaire

VOL (VolNum, #PlNum, #AvNum, VilleDep, VilleArr,

eure ep, eure rr

81


Exercice 1:

Dans un bloc PL/SQL anonyme, dclarer un curseur

de vol, numro davion, heure de dpart et heure

n 1,2,4 ou 8. Pour chaque vol lu par le curseur,

ca cu er e temps e vo .

82


Solution:

DECLARE

CURSOR avamodif IS

o um, v um, eure ep, eure rr

AvNum IN (1,2,4,8);

volmod avamodif%ROWTYPE;

Tvol REAL;

BEGIN

FOR volmod IN avamodif LOOP

Tvol:= volmod.HeureArr volmod.HeureDep;

DBMS_OUTPUT.put_line ( Le vol n || volmod.VolNum || a

dur || Tvol);

END

83


Exercice 2:

On dsire mettre en place un paquetage logiciel

Afficher le contenu de la table.

Ajouter un Pilote

uppr mer un ote

Compter les Pilotes

84


22/22


Solution:

CREATE OR REPLACE PACKAGE Pilotes AS

CURSOR les_pilotes RETURN PilNuplet IS SELECT FROM PILOTE;

PROCEDURE afficher IS

t PilNuplet;

BEGIN

FOR t IN les pilotes LOOP_

DBMS_OUTPUT.put_line (t.num || || t.prenom || || t. nom);

END;

, , ,

BEGIN85

Documents

SGBD Prof Part2