Typage et déclaration de variables simples

Le PLSQL comme la plupart des langages de programmation est un langage Typé. Il existe des types prédéfinis et des types définis par le programmeur ou dépendant des tables.
Une déclaration de type a la forme suivante:

nomvar [constant] type [NOT NULL] [:= valeur | DEFAULT expression ]

On peut utiliser tous les types internes d’Oracle: NUMBER, CHAR, VARCHAR, VARCHAR2, DATE  +  les types standards SQL comme INT, INTEGER, FLOAT ou le type BOOLEAN.

Exemples:

X NUMBER NOT NULL :=10.40;
C VARCHAR(100) := 'Coucou';
N CONSTANT INTEGER DEFAULT 100;
V BOOLEAN := TRUE;

SQL> declare x integer;
  2  begin
  3    null;
  4  end;
  5* .
  /
SQL> PL/SQL procedure successfully completed
SQL> declare x integre;
          *
ERROR at line 1:
ORA-06550: line 1, column 11:
PLS-00201: identifier 'INTEGRE' must be declared
...

Il existe plusieurs catégories de types de variables :

• simples (INTEGER, NUMBER, DATE, CHAR, BOOLEAN, VARCHAR, …)
• composées (RECORD, TABLE, VARRAY , NESTED TABLE)
• référence
• pointeur de LOB (CLOB, BLOB, BFILES, NCLOB )

+ le type ‘dynamique’ %TYPE qui définit une variable  du type de la colonne d’une table existante

-- pour déclarer une variable nom pour la colonneENAME de EMP
DECLARE nom emp.ename%TYPE;

Attention ! dans tous les cas pour que les sorties s’affichent à l’écran il faudra positionner une variable dans votre environnement SQL*Plus ou iSQL*Plus pour valider le futur affichage:

SQL>set serveroutput on

et dans le code faire un DBMS_OUTPUT.PUT_LINE (s) pour faire l’affichage

Les Variables attachées / Bind variables

Variables de substitution dans les requêtes SQL. Variable référençant des variables non-PL/SQL.

• Economie d’analyse de la requête, donc gain de temps pour Oracle
• Mecanisme considéré comme le point clé de la performance du code Oracle

rem: peuvent être utilisées en PLSQL  ET en SQL. Dans le bloc PLSQL ET après son exécution.
Declaration, en SQL*Plus, d’une variable attachée : on utilise le mot clé VARIABLE
Exemple :

-- VARIABLE nom _variable    type_variable ;
VARIABLE gsal NUMBER( 7 , 2 ) ;

ATTENTION: cette déclaration se fait AVANT le bloc PLSQL dans l’environnement (sqlplus par exemple). Tous les types ne sont pa s autorisés (VARCHAR par exemple ou xxx%TYPE sont interdits)

Pour faire référence aux variables non-PL/SQL comme des variables hôte, précéder la référence par un ‘ : ‘

:sal  :=  vsal ;

La commande PRINT peut être utilisée (dans l’environnement) pour afficher une variable attachée.

Exemple d’utilisation de variable simple et de variable attachée:

VARIABLE nv varchar(30)
-- suivi par un RETURN avant de commencer le bloc!
declare s float;
  n emp.ename%type := 'KING';
begin
  select sal into s from emp where ename=n;
  dbms_output.put_line('sal de king:'||s);
  select 'MILLER' into :nv FROM DUAL;
  select sal into s from emp where ename=:nv;
  dbms_output.put_line('sal de '||:nv ||':'||s);
end;

sal de king:5000
sal de MILLER:1300
PL/SQL procedure successfully completed.

SQL> print nv
NV
--------------
MILLER

Utilité des variables attachées

Les plupart des requête SELECT dans un applicatif sont réitérées des milliers de fois :
SELECT lname , code FROM customer WHERE id = 674;
SELECT name , code FROM customer WHERE id = 234;
SELECT name , code FROM customer WHERE id = 332;
. . .
A chaque soumission d’un requête, Verification si la requête a deja ete ete soumise. Si oui, recuperation du plan d’execution de la requête, et execution de la requête,

Si non,
analyse syntaxique de la requête
definition du plan d’execution optimal
! Processus coûteux en temps CPU, alors que seule la valeur de id change !
Solution : reutiliser le plan d’execution existant ! Necessite d’utiliser des variables attachees : Substitution de la valeur par la variable attachée. Envoi de la même requête pour toutes les valeurs de id
Exemple :

SELECT name , code FROM customer  WHERE  id = : custno ;

Types structurés : les enregistrements (RECORDs)

rem: tous les types structurés (RECORDs ou COLLECTIONs)  n’existent qu’en PL/SQL et n’ont pas d’équivalent dans la base Oracle. Il n’est par exemple pas possible de stocker un type enregistrement directement dans la base.

Le type ‘RECORD’ permet de déclarer une variable de type ‘enregistrement’ ou ‘ligne’ d’une table, c’est donc un type structuré décomposable en colonnes élémentaires.

La syntaxe générale est la suivante:

TYPE nom type IS RECORD ( nom_champ type_élément [[ NOT NULL] := expression ]
[, ….] ) ;
Nom_variable nom_type;

Voici un exemple:

DECLARE
 TYPE t_rec_emp IS RECORD
 (nom emp.ename%TYPE, salaire emp.sal%TYPE,comission emp.comm%TYPE);
 rec_emp t_rec_emp;

les RECORDS peuvent être imbriqués et contenir d’autres RECORDS

Les records peuvent être affectés de manière élémentaire, champ par champ ou de manière globale grace à un SELECT INTO :

rec_emp.salaire := 2400;
 SELECT * INTO rec_emp FROM emp
 WHERE empno = &no_saisi;

