[Oracle 9i] Bind variables et sql dynamique

**jld_33** · 08/06/2006, 14h44

Bonjour,
nous utilisons du sql dynamique pour la construction de nos requetes dans des procédures et des packages oracles.
Suite à la lecture de plusieurs articles, dont celui ci
http://www.rittman.net/archives/000832.html
nous désirons utiliser les variables "bindées" afin d'amélirorer les performances globales.
Ci dessous un exemple simpliste de ce que nous faisons et de ce que nous voudrions faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE PROCEDURE exemple(
 pi_id_user  IN	NUMBER,
 cr_cursor OUT Cr_Rpt_Cursors.cr_rpt_cursor   -- type REF CURSOR
)
AS
 l_qry VARCHAR2(2000);
BEGIN
 
/* ancienne méthode sans utilisation de bind variables
l_qry :='SELECT LOGIN FROM T_USERS where ID_USER=' || pi_id_user;
   OPEN cr_cursor FOR l_qry ;
*/
 
 
--nouvelle méthode 
l_qry :='SELECT LOGIN FROM T_USERS where ID_USER=:bind_id_user';
   OPEN cr_cursor FOR l_qry USING pi_id_user ;
 
 END;

ceci marche très bien,
Seul GROS problème, c'est exemple est trop simple et en fait la génération de notre chaine sql dynamique est plutot du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE PROCEDURE exemple(
 pi_id_user  IN	NUMBER,
 pi_param1  IN	NUMBER,
 pi_param2  IN	NUMBER,
 cr_cursor   OUT Cr_Rpt_Cursors.cr_rpt_cursor   -- type REF CURSOR
)
AS
 l_qry VARCHAR2(2000);
BEGIN
 
l_qry :='SELECT LOGIN FROM T_USERS where 1=1';
 
IF pi_id_user  != -1 THEN
	l_qry:=l_qry || ' AND ID_USER=:bind_pi_id_user';
END IF;
 
IF pi_param1  != -1 THEN
	l_qry:=l_qry || ' AND ID_PAYS = :bind_pi_param1';
END IF;
 
IF pi_param2  != -1 THEN
	l_qry:=l_qry || ' AND ID_VILLE = :bind_pi_param2';
END IF;
 
--[...]
 
-- impossible de faire le bind ci dessous car je ne suis pas sûr d'avoir une 1,2 ou 3 variable à binder
  OPEN cr_cursor FOR l_qry USING pi_id_user, pi_param1 ,pi_param2 ;
 
 END;

J'ai un probleme dans la clause USING ci dessus car je ne suis pas sûr d'avoir une 1,2 ou 3 variable à passer (puisque la chaine générée dépend des valeurs des parametres)

Il faudrait en fait que je puisse renseigner les variables :bind_pi_param1, :bind_pi_param2 au moment de la création de la chaine (dans le if)

j'ai essayé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

:bind_pi_param2:=pi_param2

mais le compilateur n'en veut pas

Si je met directement dans la chaine générée :pi_param2 le message est au moment de l'execution: ORA-01008: not all variables bound

J'ai bien vu la possibilité d'utiliser le package dbms_sql , mais la syntaxe est beaucoup plus lourde et serait plus lente dixit :
http://sheikyerbouti.developpez.com/execute_immediate/

Impossible de trouver sur le net d'exemples correspondant à mon pb.
Soit je suis passé completement a coté et c'est tellement simple que personne n'a eu l'idée de poser la question, soit j'ai mal cherché (dans ces 2 cas désolé de la question

), soit il n'y a pas de solution...mais ça m'étonne quand même

soit la vérité est ailleur, mais dans ce cas.. OU..?

Merci pour toute aide ou conseil pour résoudre ce problème (en espérant ne pas avoir été trop long dans la description )

Pour info, outils utilisés
[Oracle9i Enterprise Edition Release 9.2.0.2.0]
[TOAD 8.5.3.2]

**orafrance** · 08/06/2006, 14h50

je ne comprends pas ce que tu veux au bout du compte... tu veux pas donner la requête "en clair" pour chacun des cas ?

**jld_33** · 08/06/2006, 15h21

Hum, un petit copié collé malhencontreux a obscurci l'exemple (je viens de rééditer le précédent post

