Fonction sur PL / SQL

Version imprimable

Bonjour la famille,

je suis etudiant et je m interesse au langage SQL.

j ai cette fonction et j aimerais savoir sa fonctionnalité et comment je pourrais l ameliorer:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE OR REPLACE FUNCTION F_1300_GET_DATE
RETURN DATE AS PROCESSING_DATE DATE;
       MANUAL_DATE VARCHAR2(10);
       V_SYSDATE VARCHAR2(10);
       stmt VARCHAR2(50);
       COUNTER NUMBER;
       RUN_MODE NUMBER(1) DEFAULT 0;
 
BEGIN
-- get the run mode from the control table "FND_1200_CONTROL"
EXECUTE IMMEDIATE 'select count(*) from FND_1200_CONTROL_TABLE' INTO COUNTER;
IF COUNTER=1
  THEN EXECUTE IMMEDIATE 'select run_mode from FND_1200_CONTROL_TABLE' INTO RUN_MODE;
  EXECUTE IMMEDIATE 'select to_char(date_manual,''DD.MM.YYYY'') from FND_1200_CONTROL_TABLE' INTO MANUAL_DATE;
END IF;
-- current date
stmt := 'select to_char(sysdate,''DD.MM.YYYY'') from dual';
execute immediate stmt into V_SYSDATE;
IF RUN_MODE=0
-- last day of the previous month
THEN PROCESSING_DATE := LAST_DAY(ADD_MONTHS(TO_DATE(V_SYSDATE,'DD.MM.YYYY'),-1));
ELSIF RUN_MODE=1
-- current date
 THEN PROCESSING_DATE := V_SYSDATE;
 ELSIF RUN_MODE=2
-- set the date manual
THEN PROCESSING_DATE := TO_DATE(MANUAL_DATE,'DD.MM.YYYY');
END IF;
 
RETURN PROCESSING_DATE;
 
END F_1300_GET_DATE;

Merci de me repondre

1/ Le execute immediate set uniquement dans 2 cas :
+ La requête est dynamique (Nom de table qui change, colonne ou condition qui change suivant des paramètres, requête stockée dans une variable)
+ Les objets du SELECT n'existent pas au moment de la compilation (exemple une table créée dans la procédure, puis droppée en fin de procédure

Le reste du temps : A proscrire.

Donc ça, jamais :

Code:

1
2
3
4
5
-- current date
  stmt := 'select to_char(sysdate,''DD.MM.YYYY'') from dual';
  execute immediate stmt INTO V_SYSDATE;
 
=> v_sysdate := TRUNC(SYSDATE); -- date tronquée au jour, même pas besoin de SELECT

2/ "Less code, less bug" : Pourquoi faire un to_char de la date pour après refaire un To_Date dessus.. il faut toujours aller au plus court (https://youtu.be/7Xfape2TM_A?t=16)

3/ Il faut éviter d'avoir des noms de variables identiques à des noms de colonne (le run_mode par exemple)
4/ 2 SELECT sur la même table pour ramener 2 colonnes ? un select peut être multicolonne

Code:

1
2
3
4
5
6
EXECUTE IMMEDIATE 'select run_mode from FND_1200_CONTROL_TABLE' INTO RUN_MODE;
  	EXECUTE IMMEDIATE 'select to_char(date_manual,''DD.MM.YYYY'') from FND_1200_CONTROL_TABLE' INTO MANUAL_DATE;
=>
SELECT run_mode, date_manual
INTO v_run_mode, v_manual_date
FROM FND_1200_CONTROL_TABLE

Sans execute immediate

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE OR REPLACE FUNCTION F_1300_GET_DATE
RETURN DATE AS
 
 v_counter NUMBER;
 v_processing_date DATE;
 v_manual_date DATE;
 v_run_mode NUMBER(1) DEFAULT 0;
 
BEGIN
 
  -- get the run mode from the control table "FND_1200_CONTROL"
  SELECT COUNT(*) INTO v_counter FROM FND_1200_CONTROL_TABLE
 
  IF v_counter = 1
  THEN 
    SELECT run_mode, date_manual
    INTO v_run_mode, v_manual_date
    FROM FND_1200_CONTROL_TABLE;
	END IF;
 
 
  IF v_run_mode = 0
  THEN 
  	-- last day of the previous month
  	v_processing_date := LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),-1));
  ELSIF v_run_mode = 1
  THEN 
  	-- current date
	  v_processing_date := TRUNC(SYSDATE);
  ELSIF v_run_mode = 2
  THEN 
  	-- set the date manual
  	v_processing_date := TRUNC(v_manual_date);
  END IF;
 
	RETURN v_processing_date;
 
END F_1300_GET_DATE;

Ceci peut être amélioré

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE OR REPLACE FUNCTION F_1300_GET_DATE
RETURN DATE AS
 
 v_manual_date DATE;
 v_run_mode NUMBER(1) := 0;
 
BEGIN
 
  -- get the run mode from the control table "FND_1200_CONTROL"
  BEGIN
    SELECT run_mode, date_manual
    INTO v_run_mode, v_manual_date
    FROM FND_1200_CONTROL_TABLE;
	EXCEPTION WHEN NO_DATA_FOUND THEN NULL; 
  WHEN TOO_MANY_ROWS THEN NULL;	
  END;
 
  IF v_run_mode = 0
  THEN 
  	-- last day of the previous month
  	RETURN LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),-1));
  ELSIF v_run_mode = 1
  THEN 
  	-- current date
	  RETURN TRUNC(SYSDATE);
  ELSIF v_run_mode = 2
  THEN 
  	-- set the date manual
  	RETURN TRUNC(v_manual_date);
  END IF;
 
	RETURN TO_DATE(NULL);
 
END F_1300_GET_DATE;

S'il faut garder l'execute immediate

Code:

1
2
3
4
5
BEGIN
    EXECUTE IMMEDIATE 'SELECT run_mode, date_manual FROM FND_1200_CONTROL_TABLE' INTO v_run_mode, v_manual_date;
  EXCEPTION WHEN NO_DATA_FOUND THEN NULL; 
WHEN TOO_MANY_ROWS THEN NULL;	
END;

18/09/2019, 17h44
yann1903

Merci pour ta reponse au aussi pour la video a youtube :)

mais dit moi: que fait cette fonction au juste?

Merci de me repondre
Coridallement
18/09/2019, 17h57
McM

Si la table FND_1200_CONTROL_TABLE contient 1 ligne et que le champ run_mode est 1, elle renvoie la date du jour
Si la table FND_1200_CONTROL_TABLE contient 1 ligne et que le champ run_mode est 2, elle renvoie la date_manual de la table FND_1200_CONTROL_TABLE
Si la table FND_1200_CONTROL_TABLE contient 1 ligne et que le champ run_mode n'est ni 0, 1 ou 2, elle ne renvoie rien
Dans tous les autres cas (pas de ligne, plusieurs lignes, 1 ligne et run_mode = 0), elle renvoie le dernier jour du mois précédent