PL/SQL中的单词循环

这可以通过单个SQL查询完成,例如。

WITH mydata AS
  (SELECT 1 AS id, 'Sudan China USA Africa' AS string FROM dual
   UNION ALL
   SELECT 2      , 'China Europe Sudan Asia'          FROM dual
  )
   , lookup AS
  (SELECT 'Sudan' AS word FROM dual
   UNION ALL
   SELECT 'China' FROM dual
   UNION ALL
   SELECT 'USA' FROM dual
  )
SELECT t.id
     , max(t.word) KEEP (DENSE_RANK LAST ORDER BY t.pos) lastword
FROM (SELECT mydata.id
           , pos.column_value AS pos
           , regexp_substr(mydata.string, '([^ ])+', 1, pos.column_value ) AS word
      FROM mydata
           CROSS JOIN
             TABLE
               (CAST
                  (MULTISET
                    (SELECT level
                     FROM dual 
                     CONNECT BY level <= regexp_count(mydata.string, '([^ ])+')
                    ) AS sys.odciNumberList
                 )
              ) pos) t
     INNER JOIN lookup l
       ON (t.word = l.word)
GROUP BY t.id

        ID LASTWORD              
---------- -----------------------
         1 USA                     
         2 Sudan                   

declare

function searchWord(inputString in varchar2) return varchar2
is 

TYPE arrayWord IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
word    arrayWord;
tmpStr  varchar2(32767);
i       pls_integer := 0;
tmp     number;
begin
tmpStr := inputString;
loop
  word(i) := (substr(tmpStr,1, instr(tmpStr,' ')-1));
  tmpStr  := substr(tmpStr, instr(tmpStr,' ')+1);
  if instr(tmpStr,' ') = 0 then
    i := i+1;
    word(i):= tmpStr;
    exit;  
  end if;
  i := i+1;
end loop;

if word.count > 0 then
  for i in reverse word.first..word.last
  loop
    select count(*) into tmp from t1 t where t.word = word(i) and rownum = 1;
    if tmp != 0 then
      return word(i);
    end if;
  end loop;
end if;
return null;

end searchWord;

begin
dbms_output.put_line(searchWord('Sudan China USA Africa'));
end;