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大数据字段类型操作方法，希望对大家有所帮助