Oracle LOB类型

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、Oracle中的varchar2类型
1.我们在Oracle数据库存储的字符数据一般是用VARCHAR2。VARCHAR2既分PL/SQL Data Types中的变量类型，也分Oracle Database中的字段类型，不同场景的最大长度不同。
2.在Oracle Database中，VARCHAR2 字段类型，最大值为4000；PL/SQL中 VARCHAR2 变量类型，最大字节长度为32767。
3.当VARCHAR2容纳不下我们需要存储的信息时，就出来的Oracle的大数据类型LOB(Large Object，大型对象)。
二、Oarcle中的LOB类型
1.在Oracle中，LOB（Large Object，大型对象）类型的字段现在用得越来越多了。因为这种类型的字段，容量大（最多能容纳4GB的数据），且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域（如图象、档案等）。
2.LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象（Binary Large Object），适用于存贮非文本的字节流数据（如程序、图象、影音等）。
3.而CLOB，即字符型大型对象（Character Large Object），则与字符集相关，适于存贮文本型的数据（如历史档案、大部头著作等）。
三、DB中使用CLOB类型字段
（一）创建表（使用sql或者直接在PL/SQL客户端创建），字段类型CLOB

-- Create table

create table TEMP

(

name VARCHAR2(200),

age NUMBER,

temp_clob CLOB

)

tablespace INSIGHTDATA3_TS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 160K

next 1M

minextents 1

maxextents unlimited

);

（二）增删改查
先看一下使用普通方式操作CLOB类型：

SELECT t.name, t.temp_clob FROM temp t; --普通查询 INSERT INTO temp t VALUES ('Grand.Jon', 22, '加入博客园的第一天');

查询因为不是varchar2类型，所以普通查询看不到CLOB类型的字段内容，结果如下:

而普通插入操作也会因为Oracle的隐式转换，默认把字符串转换成varchar2类型，一旦字符串内容超过varchar2的最大限度就会报会报ora-01704（字符串太长）错误。
正确操作

--使用PL/SQL语法，采取绑定变量的方式解决，而不是直接拼接SQL

DECLARE

V_LANG CLOB := '待插入的海量字符串';

V_UPDATE CLOB := '更新的海量字符串';

BEGIN

INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG); --增加

UPDATE temp t SET t.temp_clob = V_UPDATE WHERE rownum = 1; --修改

SELECT t.NAME, dbms_lob.substr(t.temp_clob) FROM TEMP t; --查询　　将CLOB转成字符类型

DELETE temp t WHERE rownum = 1; --按列删除　

　

COMMIT;

END;

/

查询结果如下：

对CLOB的操作我们在存储过程中基本上使用 DBMS_LOB包里面的方法。
dbms_lob 方法总结

dbms_lob.open() 打开LOB

dbms_lob.close() 关闭LOB

dbms_lob.append() 将源LOB中的内容追加到目标LOB

dbms_lob.copy() 从源LOB中的一部分或者全部数据复制到目标LOB

dbms_lob.erase() 删除LOB中全部或部分内容

dbms_lob.trim() 将LOB值减少到指定的长度

dbms_lob.read() 从LOB中读取数据

dbms_lob.write() 写入数据

dbms_lob.compare() 比较两个同种数据类型的LOB的部分或全部值是否相同

dbms_lob.getlength() 获取LOB的长度

dbms_lob.fileopen() 打开文件

dbms_lob.fileclose() 关闭文件

dbms_lob.fileexits() 检查文件的存在性

dbms_lob.createtemporary(V_SQL,true) 创建一个临时clob，用来存储拼接的sql

dbms_lob.write(v_SQL,'写入信息') 写入操作

dbms_lob.append(v_SQL,',') 拼接clob

dbms_lob.substr(v_SQL) 截取clob，不传参数就是全部读取

dbms_lob.freetemporary(v_SQL) 释放clob

四、在存储过程中使用CLOB类型实例
需求：以开发的存储过程为例，需要循环遍历时间范围拼接sql，将时间日期按列反转（pivot），如果时间太长（1年以上）sql语句（varchar2）就会超出范围报错，这时候就需要使用CLOB来存储拼接的sql。

