Supposons que vous ayez un paramètre : p_companys qui est une liste de valeurs à sélection multiple:
select comp -- 50 and 55 from companies where comp in :p_companys
Maintenant, disons que dans cette liste, un utilisateur sélectionne 50 - ar et 55 - ag comme valeurs. Le paramètre est maintenant une liste de ces deux valeurs.
Dans notre requête SQL, nous utilisons le numéro de société et vérifions si elles sont contenues dans ce paramètre. Le numéro d'entreprise est composé des 2 premiers chiffres (50 et 55.) Existe-t-il un moyen de faire quelque chose comme ceci:
----------- | company | ----------- | 50 - ar | | 55 - ag | | 62 - hh | | 70 - nr | -----------
mais au lieu de comparer à la chaîne entière dans la liste on compare simplement au premier x nombre de caractères (essentiel en prenant une sous-chaîne de toutes les valeurs du paramètre?)
3 Réponses :
Vous devez le mettre dans la clause WHERE.
Si vous attendez 2 chiffres, vous pouvez utiliser une expression régulière comme ceci:
REGEXP_SUBSTR (comp, '\ d \ d') dans ('50', '55')
Ou si vous voulez simplement couper de votre chaîne les 2 premiers caractères:
SUBSTR (comp, 1, 2) dans ('50', '55')
En supposant que vous ayez un nombre indéterminé de paramètres dans votre sélection d'entrée, je pense que vous devez utiliser une combinaison de expression régulière et connexion par niveau
Quelque chose comme ça devrait faire l'affaire
with x as
(
SELECT regexp_substr('A,B,C' ,'[^,]+',1,level) as mycol FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('A,B,C' ,'[,]')+1
) select * from t where c1 in ( select mycol from x )
;
C
-
A
B
C
Exemple
SQL> create table t ( c1 varchar2(1) ) ; Table created. SQL> insert into t values ( 'A' ); 1 row created. SQL> insert into t values ( 'B' ); 1 row created. SQL> insert into t values ( 'C' ); 1 row created. SQL> select * from t ; C - A B C
Maintenant, testons quand la variable d'entrée est une liste de chaînes p>
with t as ( SELECT regexp_substr(:p_companys ,'[^,]+',1,level) as mycol FROM dual CONNECT BY LEVEL <= REGEXP_COUNT(:p_companys ,'[,]')+1 ) select comp from companies where comp in ( select mycol from t ) ;
D'accord, il s'avère que vous n'avez pas à utiliser une seule ligne dans Oracle lorsque vous traitez des listes. Vous pouvez utiliser deux lignes, et j'ai constaté que, ce faisant, la deuxième ligne est traitée comme la valeur transmise à SQL. La première ligne est traitée comme la valeur affichée aux utilisateurs. Vous pourriez donc avoir une liste comme celle-ci:
--------------------------- | comp_desc | comp | --------------------------- | 50 - retail | 50 | . . . . . . . . . . . . . | 90 - lodging | 90 |
Où l'utilisateur verra les valeurs de la colonne comp_desc passées dans le menu select, mais la requête SQL recevra le comp valeurs de la colonne via la valeur : p_company .
Donc, en utilisant cette configuration, nous pouvons simplement utiliser où comp dans (: p_company) code > (notez que la parenthèse est nécessaire ici)
Qu'entendez-vous par «n'utiliser qu'une seule ligne» et «traiter des listes»? Qu'est-ce qu'une liste? Quelle valeur est transmise à SQL?
@WilliamRobertson docs.oracle.com/cd/E21764_01/bi. 1111 / e18862 / T527073T560157.h tm cette page doit décrire ce qu'est une liste. Fondamentalement, vous pouvez définir une liste (dans ce cas une requête SQL) qui est exécutée et génère des valeurs pour un utilisateur final à sélectionner. Ce que l'utilisateur sélectionne est ensuite transmis à la requête principale en tant que paramètre : p_company . Toutes mes excuses si cela prête à confusion. Cette fonctionnalité fait partie de la suite BI Publisher d'Oracle, avec laquelle je travaille. Je ne sais pas si ces listes sont un composant Oracle DB standard ou si elles sont uniques à BI Publisher.
La page ne dit pas ce qu'est une liste en termes techniques, mais comment travailler avec elle dans l'interface utilisateur de BI Publisher, ce que je doute que beaucoup de gens aient utilisé ou dont ils aient entendu parler. Rien de tout cela n'est une fonctionnalité de base de données standard donc aucune des réponses n'est pertinente. Je suggérerais de taguer oracle-bi-publisher mais j'ai bien peur qu'il n'y en ait pas.
@WilliamRobertson Merci pour le tuyau. Je ne savais pas que ce n'était pas une fonctionnalité standard dans Oracle DB. Je suis à peu près sûr que, puisque la liste est une requête Oracle SQL standard, ce serait une table, que vous pouvez référencer comme n'importe quelle autre table.
Dans votre exemple, where comp in (: p_company) - cette syntaxe ne fonctionnerait pas si : p_company était un nom de table ou une collection, ou si elle était remplacée par un nom de la table (vous auriez besoin d'une sous-requête). Peut-être que BI Publisher le remplace par une liste de littéraux. Vous pouvez dire si vous pouvez trouver le texte du curseur dans v $ sql.
D'où et comment la valeur sera transmise en tant que
: p_companyset quel est le type de données de: p_companys?@ Sujitmohanty30
: p_companysest une liste de valeurs destring. L'utilisateur aura un menu avec toutes les entreprises possibles, il pourra sélectionner une ou plusieurs entreprises, et qui sera passé à la requête SQL comme: p_companysOui je l'ai mais dans quel format? (pourquoi je demande qu'il n'y a pas un tel concept de liste dans oracle directement) est-ce JSON ou comment?
@ Sujitmohanty30 voici un site Web qui fait référence à l'outil que j'utilise pour créer ces listes: docs.oracle.com/cd/E21764_01/bi.1111/e18862/T527073T560157.h tm Si je ne me trompe pas une fois le paramètre
: p_companysinséré dans la requête SQL, il peut être référencé de la même manière que vous diriezoù x in ('str1', 'str2', ... 'strx')Il n'est pas clair sur cette page quel est le type de données du paramètre, et je ne sais pas lequel vous utilisez parmi les différents types de paramètres qu'il mentionne. Cela fait une grande différence pour la réponse s'il s'agit d'une chaîne séparée par des virgules ou d'une collection, par exemple.
@WilliamRobertson J'ai eu affaire à une documentation assez vague tout au long de l'utilisation de ce logiciel. J'ai fini par résoudre le problème en ajoutant une deuxième colonne à ma requête de paramètre
: p_company. Si vous avez deux colonnes, Oracle utilisera la première colonne pour les menus (c'est donc là que j'ai mis la chaîne lisible par l'homme) et la deuxième colonne (numéro d'entreprise brut) est utilisée dans la requête SQL. Je viens ensuite d'utiliserwhere comp in (: p_companys)@CodyMaxie
dans (: bindvar)ne peut pas fonctionner, sauf si: bindvarest traité comme une variable de substitution par BI Publisher. N'est-il pas possible de savoir ce qui est réellement utilisé pour commencer à répondre à la question?