Download - SGBD Prof Part2
-
7/25/2019 SGBD Prof Part2
1/22
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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
Squences:Exemples(2)
_
START WITH 5
INCREMENT BY 3
Cette commande SQL permet de crer une squence
SEQUENCE_VOITURE commenant la valeur 5 avec un pas
, .obtenue est alors : 5, 8, 11, 14, 17, 20, 23, 26, 29.
30
Squences:Exemples(3)
CREATE SEQUENCE SEQUENCE_VOITURE
START WITH 1
MAXVALUE 10
MINVALUE10
CYCLE;
Cette commande SQL permet de crer une squence
SEQUENCE_VOITURE commenant la valeur 1 avec un pas
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
-
7/25/2019 SGBD Prof Part2
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
PL/SQL:Introduction(2)
Les principaux avantages / inconvnients sont les
nedispensepasdeconnatreleSQL;
letraficrseau;
' etstructurdynamique(%TYPE,%ROWTYPE,);
,gestiondescasparticuliersetdeserreurs(traitement
unparamtrage
et
la
cration
d'ordres
SQL
.
36
-
7/25/2019 SGBD Prof Part2
10/22
PL/SQL:Introduction(3)
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
PL/SQL:Blocsetsections(2)
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
-
7/25/2019 SGBD Prof Part2
11/22
PL/SQL:Blocsetsections(3)
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
PL/SQL:Blocsetsections(4)
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
mon traitement des exceptions
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
-
7/25/2019 SGBD Prof Part2
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
mon traitement des exceptions
END;
45
PL/SQL:Fonctionsstockes(2)
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
PL/SQL:Fonctionsstockes(3)
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
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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)
Le type de variables
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)
Le type de variables
DATE
PardfautDDMONYY(18DEC02)
FonctionTO_DATE
_ _ ,
MONYYYY);start_date :=to_date(29SEP
,
BOOLEAN
TRUE
FALSE
NULL68
-
7/25/2019 SGBD Prof Part2
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;
v_salaire := v_salaire * 1.05;
END IF;
71
PL/SQL:Langage(11)
Test conditionnel
IFTHENELSIF
IF v_nom = PAKER THEN
v_salaire := v_salaire * 1.15;
= _
v_salaire := v_salaire * 1.05;
END IF;
72
-
7/25/2019 SGBD Prof Part2
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;
instructions excutables;
Obligation dutiliser la commande EXIT pour viter uneboucle infinie, facultativement quand une condition estvraie.
WHILE condition LOOP
instructions excutables;
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
-
7/25/2019 SGBD Prof Part2
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
-
7/25/2019 SGBD Prof Part2
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
PL/SQL:Applications(2)
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
PL/SQL:Applications(3)
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
PL/SQL:Applications(4)
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
-
7/25/2019 SGBD Prof Part2
22/22
PL/SQL:Applications(5)
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