INSTR

查看数据是否存在于指定LOB中

声明

DBMS_LOB.INSTR (
   lob_loc    IN   BLOB,
   pattern    IN   VARBINARY(8000),
   offset     IN   BIGINT := 1,
   nth        IN   BIGINT := 1)
RETURN BIGINT;

DBMS_LOB.INSTR (
   lob_loc    IN   CLOB      ,
   pattern    IN   VARCHAR2  ,
   offset     IN   BIGINT := 1,
   nth        IN   BIGINT := 1)
RETURN BIGINT;


DBMS_LOB.INSTR (
   file_loc   IN   BFILE,
   pattern    IN   VARBINARY(8000),
   offset     IN   BIGINT := 1,
   nth        IN   BIGINT := 1)
RETURN BIGINT;

结构

参数	说明
lob_loc/file_loc	lob_loc/file_loc定位器
pattern	测试模式
offset	偏移量
nth	出现数字，从1开始

返回值0表示没有找到

示例

    
    
drop table tLob cascade;
    
Create Table tLob (
     no Number(4),
     name VarChar2(10),
     resume CLob,
     photo BLob,
     record BFile
     );
	
insert into tlob values(1,'Nick','CLOB对象列',empty_blob(),bfilename('MYDIR','1.JPG'));
   
insert into tlob values(2,'Jack','CLOB对象列',empty_blob(),bfilename('MYDIR','2.JPG'));
   
insert into tlob values(3,'Mary','对象列CLOB',empty_blob(),bfilename('MYDIR','3.JPG'));
   
create or replace procedure test_instr() as  
Declare 
    varC clob;
    vSubStr varchar2(1000);
    vRStr varchar2(1000);
    ln number(4);
Begin
    select resume into varC from tlob where no = 1;
    vSubStr := '大对象';
    ln := DBMS_LOB.InStr(varC,vSubStr);
    DBMS_output.put_line('位置为: '||ln);
End;

/
exec test_instr();
 
/