Home » Non-English Forums » French » Exemple de récupération de données d'une page Web via SQL (10.2 et +)
Exemple de récupération de données d'une page Web via SQL [message #478858] Wed, 13 October 2010 01:14
Michel Cadot
Messages: 58902
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ceci n'est pas une question mais juste le partage d'un exemple de fonction PL/SQ récupérant des informations d'une page Web.

Cet exemple montre une manière de récupérer une page Web, d'extraire les informations utiles de la page, de les convertir en XML et d'utiliser les fonctions XML du SQL d'Oracle pour retourner le résultat désiré.

Ici j'ai pris le cas des records d'athlétisme donnés dans des pages du site de l'Equipe (http://www.lequipe.fr/Athletisme/).

La fonction prend 3 paramètres :
* le type de record (monde, olympique, europe, france)
* l'épreuve (ou "spécialité" dans le code) (voir le dernier exemple ci-dessous pour connaître les valeurs). La fonction utilise l'opérateur LIKE sur la valeur donnée à ce paramètre
* le genre (homme ou femme)

Voici le script de création de la fonction et des types associés :
drop type records;
drop type record;
create or replace type record as object (
  type_rec    varchar2(9),
  genre       varchar2(5),
  specialite  varchar2(13),
  athlete     varchar2(21),
  pays        varchar2(4),
  performance varchar2(11),
  vent        varchar2(5),
  perf_date   date,
  competition varchar2(33)
  )
/
create or replace type records is table of record
/
set define #
create or replace function recup_record (
  p_record     in varchar2 default 'monde',
  p_specialite in varchar2 default '%',
  p_genre      in varchar2 default 'homme'
  ) 
