SUBSTR

获得指定LOB的部分数据

声明

DBMS_LOB.SUBSTR (
   lob_loc     IN    BLOB,
   amount      IN    INTEGER := 8000,
   offset      IN    BIGINT := 1)
RETURN VARBINARY(8000);

DBMS_LOB.SUBSTR (
   lob_loc     IN    CLOB   ,
   amount      IN    INTEGER := 8000,
   offset      IN    BIGINT := 1)
RETURN VARCHAR2(8000) ;

DBMS_LOB.SUBSTR (
   file_loc     IN    BFILE,
   amount      IN    INTEGER := 8000,
   offset      IN    BIGINT := 1)
RETURN VARBINARY(8000);

结构

参数	说明
lob_loc/file_loc	lob_loc/file_loc定位器
amount	字符个数
offset	偏移量

示例

    
    
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_substr() as

    varC clob;
    vRStr varchar2(10);
    ln number(4);
    Strt number(4);
Begin
    select resume into varC from tlob where no = 1;
    ln := 4;
    Strt := 1;
    vRStr := DBMS_LOB.SubStr(varC, ln, Strt);
    DBMS_output.put_line('结果为: '||vRStr);
End;

/
exec test_substr();

/