一、Oracle中的varchar2类型

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

二、Oarcle中的LOB类型

在Oracle中，LOB(Large Object，大型对象)类型的字段现在用得越来越多了。因为这种类型的字段，容量大(最多能容纳4GB的数据)，且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域(如图象、档案等)。 LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象(Binary Large Object)，适用于存贮非文本的字节流数据(如程序、图象、影音等)。 而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 中 substr , append , write 等方法。

dbms_lob 方法总结

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;

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

(一)、原生JDBC处理CLOB类型

增加，一般会插入一个空的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;    }

(二)、Hibernate、Mybatis框架操作

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

譬如：Mybatis

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

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

六、总结

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