Sinon l'idée est la suivante:
Si on a passé un identifiant user différent de -1 alors on filtre sur l'id_user
et/ou si on a passé un identifiant de ville différent de -1 on filtre sur la ville
et/ou si on a passé un identifiant de pays différent de -1 on filtre sur le pays
...

Résultat la requete générée dans la chaine l_qry peut être:
SELECT LOGIN FROM T_USERS where 1=1
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_USER=:bind_pi_id_user
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_USER=:bind_pi_id_user
AND ID_PAYS = :bind_pi_param1
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_USER=:bind_pi_id_user
AND ID_PAYS = :bind_pi_param1
AND ID_VILLE = :bind_pi_param2
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_PAYS = :bind_pi_param1
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_PAYS = :bind_pi_param1
AND ID_VILLE = :bind_pi_param2
etc....

Le probleme est qu'il y a donc un nombre variable de variable à binder et
je ne peut pas faire
OPEN cr_cursor FOR l_qry USING pi_id_user, pi_param1 ,pi_param2 ;
car selon les cas, il faudrait:
OPEN cr_cursor FOR l_qry ;
ou
OPEN cr_cursor FOR l_qry USING pi_id_user
ou
OPEN cr_cursor FOR l_qry USING pi_id_user, pi_param1 ;
ou
OPEN cr_cursor FOR l_qry USING pi_id_user, pi_param1 ,pi_param2 ;
ou
OPEN cr_cursor FOR l_qry USING pi_param1 ;
ou
OPEN cr_cursor FOR l_qry USING pi_param1 ,pi_param2 ;

etc...

Le nombre de combinaisosn possible augmentant vite avec le nombre de parametre, je ne peux pas vraiment mettre tous ces "OPEN cr_cursor FOR" dans des if imbriqués ...

et ce que c'est plus clair ?

**PpPool** · 08/06/2006, 15h23

Bonjour

il faut que tu ajoutes tes blocs if EGALEMENT au niveau de l'ouverture de l'OPEN CURSOR

dans la préparation de ta chaine SQL, il te faut simplement avoir recours à :1, :2,... etc..

j'ai déjà eut à gérer ce genre de pb, et cela est passé ainsi !

@

**SheikYerbouti** · 08/06/2006, 16h02

Vous pouvez vous en sortir avec une seule syntaxe d'ordre select prévoyant tous les cas.
Voici un exmple simple avec la table EMP:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
DECLARE
  l VARCHAR2(200) := 'select count(*) from emp where (deptno = :1 or :1 is null) and (ename = :2 or :2 is null )' ;  
  vide VARCHAR2(1) := '' ;
  ret PLS_INTEGER ;
BEGIN
  EXECUTE IMMEDIATE l INTO ret USING 10,10,vide,vide ;
  dbms_output.put_line('ret= ' || ret);
  EXECUTE IMMEDIATE l INTO ret USING 10,10,'CLARK','CLARK' ;
  dbms_output.put_line('ret= ' || ret);  
END;

**jld_33** · 08/06/2006, 16h28

Envoyé par SheikYerbouti

Vous pouvez vous en sortir avec une seule syntaxe d'ordre select prévoyant tous les cas.
Voici un exmple simple avec la table EMP:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
DECLARE
  l VARCHAR2(200) := 'select count(*) from emp where (deptno = :1 or :1 is null) and (ename = :2 or :2 is null )' ;  
  vide VARCHAR2(1) := '' ;
  ret PLS_INTEGER ;
BEGIN
  EXECUTE IMMEDIATE l INTO ret USING 10,10,vide,vide ;
  dbms_output.put_line('ret= ' || ret);
  EXECUTE IMMEDIATE l INTO ret USING 10,10,'CLARK','CLARK' ;
  dbms_output.put_line('ret= ' || ret);  
END;

hélas on y a pensé mais sauf erreur de ma part, si on fait ça, plus d'utilisation des index.

De plus on profitait de la construction dynamique pour n'ajouter certaines tables que quand c'etait vraiment nécessaire, donc j'ai peur que si on fasse ça, a l'arrivée on perde plus de temps et de ressources qu'on en aura gagné...

**SheikYerbouti** · 08/06/2006, 16h33

Non, car :1 représente la bind variable, pas le nom de la colonne. donc les index seront naturellement utilisés.

[Oracle 9i] Bind variables et sql dynamique

Oracle

Vue hybride

Discussions similaires

Partager

Partager