Oracle中instr和substr存储过程详解

dbms_lob.instr(

lob_loc in blob,

pattern in raw,

offset in integer := 1;

nth in integer := 1)

return integer ;

  dbms_lob.instr(

lob_loc in clob character set any_cs,

pattern in varchar2 character set lob_loc%charset,

offset in integer :=1,

nth in integer := 1)

return integer ;

dbms_lob.substr(

  lob_loc in blob,

  amount in integer := 32767,

  offset in integer := 1)

return raw;

  dbms_lob.substr(

  lob_loc in clob character set any_cs,

  amount in integer := 32767,

  offset in integer := 1)

return varchar2 character set lob_loc%charset;

declare

  source_lob clob;

  pattern varchar2(6) := 'Oracle' ;

  start_location integer := 1;

  nth_occurrence integer := 1;

  position integer ;

  buffer varchar2(100);

begin

  select clob_locator into source_lob from mylobs where lob_index = 4;

  position := dbms_lob.instr(source_lob, pattern, start_location, nth_occurrence);

  dbms_output.put_line( 'The first occurrence starts at position:' || position);

  nth_occurrence := 2;

  select clob_locator into source_lob from mylobs where lob_index = 4;

  position := dbms_lob.instr(source_lob, pattern, start_location, nth_occurrence);

  dbms_output.put_line( 'The first occurrence starts at position:' || position);

  select clob_locator into source_lob from mylobs where lob_index = 5;

  buffer := dbms_lob.substr(source_lob, 9, start_location);

  dbms_output.put_line( 'The substring extracted is: ' || buffer);

end ;

/

The first occurrence starts at position:8

The first occurrence starts at position:24

The substring extracted is : Oracle 9i