|
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</ | |
