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
-- 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类型一样处理即可。
总结
以上所述是小编给大家介绍的oracle的clob大数据字段类型操作方法,希望对大家有所帮助