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大数据字段类型操作方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

# oracle  # clob  # 大数据字段类型  # .NET多种数据库大数据批量插入、更新(支持SqlServer、MySql、PgSql和Oracle  # Python操作MySQL MongoDB Oracle三大数据库深入对比  # 浅谈Mysql、SqlServer、Oracle三大数据库的区别  # 如何将Oracle的一个大数据表快速迁移到 Sqlserver2008数据库(图文教程)  # oracle大数据删除插入方式  # 会报  # 创建一个  # 适用于  # 太长  # 小编  # 过程中  # 都是  # 就会  # 好了  # 放在  # 也会  # 多个  # 在此  # 两种  # 遍历  # 是从  # 给大家  # 可以直接  # 为例  # 报错 

相关文章： 成都品牌网站制作公司,成都营业执照年报网上怎么办理？  建站上市公司网站建设方案与SEO优化服务定制指南  如何快速搭建个人网站并优化SEO？  代购小票制作网站有哪些,购物小票的简要说明？  ,柠檬视频怎样兑换vip？  如何通过VPS建站无需域名直接访问？  建站之星后台管理如何实现高效配置？  c++23 std::expected怎么用 c++优雅处理函数错误返回【详解】  详解一款开源免费的.NET文档操作组件DocX（.NET组件介绍之一）  Dapper的Execute方法的返回值是什么意思 Dapper Execute返回值详解  音响网站制作视频教程,隆霸音响官方网站？  如何通过多用户协作模板快速搭建高效企业网站？  建站主机默认首页配置指南：核心功能与访问路径优化  如何快速查询网站的真实建站时间？  如何将凡科建站内容保存为本地文件？  哈尔滨网站建设策划,哈尔滨电工证查询网站？  如何解决ASP生成WAP建站中文乱码问题？  江苏网站制作公司有哪些,江苏书法考级官方网站？  如何选择最佳自助建站系统？快速指南解析优劣  mc皮肤壁纸制作器,苹果平板怎么设置自己想要的壁纸我的世界？  ,如何利用word制作宣传手册？  在线制作视频网站免费,都有哪些好的动漫网站？  建站org新手必看：2024最新搭建流程与模板选择技巧  ppt在线制作免费网站推荐,有什么下载免费的ppt模板网站？  制作电商网页,电商供应链怎么做？  完全自定义免费建站平台：主题模板在线生成一站式服务  如何在云服务器上快速搭建个人网站？  如何在服务器上配置二级域名建站？  建站主机与虚拟主机有何区别？如何选择最优方案？  利用JavaScript实现拖拽改变元素大小  网站制作与设计教程,如何制作一个企业网站，建设网站的基本步骤有哪些？  官网建站费用明细查询_企业建站套餐价格及收费标准指南  如何快速生成橙子建站落地页链接？  图片制作网站免费软件,有没有免费的网站或软件可以将图片批量转为A4大小的pdf？  北京专业网站制作设计师招聘,北京白云观官方网站？  深圳网站制作公司好吗,在深圳找工作哪个网站最好啊？  相册网站制作软件,图片上的网址怎么复制？  如何在腾讯云服务器上快速搭建个人网站？  定制建站价位费用解析与套餐推荐全攻略  建站OpenVZ教程与优化策略：配置指南与性能提升  建站之星如何配置系统实现高效建站？  如何在Windows环境下新建FTP站点并设置权限？  建站之星安装后如何自定义网站颜色与字体？  建站之星免费模板：自助建站系统与智能响应式一键生成  建站之星导航菜单设置与功能模块配置全攻略  如何生成腾讯云建站专用兑换码？  建站之星安装需要哪些步骤及注意事项？  大连网站设计制作招聘信息,大连投诉网站有哪些？  道歉网站制作流程,世纪佳缘致歉小吴事件，相亲网站身份信息伪造该如何稽查？  c# 在高并发场景下，委托和接口调用的性能对比