Probleme condition sur select PL/SQL

**l4r3nZu** · 02/06/2010, 11h34

Bonjour,

Voila mon problème j'aimerai savoir si une variable est déjà enregistrée en table afin de savoir si celui si est un doublon ou pas.

Au debut j'ai essayé avec un curseur sur la table a interroger en passant donc par des variables mais le traitement était super long.

Là j'essaie de passer par une interrogation de base mais je rencontre un problème, quand ma requête ne contient pas résultat (donc pas un doublon) cela m'arrête le programme.

Voici mon code n'y a-t-il pas une manière plus intelligente de le coder ?

Merci d'avance.

**Waldar** · 02/06/2010, 11h43

Si vous voulez éviter les doublons, pourquoi ne pas simplement mettre une contrainte d'unicité sur votre colonne ?

**l4r3nZu** · 02/06/2010, 11h54

Je ne veux pas les éviter mais juste savoir si mon identifiant a déja été rencontré pour effectuer des cumuls par la suite.

Pour mieux expliquer :

Je lis un fichier ligne par ligne
J'extrait de cette ligne des données en variable
Si l'identifiant est déja enregistré en table alors traitement1
SI l'identifiant n'est pas enregistré en table alors traitement2

**l4r3nZu** · 02/06/2010, 12h06

Et quand je dis traitement c'est pas mal de lignes de code (200 si pas doublons et 100 si doublon 4 cumuls avec pour chaque cumul des conditions).

En fait avec un curseur ca marchait nickel, mais le problème est le temps d'exécution que je dois diminuer de 30-40 %.
Et je pense que c'est vraiment à ce niveau que je peux optimiser.

Mon code à optimiser :

**McM** · 02/06/2010, 12h07

Des doublons avec un like ? C'est bizarre !

mais bon, la bonne manière de tester des doublons en select c'est de gérer les exception.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE 
	v NUMBER;
BEGIN 
  SELECT 1
  INTO v
  FROM TA_INT_SAS
  WHERE NUMOPER LIKE '%'||my_CRO.NOREFINT||'%' 
  AND ROWNUM= 1;
  eDoublon := TRUE;
EXCEPTION WHEN NO_DATA_FOUND 
THEN eDoublon := FALSE;
END;

**l4r3nZu** · 02/06/2010, 12h09

YES !! Je n'avais pas pensé à coder une exception !

Merci beaucoup !

**McM** · 02/06/2010, 12h12

Cela dit, si tu veux aussi qu'on t'aide à optimiser ton traitement, y'a pas de problèmes, t'as qu'à poster le code complet.

**McM** · 02/06/2010, 13h48

L'exception n'est pas à mettre à la fin de ta procédure !
Voilà ce que tu as fait :

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
BEGIN
   LOOP
        IF eUnitaire=TRUE
        THEN
          SELECT NUMOPER
          INTO my_doublonSAS.NUMOPER
          FROM TA_INT_SAS
          WHERE NUMOPER LIKE '%'||my_CRO.NOREFINT||'%';
       END IF;
 
       IF eDoublon = TRUE.
       THEN
       	..
        END IF;
        END LOOP;
    EXCEPTION 
    WHEN NO_DATA_FOUND 
        THEN eDoublon := FALSE;
END;

Donc s'il n'y a pas de doublon, tu sors de ta boucle et de la procédure.
Il faut faire ceci :

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
BEGIN
   LOOP
        IF eUnitaire=TRUE
        THEN
          BEGIN
       SELECT NUMOPER
          INTO my_doublonSAS.NUMOPER
          FROM TA_INT_SAS
          WHERE NUMOPER LIKE '%'||my_CRO.NOREFINT||'%' AND rownum = 1;
  EXCEPTION 
    WHEN NO_DATA_FOUND 
        THEN eDoublon := FALSE;
       END IF;
 
       IF eDoublon = TRUE.
       THEN
       	..
        END IF;
        END LOOP;
 
END;

Attention le rownum est important, parce que si tu as plusieurs lignes, l'exception levée sera too_many_rows.

Sinon, je ne comprends pas vraiment le code. Pourquoi passer par un LIKE et récupérer le NUMOPER dans my_doublonSAS.NUMOPER ?
Si tu as 3 lignes avec comme numoper A, AA, AAB et que ton NOREFINT vaut A, qu'est ce que tu dois faire ?

**l4r3nZu** · 02/06/2010, 15h19

En fait j'initialise eDoublon à TRUE pas à FALSE, donc si ma requête trouve un resultat l'exception ne se déclenche pas et eDoublon sera à TRUE et effectuera les traitements de TRUE.

J'ai testé ta solution et j'ai gagné 1/3 de temps vraiment merci.

**l4r3nZu** · 02/06/2010, 15h59

Ha oui aussi pour lire mon fichier et en extraire les données qui m'interresse je passe par une fonction SQL*LOAD qui charge ces données en base....je pensse que si j'arriverai a lire mon fichier avec PL*SQL et attribuer les positions a des variables ca me ferai gagner énormement de temps
Par exemple :

ouverture fichier
pour chaque ligne
ma variable <--- chaine pos 100 à 115
fin
fermeture

merci d'avance

**Waldar** · 02/06/2010, 16h06

Quelle est votre version d'Oracle ?

**l4r3nZu** · 02/06/2010, 16h10

SQL*Plus: Release 10.2.0.4.0

**Waldar** · 02/06/2010, 16h19

