Procédure Stockée

Version imprimable

30/05/2003, 17h23
brisky

Procédure Stockée

Je suis un nouvel utilisateur de Interbase.
J'aimerai savoir comment executer une procédure stockée avec un corps dynamique comme sous SQL Server.
Voici par exemple le code que je tape sous SQL server:

Create procedure usp_Card_Select(@Id Int=NULL,@Name varchar=NULL)
AS
declare @Query as varchar(500)

set @Query='select Car_Id, Car_Name, Car_Amount from Card '
set @Qeury=@Query + 'Where Car_State=1 '

if not @Id is null set @Query=@Query + ' and Id=' + @Id
if not @Name is null set @Query=@Query + ' and Car_Name=' + @Name

exec @Query
Go

ici je déclare une chaine qui contient la requete SQL à executer.
Cette chaine change en fonction de la valeur du paramète @Id et/ou du paramètre @Name.
Si un paramètre est défini, la requète change en fonction de ce paramètres. Si aucun paramètre n'est défini on renvoi tous les enregistrements.

Enfin on execute la chaine SQL.

Merci pour votre contribution.

Brisky

Re,
Il te suffit d'adapter la procédure que je t'ai donné dans le forum Delphi et SGBD...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SET TERM !! ; 
CREATE PROCEDURE usp_Card_Select(ID INTEGER, Nom varchar(50)) 
RETURNS (Car_id integer, Car_Name varchar(50), Car_Amount decimal(10,2)) 
AS 
BEGIN 
  if ((:ID is NULL) and (:Nom is null)) then 
    FOR SELECT Car_id, Car_Name, Car_Amount from Card where Car_State=1
      INTO :Car_id, :Car_Name, :Car_Amount
    DO 
      suspend; 
  else 
  if (:ID is NULL) then
    FOR SELECT Car_id, Car_Name, Car_Amount from Card where Car_State=1 and Car_Name=:Nom
      INTO :Car_id, :Car_Name, :Car_Amount
    DO 
      suspend; 
  else
  begin 
    SELECT Car_id, Car_Name, Car_Amount FROM Card where ID=:ID 
      INTO :Car_id, :Car_Name, :Car_Amount; 
    SUSPEND; 
  end 
END !! 
SET TERM ; !!

Encore un fois je le rappel j'ai considéré que ID est la clé primaire de la table Car...

30/05/2003, 18h10
brisky

Si je comprend bien, avec Interbase il faut définir chaque branche séparement. Ceci dit j'obtiendrai une procédure monstrueuse si le nombre de paramètres devient important. Pour info, la procédure en question utilise 7 paramètres différents qui permettent de filtrer les données selon qu'il soient définis ou non.

Merci pour votre éclairage.

Brisky
30/05/2003, 18h44
Barbibulle

Arff
A ma connaissance on ne peux pas faire de SQL Dynamique dans une procédure stockée. Mais je ne suis pas expert...

En effet si tu as 7 paramétres (6 plus un qui represente la clé primaire) ca fait 6^2+1 = 65 possibilitées... Un peu chiant à développer...

