J'ai la fonction suivante, qui renvoie le prochain identifiant client disponible du tableau Client:

CREATE OR REPLACE FUNCTION getNextClientID RETURN INT AS
ctr INT;

BEGIN
SELECT MAX(NUM) INTO ctr FROM Client;

IF SQL%NOTFOUND THEN
    RETURN 1;
ELSIF SQL%FOUND THEN
    -- RETURN SQL%ROWCOUNT;
    RAISE_APPLICATION_ERROR(-20010, 'ROWS FOUND!');
    -- RETURN ctr + 1;
END IF; 

END;

Mais lors de l'appel de cette fonction,

BEGIN 
DBMS_OUTPUT.PUT_LINE(getNextClientID());

END;

J'obtiens le résultat suivant:

execution

Ce que j'ai trouvé un peu étrange, car la table Client ne contient aucune donnée:

Client table

De plus, si je commente RAISE_APPLICATION_ERROR(-20010, 'ROWS FOUND!'); & amp; enregistrez la valeur de SQL%ROWCOUNT dans la console, j'obtiens 1 en conséquence.

Par contre, lors du changement

SELECT MAX(NUM) INTO ctr FROM Client;

À

SELECT NUM INTO ctr FROM Client;

L'exécution s'est déroulée comme prévu. Quelle est la raison de ce comportement?

-2
Mohammed Aouf Zouag 2 janv. 2016 à 01:04

4 réponses

Meilleure réponse

Les Les fonctions d'agrégation renverront toujours un résultat:

Toutes les fonctions d'agrégation à l'exception de COUNT (*), GROUPING et GROUPING_ID ignorent les valeurs nulles. Vous pouvez utiliser la fonction NVL dans l'argument d'une fonction d'agrégation pour remplacer une valeur par null. COUNT et REGR_COUNT ne renvoient jamais null, mais renvoient un nombre ou zéro. Pour toutes les fonctions d'agrégation restantes, si l'ensemble de données ne contient aucune ligne ou contient uniquement des lignes avec des valeurs nulles comme arguments de la fonction d'agrégation, la fonction renvoie la valeur null.

Vous pouvez modifier votre requête en:

SELECT COALESCE(MAX(num), 1) INTO ctr FROM Client;

Et supprimez complètement les conditions. Attention cependant aux problèmes de concurrence si vous n'utilisez pas SELECT FOR UPDATE.

3
Glenn 1 janv. 2016 à 23:22

Une requête avec n'importe quelle fonction d'agrégation et sans clause GROUP BY renvoie toujours 1 ligne. Si vous voulez une exception no_data_found sur une table vide, ajoutez la clause GROUP BY ou supprimez max:

SQL> create table t (id number, client_id number);

Table created.

SQL> select nvl(max(id), 0) from t;

NVL(MAX(ID),0)
--------------
         0

SQL> select nvl(max(id), 0) from t group by client_id;

no rows selected

Habituellement, des requêtes comme la vôtre (avec max et sans group by) sont utilisées pour éviter no_data_found.

2
Dmitriy 1 janv. 2016 à 22:31

Les fonctions d'agrégation comme MAX renverront toujours une ligne. Il renverra une ligne avec une valeur nulle si aucune ligne n'est trouvée.

Au fait, SELECT NUM INTO ctr FROM Client; lèvera une exception là où il y a plus d'une ligne dans la table.

Vous devriez plutôt vérifier si ctr est nul ou non.

1
Tulains Córdova 1 janv. 2016 à 22:20

D'autres ont déjà expliqué la raison pour laquelle votre code ne "fonctionne" pas, donc je ne vais pas le faire.

Vous semblez instituer vous-même une colonne d'identité avec une certaine description, probablement pour prendre en charge une clé de substitution. Faire cela vous-même est dangereux et peut entraîner de gros problèmes dans votre application.

Vous n'avez pas besoin d'implémenter vous-même les colonnes d'identité. À partir d'Oracle 12c, Oracle prend en charge nativement les colonnes d'identité, ceux-ci sont implémentés à l'aide de séquences, qui sont disponible en 12c et versions précédentes.

Une séquence est un objet de base de données qui est garanti pour fournir un nouveau numéro unique lorsqu'il est appelé, quel que soit le nombre de sessions simultanées demandant des valeurs. Votre approche actuelle est extrêmement vulnérable aux collisions lorsqu'elle est utilisée par plusieurs sessions. Imaginez 2 sessions trouvant simultanément la plus grande valeur du tableau; ils ajoutent ensuite tous les deux une à cette valeur et essaient de réécrire cette nouvelle valeur. Un seul peut avoir raison.

Voir Comment créer un identifiant avec AUTO_INCREMENT sur Oracle?

Fondamentalement, si vous utilisez une séquence, vous n'avez besoin d'aucun de ce code.


À titre secondaire, votre déclaration en haut est incorrecte:

J'ai la fonction suivante, qui renvoie le prochain ID client disponible à partir de la table Client

Votre fonction renvoie l'ID maximum + 1. S'il y a un espace dans les ID, c'est-à-dire 1, 2, 3, 5, le nombre «manquant» (4 dans ce cas) ne sera pas renvoyé. Un écart peut survenir pour un certain nombre de raisons (suppression d'une ligne par exemple) et n'a aucun impact négatif sur votre base de données - ne vous en faites pas.

1
Community 23 mai 2017 à 12:07