return records pipelined
is
  type page_url is table of varchar2(30) index by varchar2(30);
  g_base_url   constant varchar2(50) := 
                 'http://www.lequipe.fr/Athletisme/';
  g_page_url   page_url;
  g_page       clob;
  g_record     varchar2(10) := nvl (upper (p_record), 'MONDE');
  g_genre      varchar2(5)  := nvl (upper (rtrim (p_genre,'S')), 'HOMME');

  l_specialite varchar2(15) := nvl (initcap (p_specialite), '100m');
  i            pls_integer  := 0;

  procedure verif_parametre
  is
  begin
    if g_record not in ('MONDE','WORLD','OLYMPIQUE','OLYMPIC','EUROPE','FRANCE')
    then 
      raise_application_error 
        (-20000, 'Type de record ('||g_record||') invalide');
    end if;
    if g_genre not in ('HOMME','MAN','MEN','FEMME','WOMAN','WOMEN') then
      raise_application_error (-20000, 'Genre ('||g_genre||') invalide');
    end if;
  end;

  procedure initialise 
  is
  begin
    g_record := 
      case g_record
        when 'WORLD'   then 'MONDE'
        when 'OLYMPIC' then 'OLYMPIQUE'
        else g_record
      end;
    g_genre := 
      case g_genre
        when 'MAN' then 'HOMME'
        when 'MEN' then 'HOMME'
        when 'WOMAN' then 'FEMME'
        when 'WOMEN' then 'FEMME'
      else g_genre
      end;
    g_page_url('MONDE/HOMME')     := 'REC_OH.html';
    g_page_url('MONDE/FEMME')     := 'REC_OF.html';
    g_page_url('OLYMPIQUE/HOMME') := 'REC_OLYMP_H.html';
    g_page_url('OLYMPIQUE/FEMME') := 'REC_OLYMP_F.html';
    g_page_url('EUROPE/HOMME')    := 'REC_EUR_OH.html';
    g_page_url('EUROPE/FEMME')    := 'REC_EUR_OF.html';
    g_page_url('FRANCE/HOMME')    := 'REC_FRA_OH.html';
    g_page_url('FRANCE/FEMME')    := 'REC_FRA_OF.html';
  end;

  procedure recup_page (p_record in varchar2, p_genre in varchar2)
    -- Récupère la page correspondant au type de record et au genre
  is 
    l_pieces utl_http.html_pieces;
  begin
    -- Si un proxy est utilisé, l'instruction suivante doit être
    -- remplacée par :
    -- l_pieces := utl_http.request_pieces (
    --               g_page_url(g_base_url||g_page_url(p_record||'/'||p_genre),
    --               proxy => 'user:password@proxy_url:proxy_port'
    --             );
    l_pieces := 
      utl_http.request_pieces (g_base_url||g_page_url(p_record||'/'||p_genre));
    dbms_lob.createtemporary (lob_loc => g_page,
                              cache   => TRUE,
                              dur     => dbms_lob.call);
    for i in 1..l_pieces.count loop
      g_page := g_page || l_pieces(i);
    end loop;
  end;

  procedure recup_table 
    -- Récupère et conserve seulement la table désirée de "g_page"
  is
  begin
    -- La table désirée est la première, on ne garde que la partie "tbody" :
    g_page := substr (g_page, instr (g_page, '<tbody>'));
    -- Supprime tout ce qu'il y a derrière le premier "</tbody>"
    g_page := substr (g_page, 1, instr (g_page, '</tbody>')+7);
  end;

  procedure normalise
    -- Normalise le contenu de "g_page" pour avoir un XML
  is
  begin
    -- Supprime tous les retours à la ligne
    g_page := replace (g_page, chr(10), '');
    g_page := replace (g_page, chr(13), '');
    -- Remplace tout ensemble d'espaces contigues par une seule
    g_page := regexp_replace (g_page, '[[:space:]]+', ' ');
    -- Remplace "&nbsp;" par une espace (Oracle semble ne pas aimer "&nbsp;")
    g_page := replace (g_page, '&nbsp;', ' ');
    -- Découpe la partie nominative en deux cellules pays + nom
    g_page := regexp_replace (g_page, 
                              ' class="equipe"><div class="drapeau (...)"> </div> ', 
                              '>\1</td>');
    g_page := regexp_replace (g_page, ' class="equipe"> ', '>FRA</td>'); -- Cas record de France
    g_page := regexp_replace (g_page, ' *<a class="(lien|lfra)" href="[^"]+"> *', '');
    g_page := regexp_replace (g_page, '<br>[^/]+/a>', '');
    g_page := regexp_replace (g_page, '</a>[^<]*', '');
    g_page := regexp_replace (g_page,
                              '<strong> *([^<]+) *</strong>',
                              '<td>\1');
    -- Remplace les chaînes HTML par le caractère correspondant
    g_page := replace (g_page, '&quot;', '"');
    g_page := replace (g_page, '&eacute;', 'é');
    g_page := replace (g_page, '&egrave;', 'è');
    g_page := replace (g_page, '&ouml;', 'ö');
    g_page := replace (g_page, '&iuml;', 'ï');
    g_page := replace (g_page, '&uuml;', 'ü');
    g_page := replace (g_page, '&acirc;', 'â');
    g_page := replace (g_page, '&ocirc;', 'ô');
  end;

  procedure affiche (p_chaine in clob)
    -- Affiche une chaîne en lignes de largeur "ligne_lg" 
  is 
    ligne_lg constant pls_integer := 200; -- Lignes de 200 caractères max.
    lg       constant pls_integer := length(p_chaine);
    i        pls_integer := 1;
  begin
    while i <= lg loop
      dbms_output.put_line (substr (p_chaine, i, ligne_lg));
      i := i + ligne_lg;
    end loop;
  end;

begin
  -- Initialisation
  verif_parametre;
  initialise;
  -- Récupère la table désirée et normalise la comme en XML
  recup_page (g_record, g_genre);
  recup_table;
  normalise;
-- affiche (g_page); affiche ('--------------------------'); -- return -- DEBUG
  -- Récupère les valeurs demandées
  for rec in (
    select initcap (g_record) type_rec, initcap (g_genre) genre,
           initcap (trim (extractvalue (value(tr), '//td[position()=1]'))) specialite,
           initcap (trim (extractvalue (value(tr), '//td[position()=3]'))) nom,
           trim (extractvalue (value(tr), '//td[position()=2]')) pays,
           trim (extractvalue (value(tr), '//td[position()=4]')) performances, 
           ltrim (rtrim (trim (extractvalue (value(tr), '//td[position()=5]'))
                 , ')'), '(') vent,
           to_date (trim (extractvalue (value(tr), '//td[position()=6]')),
                    'DD/MM/RR') "DATE",
           initcap (trim (extractvalue (value(tr), '//td[position()=7]'))) competition
    from ( select xmltype (g_page) val from dual ) tt,
         table (xmlsequence (extract (tt.val, '/tbody/tr'))) tr
    where lower (extractvalue (value(tr), '//td[position()=1]')) like 
           '%'||lower(l_specialite)||'%'
  ) loop
    pipe row (record(
                rec.type_rec, rec.genre, rec.specialite, rec.nom, rec.pays, 
                rec.performances, rec.vent, rec.date, rec.competition
              ));
    i := i + 1;
  end loop;
  pipe row (null);
-- dbms_output.put_line ('Found '||i||' records'); DEBUG
exception
  when no_data_needed then
    -- Sauter une ligne
    pipe row (null);
  when others then -- pour deboguer
    affiche (dbms_utility.format_error_backtrace);
    affiche ('>>> contenu de g_page au moment de l''exception, enregistrement '||i||' <<<');
    affiche (g_page);
    affiche ('>>> Fin du contenu de g_page <<<');
    affiche (' ');
    raise;
end;
/
show error
set define &

Voici quelques exemples d'appel:
SQL> select * from table(recup_record('monde','100m','homme'));
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
Monde     Homme 100m          U. Bolt               JAM  9"58        +0.90 16-AUG-2009 Berlin (Allemagne)
Monde     Homme 4x100m        Jamaïque              JAM  37"10             22-AUG-2008 Pékin (Chine)

SQL> select * from table(recup_record('monde','1000m','femme'));
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
Monde     Femme 1000m         S. Masterkova         RUS  2'28"98           23-AUG-1996 Bruxelles (Belgique)

SQL> select * from table(recup_record('olympique','4x'));
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
Olympique Homme 4x100m        Jamaïque              JAM  37"10             22-AUG-2008 Pékin (Chine)
Olympique Homme 4x400m        Etats-Unis            USA  2'55"39           23-AUG-2008 Pékin (Chine)

SQL> select * from table(recup_record('olympique','longueur','femme'));
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
Olympique Femme Longueur      J. Joyner-Kersee      USA  7m40        +0.90 29-SEP-1988 Séoul (Corée Du Sud)

SQL> select * from table(recup_record('europe','triple'));
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
Europe    Homme Triple Saut   J. Edwards            GBR  18m29       +1.30 07-AUG-1995 Göteborg (Suède)

SQL> select * from table(recup_record('europe','hauteur','femme'));
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
Europe    Femme Hauteur       S. Kostadinova        BUL  2m09              30-AUG-1987 Rome (Italie)

SQL> select * from table(recup_record('france','décathlon'));
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
France    Homme Décathlon     C. Plaziat            FRA  8574 pts          29-AUG-1990 Split (Yougoslavie)

SQL> select * from table(recup_record('france','poids','femme'));
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
France    Femme Poids         L. Manfredi           FRA  18m68             27-JUL-2000 Castres (France)

SQL> select * from table(recup_record());
TYPE_REC  GENRE SPECIALITE    ATHLETE               PAYS PERFORMANCE VENT  PERF_DATE   COMPETITION
--------- ----- ------------- --------------------- ---- ----------- ----- ----------- ---------------------------------
Monde     Homme 100m          U. Bolt               JAM  9"58        +0.90 16-AUG-2009 Berlin (Allemagne)
Monde     Homme 200m          U. Bolt               JAM  19"19       -0.30 20-AUG-2009 Berlin (Allemagne)
Monde     Homme 400m          M. Johnson            USA  43"18             26-AUG-1999 Séville (Espagne)
Monde     Homme 800m          D. Rudisha            KEN  1'41"01           29-AUG-2010 Rieti (Italie
Monde     Homme 1000m         N. Ngeny              KEN  2'11"96           05-SEP-1999 Rieti (Italie)
Monde     Homme 1500m         H. El Guerrouj        MAR  3'26"00           14-JUL-1998 Rome (Italie)
Monde     Homme Mile          H. El Guerrouj        MAR  3'43"13           07-JUL-1999 Rome (Italie)
Monde     Homme 2000m         H. El Guerrouj        MAR  4'44"79           07-SEP-1999 Berlin (Allemagne)
Monde     Homme 3000m         D. Komen              KEN  7'20"67           01-SEP-1996 Rieti (Italie)
Monde     Homme 5000m         K. Bekele             ETH  12'37"35          31-MAY-2004 Hengelo (Pays-Bas)
Monde     Homme 10000m        K. Bekele             ETH  26'17"53          26-AUG-2005 Bruxelles (Belgique)
Monde     Homme Semi-Marathon S. Wanjiru            KEN  58'33"            17-MAR-2007 La Haye (Pays-Bas)
Monde     Homme Marathon      H. Gebreselassie      ETH  2h03'59"          28-SEP-2008 Berlin (Allemagne)
Monde     Homme 110m Haies    D. Robles             CUB  12"87       +0.90 12-JUN-2008 Ostrava (République Tchèque)
Monde     Homme 400m Haies    K. Young              USA  46"78             06-AUG-1992 Barcelone (Espagne)
Monde     Homme 3000m St.     S. Shaheen (Cherono)  QAT  7'53"63           03-SEP-2004 Bruxelles (Belgique)
Monde     Homme Hauteur       J. Sotomayor          CUB  2m45              27-JUL-1993 Salamanque (Espagne)
Monde     Homme Longueur      M. Powell             USA  8m95        +0.30 30-AUG-1991 Tokyo (Japon)
Monde     Homme Perche        S. Bubka              UKR  6m14              31-JUL-1994 Sestrières (Italie)
Monde     Homme Triple Saut   J. Edwards            GBR  18m29       +1.30 07-AUG-1995 Göteborg (Suède)
Monde     Homme Poids         R. Barnes             USA  23m12             20-MAY-1990 Westwood (Etats-Unis)
Monde     Homme Disque        J. Schult             RDA  74m08             06-JUN-1986 Neubrandenbourg (Rda)
Monde     Homme Marteau       Y. Sedykh             URS  86m74             30-AUG-1986 Stuttgart (Rfa)
Monde     Homme Javelot       J. Zelezny            RTC  98m48             25-MAY-1996 Iena (Allemagne)
Monde     Homme Décathlon     R. Sebrle             RTC  9026 pts          27-MAY-2001 Götzis (Autriche)
Monde     Homme 20km Marche   V. Kanaykin           RUS  1h17'16"          30-SEP-2007 Saransk (Russie)
Monde     Homme 50km Marche   D. Nizhegorodov       RUS  3h34'14"          11-MAY-2008 Cheboksary (Russie)
Monde     Homme 4x100m        Jamaïque              JAM  37"10             22-AUG-2008 Pékin (Chine)
Monde     Homme 4x400m        Etats-Unis            USA  2'54"20           22-JUL-1998 Uniondale (Etats-Unis)
Monde     Homme 4x800m        Kenya                 KEN  7'02"43           25-AUG-2006 Bruxelles (Belgique)

Cordialement,
Michel
Previous Topic: Modifier NLS_NCHAR_CHARACTERSET !
Next Topic: Problème restauration Oracle via Networker
Goto Forum:
  


Current Time: Wed Aug 27 06:52:45 CDT 2014

Total time taken to generate the page: 0.05828 seconds