Oracle的CLOB大数据字段类型操作方法

VARCHAR2既分PL/SQL Data Types中的变量类型，也分Oracle Database中的字段类型，不同场景的最大长度不同。接下来通过本文给大家分享Oracle的CLOB大数据字段类型操作方法，感兴趣的朋友一起看看吧。

一、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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- 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类型：

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

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

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

正确操作

1 2 3 4 5 6 7 8 9 10 11 12

--使用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 2 3 4 5

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。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94

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将待插入字符串写入进去。

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

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方法:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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中定位到对应的字段后，往外读：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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

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

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

总结

以上所述是小编给大家介绍的Oracle的CLOB大数据字段类型操作方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！