Discussion :

[Lagnio]

Bonjour,

J’aurai besoin d’aide pour une requête paramétrée.

J'ai d'un côté une requête très basique:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT empno, nom, prenom
FROM Employe 
WHERE date_embauche = [date_paramétrée]

De l'autre une fonction retournant le dernier jour du trimestre précédent. Par ex : aujourd’hui il retourne : 31-Dec-19

Le but étant de passer le résultat de cette fonction comme paramètre de ma requête.

Comment automatiser ce concept ? Est-ce que je suis obligé de passer par le PL/SQL ?

Merci pour vos réponses

[vanagreg]

Bonjour,

Si ta fonction est publique alors pas de problème pour l'appeler directement si as les droits d'exécution:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT empno, nom, prenom
FROM Employe 
WHERE date_embauche = ta_fonction

[Lagnio]

Oui, elle est public.

J'ai tester en l'appelant directement mais cela ralentit l'exécution de la requête (sauf erreur de ma part, si on l'apelle directement, elle sera executée pour toute les lignes de la tables retournés)

C'est pour cela que je cherche une autre solution

[JeanYvette]

Bonjour,

Il faut voir comment est fait votre fonction. Est-ce qu'elle contient des paramètres permettant un filtre, est-ce qu'elle parcours automatique toute(s) une/des table(s), la volumétrie ect ect

Donc soit l'optimiser si possible, soit s'en passer si possible

Après oui, le temps d'exécution sera forcement plus grand...

Bisous bisous

[McM]

Si c'est dans la condition where, elle ne sera appelée qu'une seule fois
Si c'est dans le select, elle sera appelée plusieurs fois.

[vanagreg]

Si comme tu le dis la fonction ne ramène que le dernier jour du trimestre précédent, ça ne doit pas avoir une grande incidence. D'ailleurs tu peux simplement faire:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT empno, nom, prenom
FROM Employe 
WHERE date_embauche = trunc(sysdate, 'q')-1

[Lagnio]

Merci pour vos réponses!

JeanYvette : c'est une fonction assez basique basé sur un select :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create or replace Function getQuarterDate
Return Date
IS
 --DECLARE
 l_date date;
 
  BEGIN
 
  select 
  case 
      when extract(month from sysdate) BETWEEN 1 and 3 
           then '31-Dec-' || TO_char(extract(year from sysdate)-1)
      when extract(month from sysdate)BETWEEN 4 and 6
           then '31-Mar-' || TO_char(extract(year from sysdate))
      when extract(month from sysdate) BETWEEN 7 and 9
           then '30-Jun-'|| TO_char(extract(year from sysdate))
      else      '31-Sep-'|| TO_char(extract(year from sysdate)) 
  end INTO l_date
 from dual;
 
  return l_date;
  END;

sans filtre, sans filet

et je l'appelai au niveau du Where donc je cromprends pas pourquoi mon exécution est devenu lente...

Je vais donc m'en passer et utiliser la fonction "trunc(sysdate, 'q')-1" comme suggéré par vanagreg (que je remercie au passage car je ne connaissais pas encore cette fonction).

Arff, j'ai encore tant a apprendre!!

[McM]

Bon, tant qu'on y est : revue de code

1/ Toujours mettre un format sur une chaîne de caractère représentant une date (et je conseille de mettre les mois en numériques, un changement de NLS peut avoir des effets de bord)
'31-Dec-' || TO_char(extract(year from sysdate)-1) => TO_DATE( '31-12-' || TO_char(extract(year from sysdate)-1), 'DD-MM-YYYY')

2/ Pourquoi faire un SELECT FROM DUAL ? C'est gérable en plsql
l_date := CASE ....END;

Ca donnerait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
create or replace Function getQuarterDate
Return Date
IS
BEGIN
  RETURN  case 
      when extract(month from sysdate) BETWEEN 1 and 3 then TO_DATE('31-12-' || TO_char(extract(year from sysdate)-1), 'DD-MM-YYYY')
      when extract(month from sysdate)BETWEEN 4 and 6 then TO_DATE('31-03-' || TO_char(extract(year from sysdate)), 'DD-MM-YYYY')
      when extract(month from sysdate) BETWEEN 7 and 9 then TO_DATE('30-06-'|| TO_char(extract(year from sysdate)), 'DD-MM-YYYY')
      else     TO_DATE( '31-09-'|| TO_char(extract(year from sysdate)), 'DD-MM-YYYY')
  end;
END;

Bien sûr ce n'est que pour info, le TRUNC(SYSDATE, 'Q')-1 est bien plus performant.

[Mohamed.Houri]

Oui, elle est public.

J'ai tester en l'appelant directement mais cela ralentit l'exécution de la requête (sauf erreur de ma part, si on l'apelle directement, elle sera executée pour toute les lignes de la tables retournés)

C'est pour cela que je cherche une autre solution

Vous devriez encapsuler l'appel à votre fonction dans un 'select function from dual' afin de bénéficier de l'effet ''scalar subquery caching''

https://hourim.wordpress.com/2019/12...om-dual-trick/

Bien Cordialement

[Lagnio]

Vous devriez encapsuler l'appel à votre fonction dans un 'select function from dual' afin de bénéficier de l'effet ''scalar subquery caching''

Merci Mohamed pour cet ajout.

J'ai prit connaissance de votre papier sur la question, c'est tres clair et vraiment tres utile.

Cordialement