| Tutorial Procédures Cataloguées InterBase - John COLIBRI. |
- résumé : utilisation de procédures cataloguées InterBase / Firebird: fonctionnement, syntaxe, fonctionalités. Avec grammaire, instructions Sql, application Delphi
- mots clé : Stored Procedure - InterBase - FireBird - paramètres IN, OUT - contrôle - création, modification, effacement - procédures récursives
- logiciel utilisé : Windows XP personnel, Delphi 6 - InterBase 6
- matériel utilisé : Pentium 2.800 Mhz, 512 Meg de mémoire, 250 Giga disque dur
- champ d'application : Delphi 1, Delphi 7, Delphi 8, Delphi 2005, Delphi 2006, Turbo Delphi, InterBase 6 à 7.5, Firebird 1 à 2
- niveau : développeur Delphi InterBase
- plan :
1 - Procédures Cataloguées InterBase
Les procédures cataloguées sont des morceaux de code exécutable stockées sur le Serveur. Le Client peut appeler ces procédures en fournissant des paramètre et récupérer le résultat s'il y en a un.
Mentionnons que les Stored Procedures furent une innovation InterBase, rapidement adaptée aux autres moteurs Sql.
Nous allons
2 - Procédures Stockées en Delphi 2.1 - Un exemple simple Commençons par un exemple simple. La base de démonstration EMPLOYEE livrée avec
Delphi contient une Table JOB avec des salaires minimum et maximum par job. Supposons que nous souhaitions réajuster les minima, avec les deux règles suivantes: - tous les salaires inférieurs à 40.000 seront augmentés de 45.000
- la valeur maximale des salaires doit au moins être de 1.5 fois le minimum
Nous pouvons réaliser ce traitement - en récupérant chez le Client chaque ligne de la Table et en modifiant
celles qui répondent aux critères de modification
- en envoyant deux requêtes SQL depuis le Client
- en appelant depuis le Client une procédure cataloguée du Serveur qui effectue les traitements
2.1.1 - Modification ligne à ligne depuis le Client Le code le plus naturel en Delphi est le suivant - une requête SELECT est envoyée au Serveur pour récupérer les lignes
de la table JOB
- le Serveur envoie les lignes:
- le Client modifie la valeur des lignes correspondant aux critères d'augmentation par Edit / Post
Nous utilisons pour cela
Le code de modification est alors le suivant:
const k_threshold= 40000;
k_increase= 45000;
k_min_max_ratio= 1.5;
procedure TForm1.edit_client_rows_Click(Sender: TObject);
var l_salary_min, l_salary_max: Double;
l_new_salary_min, l_new_salary_max: Double;
begin
f_open_ib_database('connect', IbDatabase1);
with IbDataSet1 DO
begin
Open;
First;
while not Eof DO
begin
l_salary_min:= FieldByName('MIN_SALARY').AsFloat;
l_salary_max:= FieldByName('MAX_SALARY').AsFloat;
if l_salary_min< k_threshold
then begin
l_new_salary_min:= l_salary_min+ k_increase;
l_new_salary_max:= k_min_max_ratio* l_new_salary_min;
if l_salary_max>= l_new_salary_max
then l_new_salary_max:= l_salary_max;
Edit;
FieldByName('min_salary').AsFloat:= l_new_salary_min;
FieldByName('max_salary').AsFloat:= l_new_salary_max;
Post;
end;
Next;
end; // while not eof
end;
end; // edit_client_rows_Click
| Notez que:
- si vous souhaitez visualiser les lignes, ajoutez un tDataSource et un tDbGrid
- le tDataGrid n'est pas nécessaire, les lignes appelées par SELECT
étant placées dans le tampon du tIbDataSet
2.2 - Le Client envoie des requêtes UPDATE Une autre solution est d'envoyer depuis le Client des requêtes UPDATE. Par exemple
- une première requête qui modifie d'abord les postes pour lesquels le minimum et le maximum doivent être ajusté
UPDATE job
SET
min_salary= min_salary+ 45000,
max_salary= (min_salary+ 45000)* 1.5
WHERE
(min_salary< 40000)
AND
(max_salary< (min_salary+ 45000)* 1.5);
|
- une seconde pour les minima qui sont rajustés sans avoir à toucher au maximum:
UPDATE job
SET
min_salary= min_salary+ 45000,
WHERE
(min_salary< 40000)
|
Schématiquement cela se présente ainsi:
Et en Delphi, nous utilisons - les composants tIbDataBase et tIbTransaction pour assurer la connexion
- un tIbSql en envoyant les deux requêtes successivement:
const k_threshold_string= '40000';
k_increase_string= '45000';
k_min_max_ratio_string= '1.5';
k_request_min_max=
'UPDATE job '
+ ' SET '
+ ' min_salary= min_salary + '+ k_increase_string+ ','
+ ' max_salary= (min_salary+ '+ k_increase_string+ ')'
+ ' * '+ k_min_max_ratio_string
+ ' WHERE (min_salary< '+ k_threshold_string+ ')'
+ ' AND (max_salary< (min_salary+ '+ k_increase_string+ ')'
+ ' * '+ k_min_max_ratio_string+ ')';
k_request_min=
'UPDATE job '
+ ' SET min_salary= min_salary+ '+ k_increase_string
+ ' WHERE (min_salary< '+ k_threshold_string+ ')';
procedure TForm1.update_requests_Click(Sender: TObject);
begin
f_execute_ibsql(IbSql1, k_request_min_max);
f_execute_ibsql(IbSql1, k_request_min);
end; // sql_Click
| avec la procédure générique d'écriture:
function f_execute_ibsql(p_c_ibqsl: tIbSql; p_request: String): Boolean;
begin
Result:= False;
with p_c_ibqsl do
begin
f_open_ib_database('', DataBase);
Sql.Text:= p_request;
if DataBase.DefaultTransaction.InTransaction
then DataBase.DefaultTransaction.Commit;
DataBase.DefaultTransaction.StartTransaction;
ExecQuery;
DataBase.DefaultTransaction.Commit;
Result:= True;
end; // with
end; // f_execute_ibsql
|
2.3 - Création d'une Procédure Cataloguée
La troisièmes solution est de placer sur le Serveur le code d'une procédure qui contient nos deux requêtes de modification. Le code de la procédure est le suivant:
CREATE PROCEDURE a_increase_salary_2
(
p_increase FLOAT,
p_threshold FLOAT,
p_min_max_ratio FLOAT
)
AS
BEGIN
UPDATE JOB
SET
min_salary= min_salary+ : p_increase,
max_salary= (min_salary+ : p_increase)* p_min_max_ratio
WHERE
(min_salary< : p_threshold)
AND
(max_salary< (min_salary+ 45000)* p_min_max_ratio);
UPDATE JOB
SET min_salary= min_salary+ : p_increase
WHERE min_salary< : p_threshold;
END
|
Cette procédure est placée sur le Serveur en exécutant cette requête. Cette requête peut être envoyée à l'aide de n'importe quelle application sachant
exécuter des requêtes SQL: IbConsole, ISql, WISql, et naturellement, depuis Delphi. Nous allons donc utiliser un tIbSql pour effectuer la création:
const k_stored_procedure_name= 'a_increase_salary_2';
k_create_stored_procedure=
'CREATE PROCEDURE '+ k_stored_procedure_name
+ '('
+ ' p_increase FLOAT,'
+ ' p_threshold FLOAT,'
+ ' p_min_max_ratio FLOAT'
+ ')'
+ 'AS'
+ ' BEGIN'
+ ' UPDATE JOB'
+ ' SET '
+ ' min_salary= min_salary+ :p_increase,'
+ ' max_salary= (min_salary+ :p_increase)* :p_min_max_ratio'
+ ' WHERE '
+ ' (min_salary< :p_threshold)'
+ ' AND '
+ ' (max_salary< (min_salary+ :p_increase)* :p_min_max_ratio);'
+ ' UPDATE JOB'
+ ' SET min_salary= min_salary+ :p_increase'
+ ' WHERE min_salary< :p_threshold;'
+ ' END';
procedure TForm1.create_procedure_Click(Sender: TObject);
begin
IbSql1.ParamCheck:= False;
f_execute_ibsql(IbSql1, k_create_stored_procedure);
IbSql1.ParamCheck:= True;
end; // create_procedure_Click
| Notez que
- ParamCheck à False est ESSENTIEL, sinon vous aurez une erreur du Serveur
Invalid BLR request at offset nnn |
Après exécution de cette requête, le code exécutable de la procédure est ajouté
à la base de données sur le Serveur:
2.4 - Exécution de la Procédure Stockée
Une fois que la procédure est créée, nous pouvons l'exécuter autant de fois que nous le souhaitons. Pour cela, nous utilisons un composant tIbStoredProc en initialisant ses paramètres (correspondant aux paramètres de la procédure
cataloguée) et en appelant tIbStoredProc.ExecPro. Si la procédure retourne des résultats, ils seront placés dans les paramètres après l'exécution. Par conséquent:
 | dans l'onglet InterBase de la Palette, sélectionnez un tIbStoredProc: 
