Modèles et structure des blocs PL/SQL pour Oracle

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’;

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

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

–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