Procédure stockée Oracle avec paramètres pour la clause IN

Comment puis-je créer une procédure stockée Oracle qui accepte un nombre variable de valeurs des paramètres utilisées pour nourrir une clause?

C'est ce que je suis en train de réaliser. Je ne sais pas comment déclarer en PLSQL pour passer une variable de la liste de clés primaires des lignes je veux mettre à jour.

FUNCTION EXECUTE_UPDATE
  ( <parameter_list>
   value IN int)
  RETURN  int IS
BEGIN 
    [...other statements...]
    update table1 set col1 = col1 - value where id in (<parameter_list>) 

    RETURN SQL%ROWCOUNT ;
END;

Aussi, je tiens à appeler cette procédure à partir de C#, donc il doit être compatible avec .NET des capacités.

Grâce,
Robert

source d'informationauteur Robert Mircea | 2008-10-28

  1. 25

    Au format CSV est probablement la façon la plus simple, en supposant que vous pouvez être certain à 100% que vos éléments ne seront pas eux-mêmes contiennent des chaînes de caractères.

    Une alternative, et probablement plus robuste, la façon de le faire est de créer un type personnalisé comme un tableau de chaînes de caractères. En supposant que vos chaînes n'ont jamais été plus de 100 caractères, alors vous pourriez avoir:

    CREATE TYPE string_table AS TABLE OF varchar2(100);

    Vous pouvez alors passer une variable de ce type dans votre procédure stockée et de référence directement. Dans votre cas, quelque chose comme ceci:

    FUNCTION EXECUTE_UPDATE(
    identifierList string_table,
    value int)
RETURN int
IS
BEGIN

    [...other stuff...]

    update table1 set col1 = col1 - value 
    where id in (select column_value from table(identifierList));

    RETURN SQL%ROWCOUNT;

END

    La table() fonction transforme votre type personnalisé dans une table avec une colonne unique "COLUMN_VALUE", que vous pouvez ensuite traiter comme n'importe quelle autre table (afin de faire des jointures ou, dans ce cas, les sous-sélections).

    La beauté de ceci est que Oracle va créer un constructeur pour vous, de sorte que lors de l'appel de la procédure stockée, vous pouvez simplement écrire:

    execute_update(string_table('foo','bar','baz'), 32);

    Je suis en supposant que vous pouvez gérer la création de cette commande par programmation à partir de C#.

    En aparté, dans mon entreprise nous avons un certain nombre de ces types personnalisés, définis comme standards pour les listes de chaînes de caractères, des chambres doubles, ints et ainsi de suite. Nous utilisons également des Oracle JPublisher pour être en mesure de mapper directement à partir de ces types correspondants des objets Java. J'ai eu un coup d'oeil rapide autour de lui, mais je ne pouvais pas voir les dommages directs équivalents pour C#. Juste pensé que je le mentionne dans le cas de Java devs venir à travers cette question.

  2. 2

    J'ai trouvé l'article suivant par Mark A. Williams qui j'ai pensé que cela pourrait être un complément utile à ce fil. L'article donne un bon exemple de passage de tableaux à partir de C#, PL/SQL procs en utilisant des tableaux associatifs (TYPE myType EST TABLE mytable.ligne%TYPE d'INDEX PAR PLS_INTEGER):

    Excellent article par Mark A. Williams

  3. 1

    Je pense qu'il n'y a pas de moyen direct pour créer des procédures avec un nombre variable de paramètres.
    Cependant, il ya certaines, au moins partielle, des solutions à ce problème, décrit ici.

    1. Si il y a quelques exemples de types d'appel procédure de surcharge peut aider.
    2. Si il y a une limite dans le nombre de paramètres (et leur type est également connu à l'avance), les valeurs par défaut de paramètres peut aider.
    3. La meilleure option est peut-être l'usage de variables curseur ce sont des pointeurs vers la base de données des curseurs.

    Malheureusement je n'ai pas d'expérience avec .NET environnements.

  4. 0

    Pourquoi ne pas simplement utiliser une longue liste de paramètres et de charger les valeurs dans une table constructeur? Voici le SQL/PSM pour cette astuce,

    UPDATE Foobar
   SET x = 42
 WHERE Foobar.keycol
      IN (SELECT X.parm
            FROM (VALUES (in_p01), (in_p02), .., (in_p99)) X(parm)
           WHERE X.parm IS NOT NULL);
  5. -1

    Pourquoi a-t-elle à être une procédure stockée? Vous pourriez construire une instruction préparée par programmation.

  6. -1

    Je n'ai pas fait ça pour Oracle, mais avec SQL Server, vous pouvez utiliser une fonction pour convertir un fichier CSV chaîne dans un tableau, qui peut ensuite être utilisé dans une clause in. Il devrait être simple de le ré-écrire ce pour Oracle (je pense!)

    CREATE Function dbo.CsvToInt ( @Array varchar(1000)) 
returns @IntTable table 
    (IntValue nvarchar(100))
AS
begin

    declare @separator char(1)
    set @separator = ','

    declare @separator_position int 
    declare @array_value varchar(1000) 

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0 
    begin

      select @separator_position =  patindex('%,%' , @array)
      select @array_value = left(@array, @separator_position - 1)

        Insert @IntTable
        Values (Cast(@array_value as nvarchar))

      select @array = stuff(@array, 1, @separator_position, '')
    end

    return
end

    Ensuite, vous pouvez passer dans un CSV chaîne de caractères (par exemple '0001,0002,0003') et de faire quelque chose comme 

    UPDATE table1 SET 
       col1 = col1 - value 
WHERE id in (SELECT * FROM csvToInt(@myParam))
  7. -1

    Il y a un article sur le AskTom site web qui montre comment créer une fonction pour analyser le CSV chaîne et l'utiliser dans votre déclaration d'une manière similaire à SQL Server à l'exemple donné.

    Voir Asktom