et posez-le sur la tForme | | | initialisez la propriété IbStoredProc1.Database avec IbDataBase1
| |
| sélectionnez la propriété StoredProcName et sélectionnez la procédure que nous venons de créer: "a_increase_salary_2" | |
| écrivez le code qui prépare la procédure, initialise les paramètres est exécute la procédure:
procedure TForm1.execute_stored_proc_Click(Sender: TObject);
begin
with IbStoredProc1 do
begin
Prepare;
ParamByName('p_threshold').AsString:= k_threshold_string;
ParamByName('p_increase').AsString:= k_increase_string;
ParamByName('p_min_max_ratio').AsString:= k_min_max_ratio_string;
ExecProc;
end; // with IbStoredProc1
end; // execute_stored_proc_Click
|
| Notez que:
- les tIbStoredProc.Params sont créés à la suite de l'appel Prepare. Il n'y a donc pas besoin de les créer manuellement
- nous avons appelé ParamByName, ayant eu quelques surprises en utilisant un indice
- comme notre procédure ne retourne aucun résultat, il n'y a dans ce cas pas de lecture de Params
L'exécution revient à envoyer la procédure et la valeur des paramètres sur le Serveur, qui effectue le traitement:
Dans un exemple plus complexe, nous pouvons - envoyer une requête Prepare qui retourne le nombre de paramètres:
- puis initialiser les paramètres et exécuter la procédure. Si la procédure retourne des résultats, nous récupérons ces valeurs:
2.5 - Initialisation des Paramètres Lorsque les paramètres ont une valeur litérale fixe, il est possible des les initialiser depuis l'Interface Delphi. Pour cela: |
| sélectionnez IbStoredProc1, sélectionnez sa propriété Params et cliquez l'ellipse ...
|  |
Delphi présente l'Editeur de paramètres  | | |
sélectionnez chaque paramètres tour à tour et définissez le type et la valeur du paramètre. Par exemple, sélectionnez p_increase | | |
ce paramètre est affiché dans l'Inspecteur d'Objet  | | |
dans la propriété Value, sélectionnez le type, et saisissez la valeur: 
|
2.6 - Procédure SELECT ou EXECUTE
Il existe deux sortes de procédures cataloguées: les procédures que nous lançons par EXECUTE et les procédures que nous lançons par SELECT. Les procédures SELECT retournent en général un résultat sous forme de
paramètres en sortie, ou même de Table complètes. Les procédures EXECUTE sont lancées depuis Delphi en utilisant un tIbStoredProc et en appelant tIbStoredProc.ExecProc.
Les procédures SELECT sont lancées depuis Delphi en utilisant une requête SELECT à l'aide de n'importe quel composant: tIbSql, tIbQuery,
tIbDataSet (ou tout autre composant d'accès à InterBase).
Voici une procédure simple qui récupère le nombre d'enregistrements de la table JOB:
create procedure job_count
returns
(
pv_job_count integer
)
as
begin
select count( * )
from job
into : pv_job_count;
suspend;
end
| Et voici le code Delphi qui récupère le résultat:
procedure TForm1.select_Click(Sender: TObject);
begin
with IbQuery1 do
begin
Close;
Sql.Text:= 'SELECT * FROM a_job_count';
Open;
end;
end; // select_Click
| Si IbQuery1 est lié à une tdbGrid, vous apercevrez la valeur 31 (le nombre de lignes de JOB): 
Notez que:
- lorsqu'une procédure ne retourne qu'un seule ligne (et pas une Table), nous pouvons indifféremment utiliser ExecProc ou SELECT
- lorsqu'une procédure retourne une Table (ce qui n'est possible que
lorsqu'elle contient une instruction FOR SELECT que nous présenterons ci-dessous), l'utilisation de ExecProc ne retourne dans tIbStoredProc.Params que les valeurs de la première ligne. Pour récupérer
toutes les lignes, il faut utiliser un SELECT
En résumé: - si votre procédure a pour vocation de retourner plusieurs lignes, utilisez SELECT
- dans les autres cas, la technique d'appel est une question de style
2.7 - Suppression Nous pouvons aussi supprimer une quelconque procédure en exécutant DROP PROCEDURE :
DROP PROCEDURE a_increase_salary_2
| Ce qui donne en Delphi:
const k_drop_stored_procedure= 'DROP PROCEDURE A_INCREASE_SALARY_2';
procedure TForm1.drop_procedure_Click(Sender: TObject);
begin
f_execute_ibsql(IbSql1, k_drop_stored_procedure);
end; // drop_procedure_Click
| Notez que - nous avons utilisé le nom de la procédure en majuscules, car il semble que
IbExpress passe automatiquement le nom des identificateurs InterBase en majuscules (nom de Table, Colonne, Index etc). Et en Dialecte 3, les requêtes de modification de la base risquent de ne pas trouver
l'identificateur si la casse ne correspond pas
2.8 - Modification Nous pouvons modifier une procédure en invoquant ALTER PROCEDURE.
Voici le code qui augmente le salaires minimum et ajuste systématiquement le salaire maximum:
ALTER PROCEDURE a_increase_salary_2
(
p_increase FLOAT,
p_threshold FLOAT,
p_min_max_ratio FLOAT
)
AS
BEGIN
UPDATE JOB
SET
min_salary= min_salary+ : p_increase,
max_salary= (min_salary+ : p_increase)* p_min_max_ratio
WHERE
min_salary< : p_threshold;
END
| Notez que - la modification comporte les paramètres et le corps de la procédure
- les scripts de création de base utilisent souvent une création en deux étapes:
- la première créent une procédure vide:
CREATE PROCEDURE a_increase_salary_2
(
p_increase FLOAT,
p_threshold FLOAT,
p_min_max_ratio FLOAT
)
AS
BEGIN
END
| - après la création de toutes les procédures, le corps de la procédure est
créé par des modifications ALTER PROCEDURE
La raison de cette séparation est d'éviter les problèmes d'ordre de création, qui surviennent si une procédure en appelle une autre (problème
qui existe aussi en Pascal, mais qui est résolu soit en réorganisant le code, soit en utilisant FORWARD)
2.9 - Affichage d'une Procédure Cataloguée
Nous pouvons lister toutes les procédures cataloguées en utilisant les tables système:
SELECT rdb$procedure_name
FROM rdb$procedures
| Ce que nous avons placé dans une FUNCTION Delphi:
function f_c_stored_procedure_list(p_c_ib_database: tIbDatabase): tStringList;
const k_select_procedure_names=
'SELECT rdb$procedure_name'
+ ' FROM rdb$procedures';
var l_c_ibquery: tIbQuery;
begin
f_open_ib_database('', p_c_ib_database);
l_c_ibquery:= tIbQuery.Create(Nil);
with l_c_ibquery do
begin
Database:= p_c_ib_database;
open_ibquery(l_c_ibquery, k_select_procedure_names);
Result:= tStringList.Create;
While not Eof do
begin
Result.Add(Fields[0].AsString);
Next;
end; // While not Eof
Close;
Free;
end; // with l_c_ibquery
end; // f_c_stored_procedure_list
|
Nous pouvons finalement récupérer la déclaration de la procédure en utilisant un composant tIbExtract. Essentiellement le code remplace les deux
instructions (CREATE PROCEDURE avec un corps vide et ALTER PROCEDURE) par une procédure unique:
function f_stored_procedure_script(p_c_ib_database: tIbDatabase;
p_stored_procedure_name: String): String;
var l_c_ibextract: tIbExtract;
l_item_index: Integer;
l_the_line: String;
begin
f_open_ib_database('', p_c_ib_database);
l_c_ibextract:= tIbExtract.Create(Nil);
with l_c_ibextract do
begin
Database:= p_c_ib_database;
ExtractObject(eoProcedure, p_stored_procedure_name);
with Items do
begin
l_item_index:= 0;
// -- extract everything between "ALTER ..." and " ^ "
while l_item_index< Count do
begin
l_the_line:= Strings[l_item_index];
if Pos('ALTER', l_the_line)> 0
then break;
Inc(l_item_index);
end; // while l_item_index
Result:= Trim(l_the_line)+ k_new_line;
Result:= 'CREATE'+ f_remove_start(Result, 'ALTER');
Inc(l_item_index);
while l_item_index< Count do
begin
l_the_line:= Strings[l_item_index];
if Trim(l_the_line)= '^'
then break;
Result:= Result+ l_the_line+ k_new_line;
Inc(l_item_index);
end; // while l_item_index
end; // with Items
Free;
end; // while l_c_ibextract
end; // f_stored_procedure_script
|
Le source du projet complet
p_stored_procedure_simple.zip contiennent ces exemples
2.10 - Fonctionnement Lorsque nous exécutons une requête SQL |
| le Client envoie le texte de la requête vers le Serveur
| | |
le moteur InterBase
- analyse la requête, la compile en un langage intermédiaire (BLR) qui est un pseudo-code dédié
- ce code est ensuite optimisé
- le code est exécuté, et si la requête est un SELECT, la table résultat est renvoyée vers le Client
|
Lorsque nous créons une procédure |
| le Client envoie la requête de création
| | |
le moteur InterBase compile le code de la procédure et le stocke dans une table (RDB$PROCEDURES)
|
Plus tard (en général une autre application), nous exécutons la procédure: |
| le Client envoie la requête d'exécution avec les paramètres
| | |
le Serveur exécute le code, et si la requête est un SELECT, la table résultat est renvoyée vers le Client
|
2.11 - Avantages
Voici quelques avantages qui peuvent être aisément déduits de l'exemple présenté ci-dessus: - le traffic réseau est réduit (pas besoin de récupérer les lignes de données sur le Client pour effectuer les calculs)
- la requête est pré-compilée et optimisée une seule fois (gain de temps)
- le code SQL est centralisé, ce qui facilite la maintenance / mise à jour
- la sécurité est obligatoirement gérée au niveau du moteur SQL
Parmi les contraintes et inconvénients: - if faut pouvoir paramétrer et passer les paramètre (toutes les données ne correspondent pas à des types InterBase)
- le code des procédure n'est pas adapté à l'algorithmique (pas de tableaux,
RECORDs, encore moins d'objet, de procédure emboîtées, de TYPEs etc). Juste un peu de IF et FOR, que nous allons présenter ci-dessous en détail
- le fait de récupérer les lignes à traiter chez le Client permet d'y
effectuer des traitements auxiliaires (comme compter le nombre de lignes affectées, ou calculer le montant des modifications, dans notre exemple)
- la syntaxe des Stored Procédure n'est pas portable d'un moteur SQL à
l'autre. Par exemple Oracle utilise une syntaxe assez proche de Pascal (PlSql), alors que d'autres moteurs sont plus proches de Basic
3 - Syntaxe et Fonctionnalités des Stored Procedures
3.1 - Structure de base Une procédure est composée
- de l'en-tête: nom, paramètres en entrée et en sortie, variables locales
- du corps, entre BEGIN et END avec les instructions
La structure globale est donc la suivante:
stored_procedure= CREATE PROCEDURE procedure_name
[ '(' parameter_name_and_type_list ')' ]
[ RETURNS '(' parameter_name_and_type_list ')' ]
AS [ local_variable_declaration_list ] compound_statement .
local_variable_declaration_list = declaration { declaration } .
declaration= DECLARE VARIABLE NAME data_type ';' .
compound_statement= BEGIN { compound_statement | statement [ ';' ] } END .
statement= assignment | test_loop_control
| exit_control | error_handling | ib_event
| sql_request | execute_procedure .
|
Pour une grammaire complète voyez <%_us/interbase_stored_procedure_grammar>la grammaire des Stored Procedure InterBase</A>
Schématiquement, ceci peut être représenté ainsi:
3.2 - Syntaxe Quelques éléments particuliers: - les commentaires sont compris entre /* et */
- les paramètres en entrée et sortie peuvent porter le même nom que des identificateurs InterBase (nom de Table, de colonnes etc), mais lorsqu'ils figurent dans une requête, ils doivent être préfixés par : (deux points)
3.3 - Procédure sans aucun paramètre Les procédures les plus simples n'utilisent aucun paramètre. Elles contiennent uniquement des requêtes SQL qui vont écrire des données dans la base. Par
exemple pour effectuer des INSERT, DELETE, UPDATE, ou encore pour modifier les droits, rendre des index actifs etc Voici un exemple qui ajoute une nouvelle monnaie (Europe / Euro) à la table COUNTRY.
La procédure est la suivante:
create procedure b_insert_europe_euro
as
begin
insert into country
(country, currency)
values ('Europe', 'Euro');
end
|
Pour permettre les essais des procédures que nous allons présenter, nous avons construit une seconde application Delphi, qui contient un tNoteBook avec une
page pour chaque procédure. Sur cette page, nous avons placé: - une procédure qui permet de visualiser les données qui vont être modifiées ou lues dans une dbGrid
- un tMemo qui permet d'afficher, créer, modifier, supprimer le script de la procédure
- des tEdit pour les paramètres en entrée, un tButton pour lancer la
procédure, et des tEdit pour récupérer les résultats éventuels
Dans notre cas: Nous ne détaillerons pas ce type de manipulation pour chaque exemple, car le fonctionnement est similaire: affichage préalable de la Table, exécution ou sélection, visualisation des résultats, annulation éventuelle
3.4 - Les paramères en Entrée (IN) Permet au programme Client de fournir des valeurs, soit pour modifier les données de la base, soit pour paramétrer les lectures ou les calculs La syntaxe est (partiel):
stored_procedure= CREATE PROCEDURE procedure_name
[ '(' parameter_name_and_type_list ')' ]
...ooo...
AS ...ooo... .
parameter_name_and_type_list= name_and_type { ',' name_and_type } .
name_and_type= NAME data_type .
data_type= SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION
| ( DECIMAL | NUMERIC )
[ '(' integer [ ',' integer ] ')' ]
| DATE | TIME | TIMESTAMP
| ( CHAR | CHARACTER [ VARYING ] | VARCHAR )
[ '(' integer ')' ] [ CHARACTER SET CHARACTER_SET_NAME ]
| ( NCHAR | NATIONAL ( CHARACTER | CHAR) )
[ VARYING ] [ '(' integer ')' ]
| BLOB .
| Et:
- les identificateurs sont des identificateurs InterBase quelconques.
Comme indiqué il est possible d'utiliser des paramètres qui portent le nom d'autres objets de la base (Table, colonne etc)
Nous avons choisi de préfixer les paramètres IN avec avec p_ (comme P_arameter), mais cette convention n'est naturellement pas obligatoire - les types sont les types InterBase Standard
- dans le corps de la procédure, les paramètres sont référencés
- normalement pour les instructions (affectation, calculs)
- en les préfixant avec : dans le texte de requêtes SQL
Voici un exemple qui écrit dans la Table COUNTRY le pays et le nom de la monnaie figurant dans deux tEdit:
create procedure c_insert_country_currency
(
p_country varchar(15),
p_currency varchar(10)
)
as
begin
insert into country
(country, currency)
values ( : p_country, : p_currency );
end
|
Et voici l'image de l'application: 
3.5 - Les Paramètres en Sortie Ces paramètres permettent de retourner des valeurs au Client. Ils constituent d'ailleurs la seule voie pour récupérer des informations de la procédure cataloguée. La syntaxe est (partiel):
stored_procedure= CREATE PROCEDURE procedure_name
...ooo...
[ RETURNS '(' parameter_name_and_type_list ')' ]
AS ...ooo... .
parameter_name_and_type_list= name_and_type { ',' name_and_type } .
|
Au niveau syntaxe: - les types de données sont les mêmes que les types des paramètres en entrée
- si notre procédure a pour but de fournir une Table de plusieurs lignes, elle
utilisera une instruction SELECT FOR, et les lignes contiendront comme colonnes celles définies par les paramètres en sortie
- il est possible d'utiliser comme identificateur le nom de colonnes de
certaines Tables. Dans les requêtes Sql, les identificateurs sont préfixés avec :
Et pour le fonctionnement: - nous récupérons la valeur des paramètres
- soit dans les tDataSet.Params pour les appels EXECUTE
- soit dans les colonnes d'une requête SELECT
Dans ce cas, il est IMPERATIF de placer une instruction SUSPEND après le calcul pour que la valeur des paramètres soit transmise à l'appelant
- ces variables sont similaires aux paramètres VAR Pascal:
- les valeurs affectées à ces variables est récupérée par l'appelant
En revanche, nous ne pouvons pas fournir à la procédure des valeurs à une
procédure dans un paramètre en sortie (alors qu'en Pascal, les paramètres VAR sont IN/OUT)
Voici une procédure qui calcule la somme et la moyenne des budgets des départements:
create procedure d_compute_budget
(
p_department_head char(3)
)
returns
(
pv_total_budget numeric(15, 2),
pv_average_budget numeric(15, 2)
)
as
begin
select sum(budget), avg(budget)
from department
where head_dept= : p_department_head
into : pv_total_budget, : pv_average_budget;
/* -- pour les appels SELECT */
suspend;
end
| et pour l'exécution - sélectionnez "select_department" pour visualiser les départements
- tapez un code de département
- cliquez "execproc" pour calculer les budgets et les placer dans les deux Edit
- cliquez "select_budget_" pour calculer les budgets et les afficher dans la dbGrid du pas
3.6 - Variables locales Comme en Pascal, nous déclarons des variables locales que nous pouvons
utiliser dans le corps de la procédure. Voici la grammaire (partiel):
stored_procedure= CREATE PROCEDURE procedure_name
...ooo...
AS [ local_variable_declaration_list ] compound_statement .
local_variable_declaration_list = declaration { declaration } .
declaration= DECLARE VARIABLE NAME data_type ';' .
compound_statement= ...ooo...
|
Voici une procédure qui
- augmente le plancher min
- force la valeur du salaire max à être exactement égal au nouveau salaire minimum que multiplie un coefficient
create procedure e_increase_salary
(
p_job_code varchar(5),
p_min_max_ratio float
)
as
declare variable l_min_salary float;
declare variable l_new_min_salary float;
declare variable l_new_max_salary float;
begin
select min_salary
from job
where job_code= : p_job_code
into : l_min_salary ;
l_new_min_salary= l_min_salary+ p_increase;
l_new_max_salary= l_new_min_salary* p_min_max_ratio;
update job
set
min_salary= : l_new_min_salary,
max_salary= : l_new_max_salary
where
job_code= : p_job_code;
end
|
et: - nous fournissons en entrée le code du job à ajuster et l'augmentation
- une instruction SELECT INTO récupère le salaire minimum actuel dans une variable locale
- cette variable sert à calculer les nouveaux salaires min et max
- une requête UPDATE met à jour la table
Voici un exemple d'exécution:
3.7 - Le corps de la procédure stockée 3.7.1 - Syntaxe Le seul point délicat concerne les points virgules:
3.7.2 - Instructions Les instructions possibles sont:
- l'instruction composée
- l'affectation
- les instructions de contrôle: IF WHILE
- les exceptions, l'appel d'événements
- pratiquement toutes les requêtes SQL
3.7.3 - Instruction composée BEGIN ... END Cette construction syntaxique est la même que celle de Pascal. Rappelons que le END n'est JAMAIS suivi de point virgule.
3.7.4 - L'affectation
L'affectation stocke une valeur dans une variable locale ou un paramètre. Voici la grammaire (partiel):
assignment= variable_name '=' search_condition .
search_condition= search_value { ( OR | AND ) search_condition } .
search_value= simple_value { arithmetic_operator simple_value } .
arithmetic_operator= '+' | '-' | '*' | '/' | '||' .
simple_value=
function_or_value
[ [ NOT ] ( between | like | in | compare | containing | starting )
| IS [ NOT ] NULL ]
| ( ALL | SOME | ANY ) '(' select_column_list ')'
| EXISTS '(' select_expression ')'
| SINGULAR '(' select_expression ')'
| '(' search_condition ')'
| NOT search_condition .
select_one_column= select .
select_column_list= select .
between= BETWEEN value_litteral AND value_litteral .
like= LIKE value_litteral [ ESCAPE value_litteral ].
in= IN '(' value_litteral { ',' value_litteral } | select_column_list ')' .
compare= compare_operator ( search_value | '(' select_one_column ')' ) .
compare_operator= '=' | '<' | '>' | '<=' | '>=' | '<>' .
containing= CONTAINING value_litteral .
starting= STARTING [ WITH ] value_litteral .
|
Au niveau syntaxe - le symbole d'affectation est =
3.7.5 - Le test: IF THEN ELSE Le fonctionnement est le même que celui de Pascal.
La grammaire est la suivante (partiel):
proc_if= IF '(' condition ')' THEN simple_or_compound_statement
[ ELSE simple_or_compound_statement ] .
compound_statement= BEGIN { compound_statement | statement [ ';' ] } END .
statement= assignment | test_loop_control
| exit_control | error_handling | ib_event
| sql_request | execute_procedure .
simple_or_compound_statement= compound_statement | statement [ ';' ] .
|
Au niveau syntaxe - la condition DOIT être entourée de ( et )
- si nous utilisons un ELSE, il doit être précédé de ;, sauf si le THEN
utilise une instruction composée BEGIN...END
Une nouvelle version de notre ajustement de salaire effectue un ajustement progressif des min / max - si 1.10 du max actuel dépasse la valeur visée, il est réduit de 10 %
- si 0.90 du max est sous la valeur visée, il est augmenté de 10 %
De plus la mise a jour n'est effectuée que si la nouvelle valeur est différente de la valeur actuelle.
create procedure f_adjust_max_job_salary
(
p_job_code varchar(5),
p_min_max_ratio float
)
as
declare variable l_min_salary float;
declare variable l_max_salary float;
declare variable l_target_max_salary float;
declare variable l_new_max_salary float;
begin
select min_salary, max_salary
from job
where job_code= : p_job_code
into : l_min_salary, : l_max_salary ;
l_target_max_salary= l_min_salary* p_min_max_ratio;
l_new_max_salary= 0;
if ( l_max_salary> 1.10* l_target_max_salary )
then l_new_max_salary= 0.90 * l_max_salary ;
else
if ( l_max_salary< 0.90* l_target_max_salary )
then l_new_max_salary= 1.10* l_max_salary ;
if (l_new_max_salary <> 0)
then
update job
set max_salary= : l_new_max_salary
where job_code= : p_job_code;
end
|
Voici un exemple d'exécution: 
3.7.6 - La répétition: WHILE DO Le fonctionnement est le même qu'en Pascal. Quant à la grammaire (partiel):
proc_while= WHILE '(' condition ')' DO simple_or_compound_statement .
|
Voici un exemple où nous calculons les 3 salaires les plus élevés: - nous récupérons le salaire le plus élevé par MAX
- nous définissons un salaire légèrement inférieur à ce salaire le plus élevé, et, dans une boucle, nous comptons le nombre de personnes ayant un salaire supérieur à ce plancher, en diminuant ce plancher dans chaque itération, et
en quittant lorsque nous avons atteint le nombre de personne visé
alter procedure g_sum_highest_salaries
(
p_count integer
)
returns
(
pv_sum_highest float
)
as
declare variable l_highest_salary float;
declare variable l_count integer;
declare variable l_threshold_salary float;
begin
select max(salary)
from employee
into : l_highest_salary ;
l_count= 0;
l_threshold_salary= l_highest_salary- 100000;
while (l_count< p_count) do
begin
select count(*), sum(salary)
from employee
where salary> : l_threshold_salary
order by salary descending
into : l_count, : pv_sum_highest ;
l_threshold_salary= l_threshold_salary- 100000;
end
suspend;
end
|
Voici un exemple d'exécution:
Notez que: - la procédure est plus que maladroite, et même erronnée dans certains cas (si
les trois salaires les plus élevés diffèrent de moins de notre incrément de calcul)
- en fait cela illustre bien la difficulté à trouver un "bon" exemple de WHILE: à part effectuer des concaténations d'espaces, nous n'avons pas
trouvé tellement d'exemples simples utilisant la répétition. Cela provient en partie de l'absence de tableaux en tant que variable locale ou paramètre (bien sûr nous aurions pu utiliser des tableaux InterBase)
- et pour trouver les salaires les plus élevés, nous pouvons utiliser au moins deux autres techniques: FOR SELECT ou ROW
3.7.7 - Requêtes SQL de modification Les requêtes qui modifient des données (INSERT INTO, UPDATE, CREATE) n'offrent pas de difficultés. Nous en avons rencontré plusieurs ci-dessus.
Une procédure peut effectuer autant de telles requêtes que nécessaire (nos exemples ne comportent que peu de requêtes, pour simplifier la présentation).
3.7.8 - Lecture d'une ligne: SELECT INTO
Pour lire des données de Tables SQL, nous utilisons SELECT en spécifiant dans la clause INTO dans quelle données placées les valeurs des colonnes récupérées.
Les identificateurs placés après INTO peuvent être: - des paramètre en sortie
- des variables locales
Voici la grammaire (partiel):
sp_select= select [ into_clause ] .
select= SELECT
column_clause
FROM from_table_reference { ',' from_table_reference }
[ WHERE search_condition ]
[ GROUP BY column_name
[ COLLATE collation_name ]
{ ',' column_name [ COLLATE collation_name ] } ]
[ HAVING search_condition ]
[ UNION select_expression [ ALL ] ]
[ ORDER BY order_list ] .
into_clause= INTO output_parameter { ',' output_parameter } .
output_parameter= ':' NAME .
|
Notez que
- nous ne pouvons pas utiliser des requêtes paramétrées à l'intérieur de procédures cataloguées
3.7.9 - FOR SELECT Cette instruction permet de fournir un ensemble de lignes. Ces lignes sont
récupérées par un appel SELECT. Voici la grammaire (partiel):
for_select= FOR sp_select DO simple_or_compound_statement .
|
Voici un exemple simple où nous récupérons les salaires d'un département triés dans un ordre décroissant en affichant leur somme cumulée:
create procedure h_decreasing_deparment_salary
(
p_department_code char(3)
)
returns
(
pv_last_name varchar(20),
pv_salary float,
pv_cumulated_salary float
)
as
begin
pv_cumulated_salary= 0;
for
select last_name, salary
from employee
where dept_no= : p_department_code
order by salary descending
into : pv_last_name, : pv_salary
do
begin
pv_cumulated_salary= pv_cumulated_salary+ pv_salary;
suspend;
end
end
|
Voici un exemple d'exécution:  Notez que:
- SUSPEND ne doit normalement figurer que dans les procédures appelées par SELECT.
- l'explication officielle (LANGREF.PDF p 169) est que:
- "SUSPEND bloque l'exécution de SELECT en attendant que l'appelant
lise la ligne courante"
- si la procédure est appelée par ExecProc, SUSPEND saute à la fin de la procédure
- si la procédure contient des instructions après le dernier SUSPEND,
elles seront exécutées (mais les valeurs ne seront pas transmises à l'appelant)
3.7.10 - EXIT Nous utilisons EXIT pour quitter la procédure. Les instructions situées après EXIT ne seront pas exécutées.
La différence entre EXIT et SUSPEND est la suivante: - EXIT est utilisé en général dans les procédures appelées par ExecProc
- SUSPEND figure dans les procédures appelées par SELECT.
Voici un exemple de calcul des n salaires les plus élevés: - nous effecutons un FOR SELECT pour trouver toutes les lignes
- à chaque tour dans la boucle, nous exécutons SUSPEND pour que les valeurs
soient accessibles par l'utilisateur qui utilise SELECT
- si le nombre de ligne analysée dépasse n, nous sortons brutalement par EXIT
create procedure i_top_salaries
(
p_num_records integer
)
returns
(
pv_last_name varchar(20), pv_first_name varchar(15),
pv_dept_no char(3), pv_salary numeric(15, 2)
)
as
declare variable l_count int;
begin
l_count= 0;
for
select last_name, first_name, dept_no, salary
from employee
order by salary desc
into : pv_last_name, : pv_first_name, : pv_dept_no,
: pv_salary
do
begin
l_count= l_count+ 1;
if (l_count> p_num_records)
then
exit;
suspend;
end
end
|
Voici un exemple d'exécution:  Notez que:
3.7.11 - EXECUTE PROCEDURE
Dans une procédure, nous pouvons exécuter une autre procédure en appelant EXECUTE PROCEDURE La grammaire est (partiel):
execute_procedure= EXECUTE PROCEDURE NAME [ input_parameters ]
[ RETURNING_VALUES output_parameters ] .
parameter_name= ':' NAME .
input_parameters= parameter_name { ',' parameter_name } .
output_parameters= parameter_name { ',' parameter_name } .
|
Voici un exemple qui calcule le budget d'un département. Les départements sont organisés en hiérarchie. Par exemple le département "100 Sales and Marketing" a
un sous-département "140 Field Office Canada - head= 100. La liaison se fait par la colonne HEAD_DEPT qui est égale au DEPT_NO du département englobant.
Pour calculer le budget, nous créons une procédure
j_compute_department_budget(p_department, var pv_budget) et pour calculer le budget d'un département et ses sous département: - nous initialisons le cumul avec le budget du département
- nous récupérons la liste des sous-département
- pour chaque sous-département, nous appelons j_compute_department_budget qui nous retourne son budget. Ce budget est additionné au budget précédent
Dans la procédure j_compute_department_budget nous appelons donc une autre procédure, qui dans ce cas particulier, se trouve être la même procédure (appel récursif). Voici le texte de la procédure:
create procedure j_compute_department_budget
(
p_department_code char(3)
)
returns
(
pv_total_budget numeric(15, 2)
)
as
declare variable l_sub_department_count integer;
declare variable l_sub_department_code char(3);
declare variable l_sub_department_budget decimal(12, 2);
begin
pv_total_budget= 0;
select budget
from department
where dept_no= : p_department_code
into : pv_total_budget;
/* -- how many sub deparments ? */
select count(budget)
from department
where head_dept= : p_department_code
into : l_sub_department_count;
if (l_sub_department_count= 0)
then
suspend;
/* -- add the sub department budgets */
for
select dept_no
from department
where head_dept= : p_department_code
into : l_sub_department_code
do
begin
execute procedure j_compute_department_budget : l_sub_department_code
returning_values : l_sub_department_budget;
pv_total_budget= pv_total_budget+ l_sub_department_budget;
end
suspend;
end
|
Voici un exemple d'exécution: 
3.7.12 - Procédures contenant SELECT procédure
Nous avons vu ci-dessus comment appeler une procédure ExecProc. Nous pouvons aussi appeler une procédure SELECT. La grammaire est la même que pour SELECT INTO, sauf le le nom de table
est remplacé par le nom de la procédure.
Voici un exemple:
create procedure k_sub_department_tree
(
p_parent_department_code char(3),
p_parent_level integer
)
returns
(
pv_department_name varchar(25),
pv_department_code char(3),
pv_parent_department_code char(3),
pv_department_level integer,
pv_padded_name varchar(50)
)
as
declare variable l_indentation varchar(25);
declare variable l_level integer;
begin
/* -- select the first level deparments */
for
select department, dept_no, head_dept
from department
where head_dept = : p_parent_department_code
into : pv_department_name, : pv_department_code,
: pv_parent_department_code
do
begin
pv_department_level = p_parent_level + 1;
l_indentation = '';
l_level = 0;
while (l_level < pv_department_level) do
begin
l_level = l_level + 1;
l_indentation = l_indentation || ' ';
end
pv_padded_name = l_indentation || pv_department_name;
/* -- return the row */
suspend;
/* -- recurse on the children */
for
select pv_department_name, pv_department_code,
pv_parent_department_code, pv_department_level
from k_sub_department_tree(: pv_department_code, : pv_department_level)
into : pv_department_name, : pv_department_code,
: pv_parent_department_code, : pv_department_level
do
begin
/* -- child row */
l_indentation = '';
l_level = 0;
while (l_level < pv_department_level) do
begin
l_level = l_level + 1;
l_indentation = l_indentation || ' ';
end
pv_padded_name = l_indentation || pv_department_name;
/* -- return the row */
suspend;
end
end
end
|
Voici un exemple d'exécution:  Et - vous remarquerez que dans la tDbGrid du bas, colonne INDENT, "SoftWare
Development" est indenté par rapport à son parent "Software"
- cependant le département choisi (600) n'est pas affiché: notre procédure n'affiche que les sous-départements. Pour afficher le département lui-même,
il faut écrire une procédure qui affiche ce département et ensuite ses sous-départements. Vous pouvez examiner la procédure et la lancer en utilisant l'onglet "rec_2_".
3.8 - Procédure générique
Notre application permet de tester les procédures que nous avons prévues. Pour pouvoir créer, modifier, supprimer, exécuter (ExecProc ou SELECT),
nous avons ajouté un onglet "any_". Supposons que nous souhaitions lister les employés affecté à un projet:
create procedure x_get_project_employees
(
p_project_id char(5)
)
returns
(
pv_employee_no smallint,
pv_employee_last_name varchar(20)
)
as
begin
for
select emp_no, last_name
from employee_project p, employee e
where
(proj_id= : p_project_id)
and
(p.emp_no= e.emp_no)
into : pv_employee_no, : pv_employee_last_name
do
suspend;
end
|
Pour cela:
4 - Le Projet Delphi Cet article étant dédié aux procédures cataloguées, nous n'allons pas détailler le code Delphi des deux projets utilisés. Mentionnons toutefois que:
- les extractions de noms de Tables et de procédures, ainsi que l'ouverture de tIbDatabase, tIbQuery et l'exécution de tIbSql ont utilisé les routines utilitaires de U_IBX
- cette unité contient de plus en plus de procédures et fonctions que nous utilisons dans nos applications InterBase. Ce qui manque toutefois, c'est une application générale qui nous permet d'explorer une base quelconque.
Pour cela, nous pouvons utiliser les explorateurs fournis avec Delphi, ou IbConsole, dédié à InterBase. Pour éviter d'avoir à taper sempiternellement notre mot de passe, nous avons
créer notre propre explorateur, ColIbEx, que nous présenterons dans un prochain article.
5 - Télécharger le code source Delphi Vous pouvez télécharger:
- ib_sp_tutorial.zip ib_sp_tutorial.zip: le premier projet (29 K)
- ib_sp_examples.zip ib_sp_examples.zip: les 12 exemples et l'outil de
démonstration et de test de procédures cataloguées (36 K)
- ib_stored_procedure_database.zip ib_stored_procedure_database.zip: la
base de données (version 6) et les scripts de création du schéma et des données (104 K)
Ce .ZIP qui comprend: - le .DPR, la forme principale, les formes annexes eventuelles
- les fichiers de paramètres (le schéma et le batch de création)
- dans chaque .ZIP, toutes les librairies nécessaires à chaque projet (chaque .ZIP est autonaume)
Ces .ZIP, pour les projets en Delphi 6, contiennent des chemins RELATIFS. Par conséquent: - créez un répertoire n'importe où sur votre machine
- placez le .ZIP dans ce répertoire
- dézippez et les sous-répertoires nécessaires seront créés
- compilez et exécutez
Ces .ZIP ne modifient pas votre PC (pas de changement de la Base de Registre, de DLL ou autre). Pour supprimer le projet, effacez le répertoire.
La notation utilisée est la notation alsacienne qui consiste à préfixer les identificateurs par la zone de compilation: K_onstant, T_ype, G_lobal,
L_ocal, P_arametre, F_unction, C_lasse. Elle est présentée plus en détail dans l'article La
Notation Alsacienne
Comme d'habitude: - nous vous remercions de nous signaler toute erreur, inexactitude ou
problème de téléchargement en envoyant un e-mail à jcolibri@jcolibri.com. Les corrections qui en résulteront pourront aider les prochains lecteurs
- tous vos commentaires, remarques, questions, critiques, suggestion d'article, ou mentions d'autres sources sur le même sujet seront de même les bienvenus à jcolibri@jcolibri.com.
- plus simplement, vous pouvez taper (anonymement ou en fournissant votre e-mail pour une réponse) vos commentaires ci-dessus et nous les envoyer en cliquant "envoyer" :
- et si vous avez apprécié cet article, faites connaître notre site, ajoutez un lien dans vos listes de liens ou citez-nous dans vos
blogs ou réponses sur les messageries. C'est très simple: plus nous aurons de visiteurs et de références Google, plus nous écrirons d'articles.
6 - Références - Suggestions Quelques références externes:
- les manuels livrés avec InterBase ou téléchargeables (cf Google) sont la meilleure documentation de référence sur les procédures cataloguées. En particulier
- LANGREF.PDF avec un chapitre complet sur les stored procedures
- The Firebird Book, Helen BORRIE - ISBN 1 59059 279 4 qui contient deux chapitres sur les procédures cataloguées (mais en InterBase pur, pas de Delphi)
- plusieurs articles ont été publiés:
Vous pouvez aussi consulter sur ce site d'autres articles concernant InterBase: - des tutoriels pour utiliser les différents composants d'accès:
- des présentations sur les blobs
- sur la gestion des scripts
- ou les procédures externes
- UDF InterBase : comment écrire des fonctions Delphi qui seront appelées par des requêtes SQL ou des procédures cataloguées InterBase. Projet complet avec tests à toutes les étapes
Nous intervenons chez nos clients pour réaliser des développements utilisant InterBase, ou apporter des soutiens plus ponctuels. Finalement j'anime personnellement à l'Institut Pascal diverses formations
sur les bases de données portant sur, ou utilisant InterBase: - des formations sur InterBase :
- des formations utilisant InterBase comme moteur SQL :
7 - L'auteur
John COLIBRI est passionné par le développement Delphi et les applications de Bases de Données. Il a écrit de nombreux livres et articles, et partage son temps entre le développement de projets (nouveaux projets, maintenance, audit, migration BDE, migration Xe_n, refactoring) pour ses clients, le
conseil (composants, architecture, test) et la
formation. Son site contient des articles
avec code source, ainsi que le programme et le calendrier des stages de formation Delphi, base de données, programmation objet, Services Web, Tcp/Ip et
UML qu'il anime personellement tous les mois, à Paris, en province ou sur site client.
|