Tu as une autre alternative qui consiste à faire du SQLDynamique via le langage de programmation que tu vas utiliser pour interroger la base.
Ceci dis il faut savoir qu'en terme de performance ca risque d'être moins bon que la procédure stoquée...(A tester...) mais du point de vue maintenance la solution de la procédure stockée risque de vite devenir ingérable (notamment si l'on doit ajouter un paramètre...)

Bref à ta place, je ferai du SQLDynamique via le langage qui me sert à interroger la base (Delphi dans ton cas...)
19/10/2004, 13h41
iguanevif

On ne peut pas avec firebird???
J'ai cru voir que l'on pouvait le faire avec la commande execute statement... Mais pas sur du tout
19/10/2004, 15h12
Andry

Citation:

Envoyé par brisky

Si je comprend bien, avec Interbase il faut définir chaque branche séparement. Ceci dit j'obtiendrai une procédure monstrueuse si le nombre de paramètres devient important. Pour info, la procédure en question utilise 7 paramètres différents qui permettent de filtrer les données selon qu'il soient définis ou non.

Merci pour votre éclairage.

Brisky

Bienvenue dans le club l'ami,
J'avais créer une procedure stocké qui prends 4 params, je t'assure que c'est chiants à faire mais je me suis tapé 16 bloc de if end.
Sinon, tu as tjs le choix de le faire via Delphi, comme Barbibulle te l'a dit.

Courage
19/10/2004, 16h21
Barbibulle

Citation:

Envoyé par iguanevif

On ne peut pas avec firebird???
J'ai cru voir que l'on pouvait le faire avec la commande execute statement... Mais pas sur du tout

Le message est vieux de plus d'un an et demi. Firebird 1.5 n'existait pas.
Et donc sous Interbase 6 ou firebird 1.0 il était impossible de faire des requetes dynamiques.

Et en effet avec execute statement sous firebird1.5 on peut faire un peu de SQL dynamique (Il faut que les parametres de retours soient toujours en même nombre et de même type).

Je n'ai pas encore testé cette nouveauté de firebird 1.5, et c'est vrai que j'ai du coup tendance à en oublier l'existance.
19/10/2004, 16h50
iguanevif

Dsl barbibulle, je ne disaiis pas du tout ca pour toi. :oops:

Je faisais une recherche sur le forum sur le SQL dynamique,
je suis tombé dessus et j'ai répondu sans voir la date.

Peut on avoir des parametres d'entrée avec le execute statement, qui me serviraient dans les clauses where?

Citation:

Envoyé par iguanevif

Dsl barbibulle, je ne disaiis pas du tout ca pour toi. :oops:

Tu n'as pas à t'excuser, je remettais juste ce fil de discution dans son contexte.

Citation:

Envoyé par iguanevif

Peut on avoir des parametres d'entrée avec le execute statement, qui me serviraient dans les clauses where?

Le execute statement s'emploi de 3 façons :

Soit la requete ne revoie pas de données :
Code:

1 2 REQUETE = 'Delete from Matable where ID=10'; Execute statement REQUETE;
Soit elle renvoit un enregistrement :
Code:

1 2 REQUETE = 'Select Macol1, Macol2 from Matable where ID=10'; Execute statement REQUETE INTO :MACOL1, :MACOL2;
soit elle renvoit un ensemble d'enregistrement :
Code:

1 2 3 4 REQUETE = 'Select Macol1, Macol2 from Matable where ID>26; FOR Execute statement REQUETE INTO :MACOL1, :MACOL2 DO ...
Et REQUETE peut être un parametre d'entrée de la PS ou une variable locale qui est constituée dans la PS.

Par exemple :
Param1 est un parametre d'entré de ma PS de type integer et param2 de type varchar(10)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 REQUETE = 'Select MaCol1, MaCol2 from Matable where 1'; if (Param1 is not null) then REQUETE = REQUETE || ' and MaCol3=' || param1; if (Param2 is not null) then REQUETE = REQUETE || ' and MaCol4=''' || param2 || ''''; FOR Execute statement REQUETE INTO :MACOL1, :MACOL2 DO SUSPEND;
Voilà donc si j'appel la PS avec Param1 et Param2 à null tous les enregistrements de Matable seront renvoyés.

Voilà ce qui ouvre pas mal de possibilités.

Citation:
Envoyé par Barbibulle
Et REQUETE peut être un parametre d'entrée de la PS ou une variable locale qui est constituée dans la PS.

Par exemple :
Param1 est un parametre d'entré de ma PS de type integer et param2 de type varchar(10)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 REQUETE = 'Select MaCol1, MaCol2 from Matable where 1'; if (Param1 is not null) then REQUETE = REQUETE || ' and MaCol3=' || param1; if (Param2 is not null) then REQUETE = REQUETE || ' and MaCol4=''' || param2 || ''''; FOR Execute statement REQUETE INTO :MACOL1, :MACOL2 DO SUSPEND;
Voilà donc si j'appel la PS avec Param1 et Param2 à null tous les enregistrements de Matable seront renvoyés.

Voilà ce qui ouvre pas mal de possibilités.
Merci beaucoup barbibulle, c'est exactement ce que je cherchais a faire...
Je dois le faire sur une base oracle et sur firebird.
J'avais parié à mes collegues que firebird pouvait le faire....

Merci encore

19/10/2004, 19h02
Barbibulle

Citation:

Envoyé par iguanevif

Merci beaucoup barbibulle, c'est exactement ce que je cherchais a faire...
Je dois le faire sur une base oracle et sur firebird.
J'avais parié à mes collegues que firebird pouvait le faire....

Merci encore

A quelques moins près vous perdiez votre pari :lol:
20/10/2004, 07h58
Andry

Merci à vous tous pour ces infos.
Mais dois je migrer vers Firebird pour avoir cette fonctionnalité, parce que c'est quand même plus souple la requête dynamique que le codage que je fais actuellement avec Interbase 6 open edition.
Comme je viens de lire qu'il n'y a pas de risque ,je vous tiens au courant de l'evolution des choses.

A+
20/10/2004, 14h34
J-P-B

Salut

Sinon, petite info:
moi j'utilise Interbase et j'étais confronté au même problème ...

- j'utilise une sous procédure pour la requete principale (un curseur) à qui je passe tous les paramètres.

- Dans le curseur, je ne double que les paramètres qui mettent en jeu les index, pour les autres, je fais des OR:
admettons que le champ IDMARQUE ne soit pas indexé dans la table ARTICLE :

Code:

select NOM_ARTICLE from ARTICLE where (IDMARQUE = :MARQUE or :MARQUE = -1)

Sino, dès que tous mes clients seront sous Firebird, ben ce sera super !
20/10/2004, 15h26
Barbibulle

Citation:

Envoyé par J-P-B

Code:

select NOM_ARTICLE from ARTICLE where (IDMARQUE = :MARQUE or :MARQUE = -1)

excellente astuce :bravo:

La seule contrainte c'est qu'on ne peux rechercher un IDARTICLE=-1. Donc faut prendre une valeur qui n'existera jamais dans la colonne.

Mais sur le même principe on doit pouvoir faire :

Code:

select NOM_ARTICLE from ARTICLE where (IDMARQUE = :MARQUE or :MARQUE is null)

Si mon parametre MARQUE est à null la recherche ne prend pas en compte le critère IDMARQUE.

On a bien entendu la même contrainte on ne peux rechercher les IDMARQUE qui sont null. (mais si la colonne est NOT NULL il ne peux pas y en avoir et donc c'est la solution à adopter !)

Merci J-P-B
22/10/2004, 08h36
Andry

Salut à tous,

Je me suis donc migrer vers Firebid 1.5.1.
C'est vrai que j'ai pratiquement rien fais au niveau de mon système.
Tous les autres appli fonctionnent.
Seulement voilà, j'aimerais savoir la performance au niveau des requête dynamiques.
Car la plupart de mes procedures stockés vont en dependre.

Merci
22/10/2004, 09h46
Barbibulle

Et bien vue que c'est dynamique, le moteur d'interbase ne peut pas préparer la requete, et donc elles vont être un peu moins performantes.

Un PS avec que des ordres SQL statiques est compilé et l'ordre est préparé, ainsi lors de l'exécution le moteur à moins de chose à faire.

Mais bon il faut relativiser aussi et ne pas s'affoler pour rien, la baisse de performance n'est pas ennorme à mon avis. Suivant la requete et son temps d'exécution, il est possible que cette baisse de performance ne soit même pas mesurable ou du moins est non significatif.

Mais pour le vérifier rien de mieux que l'analyse de performance sur un jeux de données représentatif.

Ah oui dernière chose : Firebird 1.5 a été revue entièrement et optimisé, il est donc plus rapide d'interbase 6.0, donc même si vous perdez un peu en faisant du dynamique à mon avis c'est largement ratrapé par votre passage à firebird.