Oracle的CLOB大数据字段类型

共计 7315 个字符，预计需要花费 19 分钟才能阅读完成。

我们在 Oracle 数据库存储的字符数据一般是用 VARCHAR2。VARCHAR2 既分 PL/SQL Data Types 中的变量类型，也分 Oracle Database 中的字段类型，不同场景的最大长度不同。
在 Oracle Database 中，VARCHAR2 字段类型，最大值为 4000；PL/SQL 中 VARCHAR2 变量类型，最大字节长度为 32767。
当 VARCHAR2 容纳不下我们需要存储的信息时，就出来的 Oracle 的大数据类型 LOB(Large Object，大型对象)。

在 Oracle 中，LOB（Large Object，大型对象）类型的字段现在用得越来越多了。因为这种类型的字段，容量大（最多能容纳 4GB 的数据），且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域（如图象、档案等）。
LOB 类型分为 BLOB 和 CLOB 两种：BLOB 即二进制大型对象（Binary Large Object），适用于存贮非文本的字节流数据（如程序、图象、影音等）。
而 CLOB，即字符型大型对象（Character Large Object），则与字符集相关，适于存贮文本型的数据（如历史档案、大部头著作等）。

-- 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 的 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 中 substr , append , write 等方法。

dbms_lob 方法总结

1 dbms_lob.createtemporary(V_SQL,true);     --创建一个临时 clob，用来存储拼接的 sql
2 dbms_lob.write(v_SQL,'写入信息 ');          -- 写入操作
3 dbms_lob.append(v_SQL,',');               --拼接 clob
4 dbms_lob.substr(v_SQL);                   --截取 clob，不传参数就是全部读取
5 dbms_lob.freetemporary(v_SQL);            --释放 clob

查询结果如下：

Oracle 的 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;

增加，一般会插入一个空的 clob 到数据库对应的字段, 然后锁定该列，用 Write 将待插入字符串写入进去。

重点：这两步操作要放在同一个事务里面。具体增加的方法如下:

public boolean save(Article article){boolean result = true;
        Connection conn = ConnectionUntils.getInstance();
        String sql = "insert into temp values(?,?,empty_clob())";
        //锁住该列，防止并发写入时候该字段同时被多次写入造成错误
        String sqlClob = "select temp_clob from temp t where t.name=? for update";
        PreparedStatement pst =null;
        ResultSet rs = null;
        Writer writer = null;
        try {conn.setAutoCommit(false);//设置不自动提交，开启事务
            pst = conn.prepareStatement(sql);
            pst.setString(1,article.getName());
            pst.setString(2,article.getAge());
            pst.executeUpdate();

            pst= conn.prepareStatement(sqlClob);
            pst.setInt(1, article.getId());

            rs = pst.executeQuery();
            CLOB clob = null;
            if(rs.next()){try {clob = (CLOB) rs.getClob(1);
                    writer = clob.getCharacterOutputStream(); //拿到 clob 的字符输入流
                    writer.write(article.getContent());
                    writer.flush();
                    writer.close();} catch (IOException e) {e.printStackTrace();
                }
            }
            conn.commit();} catch (SQLException e) {result = false;
            try {conn.rollback();//当 commit 或者 rollback 后会自动释放该列的锁定
            } catch (SQLException e1) {e1.printStackTrace();
            }
            e.printStackTrace();} finally {conn.setAutoCommit(true);//还原
            ConnectionUntils.close(rs, pst, conn);
        }
        return result;
    }

update 操作，update 时候主要利用 PreparedStatement 的 setClob 方法:

public boolean update(String name,String content){int result = 0;
        Connection conn = ConnectionUntils.getInstance();
        String sql = "update temp set temp_clob=? where name=?";
        PreparedStatement pst =null;
        try {CLOB clob   = oracle.sql.CLOB.createTemporary(conn, false,oracle.sql.CLOB.DURATION_SESSION);
            clob.setString(1L, content);
            pst = conn.prepareStatement(sql);
            pst.setClob(1, clob);
            pst.setString(2,name);
            result = pst.executeUpdate();} catch (SQLException e) {e.printStackTrace();
        }finally{ConnectionUntils.close(null, pst, conn);
        }
        if(result==0)
            return false;
        return true;
    }

查询就主要是从结果集 ResultSet 中定位到对应的字段后，往外读：

public Article select(String name){Article article = new Article();
        Connection conn = ConnectionUntils.getInstance();
        String sql = "select name,age,temp_clog from temp where name = ?";
        PreparedStatement pst =null;
        ResultSet rs = null;
        try {pst = conn.prepareStatement(sql);
            pst.setInt(1,id);
            rs = pst.executeQuery();
            StringBuilder builder = new StringBuilder();
            if(rs.next()){Clob clob = rs.getClob("temp_clog");
                Reader rd = clob.getCharacterStream();
                char [] str = new char[12];
                while(rd.read(str) != -1) {builder.append(new String(str));
                }
                article.setContent(builder.toString());
                article.setName(rs.getString("name"));
                article.setAge(rs.getInt("age"));
            }
        } catch (SQLException e) {e.printStackTrace();
        } catch (IOException e) {e.printStackTrace();
        }finally{ConnectionUntils.close(rs, pst, conn);
        }
        return article;
    }

这个因为框架都封装集成好了，所以我们使用的时候直接配置变量的类型为 CLOB 就可以。

譬如：Mybatis

<result column="temp_clob" property="content" jdbcType="Clob"  typeHandler="org.apache.ibatis.type.ClobTypeHandler"/>

貌似 Hibernate5 内部做了对应的处理，可以直接当 string 类型一样处理即可。

虽然 CLOB 能解决 VARCHAR2 字符大小的限制，但是我们的 DBA 们都不建议用这些来处理，可能效率问题吧，大的文件应该放在服务器上，然后 Database 中存响应地址即可。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-08/146344.htm

Oracle的CLOB大数据字段类型

一、Oracle 中的 varchar2 类型

二、Oarcle 中的 LOB 类型

三、DB 中使用 CLOB 类型字段

（一）、创建表（使用 sql 或者直接在 PL/SQL 客户端创建），字段类型 CLOB

（二）、增删改查

先看一下使用普通方式操作 CLOB 类型：

正确操作

dbms_lob 方法总结

四、在存储过程中使用 CLOB 类型实例

五、使用 Java 开发操作 CLOB 字段

（一）、原生 JDBC 处理 CLOB 类型

（二）、Hibernate、Mybatis 框架操作

六、总结

选择PHP与Python，可以考虑这三个问题

Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

介绍ansible的Ad-hoc与commands模块

Linux安装使用pidstat命令以对进程数据进行监控

linux下使用tree命令以树形结构显示文件目录结构

快速判定运行的 Linux 是什么版本？

Vmware三大网络之Host-Only

Ubuntu之jdk安装

linux下如何查看服务器wwn号

给你的网站加一道安全锁，免费获取ssl证书