欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle的CLOB大数据字段类型操作方法

程序员文章站 2022-06-25 12:27:54
一、oracle中的varchar2类型 我们在oracle数据库存储的字符数据一般是用varchar2。varchar2既分pl/sql data types中的变量类...

一、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的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

查询结果如下:

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