Oracle中instr和substr存储过程详解_oracle_

 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