Discussion :

[Yasso44]

Bonjour à tous,

Alors voilà j'ai fait le tour du site sans pouvoir trouver une solution a mon problème et je m'en remet à vous.

Le contexte : Je suis assez débutant en PL/SQL. Je fais tourner une base sous oracle 10g XE et je dois réaliser une vue qui prend un paramètre dans le cadre d'un TP. Sinon je travaille aussi avec SQLDeveloper.

Alors voilà, j'ai mis en place une base de données qui gère des Dragons et des Chevaliers et des combats entre eux (si, si c'est vrai !).

Je dois définir une vue permettant à chaque chevalier de consulter des informations sur les dragons qu'il combat.

J'ai proprement fait le SELECT qui me ramène les bonnes informations. Cependant ma requête fonctionne pour un chevalier que l'on écrit "en dur" dans une clause WHERE.
(Ici pour le chevalier Lancelot :

Code :

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

1
2
3
SELECT Dragon,Sexe,Longueur,Nombreecailles,Crachedufeu, Comportementamoureux, AIME.dragonaimé, AIME.force 
From DRAGONS, AIME 
Where (DRAGONS.Dragon = ANY (Select Dragon From COMBAT Where Chevalier = 'Lancelot')  AND AIME.DragonAimant = DRAGONS.Dragon);

) Le problème n'est pas sur le SELECT donc...

Mais ce que j'aimerai c'est créer cette vue en fonction du chevalier qu'on passera en paramètre.

J'ai trouvé sur la FAQ une solution passée par Fred_D ici : http://oracle.developpez.com/faq/?page=3-2#viewparam

J'ai tout fait comme expliqué (j'ai fait un package appelé chevalier) puis définit ma vue comme expliqué.

Mais là ou ça coince c'est quand je souhaite initialiser le paramètre.
Je fait : execute chevalier.param1 := 'Lancelot'; dans SQLDeveloper et j'obtiens une erreur Invalid SQL Statement.

J'imagine bien que "execute chevalier.param1 := 'Lancelot';" n'est pas une requete SQL et donc n'est pas interprétable par SQLDeveloper, mais je ne sais pas comment faire pour avoir un moyen "simple" d'initialiser le paramètre...
J'entend parler de sql dynamique et de pas mal de choses que je ne maîtrise pas du tout, alors j'aimerais savoir si vous pouviez m'éclairer pour que je puisse faire ma vue paramétrée...

P.S : Je connais la notion de procedure et j'imagine que peut passer par une procedure stockée pour faire ce que je veux (créer la vue en fonction d'un chevalier passé en paramètre) mais je ne comprend pas comment faire passer le paramètre dans le SELECT...

Je suis preneur pour toutes vos idées... Merci mille fois !

[Heaven93]

Bonjour,

Il est pris comment le paramètre? c'est l'utilisateur qui le saisit en interactif?
Je ne m'y connais pas dans le domaine mais est-ce que ce style de passage de paramètre marcherait ?

Code :

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

1
2
3
4
5
PROMPT Saisissez le nom du chevalier
ACCEPT &CHEVALIER PROMPT 'Chevalier :'
SELECT Dragon,Sexe,Longueur,Nombreecailles,Crachedufeu, Comportementamoureux, AIME.dragonaimé, AIME.force 
From DRAGONS, AIME 
Where (DRAGONS.Dragon = ANY (Select Dragon From COMBAT Where Chevalier = &CHEVALIER) AND AIME.DragonAimant = DRAGONS.Dragon);

[Yasso44]

Alors voilà après une longue nuit ce que ça donne.

J'ai trouvé comment faire en passant par une procédure qui lance une requête (qui elle meme lance la création de la vue). La procédure prend en paramètre le nom du chevalier et la remet dans la requete :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace
procedure vue_renseignements(chevalier IN VARCHAR2) as
  rq VARCHAR2 (400);
 
BEGIN
  rq := 'CREATE OR REPLACE VIEW Renseignements AS
          Select Dragon,sexe,longueur,nombreecailles,crachedufeu, comportementamoureux, AIME.dragonaimé, AIME.force 
          From DRAGONS, AIME 
          Where (DRAGONS.Dragon = ANY (Select Dragon From COMBAT Where Chevalier =''' || chevalier || ''')  AND AIME.DragonAimant = DRAGONS.Dragon)';
 
  EXECUTE IMMEDIATE rq;
 
END;

L'appel de cette procédure se fait du coup comme ça :

Code :

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

1
2
3
BEGIN
vue_renseignements('Lancelot');
END;

et tout baigne...

Cependant j'ai failli m'arracher les cheveux à un moment pcq à un moment ça ne marchait pas si on ne mettait pas les trois quotes ''' ici (Where Chevalier =''' || chevalier || ''') ! Je n'en mettais qu'un et j'avais une erreur Identificateur non valide "LANCELOT" quand je faisais appel à la procédure !

Alors pour ne pas juste faire un travail de perroquet j'aimerai juste comprendre pourquoi il fallait mettre 3 quotes ???

Je serai extrêmement reconnaissant à qui pourra me donner sa réponse !

Sinon Heaven93, merci pour ta réponse, mais ta méthode ne marche pas ! J'ai une erreur qui est à priori en rapport avec le PROMPT...
Celà dit j'aurai aimé faire comme tu le proposes un invite qui permet à l'utilisateur de saisir le nom du chevalier... mais bon je me contente de ma procédure sachant que sql n'est pas trop fait pour ça...

[orafrance]

Alors pour ne pas juste faire un travail de perroquet j'aimerai juste comprendre pourquoi il fallait mettre 3 quotes ???

'' c'est un apostrophe dans une chaine de caractères.

Essaye :

Code :

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

1
2
3
SELECT 'j''aime Oracle' FROM DUAL;
 
SELECT 'j''' || 'aime Oracle' FROM DUAL;

