Discussion :

[alichoupi]

Bonjour tout le monde,

J'ai créé une procédure stockée en Transact SQL et j'aimerai la mettre en place pour Oracle.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DECLARE @date DATETIME
	DECLARE @Unit SMALLINT
	DECLARE @Long SMALLINT
	SELECT @Unit = Base1.dbo.T_LI.UNIT from T_LI
	SELECT @Long = Base1.dbo.T_LI.LONG from T_LI
	SELECT @Long = -@Long_Archivage -- Mets là valeur en négatif
 
IF @Unit_Archivage = 2
BEGIN
SELECT @date = DATEADD(month, @, CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME))
END

Le but de ma procédure est de supprimer des valeurs qui deviennent trop anciennes.

Comment puis-je mettre cela en place en PL/SQL ?

[orafrance]

Tu parles de suppression mais j'vois pas de DELETE

Elle fait quoi exactement cette fonction ?

Je pense que ce serait un truc de ce style pour le début :

Code :

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

1
2
3
4
5
6
7
8
DECLARE
I_Unit INTEGER;
I_Long INTEGER;
BEGIN
 
SELECT T_LI.UNIT, T_LI.LONG
INTO  I_Unit, I_Long
FROM T_LI;

Mais après, je ne comprends pas ce que fait le reste

Pour info, date du jour c'est TRUNC(SYSDATE) (aujourd'hui à 0h00) dans Oracle, SYSDATE étant la date plus l'heure.

[alichoupi]

Exact ! Que je suis bête ...

Le but de ma procédure est d'insérer les valeurs qui deviennent trop anciennes dans une nouvelle base de données Archive puis les supprimer dans la base courante.

Voilà en fichier joint la procédure que j'ai réalisé en Transact SQL.

[mnitu]

Supprimez la partie de la procédure qui crée les tables si elles n'existent pas. Ce n'est pas la façon de procéder avec Oracle, ces objets doivent déjà être crées à l'exécution de la procédure.

Maintenant, ce qui reste est une suite des 3 INSERT Into … Select From... suivis des 3 DELETE … qui semble à ne pas poser aucune difficulté. Sauf que, en Oracle au moins, faire ce traitement de cette manière pourrait donner des résultat incorrectes, vu que le niveau d'isolation par défaut des transactions en Oracle est READ_COMMITED.

[orafrance]

Sauf que, en Oracle au moins, faire ce traitement de cette manière pourrait donner des résultat incorrectes, vu que le niveau d'isolation par défaut des transactions en Oracle est READ_COMMITED.

Tant qu'il ne se reconnecte pas ça doit pas poser de problème. Oracle est en READ_COMMITED mais voit tout de même les lignes modifiées dans sa session.

[mnitu]

Tant qu'il ne se reconnecte pas ça doit pas poser de problème. Oracle est en READ_COMMITED mais voit tout de même les lignes modifiées dans sa session.

t0. Transaction T1 insert dans la table un enregistrement ou dat_rec < &date sans faire commit.

t1. La transaction T2, qui fait l'archivage, démarre en faisaient l'INSERT INTO... SELECT... FROM …. Le niveau d'isolation par défaut READ_COMMITED garanti que l'enregistrement inséré par la transaction T1 n'est pas visible pour T2.

t2. La transaction T1 fait commit.

t3. La transaction T2 passe à la partie DELETE. L'enregistrement inséré par T1 est désormais visible et donc elle est supprimé sans avoir être archivé.

[orafrance]

Pour moi de toute façon, l'idéal c'est un trigger BEFORE DELETE qui va insérer les lignes supprimées

Par contre, je ne comprends pas bien ton exemple. Là il n'est pas question de faire un COMMIT au milieu

Ca doit ressembler à un truc du genre le code :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
CREATE PROCEDURE PSARCHIVAGE AS
DECLARE
dateArchivage  DATE;
Unit_Archivage T1_LIGNE.UNIT_ARCHIVAGE%TYPE;
Long_Archivage T1_LIGNE.LONG_ARCHIVAGE%TYPE;
 
