Comment appeler une fonction PL SQL dans une instruction CHECK ?

Question

Je voudrais ajouter un CHECK qui appelle une fonction lors de l'insertion de nouvelles entrées dans une table. J'ai utilisé l'exemple de code suivant pour mettre en œuvre cette fonctionnalité :

CREATE TABLE customers(
    id NUMBER NOT NULL,
    PRIMARY KEY(id));

CREATE OR REPLACE FUNCTION totalCustomers
RETURN NUMBER IS 
   total NUMBER := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM customers; 

   RETURN total; 
END; 
/

ALTER TABLE customers
ADD CHECK (totalCustomers() < 10);

Lorsque je lance cette requête dans livesql.oracle.com J'obtiens l'erreur suivante :

ORA-00904: "TOTALCUSTOMERS": invalid identifier .

Quelle est la bonne façon d'appeler cette fonction dans l'instruction de contrôle ?

P.S. Veuillez ignorer le contenu de la fonction. Je le remplacerai par le contenu souhaité plus tard.

Answer 1

Il n'y en a pas.

Directement de la documentation d'Oracle :

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE

Les conditions des contraintes de contrôle ne peuvent pas contenir les éléments suivants suivantes :

.............

• Appels à des fonctions définies par l'utilisateur

..............

Maintenant : vous avez dit "ne pas tenir compte du contenu réel de la fonction". Ce n'est pas une attitude saine ; le contenu compte aussi. D'une part, la fonction devrait être déterministe de toute façon (la vôtre ne l'est pas) - c'est un problème indépendant du fait qu'il s'agit d'une fonction définie par l'utilisateur. De plus, les conditions dans les contraintes ne peuvent se référer qu'aux valeurs d'une seule ligne - il ne peut s'agir de contraintes de "table", comme la vôtre.

Vous pouvez donc vous demander comment mettre en œuvre une "contrainte" comme la vôtre ? Une méthode assez courante consiste à créer une vue matérialisée basée sur "select count(*)....." et à appliquer une contrainte à la vue matérialisée. La vue matérialisée doit être rafraîchie au moment de la validation. Chaque fois que vous modifiez la table de base et que vous effectuez un commit, la MV est rafraîchie - et si le compte dépasse 10, les modifications sont annulées.

Answer 2

Dans votre commentaire sur la réponse de mathguy, vous dites "J'essaie également de m'assurer que la période de temps des nouvelles entrées ne chevauche pas les entrées existantes." J'ai fait cela avec des vues matérialisées "rafraîchir rapidement sur le commit". Attention : Les rafraîchissements "rapides" peuvent être lents si vous ne faites pas attention, veuillez vous référer à ce blog. http://www.adellera.it/ notamment en ce qui concerne statistiques sur le journal de la vue matérialisée.

Je suppose que les dates de fin sont exclusives. Si une date de fin est nulle, cela signifie que la plage de dates se poursuit indéfiniment. De nombreux chevauchements seront immédiatement détectés par la clé primaire et les contraintes uniques. Les autres seront détectés au moment de la validation par la contrainte sur la vue matérialisée. Notez qu'à la fin de la transaction, le MV n'aura jamais de lignes.

SQL> create table date_ranges (
  2    key1, start_date,
  3    primary key(key1, start_date),
  4    end_date,
  5    unique(key1, end_date),
  6    check(start_date < end_date)
  7  )
  8  as
  9  with a as (select date '2000-01-01' dte from dual)
 10  select 1, dte, dte+1 from a
 11  union all
 12  select 1, dte+1, dte+2 from a
 13  union all
 14  select 1, dte-1, dte from a
 15  union all
 16  select 2, dte+10, dte+11 from a
 17  union all
 18  select 2, dte+12, dte+13 from a
 19  union all
 20  select 2, dte+8, dte+9 from a
 21  /

Table DATE_RANGES created.

SQL> create materialized view log on date_ranges
  2  with sequence, rowid, primary key, commit scn (end_date) including new values
  3  /

Materialized view log DATE_RANGES created.

SQL> create materialized view overlapping_ranges refresh fast on commit
  2  as
  3  select a.rowid arid, b.rowid brid
  4  from date_ranges a, date_ranges b
  5  where a.key1 = b.key1
  6  and a.rowid != b.rowid
  7  and a.start_date < b.end_date
  8  and a.end_date > b.start_date;

Materialized view OVERLAPPING_RANGES created.

SQL> 
SQL> alter materialized view overlapping_ranges
  2  add constraint overlaps_not_allowed check (1=0) deferrable initially deferred
  3  /

Materialized view OVERLAPPING_RANGES altered.

SQL> insert into date_ranges select 1, date '1999-12-30', date '2000-01-4' from dual;

1 row inserted.

SQL> commit;

Error starting at line : 42 in command -
commit
Error report -
ORA-02091: transaction rolled back
ORA-02290: check constraint (STEW.OVERLAPS_NOT_ALLOWED) violated

Answer 3

Je suggère un déclencheur pour une telle exigence.

CREATE OR REPLACE TRIGGER AI_customers 
    AFTER INSERT ON customers 
DECLARE

total NUMBER; 

BEGIN
   SELECT count(*) into total 
   FROM customers; 
   IF total > 10 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Total number of customers must not exceed 10');
   END IF;
END;
/

Notez qu'il s'agit d'un déclencheur de niveau déclaration (pas de déclencheur de type "déclaration"). FOR EACH ROW ), vous ne pouvez donc pas obtenir la fameuse erreur "ORA-04091 : table is mutating, trigger/function may not see it".

Toutefois, ce déclencheur présente certaines limites dans un environnement multi-utilisateurs. Si l'utilisateur_1 insère des enregistrements dans la table des clients et que l'utilisateur_2 insère également des enregistrements (avant que l'utilisateur_1 n'ait effectué un COMMIT), vous pouvez obtenir plus de 10 enregistrements dans votre table des clients.