Oracle PL/SQL中解析HTML

SQL> drop table t purge;

Table dropped.

SQL> create table t ( c clob );

Table created.

SQL>
SQL> set define off
SQL> begin
  2  insert into t values (
  3  '/*]]>*/
  4  

  5  
List of SEPA countries or territories and their ISO country and currency codes 
  6  
SEPA consists of the 27 EU countries (including the Euro 16 countries) and the additional 3 EEA 
  7  
(European Economic Area) countries. 
  8  
 
  9  
In SEPA messages, countries are denominated by their ISO country codes contained in the BICs 
 10  
and the IBANs. Amounts are expressed in the currencies of these countries, denominated by their 
 11  
ISO currency codes. The list presents the name of the countries or territories with the corresponding 
 12  
ISO country and currency codes. 
 13  
 
 14   '||
 16  ' '||
 17  ''||
 22  ' '||
 23  '  '||
 24  ' '||
 25  ' '||
 26  ' '||
 27  ' '||
 28  '
     
 
 
Country/territory 
 
BIC 
 
IBAN 
 
Currency code '||
 15  '
 
 
 
Åland Islands 
 
FI 
 
FI 
 
EUR 
 
 
 
Austria 
 
AT 
 
AT 
 
EUR 
 
 
 
Belgium 
 
BE 
 
BE 
EUR 
 
 
 
Bulgaria 
 
BG 
 
BG 
 
BGN 
 
 
 
Canary Islands  
 
ES  
 
ES  
 
EUR 
 
 
 
Croatia
 
HR
 
HR
 
HRK
 
 
Cyprus 
 
CY 
 
CY 
 
EUR 
 
 
 
Czech Republic 
 
CZ 
 
CZ 
 
CZK 
 
 
 
Denmark 
 
DK 
 
DK 
 
DKK 
 
 
 
'||
 18  'Estonia 
 
EE 
 
EE 
 
EEK 
 
 
 
Finland 
 
FI 
 
FI 
 
EUR 
 
 
 
France 
 
FR 
 
FR 
 
EUR 
 
 
 
'||
 19  'French Guiana 
 
GF 
 
FR 
 
EUR 
 
 
 
Germany 
 
DE 
 
DE 
 
EUR 
 
 
 
Gibraltar 
 
GI 
 
GI 
 
GIP 
 
 
 
'||
 20  'Greece 
 
GR 
 
GR 
 
EUR 
 
 
 
Guadeloupe 
 
GP 
 
FR 
 
EUR 
 
 
 
Hungary 
 
HU 
 
HU 
 
HUF 
 
 
 
Iceland '||
 21  '
 
IS 
 
IS 
 
ISK 
 
 
 
Ireland 
 
IE 
 
IE 
 
EUR 
 
 
 
Italy 
 
IT 
 
IT 
 
EUR 
 
 
 
Latvia 
 
LV 
LV 
 
EUR
 
 
 
Liechtenstein 
 
LI 
 
LI 
 
CHF 
 
 
 
Lithuania 
 
LT 
 
LT 
 
LTL 
 
 
 
Luxembourg 
 
LU 
 
LU 
EUR 
 
 
 
Malta 
 
MT 
 
MT 
 
EUR 
 
 
 
Martinique 
 
MQ 
 
FR 
 
EUR 
 
 
 
Mayotte 
 
YT 
 
FR 
 
EUR 
 
 
Monaco 
 
MC 
 
MC 
 
EUR 
 
 
 
Netherlands 
 
NL 
 
NL 
 
EUR 
 
 
 
Norway 
 
NO 
 
NO 
 
NOK 
 
 
Poland 
 
PL 
 
PL 
 
PLN 
 
 
 
Portugal  
 
PT 
 
PT 
 
EUR 
 
 
 
Réunion 
 
RE 
 
FR 
 
EUR 
 
 
Romania 
 
RO 
 
RO 
 
RON 
 
 
 
Saint Barthélemy 
 
BL 
 
FR 
 
EUR 
 
 
 
Saint Martin (French part) 
 
MF 
 
FR 
 
EUR 
 
 
Saint Pierre and Miquelon
 
PM 
 
FR 
 
EUR 
 
 
 
Slovakia 
 
SK 
 
SK 
 
EUR 
 
 
 
Slovenia 
 
SI 
 
SI 
 
EUR 
 
 
Spain 
 
ES 
 
ES 
 
EUR 
 
 
 
Sweden 
 
SE 
 
SE 
 
SEK 
 
 
 
Switzerland 
 
CH 
 
CH 
 
CHF 
 
 
 
'||
 29  'United Kingdom 
 
GB 
 
GB 
 
GBP 
 
 
');
 30  end;
 31  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> set define off
SQL> set serverout on
SQL> declare
  2    c1 clob;
  3    l_list varchar2(32000);
  4    pos pls_integer;
  5    endpos pls_integer;
  6
  7    type str_array is table of varchar2(100) index by pls_integer;
  8    l_country str_array;
  9    l_bic str_array;
 10    l_iban str_array;
 11    l_curr str_array;
 12
 13    l_row_count pls_integer := 0;
 14    l_col_count pls_integer := 0;
 15    idx pls_integer;
 16  begin
 17    select c into c1 from t;
 18    pos := dbms_lob.instr(c1,'Country/territory');
 19    pos := dbms_lob.instr(c1,'',pos-50);
 20    endpos := dbms_lob.instr(c1,'',pos);
 21
 22    l_list := dbms_lob.substr(c1,endpos-pos,pos);
 23    l_list := ltrim(rtrim(l_list));
 24
 25    l_list := replace(replace(replace(l_list,''),''),' ');
 26
 27    loop
 28      exit when l_list = '';
 29
 30      if substr(l_list,1,5) in ('','') then
 31         l_list := ltrim(substr(l_list,6));
 32         continue;
 33      end if;
 34
 35      if substr(l_list,1,4) = '' then
 36         l_row_count := l_row_count + 1;
 37         l_list := ltrim(substr(l_list,5));
 38         l_col_count := 0;
 39      end if;
 40
 41      if substr(l_list,1,3) = '')+1));
 44      end if;
 45
 46      idx := instr(l_list,'<');
 47
 48      if l_col_count = 1 then
 49        l_country(l_row_count) := substr(l_list,1,idx-1);
 50      elsif l_col_count = 2 then
 51        l_bic(l_row_count) := substr(l_list,1,idx-1);
 52      elsif l_col_count = 3 then
 53        l_iban(l_row_count) := substr(l_list,1,idx-1);
 54      elsif l_col_count = 4 then
 55        l_curr(l_row_count) := substr(l_list,1,idx-1);
 56      end if;
 57
 58      l_list := ltrim(substr(l_list,idx));
 59
 60    end loop;
 61
 62    for i in 2 .. l_row_count loop
 63      dbms_output.put_line(
 64        rpad(l_country(i),30)||
 65        rpad(l_bic(i),10)||
 66        rpad(l_iban(i),10)||
 67        rpad(l_curr(i),10)
 68        );
 69    end loop;
 70
 71  end;
 72  /
