Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
PL / SQL
Les blocs anonymes
Ines BAKLOUTI
Ecole Sup´erieure Priv´ee d’Ing´enierie et de Technologies
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
Plan
1 Mod`eles de programmes en PL/SQL
2 Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
2/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
Introduction
Rappel: le language SQL
SQL: Structured Query Language
LID: un langage d’interrogation de la base (ordre SELECT)
LDD: un langage de d´efinition des donn´ees (ordres CREATE,
ALTER, DROP),
LMD: un langage de manipulation des donn´ees (ordres INSERT,
UPDATE, DELETE).
D´efinition: PL/SQL
Le langage PL/SQL est une extension proc´edurale du langage SQL
(propre `a oracle).
3/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
Plan
1 Mod`eles de programmes en PL/SQL
2 Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
4/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
Mod`eles de programmes en PL/SQL
Parmi les mod`eles de programme PL/SQL on trouve:
Les blocs anonymes,
Les sous programmes (proc´edures et fonctions),
Les proc´edures et les fonctions stock´ees,
Les triggers,
Les packages,
. . .
Les programmes ´ecrits en PL/SQL respectent tous une structure en
blocs pr´ed´etermin´es.
5/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Plan
1 Mod`eles de programmes en PL/SQL
2 Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
6/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Structure d’un bloc PL/SQL
7/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Structure d’un bloc PL/SQL
Exemple
DECLARE
v last name varchar(20);
BEGIN
Select last name into v last name from employees where
employee id=124;
END ;
/
Chaque instruction SQL ou PL/SQL doit se terminer par un point
virgule (;)
Le slash (/) pour ex´ecuter un bloc PL/SQL anonyme
8/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
La section d´eclaration
D´ebute par le mot DECLARE et permet la d´eclaration des variables
et constantes.
Exemple
No Produit Number ;
D´esignation VARCHAR2(20) ;
Publicité
PU NUMBER(6,2) := 100.00;
9/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les variables PL/SQL
INTEGER – max 38chiffres
NUMBER – max 125 chiffres
BINARY INTEGER : -2147483647 - +2147483647
NATURAL :0 - 2147483647
POSITIVE : 1 - 2147483647
CHAR; – Max 32767 caract`eres
VARCHAR2; – Max 32767 caract`eres
LONG; – Max 2147483647 caract`eres
DATE; – 4712 avant AVJC `a 9999 APJC
BOOLEAN :TRUE, FALSE, NULL, NOT NULL
%TYPE : type de variable ´equivalent au type de colonne d’une table ou
d’une autre variable
%ROWTYPE : type de variable ´equivalent `a une ligne d’une table
10/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Exemples
DECLARE
v remise CONSTANT real := 0.10;
v hiredate date;
g deptno number(2) NOT NULL := 10;
v integer integer; – max 38chiffres
v number1 number; – max 125 chiffres
v number2 number(38,3);
v bool boolean; – valeurs possibles TRUE, FALSE, NULL et NOT NULL
v char char(5); – Max 32767 caract`eres
v varchar2 varchar2(20); – Max 32767 caract`eres
v long LONG; – Max 2147483647 caract`eres
v date date; – Les dates peuvent aller de -4712 avant AVJC `a 9999 APJC
v last name employees.last name%type;
v employee employees%rowtype
v n1 number(5,3);
v n2 v n1%type;
11/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Le type RECORD
Les enregistrements PL/SQL sont des types composites d´efinis par l’utilisateur. Pour les
utiliser il faut:
d´eclarer l’enregistrement dans la section d´eclarative d’un bloc PL/SQL en utilisant le
mot cl´e TYPE
d´eclarer et ´eventuellement initialiser les composants internes de ce type
d’enregistrement
D´eclaration d’un type record
TYPE record name IS RECORD
(field 1 [, field 2, ... field N]. . . );
new record record name;
Exemple
TYPE emp record type IS RECORD
(last name VARCHAR2(25),
job id VARCHAR2(10),
salary employees.salary%type);
emp record emp record type;
12/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
R`egles de nommination des variables
Deux variables peuvent porter le mˆeme nom si elles sont dans des
blocs distincts
Les noms des variables doivent ˆetre diff´erents des noms des colonnes
et des tables utilis´es dans le bloc
L’identifiant ne doit pas d´epasser 30 caract`eres
Le premier caract`ere doit ˆetre une lettre, les autres peuvent ˆetre des
lettres, des nombres ou des caract`eres sp´eciaux.
13/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
D´eclaration et initialisation des variables
Les variables sont d´eclar´ees et initialis´ees dans la section d´eclarative
Initialisation `a l’aide de l’op´erateur d’affectation (:=) ou du mot r´eserv´e DEFAULT
D´eclarer un seul identifiant par ligne
D´eclarer une constante en utilisant le mot cl´e CONSTANT
Initialisation des variables si on a:
Mot cl´e DEFAULT
Contrainte NOT NULL
Constante
Exemples
DECLARE
v integer integer := 12345;
v bool Boolean :=TRUE;
v char varchar(30) NOT NULL := ’SGBD’;
v date date DEFAULT ’01-Janv.-2009’;
Publicité
V constant CONSTANT:=10;
14/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Affectation de valeurs aux variables
De nouvelles valeurs sont affect´ees aux variables dans la section
ex´ecutable (BEGIN .. END)
Exemples
BEGIN
v integer := 12345;
v number1 := 1234567.453;
v number2 := 1.9999E+15;
v bool :=TRUE;
v char := ’SGBD’;
v long := ’Cours PL/SQL’;
v date := ’21-f´evr.-2008’;
END;
15/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
La port´ee des variables
DECLARE
v1 integer := 100;
v2 varchar(5) :=’A’;
BEGIN
DECLARE
v1 integer;
v3 varchar2(10):=v2||’1’;
v4 varchar2(10):=’B’;
BEGIN
dbms output.put line(’v1(1) =’ || v1 || chr(10) ||
’v2(1) =’ || v2 || chr(10) || ’v3(1) =’ || v3);
END;
dbms output.put line(’v1(2) =’ || v1 || chr(10) || ’v2(2) =’ || v2);
dbms output.put line(’v3(2) =’ || v3);
END;
/
16/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
La section ex´ecutable
Int`egre diff´erents types d’instructions:
Instruction d’affectation
Instruction de contrˆole de flux (boucles, structures de
contrˆole)
Instruction SQL
Instruction curseur
17/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les structures de contrˆole
IF .. THEN .. END IF
IF condition THEN
– Instructions
END IF;
IF .. THEN .. ELSE .. END IF
IF condition THEN
– Instructions
ELSE
– Instructions
END IF;
IF .. THEN .. ELSEIF .. THEN .. ELSE .. END IF
IF condition THEN
– Instructions
ELSIF condition2 THEN
– Instructions
ELSE
– Instructions
END IF;
18/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les structures de contrˆole
Exemple 1
DECLARE
v1 integer := 1100;
v2 integer := 200;
BEGIN
IF v1 < v2 THEN
dbms output.put line(’v1 < v2’);
ELSE
dbms output.put line(’v2 <= v1’);
END IF;
END;
/
19/27
Introduction
Publicité
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les structures de contrˆole
Exemple 2
DECLARE
v1 number:= 685;
v2 number := 125;
v3 number :=870;
BEGIN
IF v1 < v2 THEN
IF v2<v3 then
dbms output.put line(’v1 < v2 < v3’);
ELSIF v3 < v1 then
dbms output.put line(’v3 < v1 < v2’);
ELSE
dbms output.put line(’v1 <=v3 <v2’);
END IF;
ELSIF v1 < v3 then
dbms output.put line(’v2 < v1 < v3’);
ELSIF v3<v2 then
dbms output.put line(’v3 < v2 <= v1’);
ELSE
dbms output.put line(’v2 <= v3 <= v1’);
END IF;
END;
/
20/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les structures de contrˆole
CASE expression
CASE expression
WHEN valeur1 THEN
–instructions1;
WHEN valeur2 THEN
–instructions2;
. . .
ELSE
–instructionsN;
END CASE;
CASE
CASE
WHEN expr1 THEN
–instructions1;
WHEN expr2 THEN
–instructions2;
. . .
ELSE
–instructionsN;
END CASE;
21/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les structures de contrˆole
Exemple 1
DECLARE
v1 integer := 4;
BEGIN
CASE v1
WHEN 1 THEN
dbms output.put line(’A’);
WHEN 2 THEN
dbms output.put line(’B’);
WHEN 3 THEN
dbms output.put line(’C’);
ELSE
dbms output.put line(’X’);
END CASE;
END;
/
Exemple 2
DECLARE
v1 integer := 18;
BEGIN
CASE
WHEN v1<5 THEN
dbms output.put line(’A’);
WHEN v1<10 THEN
dbms output.put line(’B’);
WHEN v1<15 THEN
dbms output.put line(’C’);
ELSE dbms output.put line(’X’);
END CASE;
END;
/
22/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les structures de contrˆole
WHILE .. LOOP .. END LOOP
WHILE condition LOOP
Publicité
–instructions;
END LOOP;
Exemple
DECLARE
v1 integer :=1;
BEGIN
WHILE v1 <10 LOOP
dbms output.put line(v1);
v1 := v1+1;
END LOOP;
END;
/
23/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les structures de contrˆole
LOOP .. EXIT WHEN .. END LOOP
LOOP
–instructions;
EXIT WHEN condition
–instructions;
END LOOP;
Exemple
DECLARE
v1 integer :=1;
BEGIN
LOOP
dbms output.put line(v1);
EXIT WHEN v1 =10;
v1 := v1+1;
END LOOP;
END;
/
24/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
Les structures de contrˆole
FOR .. IN .. LOOP .. END LOOP
FOR compteur IN [REVERSE] inf .. sup LOOP
–instructions;
END LOOP;
Exemple
DECLARE
v1 integer :=1;
BEGIN
FOR v1 IN 1..10 LOOP
dbms output.put line(v1);
END LOOP;
END;
/
25/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
L’instruction SELECT
SELECT col1, col2, .., colN INTO v col1, v col2, .., v colN FROM nom table1, nom table2;
Utilisation de la clause INTO pour identifier les variables PL/SQL
qui doivent recevoir des valeurs des colonnes des tables d’une base
de donn´ees
La requˆete select doit retourner une seule ligne
Exemple
DECLARE
salaire moy employees.salary%type;
BEGIN
Select avg(sal) into salaire moy From employees where department id=10;
dbms output.put line(’Le salaire moyen des employ´es du d´epartement 10 est : ’ ||
to char(salaire moy ,’99999.99’));
END ;
/
26/27
Introduction
Mod`eles de programmes en PL/SQL
Structure d’un bloc PL/SQL
La section d´eclaration
La section ex´ecutable
L’instruction SELECT
Exemple
DECLARE
v emp employees%ROWTYPE;
BEGIN
Select * into v emp From employees where employee id = 124;
dbms output.put line(’Nom employ´e :’ || v emp.last name || chr(10)||
’Fonction : ’ || v emp.job id || chr(10)|| ’Departement : ’||
v emp.department id ||chr(10)|| ’Date recrutement : ’||
to char(v emp.hire date, ’dd/mm/yyyy’) || chr(10) || ’Salaire : ’ ||
v emp.salary );
END;
/
27/27