Oracle中CLOB大数据字段类型操作的示例分析
这篇文章主要为大家展示了“Oracle中CLOB大数据字段类型操作的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“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
--CreatetablecreatetableTEMP(nameVARCHAR2(200),ageNUMBER,temp_clobCLOB)tablespaceINSIGHTDATA3_TSpctfree10initrans1maxtrans255storage(initial160Knext1Mminextents1maxextentsunlimited);
（二）、增删改查
先看一下使用普通方式操作CLOB类型：
SELECTt.name,t.temp_clobFROMtempt;--普通查询INSERTINTOtemptVALUES('Grand.Jon',22,'加入博客园的第一天');
查询因为不是varchar2类型，所以普通查询看不到CLOB类型的字段内容，结果如下
而普通插入操作也会因为Oracle的隐式转换，默认把字符串转换成varchar2类型，一旦字符串内容超过varchar2的最大限度就会报会报ora-01704（字符串太长）错误。
正确操作
--使用PL/SQL语法，采取绑定变量的方式解决，而不是直接拼接SQLDECLAREV_LANGCLOB:='待插入的海量字符串';V_UPDATECLOB:='更新的海量字符串';BEGININSERTINTOtemptVALUES('Grand.Jon',22,V_LANG);--增加UPDATEtemptSETt.temp_clob=V_UPDATEWHERErownum=1;--修改SELECTt.NAME,dbms_lob.substr(t.temp_clob)FROMTEMPt;--查询 将CLOB转成字符类型DELETEtemptWHERErownum=1;--按列删除 COMMIT;END;/
对CLOB的操作我们在存储过程中基本上使用 dbms_lob 中 substr , append , write 等方法。
dbms_lob 方法总结
dbms_lob.createtemporary(V_SQL,true);--创建一个临时clob，用来存储拼接的sqldbms_lob.write(v_SQL,'写入信息');--写入操作dbms_lob.append(v_SQL,',');--拼接clobdbms_lob.substr(v_SQL);--截取clob，不传参数就是全部读取dbms_lob.freetemporary(v_SQL);--释放clob
查询结果如下：
四、在存储过程中使用CLOB类型实例
需求：以开发的存储过程为例，需要循环遍历时间范围拼接sql，将时间日期按列反转（pivot），如果时间太长（1年以上）sql语句（varchar2）就会超出范围报错，这时候就需要使用CLOB来存储拼接的sql。
PROCEDUREP_AND_CPT_RATIOOTH_APP_BAK2_N(V_APPIDSINVARCHAR2,V_TYPEINVARCHAR2,V_CHANNELINVARCHAR2,V_TABLEINVARCHAR2,V_STARTINVARCHAR2,V_ENDINVARCHAR2,RESULTOUTmycursor)ISV_SQLCLOB;V_SQLWHEREVARCHAR2(32767)default'';V_SQLWHERE_CHANNELVARCHAR2(32767)default'';V_SQL_DATESCLOB;V_SdateDATE;V_EdateDATE;V_TABLE_DATEVARCHAR2(50);V_TABLE_TYPEVARCHAR2(50);V_START_DATEVARCHAR2(50);V_END_DATEVARCHAR2(50);V_DAYVARCHAR2(50);BEGINselectcolumn_nameintoV_TABLE_DATEfromuser_tab_columnswheretable_name=''||V_TABLE||''andcolumn_id=1;selectcolumn_nameintoV_TABLE_TYPEfromuser_tab_columnswheretable_name=''||V_TABLE||''andcolumn_id=5;dbms_lob.createtemporary(V_SQL,true);--创建一个临时lobdbms_lob.createtemporary(V_SQL_DATES,true);--创建一个临时lobIFV_APPIDSisNOTNULLTHENV_SQLWHERE:='ANDt.appidin('||V_APPIDS||')';ENDIF;IFV_CHANNELISNOTNULLTHENV_SQLWHERE_CHANNEL:='ANDt.channel='''||V_CHANNEL||'''';ENDIF;IFV_TABLE_DATE='MON'THENV_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)LOOPdbms_lob.append(v_SQL_DATES,to_char(v_sdate,'yyyymm'));--把临时字符串付给v_strIFv_sdate!=v_edateTHENdbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_strENDIF;v_sdate:=add_months(v_sdate,1);ENDLOOP;ELSE--周和日类型都是DAYv_sdate:=to_date(V_START,'yyyymmdd');v_edate:=to_date(V_END,'yyyymmdd');V_END_DATE:=V_END;IFSUBSTR(V_TYPE,0,1)='d'THENV_START_DATE:=to_char(v_sdate,'yyyymmdd');WHILE(v_sdate<=v_edate)LOOPdbms_lob.append(v_SQL_DATES,to_char(v_sdate,'yyyymmdd'));--把临时字符串付给v_strIFv_sdate!=v_edateTHENdbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_strENDIF;v_sdate:=v_sdate+1;ENDLOOP;ELSIFSUBSTR(V_TYPE,0,1)='w'THENselectto_char(V_Sdate,'d')INTOV_DAYfromdual;IFV_DAY!=2THENV_Sdate:=V_Sdate-7;ENDIF;V_START_DATE:=to_char(v_sdate,'yyyymmdd');WHILE(v_sdate<=v_edate)LOOPselectto_char(V_Sdate,'d')INTOV_DAYfromdual;IFV_DAY=2THENdbms_lob.append(v_SQL_DATES,to_char(v_sdate,'yyyymmdd'));--把临时字符串付给v_strIFV_Edate-v_sdate>7THENdbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_strENDIF;ENDIF;v_sdate:=v_sdate+1;ENDLOOP;ENDIF;ENDIF;dbms_lob.append(v_sql,'SELECT*FROM(SELECT*FROM'||V_TABLE||'tWHEREt.'||V_TABLE_TYPE||'='''||V_TYPE||'''ANDt.'||V_TABLE_DATE||'>='''||V_START_DATE||'''ANDt.'||V_TABLE_DATE||'<='''||V_END_DATE||''''||V_SQLWHERE||''||V_SQLWHERE_CHANNEL||')t1pivot(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);OPENresultFORv_sql;dbms_lob.freetemporary(v_sql);--释放lobdbms_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将待插入字符串写入进去。
重点：这两步操作要放在同一个事务里面。具体增加的方法如下:
publicbooleansave(Articlearticle){booleanresult=true;Connectionconn=ConnectionUntils.getInstance();Stringsql="insertintotempvalues(?,?,empty_clob())";//锁住该列，防止并发写入时候该字段同时被多次写入造成错误StringsqlClob="selecttemp_clobfromtemptwheret.name=?forupdate";PreparedStatementpst=null;ResultSetrs=null;Writerwriter=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();CLOBclob=null;if(rs.next()){try{clob=(CLOB)rs.getClob(1);writer=clob.getCharacterOutputStream();//拿到clob的字符输入流writer.write(article.getContent());writer.flush();writer.close();}catch(IOExceptione){e.printStackTrace();}}conn.commit();}catch(SQLExceptione){result=false;try{conn.rollback();//当commit或者rollback后会自动释放该列的锁定}catch(SQLExceptione1){e1.printStackTrace();}e.printStackTrace();}finally{conn.setAutoCommit(true);//还原ConnectionUntils.close(rs,pst,conn);}returnresult;}
update操作，update时候主要利用PreparedStatement的setClob方法:
publicbooleanupdate(Stringname,Stringcontent){intresult=0;Connectionconn=ConnectionUntils.getInstance();Stringsql="updatetempsettemp_clob=?wherename=?";PreparedStatementpst=null;try{CLOBclob=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(SQLExceptione){e.printStackTrace();}finally{ConnectionUntils.close(null,pst,conn);}if(result==0)returnfalse;returntrue;}
查询就主要是从结果集ResultSet中定位到对应的字段后，往外读：
publicArticleselect(Stringname){Articlearticle=newArticle();Connectionconn=ConnectionUntils.getInstance();Stringsql="selectname,age,temp_clogfromtempwherename=?";PreparedStatementpst=null;ResultSetrs=null;try{pst=conn.prepareStatement(sql);pst.setInt(1,id);rs=pst.executeQuery();StringBuilderbuilder=newStringBuilder();if(rs.next()){Clobclob=rs.getClob("temp_clog");Readerrd=clob.getCharacterStream();char[]str=newchar[12];while(rd.read(str)!=-1){builder.append(newString(str));}article.setContent(builder.toString());article.setName(rs.getString("name"));article.setAge(rs.getInt("age"));}}catch(SQLExceptione){e.printStackTrace();}catch(IOExceptione){e.printStackTrace();}finally{ConnectionUntils.close(rs,pst,conn);}returnarticle;}
（二）、Hibernate、Mybatis框架操作
这个因为框架都封装集成好了，所以我们使用的时候直接配置变量的类型为CLOB就可以。
譬如：Mybatis
<resultcolumn="temp_clob"property="content"jdbcType="Clob"typeHandler="org.apache.ibatis.type.ClobTypeHandler"/>
貌似Hibernate5内部做了对应的处理，可以直接当string类型一样处理即可。
以上是“Oracle中CLOB大数据字段类型操作的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
