ORACLE PL/SQL: Fonctions et des Paramètres Facultatifs, Comment?

Je suis à la recherche de la meilleure façon de créer une fonction qui peut accepter aucun des paramètres et retourner tous les résultats, mais accepte également les paramètres et le retour de ces résultats.

La norme j'ai du faire avec mon travail est ceci:

FUNCTION get_records (
  i_code                                 IN records.code%type := NULL,
  i_type                                 IN records.type%type := NULL
) RETURN results

Le problème est que je veux renvoyer des enregistrements qui ont un type de NULL ainsi, et à l'aide de:

WHERE type = nvl(i_type, type)

Il retourne uniquement les enregistrements avec les types réels et non pas les enregistrements null.. pour des raisons évidentes. Je me demandais juste si il y a un niveau moyen de faire ce que pourrait être mise en œuvre à travers toutes les fonctions que nous utilisons. Par coïncidence, si je fournir un paramètre... je ne veux pas les valeurs NULL dans ce domaine.

InformationsquelleAutor jlrolin | 2010-10-19
  1. 5

    pourquoi ne pas tout simplement ce que vous avez avec l'ajout de 

     type = i_type OR (i_type IS NULL AND type IS NULL)

    de cette façon, lorsque le passé en paramètre est null, il semble pour tout (y compris les valeurs null) ou la valeur spécifiée. Maintenant, si vous voulez juste les valeurs null...

    exemple (changement de la valeur de null à 5 et vous verrez le résultat)

    WITH TESTDATA AS (
        SELECT LEVEL dataId
          FROM DUAL
         CONNECT BY LEVEL <= 100
        UNION
        SELECT NULL 
         from dual
)
SELECT * 
  FROM TESTDATA
 where dataId = :n or (:n is null AND dataId is null) ;

    Considérant ce qui suit :n = 6
    les résultats seront 

    DATAID                 
---------------------- 
6

    (supprimé un nouveau poste, de se méprendre sur un param)
    Mais j'aime bien la nouvelle approche et en évitant la NVL

    voici une chouette façon de le faire si vous n'êtes pas préjudiciables à la dynamique sql

    http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

    • C'est effectivement ce que j'ai mis en place après quelques tests...
    • Je suggère de changer de type = i_type OR i_type IS NULL. C'est à mon expérience de plus de chances de profiter d'un index sur type que l'utilisation de NVL(). Aussi, semble plus clair pour moi.
    • C'est la solution que j'ai mis en œuvre.
    • Cette approche n'est généralement pas sargable
    InformationsquelleAutor Harrison
  2. 4

    Le standard pour résoudre ce problème consiste à surcharger la fonction, au lieu d'utiliser les valeurs par défaut:

    FUNCTION get_records (
  i_code                                 IN records.code%type,
  i_type                                 IN records.type%type
) RETURN results;

FUNCTION get_records (
  i_code                                 IN records.code%type
) RETURN results;