Types structurés: Les collections

Une collection est un ensemble ordonné d’éléments de même type.
Elle est indexée par une valeur de type numérique ou alphanumérique
Elle ne peut avoir qu’une seule dimension ( mais en créant des collections de collections on peut obtenir des tableaux à plusieurs dimensions)

On peut distinguer trois types différents de collections :

• Tableau associatifs (index by table)
• Tableaux imbriqués (Nested tables)
• Tableaux variables (Varrays)

Voici leurs principales caractéristiques:

Si on veut comparer par rapport  à des langages plus classiques/

• Tables de Hashage et autres tableaux no ordonnés deviennent des ASSOCIATIVE ARRAYs (ou INDEX BY TABLE) en PL/SQL.
• les ensembles classiques (SETs) deviennent des Nested Tables en PL/SQL
• Les tableaux classiques deviennent des VARRAYs en PL/SQL.

Tableaux associatifs (INDEX-BY TABLES)

C’est un tableau constitué de paire (clé, valeur) qui peut être indexé autrement que par des clés numériques consécutives.

Déclaration d’une collection de type Table index by
TYPE nom type IS TABLE OF type élément [NOT NULL] INDEX BY index_by_type ;

index_by_type représente l’un des types suivants :

• BINARY_INTEGER
• PLS_INTEGER(9i)
• VARCHAR2(taille)
• LONG

Voici un exemple de code manipulant un tableau associatif de population…indexée par la ville

DECLARE
-- Associative array indexed by string:
TYPE population IS TABLE OF NUMBER  -- Associative array type
INDEX BY VARCHAR2(64);
city_population  population;        -- Associative array variable
i                VARCHAR2(64);
BEGIN
 -- Add new elements to associative array:
 city_population('Smallville')  := 2000;
 city_population('Midland')     := 750000;
 city_population('Megalopolis') := 1000000;
 -- Change value associated with key 'Smallville':
 city_population('Smallville') := 2001;
 -- Print associative array:
 i := city_population.FIRST;
 WHILE i IS NOT NULL LOOP
   DBMS_Output.PUT_LINE
   ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));
   i := city_population.NEXT(i);
 END LOOP;
 END;
 /
Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001
 
PL/SQL procedure successfully completed.

Nested tables

Déclaration d’une collection de type nested table
TYPE nom type IS TABLE OF type élément [NOT NULL] ;

C’est un tableau a une dimension avec un nombre arbitraire d’éléments.
C’est un ensemble d’éléments pas forcément ordonnés.

Une NESTED TABLE n’est pas un tableau:

• un tableau a une taille fixe , pas une NT. Il y a juste une taille max
• Un tableau est dense (ses cellule  contigues contiennent toutes des éléments, pas une NT

SQL> declare 
2 -- collection de type nested table 
3 TYPE TYP_NES_TAB is table of varchar2(100) ;
 4 -- collection de type index by   
5 TYPE TYP_IND_TAB is table of number index by binary_integer ; 
6 tab1 TYP_NES_TAB ; 
7 tab2 TYP_IND_TAB ; 
8 Begin 
9 tab1 := TYP_NES_TAB('Lundi','Mardi','Mercredi','Jeudi' ) ; 
10 for i in 1..10 loop 
11 tab2(i):= i ; 
12 end loop ; 
13 End; 
14 / 
Procédure PL/SQL terminée avec succès.

declare  
-- collection de type VARRAY 
TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ; 
tab1 TYP_VAR_TAB := TYP_VAR_TAB('','','','','','','','','',''); 
Begin 
 for i in 1..10 loop 
 tab1(i):= to_char(i) ; 
 end loop ; 
End; 
/ 
Procédure PL/SQL terminée avec succès.

d’autres exemples:

Tableau de 2 entiers

-- table de multiplication par 8 et par 9...
 declare
 type tablemul is record ( par8 number, par9 number);
 type tabledentiers is table of tablemul index by binary_integer;
 ti tabledentiers;
 i number;
 begin
 for i in 1..10 loop
    ti(i).par9 := i*9 ;
    ti(i).par8:= i*8;
    dbms_output.put_line (i||'*8='||ti(i).par8||' '||i||'*9='||ti(i).par9 );
 end loop;
 end;
 /

Tableau de champs numeriques

-- on va lire les salaires de emp...
 declare
 type tsal is table of emp.sal%type index by binary_integer;
 tasal tsal;
 i number;
 cursor cur is select sal from emp;
 begin
 open cur;
 i :=1;
 loop
    exit when cur%notfound;
    fetch cur into tasal(i) ;
    dbms_output.put_line ('salaire ...'||tasal(i));
    i := i + 1;
 end loop;
 end;
 /

 Description des attributs / méthodes des TABLES et VARRAY PL/SQL

Utilisation des attributs / méthodes de TABLES et VARRAY

 declare
 type tsal is table of emp.sal%type index by binary_integer;
 tasal tsal;
 i number;
 cursor cur is select sal from emp;
 begin
 open cur;
 i :=1;
 loop
 exit when cur%notfound;
 fetch cur into tasal(i) ;
 i := i + 1;
 end loop;
 close cur;
 dbms_output.put_line ('premier élément : '||tasal(tasal.FIRST) );
 dbms_output.put_line ('dernier élément : '||tasal(tasal.LAST) );
 dbms_output.put_line ('nombre d élément : '||tasal.COUNT);
 dbms_output.put_line ('suppresion des éléments '||tasal.next(1)||' à '||tasal.prior(tasal.COUNT));
 tasal.delete(2,tasal.COUNT-1);
 dbms_output.put_line ('nombre d élément : '||tasal.COUNT);
 end;
 /