'j''' la chaine de caractère j'

[mnitu]

Alors voilà après une longue nuit ce que ça donne.

J'ai trouvé comment faire en passant par une procédure qui lance une requête (qui elle meme lance la création de la vue). La procédure prend en paramètre le nom du chevalier et la remet dans la requete...

Ce n'est pas une solution que vous avez trouvé mais «*n*» autres problèmes . Si vous avez besoin d'interroger avec le chevalier vous n'avez que à inclure la table des combats dans la jointure. Cella vous donnerai l'accès à la zone Chevalier. Sinon il va falloir revoir aussi les jointures parce que comme vous l'avez écrit ça fait un produit cartésien entre les tables Dragon et Aime.

[Yasso44]

Oulà !

Alors d'abord je repond a orafrance : merci pour l'info !
Donc si je comrpend bien en faisant comme j'ai fait, c'est équivalent à faire :
...Where Chevalier = 'Lancelot' (Si j'avais appelé la procedure avec vue_renseignements('Lancelot'). En fait le paramètre chevalier qui circule dans la procédure est LANCELOT est non la chaîne de caractères 'Lancelot' (ce que je croyais avant ) Du coup il faut bien remettre la chaine de caractères entre quotes !
Je ne savais pas du tout ! Merci de m'avoir éclairé !

Ensuite pour mnitu : je ne comprend pas tres bien.
Je donne la structure de mes tables.
Une ligne de la table COMBAT contient une date (la date du combat) un Chevalier (identifié par son Nom) et un Dragon qui s'affrontent.

La table AIME contient pour chaque ligne un DragonAimant, un DragonAimé et la Force de leur amour.

La table DRAGONS quant à elle contient pour un Dragon toutes ses caractéristiques (sexe, nombreecailles,etc...)

En fait je ne vois pas un autre moyen d'effectuer la jointure ! Je rappelle ce qu'il faut faire : Il faut pour un chevalier donné, regarder les dragons qu'il a affrontés dans la table COMBAT puis faire une jointure avec les tables DRAGONS et AIME pour rappatrier les caractéristique de ces Dragons et leurs amours.

Donc ici je ne vois pas tres bien comment faire d'autre, et même, je ne vois pas où je fait un produit cartésien !
En effet, AIME.DragonAimant = DRAGONS.Dragon m'empêche de faire un produit cartésien puisque je ne souhaite sélectionner que les amours des Dragons qui sont combattus par le chavalier en question.

A moins que je n'aie pas très bien compris quelquechose ?! Ce qui entre nous est bien possible... pourrais-tu me dire ce que tu en penses plus precisement ?

Je dois avouer que maintenant je ne sais plus ou j'en suis !

[mnitu]

Il n'y a pas de produit cartésien (j'ai mal défilé votre requête).
Mais si, si je l'ai trouvé

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
SQL> select count(*) from scott.emp
  2  /
 
  COUNT(*)
----------
        14
 
SQL> select count(*) from scott.dept
  2  /
 
  COUNT(*)
----------
         4
 
SQL>
SQL> CREATE OR REPLACE procedure vue_renseignements(chevalier IN VARCHAR2) AS
  2    rq VARCHAR2 (400);
  3
  4  BEGIN
  5    rq := 'CREATE OR REPLACE VIEW Renseignements AS
  6            Select e.*
  7             From scott.emp e, scott.dept d
  8            Where e.deptno = d.deptno
  9         and d.dname = '''||chevalier||'''';
 10
 11    EXECUTE IMMEDIATE rq;
 12
 13  END;
 14  /
 
ProcÚdure crÚÚe.
 
SQL>
SQL> exec vue_renseignements('ACCOUNTING');
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
SQL> /
 
ProcÚdure crÚÚe.
 
SQL> select count(*) from Renseignements
  2  /
 
  COUNT(*)
----------
         3
 
SQL> drop view Renseignements
  2  /
 
Vue supprimÚe.
SQL> exec vue_renseignements('ACCOUNTING'' or ''1'' = ''1');
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
SQL> select count(*) from Renseignements
  2  /
 
  COUNT(*)
----------
        56

[salim11]

Salut,

Mais on peut remédier à ce problème( produit cartisien), en rajoutant les parenthèses

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13

SQL> exec vue_renseignements('ACCOUNTING'' or ''('' = ''1)');

Procédure PL/SQL terminée avec succès.

SQL> SELECT count(*) FROM Renseignements;

  COUNT(*)
----------
         2

SQL>

[mnitu]

Salut salim11,

Une des problèmes avec cette solution est dans l'utilisation des littéraux en dur à la place des variables de liaison et non pas dans le contenu de l’appel à la procédure.