BEGIN
  -- si T1_PRODUIT n'existe pas -> NO_DATA_FOUND -> création de la table
  BEGIN
    SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM user_tables WHERE table_name='T1_PRODUIT');
 
  EXCEPTION WHEN NO_DATA_FOUND THEN
    EXECUTE IMMEDIATE ('CREATE TABLE t1_produit (id_produit INTEGER NOT NULL PRIMARY KEY, date_last_modif DATE NOT NULL, idecli VARCHAR2(40) NOT NULL, num_ligne INTEGER NOT NULL, reference VARCHAR2(30) NOT NULL, lot VARCHAR2(30), odf VARCHAR2(30))');
  END;
 
  -- si T1_DEFPRCD n'existe pas -> NO_DATA_FOUND -> création de la table
  BEGIN
    SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM user_tables WHERE table_name='T1_DEFPRCD');
 
  EXCEPTION WHEN NO_DATA_FOUND THEN
    EXECUTE IMMEDIATE ('CREATE TABLE t1_defprcd (id_defprcd INTEGER NOT NULL PRIMARY KEY, num_ligne INTEGER NOT NULL, num_station INTEGER NOT NULL, date_rec NOT NULL, code_prcd INTEGER NOT NULL)');
  END;
 
  -- si T1_MODMARCH n'existe pas -> NO_DATA_FOUND -> création de la table
  BEGIN
    SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM user_tables WHERE table_name='T1_MODMARCH');
 
  EXCEPTION WHEN NO_DATA_FOUND THEN
    EXECUTE IMMEDIATE ('CREATE TABLE t1_modmarch (id_modmarch INTEGER NOT NULL PRIMARY KEY, num_ligne INTEGER NOT NULL, num_station INTEGER NOT NULL, date_rec NOT NULL, code_mode INTEGER NOT NULL)');
  END;
 
	SELECT T1_LIGNE.UNIT_ARCHIVAGE,T1_LIGNE.LONG_ARCHIVAGE * (-1) INTO Unit_Archivage,Long_Archivage  from T1_LIGNE;
 
 
  IF Unit_Archivage = 2
   dateArchivage = ADD_MONTHS(SYSDATE,Long_Archivage);
  ELSE
   -- j'aoute 7 fois le nombre de semaine pour ajouter le nombre de jour correspondant
   dateArchivage = SYSDATE + (Long_Archivage*7);
  END IF;
 
 
  INSERT INTO Base_Archive.t1_modmarch
    SELECT num_ligne, num_station, date_rec, code_mode
	  FROM t1_modmarch
	 WHERE date_rec < dateArchivage;
 
  INSERT INTO Base_Archive.t1_defprcd
    SELECT num_ligne, num_station, date_rec, code_prcd
	  FROM t1_defprcd
	 WHERE date_rec < dateArchivage;
 
  INSERT INTO Base_Archive.t1_produit
    SELECT date_last_modif, idecli, num_ligne, reference, lot, odf
	  FROM t1_produit
	 WHERE date_last_modif < dateArchivage;
 
 
  DELETE FROM t1_produit WHERE date_last_modif < dateArchivage;
 
  DELETE FROM t1_modmarch	WHERE date_rec < dateArchivage;
 
  DELETE FROM t1_defprcd WHERE date_rec < dateArchivage;
 
  COMMIT;
END;
/

Et on peut remplacer la fin par :

Code :

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

1
2
3
4
5
  DELETE FROM t1_produit WHERE id_produit IN ( SELECT id_produit FROM Base_Archive.t1_modmarch);
 
  DELETE FROM t1_modmarch WHERE id_modmarch IN ( SELECT id_modmarch FROM Base_Archive.t1_modmarch);
 
  DELETE FROM t1_defprcd WHERE id_defprcd IN ( SELECT id_defprcd FROM Base_Archive.t1_defprcd);;

Puisqu'on a les PK

[mnitu]

...
Là il n'est pas question de faire un COMMIT au milieu
...

Je n'ai jamais dit ça ni l'ai suggéré.
Et parce qu'il s'agit d'une procédure d'archivage il y a pas mal de chances que l'on peut faire quelque chose de bien plus simple

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
-- interdire l'accès à la table
  Lock Table ma_table in exclusive mode nowait;
-- Sauvegarde
  Insert Into ma_table_arch 
  Select * From ma_table
  Where ...
--Suppression 
  Delete ...
Exception
  When RESERVE Then
    Raise_Application_Error ...

Et il reste encore à prendre en compte quelques améliorations.

[orafrance]

J'vois pas l'intérêt de locker la table alors que ce sont les plus anciennes lignes que tu supprimes...

Je n'ai jamais dit ça ni l'ai suggéré.

t2. La transaction T1 fait commit.

c'est quoi ce COMMIT alors ?

[mnitu]

J'vois pas l'intérêt de locker la table alors que ce sont les plus anciennes lignes que tu supprimes...

Pour interdire aux autres transaction de modifier la table ma_table. Tu peux relire le scénario que j'ai proposé ?

c'est quoi ce COMMIT alors ?

C'est la transaction T1 qui fait le commit et non pas T2 qui archive.

[alichoupi]

Merci pour vos réponses.
Cependant je ne comprend pas cette opération :

-- j'aoute 7 fois le nombre de semaine pour ajouter le nombre de jour correspondant

Pouvez vous me l'expliquer ?

[orafrance]

7 * 2 semaines = 14 jours. Si tu veux ajouter 2 semaines à la date du jour, tu dois donc faire SYSDATE + 2*7