Åland Islands           FI        FI        EUR
Austria                       AT        AT        EUR
Belgium                       BE        BE        EUR
Bulgaria                      BG        BG        BGN
Canary Islands                ES        ES        EUR
Croatia                       HR        HR        HRK
Cyprus                        CY        CY        EUR
Czech Republic                CZ        CZ        CZK
Denmark                       DK        DK        DKK
Estonia                       EE        EE        EEK
Finland                       FI        FI        EUR
France                        FR        FR        EUR
French Guiana                 GF        FR        EUR
Germany                       DE        DE        EUR
Gibraltar                     GI        GI        GIP
Greece                        GR        GR        EUR
Guadeloupe                    GP        FR        EUR
Hungary                       HU        HU        HUF
Iceland                       IS        IS        ISK
Ireland                       IE        IE        EUR
Italy                         IT        IT        EUR
Latvia                        LV        LV        EUR
Liechtenstein                 LI        LI        CHF
Lithuania                     LT        LT        LTL
Luxembourg                    LU        LU        EUR
Malta                         MT        MT        EUR
Martinique                    MQ        FR        EUR
Mayotte                       YT        FR        EUR
Monaco                        MC        MC        EUR
Netherlands                   NL        NL        EUR
Norway                        NO        NO        NOK
Poland                        PL        PL        PLN
Portugal                      PT        PT        EUR
Réunion                RE        FR        EUR
Romania                       RO        RO        RON
Saint Barthélemy       BL        FR        EUR
Saint Martin (French part)    MF        FR        EUR
Saint Pierre and Miquelon     PM        FR        EUR
Slovakia                      SK        SK        EUR
Slovenia                      SI        SI        EUR
Spain                         ES        ES        EUR
Sweden                        SE        SE        SEK
Switzerland                   CH        CH        CHF
United Kingdom                GB        GB        GBP

PL/SQL procedure successfully completed.

SQL>