FUNCTION get_records RETURN results;

    Remarque: Si vous avez besoin d'une version i_type par lui-même, vous pourriez avoir des ennuis si elle a le même type sous-jacent que i_code - dans ce cas, vous devez utiliser un nom différent pour la fonction.

    • +1: Qui, ou de l'utilisation du SQL dynamique
    • eh bien, ma solution ne dit rien au sujet du SQL dynamique est utilisé ou pas - le point est, comment dites-vous entre get_records(NULL,NULL) et get_records(NULL), à moins que vous faire un peu de "magie" valeurs pour le paramètre par défaut? La surcharge est la meilleure façon de résoudre ce problème, de l'OMI.
    InformationsquelleAutor Jeffrey Kemp
  3. 3

    Réitérer ma compréhension de la question: Vous ne pouvez pas prendre la valeur par défaut de NULL pour dire "le retour de tous les records", parce que l'actuel comportement attendu, c'est qu'il sera de retour que les enregistrements pour lesquels la valeur est NULL.

    Une possibilité consiste à ajouter un paramètre booléen correspondant à chaque paramètre de recherche, ce qui indique qu'il devrait en fait être utilisé pour filtrer les résultats. Un problème potentiel avec ceci est que l'appelant peut spécifier une valeur de recherche, mais ne parviennent pas à définir l'indicateur de vrai, de produire des résultats inattendus. Vous pouvez vous protéger contre ce à l'exécution, par le déclenchement d'une exception si le drapeau est fausse pour toute valeur de recherche autres que NULL.

    Une autre possibilité est de définir un domaine de valeur pour chaque colonne de recherche (par exemple, si la chaîne de caractères 'TOUT' est passé pour le paramètre, il ne filtre pas les valeurs de cette colonne. Cela devrait fonctionner aussi longtemps que vous pouvez trouver un peine sentinelle de la valeur pour chaque colonne. Je vous suggère de déclarer la sentinelle des valeurs constantes dans un paquet, de sorte que les appels à la fonction pourrait ressembler à get_records( PKG.ALL_CODES, 'type1' ).

    InformationsquelleAutor Dave Costa
  4. 3

    Bien sur le dessus de ma tête, je suppose que l'utilisation de la DEFAULT mot-clé ferait l'affaire, n'est-ce pas? (Le lien suivant va donner un peu plus de détails.)

    1. En utilisant le mot clé DEFAULT.

      CREATE OR REPLACE FUNCTION get_records (
    i_code IN records.code%type DEFAULT NULL,
    i_type IN records.type%type DEFAULT NULL
) RETURN results

    EDIT #1

    Si je comprends correctement à la question, vous voulez retourner tous les enregistrements lorsque le i_type paramètre est NULL. En l'absence de plus de détails, j'imagine la suite.

    CREATE OR REPLACE FUNCTION get_records (
    i_code IN records.code%TYPE DEFAULT NULL,
    i_type IN records.type%TYPE DEFAULT NULL
) RETURN results
BEGIN
    IF (i_type IS NULL) THEN
        select *
            from table
    ELSE
        select *
            from table
            where type = NVL(i_type, type)
    END IF

    EXCEPTION
        WHEN OTHERS THEN
            NULL
END

    C'est tout ce que je peux faire avec les informations fournies, bien que le corps de la fonction est commenté ci-dessous.

    EDIT #2

    Je suis un peu rouillé à partir d'Oracle, j'ai donc consulté la documentation comme lien ci-dessous:

    Oracle/PLSQL: NVL Fonction

    Comme je l'ai lu, vous feriez mieux d'utiliser le NVL fonction au sein de votre SELECT instruction, et non pas votre WHERE clause.

    En fin de compte, quelle est ta question exactement? Pourriez-vous faire ce cristal clair?

    • Le := dans les déclarations de paramètres est le même que DEFAULT. Le problème semble être qu'une valeur NULL pour le paramètre signifie correspondre à des enregistrements avec une valeur NULL dans la colonne correspondante; de sorte que la valeur par défaut doit être utilisé pour indiquer "tous les dossiers"?
    • Ne retourne pas les documents qui ont un type de NULL ainsi. En gros, je veux l'égalité du type si je fournis le paramètre, sinon, ne pas utiliser le paramètre. Ce n'est pas du SQL dynamique, de sorte que vous pouvez voir le dilemne. Si c'était le cas, je voudrais juste exclure la clause.
    • Costa: Merci pour l'information. J'ai toujours et seulement utilisé le DEFAULT mot-clé, et non pas le signe égal (:=) pour définir les valeurs par défaut.
    • Pouvez-vous fournir un exemple de code de votre corps de la fonction, avec un exemple de données dans la table de données qui vous extrayez les données à partir de? Ceci pourrait amener quelques autres idées de solutions que ce soit.
    • Le corps de la fonction est un simple SELECT * from table where type = nvl(i_type, type), d'être introduit dans un ref_cursor, puis est retourné.
    • Et la table de données de l'échantillon?
    • Deux Champs: Code et le Type. Le Code n'est jamais null, varchar2(2 OCTETS), peut être de Type null, varchar2(5 OCTETS).

    InformationsquelleAutor Will Marcouiller
  5. 1

    J'ai été en utilisant les suivantes hack (en supposant que p_product est une procédure facultative d'entrée):

    où t.produit = decode(p_product, null, t.produit, p_product)

    procedure get_data(p_product in number := null)
...
select *
from tbl t
where t.product = decode(p_product, null, t.product, p_product);
...

    Cela va créer "where 1=1' scénario lorsque le produit n'est pas passé, et d'utiliser le produit autrement. Un potentiellement inconvénient majeur est que cette analyse à la même plan d'exécution, indépendamment des paramètres.

    InformationsquelleAutor Don.Mega