共计 7657 个字符，预计需要花费 20 分钟才能阅读完成。
这篇文章将为大家详细讲解有关 Oracle 中如何使用 CLOB 大数据字段类型，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
一、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 /关于 Oracle 中如何使用 CLOB 大数据字段类型就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