PROCEDURE P_AND_CPT_RATIOOTH_APP_BAK2_N(

    V_APPIDS IN VARCHAR2,

    V_TYPE IN VARCHAR2,

    V_CHANNEL IN VARCHAR2,

    V_TABLE IN VARCHAR2,

    V_START IN VARCHAR2,

    V_END IN VARCHAR2,

    RESULT OUT mycursor

) IS

    V_SQL CLOB;

    V_SQLWHERE VARCHAR2(32767) default '';

    V_SQLWHERE_CHANNEL VARCHAR2(32767) default '';

    V_SQL_DATES CLOB;

    V_Sdate DATE;

    V_Edate DATE;

    V_TABLE_DATE VARCHAR2(50);

    V_TABLE_TYPE VARCHAR2(50);

    V_START_DATE VARCHAR2(50);

    V_END_DATE VARCHAR2(50);

    V_DAY VARCHAR2(50);

BEGIN

    select column_name into V_TABLE_DATE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=1;

    select column_name into V_TABLE_TYPE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=5;

    dbms_lob.createtemporary(V_SQL,true);--创建一个临时lob

    dbms_lob.createtemporary(V_SQL_DATES,true);--创建一个临时lob

    IF V_APPIDS is NOT NULL THEN

        V_SQLWHERE := 'AND t.appid in ('||V_APPIDS||')';

    END IF;

    IF V_CHANNEL IS NOT NULL THEN

        V_SQLWHERE_CHANNEL := 'AND t.channel = '''||V_CHANNEL||'''';

    END IF;

    IF V_TABLE_DATE = 'MON' THEN

        V_START_DATE := SUBSTR(V_START,0,6);

        V_END_DATE := SUBSTR(V_END,0,6);

        v_sdate := to_date(V_START_DATE, 'yyyymm');

        v_edate := to_date(V_END_DATE, 'yyyymm');

        WHILE (v_sdate <= v_edate) LOOP

            dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymm'));--把临时字符串付给v_str

            IF v_sdate != v_edate THEN

                dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str

            END IF;

            v_sdate := add_months(v_sdate,1);

       END LOOP;

    ELSE --周和日 类型 都是 DAY

        v_sdate := to_date(V_START, 'yyyymmdd');

        v_edate := to_date(V_END, 'yyyymmdd');

        V_END_DATE := V_END;

        IF SUBSTR(V_TYPE,0,1)='d' THEN

            V_START_DATE := to_char(v_sdate, 'yyyymmdd');

            WHILE (v_sdate <= v_edate) LOOP

                dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str

                IF v_sdate != v_edate THEN

                    dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str

                END IF;

                v_sdate := v_sdate+1;

            END LOOP;
        ELSIF SUBSTR(V_TYPE,0,1)='w' THEN

            select to_char(V_Sdate,'d') INTO V_DAY from dual;

            IF V_DAY!=2 THEN

                V_Sdate:=V_Sdate-7;

            END IF;

            V_START_DATE := to_char(v_sdate, 'yyyymmdd');
            WHILE (v_sdate <= v_edate) LOOP

                select to_char(V_Sdate,'d') INTO V_DAY from dual;

                IF V_DAY=2 THEN

                    dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str

                    IF V_Edate-v_sdate >7 THEN

                        dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str

                    END IF;

                END IF;

                v_sdate := v_sdate+1;

            END LOOP;

        END IF;


    END IF;

    dbms_lob.append(v_sql,'SELECT * FROM( SELECT *

    FROM '||V_TABLE||' t

    WHERE

    t.'||V_TABLE_TYPE||' = '''||V_TYPE||'''

    AND t.'||V_TABLE_DATE||' >= '''||V_START_DATE||'''

    AND t.'||V_TABLE_DATE||' <= '''||V_END_DATE||'''

    '||V_SQLWHERE||'

    '||V_SQLWHERE_CHANNEL||' ) t1

    pivot(sum(MARKETSHARE)

    for '||V_TABLE_DATE||' in(');

    dbms_lob.append(v_sql,v_SQL_DATES);

    dbms_lob.append(v_sql,'))');

    dbms_output.put_line(v_sql);

    OPEN result FOR v_sql; 

    dbms_lob.freetemporary(v_sql);--释放lob

    dbms_lob.freetemporary(v_SQL_DATES);--释放lob

    --dbms_output.put_line(V_SQLDATE);

    -- dbms_output.put_line(v_SQL_DATES);

    --记录操作日志及错误日志

END;