SQL*Plus c'est le client avec lequel vous vous connectez à Oracle mais ce n'est pas votre base.

Vous aurez la réponse en exécutant la requête suivante :

Code :

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

select * from sys.v_$version

Celà dit on peut supposer que vous êtes en version 10g.
Vous pouvez créer une table en mode EXTERNAL :

Toute la partie SQL*Loader devient alors transparente.

**l4r3nZu** · 02/06/2010, 16h21

oui je sais mais ca va de pair

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

**Waldar** · 02/06/2010, 16h23

Non pas forcément. Avec votre client 10g vous pouvez très bien vous connecter à une base 8i ou 9i ou encore 11g.

**l4r3nZu** · 02/06/2010, 16h46

Je pensse pas que cela ai une utilité dans mon cas , car cela me permet juste de remplacer mon SQL*LOAD alors que je veux au contraire sauter cette étape :
Je vais mieu expliquer mon cas :

Ce que je fai actuellement :

un fichier en entrée
Je charge ce fichier dans une premiere table (qui me sert a rien concretement a part a récolter les données du fichier le but étant alimenter au autre table)
a partir de la table chargé :
je passe les données de ma table en variable afin d'effectuer différent traitement ... une fois ces traitements terminés j'aliment une 2em table.

Ce qui je voudrai faire :

lire mon fichier
Pour chaque ligne de mon fichier
en extraire les champs que me seront utils dans des variables
Effectuer mes traitements
Alimenter ma table

voila j'espere avoir été assez clair ,
ce que je voudrai savoir c'est comment passer des champs de positions de chaque lignes de mon fichier dans des variables PL*SQL

merci d'avance

**l4r3nZu** · 02/06/2010, 16h51

viens de trouver une solution pour recuperer ma ligne :

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
Declare
  -- Noms des fichiers --
  LC$Fic_in   Varchar2(128) := 'EMP.TXT' ;       -- a adapter sur votre configuration
  LC$Fic_out  Varchar2(128) := 'EMP2.TXT' ;      -- a adapter sur votre configuration
  -- Noms des répertoires --
  LC$Dir_in   Varchar(30)   := 'FICHIERS_IN';    -- a adapter sur votre configuration
  LC$Dir_out  Varchar(30)   := 'FICHIERS_OUT' ;  -- a adapter sur votre configuration
  -- Pointeurs de fichier --
  LF$FicIN	UTL_FILE.FILE_TYPE ;
  LF$FicOUT	UTL_FILE.FILE_TYPE ;
  -- Tampon de travail --  
  LC$Ligne	Varchar2(32767) ;  
  -- Message --
  LC$Msg    Varchar2(256) ;
  -- Exception --
  LE$Fin    Exception ;
Begin
 
  -- Ouverture du fichier en entrée
  Begin
    LF$FicIN := UTL_FILE.FOPEN( LC$Dir_in, LC$Fic_in, 'R', 32764 ) ;
  Exception
    When OTHERS Then
	LC$Msg := SQLERRM || ' [' || LC$Dir_in || '] -> ' || LC$Fic_in;
	Raise LE$Fin ;
  End ;
 
  -- Ouverture du fichier en sortie
  Begin
    LF$FicOUT := UTL_FILE.FOPEN( LC$Dir_out, LC$Fic_out, 'W', 32764 ) ;
  Exception
    When OTHERS Then
	LC$Msg := SQLERRM || ' [' || LC$Dir_out || '] -> ' || LC$Fic_out;
	Raise LE$Fin ;
  End ;	
 
  -- Traitement --
  Begin
     Loop
	-- lecture du fichier en entrée --
	UTL_FILE.GET_LINE( LF$FicIN,  LC$Ligne ) ;
	-- écriture du fichier en sortie --
	UTL_FILE.PUT_LINE( LF$FicOUT, LC$Ligne ) ;
     End loop ;
  Exception
     When NO_DATA_FOUND Then -- Fin du fichier en entrée
        -- Fermeture des fichiers --
	UTL_FILE.FCLOSE( LF$FicIN ) ;
	UTL_FILE.FCLOSE( LF$FicOUT ) ;
  End  ;		 		
 
Exception
   When LE$Fin Then
     UTL_FILE.FCLOSE_ALL ;
     RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
End ;

n'est il pas possible a partir de ma ligne recuperer des champs en tronquant ma chaine du style mavar := substring(maligne , 10 , 15); ???

**McM** · 02/06/2010, 16h56

C'est SUBSTR sous Oracle.

Et tu as INSTR pour rechercher la position d'un caractère (ou du Xème caractère) dans une chaine.

**l4r3nZu** · 02/06/2010, 17h03

Okay thx j'implemente cela et vous fait un pti retour sur combien jai gagné en temps d'éxécution

**mnitu** · 02/06/2010, 17h08

Envoyé par l4r3nZu

...
Ce qui je voudrai faire :

lire mon fichier
Pour chaque ligne de mon fichier
en extraire les champs que me seront utils dans des variables
Effectuer mes traitements
Alimenter ma table
...

Bref, cela revient à faire

Code :

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

1
2
3
4
5
 
INSERT/UPDATE/MERGE Into ma_table
Select mes_colonnes
  from (table(ma_procédure_pipelined_qui_utilise_une_table_externe))
/

ou des autres variations de cette solution.
Improving performance with pipelined table functions

Probleme condition sur select PL/SQL

Oracle

Discussions similaires

Partager

Partager