partie 2 - le langage procedural d'oracle - Free

PARTIE 2 - LE LANGAGE PROCEDURAL D'ORACLE :
LE LANGAGE PL/SQL

I - LES CURSEURS EN PL/SQL

Dès l'instant où on exécute une instruction SQL, il y a création d'un
curseur implicite : c'est une zone de travail qui contient des informations
permettant l'exécution d'un ordre SQL.

Un curseur explicite est un curseur décrit et généré au niveau de la
procédure ainsi pour traiter une requête qui retourne plusieurs lignes,
l'utilisateur doit définir un curseur qui lui permet d'extraire la totalité
des lignes sélectionnées.

L'utilisation d'un curseur nécessite 4 étapes :
1. Déclaration du curseur : Section DECLARE
2. Ouverture du curseur : Section BEGIN
3. Traitement des lignes : Section BEGIN
4. Fermeture du curseur : Section BEGIN OU EXCEPTION

1. La déclaration d'un curseur


La déclaration du curseur permet de stocker l'ordre Select dans le
curseur.
Le curseur se définit dans la partie Declare d'un bloc PL/Sql.


Cursor nomcurseur IS Requete_SELECT ;


Declare
Cursor DEPT10 is
select ename, sal from emp where deptno=10 order by sal ;
Begin
... ...;
End ;


2. L'ouverture du curseur


L'ouverture du curseur réalise :
1. l'allocation mémoire du curseur
1. l'analyse sémantique et syntaxique de l'ordre
1. le positionnement de verrous éventuels (si select for
update...)

C'est seulement à l'ouverture du curseur que la requête SQL s'éxécute.
L'ouverture du curseur se fait dans la section Begin du Bloc.


OPEN nomcurseur ;

Declare
Cursor DEPT10 is
select ename, sal from emp where deptno=10 order by sal ;
Begin
...Open DEPT10;
.....
End ;
3. Traitement des lignes

Après l'exécution du Select les lignes ramenées sont traitées une par
une, la valeur de chaque colonne du Select doit être stockée dans une
variable réceptrice définie dans la partie Declare du bloc. Le fetch
ramène une seule ligne à la fois, pour traiter n lignes il faut une
boucle.

FETCH nomcurseur INTO liste_variables ou Nom_enregistrement;


create table resultat (nom1 char(10), sal1 number(7,2))
/
Declare -- programme plsql_ex5.sql
Cursor DEPT10 is select ename, sal from emp where deptno=20
order by sal ;
-- variables réceptrices
nom emp.ename%TYPE; -- Variable locale de même type que le champ
ename
salaire emp.sal%TYPE;
Begin
Open DEPT10;
Fetch DEPT10 into nom, salaire ; -- Lecture 1° tuple
WHILE DEPT10%found loop -- Tant qu'on trouve une ligne
If salaire > 2500 then
insert into resultat values (nom,salaire);
end if;
Fetch DEPT10 into nom,salaire ; -- Lecture tuple suivant
end loop;
Close DEPT10;
End ;
/
select * from resultat
/
drop table resultat
/
SQL> @ ../gautier/plsql_ex5
Table créée.
Procédure PL/SQL terminée avec succès.


NOM1 SAL1
---------- ---------
JONES 2975
SCOTT 3000
FORD 3000
Table supprimée.

|Attributs : |Explication : |
| Nomcurseur%Found |Vrai si exécution correcte de l'ordre|
| |SQL |
| Nomcurseur%Notfound |Vrai si exécution incorrecte de |
| |l'ordre SQL |
| Nomcurseur%Isopen |Vrai si curseur ouvert |
| Nomcurseur%Rowcount |Donne la nième ligne traitée |


Les attributs d'un curseur sont des indicateurs sur l'état d'un
curseur. Ils nous fournissent des informations quant à l'exécution de
l'ordre. Elles sont conservées par Pl/Sql après l'exécution du
curseur.
Ces attributs permettent de tester directement le résultat de
l'exécution. Tous les attributs ont un nom.


4. La fermeture du curseur


Après le traitement des lignes, l'étape de fermeture permet d'effectuer
la libération de la place mémoire.


CLOSE nomcurseur Close dept10 ;



4. Complément : Utiliser une variable de type enregistrement



1° solution : Nom-de-variable nom_table%rowtype;


Correspond à la déclaration d'une variable de même type que
l'enregistrement (= le tuple = la ligne) de la table.


DECLARE
LigFleur FLEURS%ROWTYPE ;
X number(10,3) ;
BEGIN
SELECT * INTO LigFleur where nofleur=10; -- 1 seule ligne
X := LigFleur.Prx *1.1 ; -- On peut accéder à chaque
champ de l'enregistrement


