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

(0)

相关推荐

  • oracle数据库中如何处理clob字段方法介绍

    在知识库的建立的时候,用普通VARCHAR2存放文章是显然不够的,只有区区4000的字节,放不了多少字, 而CLOB数据类型,则能最多存放8G的数据.但是这个字段处理起来有比较多的特殊性,记录一下. 插入: 直接写在SQL里面是不行的,一来SQL脚本有字符数限制,而来文章内容包含许多特殊字符,如换行,引号, 之类的东西,很麻烦.网上流行通用做法是先插入一个空CLOB字段,用empty_clob()方法来创建空字段,如: 复制代码 代码如下: INSERT INTO T_TOPIC(TOPIC_I

  • PHP处理Oracle的CLOB实例

    本文实例简述了PHP处理Oracle的CLOB的方法.分享给大家供大家参考.具体方法如下: 1. 写入数据   在使用PDO的预处理方法时,如果使用bindParam()等而不指定字段的数据类型或使用execute(),PDO都会默认为string类型,并且限定一个默认长度 所以在存clob类型字段时必须使用bindParam()或bindValue()等,并指定字符串长度,例如: 复制代码 代码如下: $pdo -> bindParam(':clobData', $clobData, PDO:

  • 向Oracle数据库的CLOB属性插入数据报字符串过长错误

    今天在项目中向数据库的CLOB属性插入一段篇文章(1000~2000)字就会报一个字符串过长的错误. 网上说用流来处理,没有这么做.这像是一个Bug,只要把插入的数据,默认扩充到2000以上就ok了. 下面是这段代码: if((temp.length()>=1000)&&(temp.length()<=2000)){ temp=StringUtils.rightPad(temp, 2008); } 使用StringUtils的rightPad方法使没超过2000的部分,在右边自

  • 关于oracle中clob字段查询慢的问题及解决方法

    最近在用oracle的过程中用到了对blob字段模糊查询的问题,对oracle来说,我并不是高手,找了很多的资料终于能够查出来了. blob字段直接用 select * from table_name where column like '%%'查找的时候是不能实现的 ,主要是字段类型不符,就想到了 字段转换成varchar2类型,然后再进行查询 select * from table_name where utl_raw.cast_to_varchar2(column) like '%orac

  • 解析jdbc处理oracle的clob字段的详解

    import java.io.BufferedOutputStream;import java.io.ByteArrayInputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.io.Reader;import java.sql.Clob;import java.sql.Connection;import java.sql.ResultSet;

  • 详解Oracle修改字段类型方法总结

    最近学习了Oracle修改字段类型方法,留做记录. 有一个表名为tb,字段段名为name,数据类型nchar(20). 1.假设字段数据为空,则不管改为什么字段类型,可以直接执行: alter table tb modify (name nvarchar2(20)); 2.假设字段有数据,则改为nvarchar2(20)可以直接执行: alter table tb modify (name nvarchar2(20)); 3.假设字段有数据,则改为varchar2(40)执行时会弹出:"ORA-

  • Oracle 插入超4000字节的CLOB字段的处理方法

    在通过拼组sql语句来实现数据插入的应用中,我们很有可能会遇到需要插入大型数据的情况,例如,在oracle中需要插入字节数超过4000的字段内容时,我们如果通过简单的拼组sql语句来实现插入,显然就会出现问题,而在sql server中却没有这个限制,个人尝试了26w个字符的sql语句在sql server2005中执行,依旧可以插入数据,但是在oracle中插入超过4000个字符的内容则会报异常. 下面就此问题的解决办法,做一下小结: 我们可以通过创建单独的OracleCommand来进行指定

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

    一.Oracle中的varchar2类型 我们在Oracle数据库存储的字符数据一般是用VARCHAR2.VARCHAR2既分PL/SQL Data Types中的变量类型,也分Oracle Database中的字段类型,不同场景的最大长度不同. 在Oracle Database中,VARCHAR2 字段类型,最大值为4000:PL/SQL中 VARCHAR2 变量类型,最大字节长度为32767. 当 VARCHAR2 容纳不下我们需要存储的信息时,就出来的Oracle的大数据类型LOB( La

  • 如何将Oracle的一个大数据表快速迁移到 Sqlserver2008数据库(图文教程)

    oracle 服务器  版本  11.2.0.1.0 Sqlserver2008  R2 前提条件是 SQLSERVER服务器上安装了Oracle客户端并且进行了配置 不会配置的请参照 这个链接 1  登录MSSM 工具 2 选中其中一个数据库  右键⇒任务⇒导入数据 3   打开窗口 Sqlserver导入和导出向导   点击下一步 4 进入选择数据源画面 a: 数据源 选择  Microsoft OLE DB Provider for Oracle  然后 点击 右侧的 属性 按钮 5  数

  • 详解MySQL中数据类型和字段类型

    目录 1. mysql的数据类型 (1)数值型 (2)字符(串)型 (3)日期和时间型 (4)null值 2. mysql的列(字段)类型 2.1数值列类型 2.2字符串列类型 2.3日期时间列类型 1. mysql的数据类型 在mysql中有如下几种数据类型: (1)数值型 数值是诸如32 或153.4 这样的值.mysql 支持科学表示法,科学表示法由整数或浮点数后跟“e”或“e”.一个符号(“+”或“-”)和一个整数指数来表示.1.24e+12 和23.47e-1 都是合法的科学表示法表示

  • Oracle在表中有数据的情况下修改字段类型或长度的解决方法

    Oracle 数据库如何在表中有数据的情况下,修改表字段的类型或者增加表字段的长度解决方法 我们偶尔需要在已有表,并且有数据的情况下,修改其某个字段的类型或改变他的长度,但是因为表中有数据,所以不可以直接修改,需要换个思路. -- Create table create table TABLE1 ( col1 number(9), col2 char(20) ); -- 尝试修改 -- 修改成功,因为表中无数据 ALTER TABLE TABLE1 MODIFY COL1 NUMBER(10);

  • Oracle和MySQL的数据导入为何差别这么大

    经常会有一些朋友咨询我一些数据库的问题,我注意到一个很有意思的现象,凡是数据导入的问题,基本上都是Oracle类的,MySQL类的问题脑子里想了下竟然一次都没有. 我禁不住开始思考这个未曾注意的问题: 为什么Oracle导入数据会碰到很多的问题? 我们来梳理一下这个问题,分别从导出导入的方式来聊聊. 首先Oracle导出的文件格式就没打算让你拿来即用,导出文件叫做dump,换句话说可以理解这是一个二进制文件.当然实际上这个文件还是有很多的方式去抓取一些关键的信息,比如dump头部的信息可以通过s

  • .NET多种数据库大数据批量插入、更新(支持SqlServer、MySql、PgSql和Oracle)

    功能介绍 (需要版本5.0.45) 大数据操作ORM性能瓶颈在实体转换上面,并且不能使用常规的Sql去实现 当列越多转换越慢,SqlSugar将转换性能做到极致,并且采用数据库最佳API 操作数据库达到极限性能 功能用法 BulkCopy性能远强于现有市场的 ORM框架,比 EFCore Efcore.Bulkextension快30% BulkUpdate吊打现有所有框架是EFCoreEfcore.Bulkextension 2-3倍之快 //插入 100万 10秒不到 db.Fastest<

  • .NET多种数据库大数据批量插入、更新(支持SqlServer、MySql、PgSql和Oracle)

    目录 功能介绍(需要版本5.0.45) 功能用法 数据库支持 性能测试 使用疑问 老版本 源码下载: 功能介绍 (需要版本5.0.45) 大数据操作ORM性能瓶颈在实体转换上面,并且不能使用常规的Sql去实现 当列越多转换越慢,SqlSugar将转换性能做到极致,并且采用数据库最佳API 操作数据库达到极限性能 功能用法 BulkCopy性能远强于现有市场的 ORM框架,比 EFCore Efcore.Bulkextension快30% BulkUpdate吊打现有所有框架是EFCoreEfco

随机推荐