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.

[Lagnio]

Merci pour la revue! Je suis preneuse! rien de tel pour s'améliorer

Pour la question pourquoi FROM Dual, juste parceque je savais pas qu'on pouvait faire autrement, j'apprends sur le tas comme qui dirait...

Par contre, petite question: Toute mes champs date (et ils sont très nombreux) ont le mois en alpha (01-JAN-20) et je n'ai bien sur pas la main pour les changer puisque c'est une base créer par mon gentil éditeur de logiciel : quand je requete sur un champs date est-il préférable que je convertisse a chaque fois ma date pour avoir le mois en numérique?

[vanagreg]

Toute mes champs date (et ils sont très nombreux) ont le mois en alpha (01-JAN-20) et je n'ai bien sur pas la main pour les changer puisque c'est une base créer par mon gentil éditeur de logiciel

Si tes colonnes représentant des dates sont de type DATE c'est très bien. J'ai connu des éditeurs de logiciels (Générix pour ne pas le nommer) qui se sont amusés à stocker des dates en tant que varchar2(8), j'espère que ce n'est pas le cas avec votre logiciel.
Ne pas confondre restitution avec stockage. Les valeurs de type DATE sont stockées sous un format propre à Oracle et non pas dans un masque de date particulier. Si tu vois le mois en alpha c'est sûrement ton client sql qui est configuré ainsi. Tu peux le changer.

[McM]

Pas de souci, le forum est fait pour apprendre

Par contre, petite question: Toute mes champs date (et ils sont très nombreux) ont le mois en alpha (01-JAN-20) et je n'ai bien sur pas la main pour les changer puisque c'est une base créer par mon gentil éditeur de logiciel : quand je requete sur un champs date est-il préférable que je convertisse a chaque fois ma date pour avoir le mois en numérique?

L'erreur de tous les débutants Oracle (ou base de données) :
Une date est une DATE ce n'est pas une chaîne de caractère indiquant Janvier ou January
La date est stockée dans une colonne dans un format propriétaire Oracle (8 octets), exemple

Code :

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

1
2
3
4
5
SELECT SYSDATE, DUMP(SYSDATE) dmp
from dual
 
SYSDATE : 15/01/2020 16:02:42	
DMP : Typ=13 Len=8: 228,7,1,15,16,2,42,0

Quand tu affiches une date par un SELECT, il y a une transformation automatique en Varchar en fonction des NLS_DATE_FORMAT de la base, du requêteur
Exemple précédent, le 15/01/2020 16:02:42 est mon format avec TOAD

Si je veux un affichage particulier (exemple 01-JAN-20), il faut que je modifie mon paramétrage de TOAD, ou changer le NLS ou simplement avec un TO_CHAR pour transformer la date en chaîne de caractère)
Exemple :

Code :

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

1
2
3
4
5
6
SELECT SYSDATE + 200, TO_CHAR(SYSDATE+200, 'DD/MON/YY') tc, TO_CHAR(SYSDATE+200, 'DD/MON/YY', 'NLS_DATE_LANGUAGE=French') tcf
FROM dual
 
SYSDATE+200	 : 02/08/2020 16:09:30
TC : 02/AUG/20
TCF : 02/AOÛT /20

Tu vois bien que les mois en Mon peuvent changer du tout au tout entre l'anglais et le français par exemple.

Conclusion :
Créer une date => Toujours mettre un To_DATE(chaine de caractère, Format)
Afficher une date (dans des programmes) => Toujours mettre un TO_CHAR (date, format)
Dans ton outils requêteur, tu fais ce que tu veux avec le paramétrage, ça n'a pas d'incidence client, mais je te conseille de paramétrer avec les Heures min sec (HH24:MI:SS)

Et répéter : Une date est une DATE, une chaîne de caractère est un VARCHAR


PS : Et pour les fonctions spéciales sur les DATES (comme TRUNC, ou ADD_MONTHS), ces fonctions renvoient des DATES
Et jamais de TO_DATE sur une DATE (c'est implicite dans ma conclusion, mais mieux vaut préciser)

[Lagnio]

Merci Messieurs pour ces explications!

J'y vois maintenant plus clair dans mes dates!

[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