一、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大数据字段类型操作方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！
查看更多关于Oracle的CLOB大数据字段类型操作方法的详细内容...