Dans un contexte curseur (résultat du select >1 tuple), l'attribut
rowtype permet la déclaration implicite d'une structure dont les
éléments sont d'un type identique aux colonnes ramenées par le
curseur.

Dans la partie déclarative du bloc. Cursor nomcurseur is
ordre_select ;
nom_structure nomcurseur%ROWTYPE;


Les éléments de la structure sont identifiés par :
nom_structure.nomcolonne


La structure est renseignée par le Fetch : Fetch nomcurseur into
nom_structure;


Au préalable afin de bien tester le programme ci-dessous, sous SQL*PLUS
SQL> update pilote
set comm = null where nopilot='1243'
create table resultat (nom1 char(35), sal1 number(8,2))
/
-- Programme Plsql_ex6.sql
Declare
Cursor C1 is select * from pilote where adresse='Paris';
-- variable réceptrice
unpilot pilote%rowtype;


Begin
Open C1;


Fetch c1 into unpilot ; -- Lecture 1° tuple
WHILE C1%found
loop
If unpilot.comm is not null then
insert into resultat values (unpilot.nompilot,
unpilot.salpilot);
end if;
Fetch c1 into unpilot ; -- Lecture tuple suivant
end loop;
Close c1;
End ;
/
select * from resultat
/
drop table resultat
/




2° solution : Déclarer un type enregistrement


TYPE nom_enregistrement IS RECORD
( Nom-de-champ1 type,
Nom-de-champ2 type,
.....) ;
-- Déclaration d'une variable de ce type
Une-Variable nom_enregistrement ;


create table resultat(nom1 char(35), sal1 number(8,2))
/
-- Programme PLSQL_EX7.sql --


DECLARE
Type EngPilote IS Record
(nom_pilote pilote.nompilot%type,
revenu_pilote pilote.salpilot%type);
Unpilot EngPilote;


BEGIN
-- Exemple d'affectation
Unpilot.nom_pilote := 'DUPUY';


-- ou Recherche d'un pilote
-- 1 seule ligne pas de curseur
SELECT nompilot,salpilot INTO Unpilot from pilote
where nopilot = '7100';
if unpilot.nom_pilote is not NULL then
Insert into resultat
values(unpilot.nom_pilote, unpilot.revenu_pilote);
end if ;
END;
/
select * from resultat
/
drop table resultat
5. Exercices d'application

1. Ecrire tous les programmes donnés dans cette partie et les tester.
Adapter les tuples des tables afin de passer en revue les différentes
possibilités


2. Ecrire le programme PLSQL_EX8.sql qui permet de retrouver tous les
pilotes de Paris ayant une commission non null en déclarant le type
d'enregistrement avec RECORD.


3. Vous avez ci-dessous un programme PL/SQL et les tables d'origine.
Etudiez ce programme, expliquez succinctement son but et donnez les
lignes affichées à la fin de son exécution ainsi que le contenu des
deux tables d'origine.

















































































II - MODIFICATION DE DONNEES

Les modifications de données s'effectuent normalement par les instructions
SQL : INSERT, UPDATE et DELETE comme nous avons pu le remarquer dans le
programme ci-dessus d'exercice : majliv.sql.

PL/SQl permet la possibilité d'utiliser l'option CURRENT OF nom_curseur
dans la clause WHERE des instructions UPDATE et DELETE. Cette option permet
de modifier ou de supprimer la ligne distribuée par la commande FETCH. Pour
utiliser cette option, il faut ajouter la clause FOR UPDATE à la fin de la
définition du curseur.

-- Programme PLSQL_EX9.sql --


DECLARE
Cursor C1 is
select ename, sal from emp
for update of sal;
resC1 c1%rowtype;
BEGIN
Open C1;
Fetch C1 into resC1;
While C1%found Loop
If resC1.sal > 1500 then
update emp
set sal = sal * 1.1
where current of c1;
end if;
Fetch C1 into resC1;
end loop;
close C1 ;
END;
/
Explications :

( ... For update of nom_colonne )
Il faut se réserver la ligne lors de la déclaration du curseur par le
positionnement d'un verrou d'intention .
( ... where current of c1 ; )
Il faut spécifier que l'on veut traiter la ligne courante au Fetch par
la clause :

Exercice : Au préalable sous SQL*Plus ajouter une colonne BUDGET de type
number à la table DEPT. Dans le programme SQL « Exo4_plsql.sql » mettre à
jour cette colonne avec la somme totale des salaires des employés du
département.

Résultat à obtenir :
|DEPTNO |DNAME |LOC |BUDGET |
|10 |ACCOU