• LOB

  • 练习 1:字符大对象
    --------------------------------------------------------------------------
    create table testclob (id number,text_col1 clob);

    declare
    longliteral varchar2(32767) := rpad('1', 32767, '1');
    begin
    execute immediate 'insert into testclob values(1,:1)' using longliteral;
    commit;
    end;
    /

    计算clob列的长度：
    select DBMS_LOB.GetLength(text_col1) col1 from testclob;

    --追加数据到大对象列:
    DECLARE
    varC CLOB;
    vAStr VARCHAR2(32767);
    BEGIN
    -- vAStr:=',this append data';
    vAStr:=rpad(',this append data:', 32767, '2');
    SELECT text_col1
    INTO varC
    FROM testclob
    WHERE id=1
    FOR UPDATE;
    DBMS_LOB.Append(varC,vAStr);
    COMMIT;
    -- DBMS_OUTPUT.put_line('append result: '|| varC);
    END;
    /

    --DBMS_LOB.Erase(LOB数据，指定删除长度, 开始删除位置);
    DECLARE
    varC CLOB; --LOB data
    LN NUMBER(5); --specify length
    strt NUMBER(5); --start location
    BEGIN
    LN:=32767;
    strt:=32768;
    SELECT text_col1
    INTO varC
    FROM testclob
    WHERE id=1
    FOR UPDATE;
    DBMS_LOB.ERASE(varC,LN,strt);
    COMMIT;
    DBMS_OUTPUT.put_line('擦除结果为: '|| strt);
    END;
    /

    alter table testclob modify lob (text_col1) (shrink space); --回收的是扩展之后的extent

    练习 2:二进制大对象
    --------------------------------------------------------------------------
    conn / as sysdba
    CREATE OR REPLACE DIRECTORY IMG AS '/home/oracle';
    GRANT READ ON DIRECTORY IMG TO scott WITH GRANT OPTION;

    conn scott/tiger
    create table testblob (id number(2),pic blob default empty_blob());

    CREATE OR REPLACE PROCEDURE IMG_INSERT (TID VARCHAR2,FILENAME VARCHAR2) AS
    F_LOB BFILE;--文件类型
    B_LOB BLOB;
    BEGIN
    iNSERT INTO TESTBLOB (ID, PIC)
    VALUES (TID,EMPTY_BLOB ()) RETURN PIC INTO B_LOB;
    --插入空的blob
    F_LOB:= BFILENAME ('IMG', FILENAME);
    --获取指定目录下的文件
    DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY);
    --以只读的方式打开文件
    DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,DBMS_LOB.GETLENGTH (F_LOB));
    --传递对象
    DBMS_LOB.FILECLOSE (F_LOB);
    --关闭原始文件
    COMMIT;
    END;
    /

    --在/home/oracle下放一张图片1.jpg

    --将该图片存入表

    EXEC IMG_INSERT('1','1.png');
    EXEC IMG_INSERT('2','1.txt');

    练习 3:外部二进制文件
    ----------------------------------------------------------------------------
    create table testbfile(id int, fname bfile);

    insert into testbfile values (1, bfilename ('IMG', '1.png'));
    commit;