lundi 2 décembre 2002

Procédure stockée


PROCÉDURE STOCKÉE

Une procédure stockée ressemble à une fonction en delphi. Cette procédure est entreposée sur le serveur de base de données, le client peut l'appeler. Une procédure peut avoir 0 ou plusieurs paramètres et retourné 0 ou plusieurs valeurs. Une procédure stockée est compilée sur le serveur lorsqu'elle est créée. Une requête est compilée à chaque fois qu'elle est envoyée au serveur. Il y a quelques avantages à utiliser les procédures stockées:
  • Plus rapide, car elle est déjà compilée sur le serveur
  • Diminue le trafic réseau car seuls les paramètres et le nom de la requête transitent par le réseau
  • Partage des ressources, plusieurs programmes peuvent utilisé la procédure
  • facilitée de maintenance, la mise à jour de la procédure profite à tous les programmes

Syntaxe d'une procédure stockée


CREATE PROCEDURE name
[( param datatype [, param datatype ...])]
[RETURNS ( param datatype [, param datatype ...])]
AS
<procedure_body>;
<procedure_body> = [< déclaration_variables >]
< block>
< déclaration_variables> =
DECLARE VARIABLE var datatype;
[DECLARE VARIABLE var datatype; ...]
<block> =
BEGIN
< compound_statement>
[< compound_statement> ...]
END
< compound_statement> = {<block> | statement;}

Suppression d'une procédure stockée

La syntaxe de suppression de la procédure stockée est fort simple.
drop procedure nom_procedure;

Déclaration de variable

La déclaration d'une variable doit suivre la convention: <DECLARE VARIABLE nomVariable Type de donnée>. Les variables globales n'existent pas.

/*Déclaration de variable nécessaire à la procédure*/
DECLARE VARIABLE tmpChar CHAR(5);
DECLARE VARIABLE tmpInt integer;
DECLARE VARIABLE tmpNum NUMERIC(15,2);

Déclaration de variable

Condition

La syntaxe du if else... ressemble étrangement à celle présents sous delphi.
IF <Expression de la condition> THEN
  <code>
ELSE
  <code>
Dans l'expression de la condition, il est possible d'utiliser certain opérateur SQL
  • BETWEEN ... and ...
  • LIKE ...
  • IN (..., ..., ...
  • EXISTS (sous-requête)
  • ANY (sous-requête)
  • ALL (sous-requête)
  • IS NULL
  • IS NOT NULL

Boucle

Cet opérateur permet comme nous le savons d'exécuter du code une certaine quantité de fois
WHILE (<Expression de la condition>) DO
        <code>

Nous allons créer une table pour notre exemple.
 
CREATE TABLE personne
(
  nopersonne INTEGER NOT NULL,
  age    INTEGER NOT NULL,
  pays         CHAR(8)  NOT NULL,
CONSTRAINT PK_nopersonne PRIMARY KEY (nopersonne)
);


Imaginons maintenant que cette table renferme des milliers d'enregistrements et que nous désirons obtenir l'âge moyen des personnes vivant dans un pays donné. Il serait possible d'écrire la requête ci-dessous et de l'assigner à un ibquery, ibsql ou à un ibdataset.
 
select avg(age)from  personne where pays = :PPAYS


Maintenant regardons la solution avec une procédure stockée

create procedure GET_AGE (PPAYS CHAR(8))
returns (AVG_AGE DECIMAL(8,2))AS
DECLARE VARIABLE i integer;
begin
  WHILE (i <= 5) DO
  begin
    select avg(age)
    from personne
  where pays = :PPAYS
  into :AVG_AGE;
  IF (AVG_AGE = 0) THEN
       AVG_AGE = 1;
  i = i +1;
  end
end
 

Dans cette procédure stockée, le select ne retourne qu'une valeur, si plusieurs valeurs auraient été retournées, il
aurait fallu utilisé: FOR SELECT...DO

Notez que c'est la valeur de avg_age qui va être retournée. En utilisant le composant: IBStoredProc, vous n'avez qu'à renseignez la propriété database et transaction. Ensuite, vous allez voir toutes les procédures stockées dans la propriété StoredProcName, vous n'avez qu'à sélectionner celle que vous désirez. Dans notre exemple ça sera: GET_AGE. Il est possible de voir les paramètres d'entrée et de sortie de la procédure en cliquant dans la propriété Params du composant IBStoredProc. Lorsque nous voulons appeler la procédure stockée dans notre code delphi et obtenir la valeur de retour, il suffit de faire pour notre exemple:

var
  avgAge:float;
begin
  IBStoredProc1.ExecProc;  //IBStoredProc1 étant le nom du composant dans delphi
  avgAge := IBStoredProc.ParamByName('AVG_AGE').AsFloat; 
end;

Il est possible d'exécuter toute sorte de requêtes avec les procédures stockées. La procédure ci-dessous supprime
les personnes ayant l'âge passé en paramètre.

create procedure AGEINF18 (persage int) as
begin
  delete from personne
  where age = :persage;
end

On a vu rapidement l'utilisation et la création de procédure stockée. Une connaissance de programmation et 
de SQL facilite leur utilisation  et permet d'alléger le client.