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:8The first occurrence starts at position:24The substring extracted is: Oracle 9i