sql 实现java 中 split 功能

/*
TODO: owner="Jonsan_Guo" created="22-12-2010"
text="用sql做一个想java 中的string 类型 的 split 方法 的功能一样的函数,
      初步, 返回分割后的最后一个字符,
      而且分割字符 只能是char 类型,
     
      以后再完善,像Javasplit一样返回一个数组"
*/

create or replace procedure Split_Pro(armString IN VARCHAR2,
                                      separator in  CHAR,
                                      lastChars out varchar2) IS
                                                                           
  v_armString VARCHAR2(1000):= NULL;
  v_separator CHAR:= NULL;
  v_lastChars VARCHAR2(500):= NULL;
 
  v_separatorCount NUMBER(4,0):= 0;
 
  v_CountLog NUMBER(4):= 0;
                                         
begin
  v_armString := armString;
  v_separator := separator;
  v_lastChars := armString;
  dbms_output.put_line('v_armString   '||v_armString);
  BEGIN
      SELECT length(translate(v_armString,v_separator||v_armString,v_separator))
      INTO   v_separatorCount
      FROM dual;
     
      dbms_output.put_line('v_separatorCount   '||v_separatorCount);
 
  EXCEPTION
  WHEN OTHERS THEN
      v_separatorCount := 0;
      dbms_output.put_line('SQLCODE: '||SQLCODE||chr(10)||'SQLERRM: '||SQLERRM );
  END;
 
      dbms_output.put_line('v_separatorCount'||v_separatorCount);
  WHILE v_CountLog < v_separatorCount LOOP
 
      IF instr(v_lastChars,v_separator) = 1 THEN
         v_lastChars := substr(v_lastChars,2);
      END IF;
 
      v_CountLog := v_CountLog + 1;
     
      v_lastChars  :=  substr(v_lastChars,instr(v_lastChars,v_separator));
      dbms_output.put_line('v_lastChars      '||v_lastChars);
 
  END LOOP;
 
  IF instr(v_lastChars,v_separator) = 1 THEN
      v_lastChars := substr(v_lastChars,2);
  END IF;
 
  lastChars := v_lastChars;

EXCEPTION
  /*WHEN trim(lastChars) IS NULL THEN
  dbms_output.putline(' is null ')*/
WHEN OTHERS THEN
dbms_output.put_line(' exists errs ');
END Split_Pro;

 

 

 

-- Created on 23-12-2010 by JONSAN_GUO
declare
  -- Local variables here
  i VARCHAR2(100);
 
  CURSOR all_record IS
  SELECT prom
begin
  -- Test statements here
   Split_Pro('D:/work/2010-12-20/IN_RECON/CBI00014136 (20100728)/Common/jar/custom/FDPh4.jar',
                  '/',
                   i );
  dbms_output.put_line(i);
 
  SELECT * FROM local_prom_file
  WHERE
end;