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

最新推荐文章于 2024-08-17 21:05:27 发布
最新推荐文章于 2024-08-17 21:05:27 发布
阅读量1.5w 收藏 20
点赞数 4

VARCHAR2既分PL/SQL Data Types中的变量类型，也分Oracle Database中的字段类型，不同场景的最大长度不同。接下来通过本文给大家分享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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 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类型：

?
1
SELECT t. name , t.temp_clob FROM temp t; --普通查询 INSERT INTO temp t VALUES ('Grand.Jon', 22, '加入博客园的第一天');

　　查询因为不是varchar2类型，所以普通查询看不到CLOB类型的字段内容，结果如下

而普通插入操作也会因为Oracle的隐式转换，默认把字符串转换成varchar2类型，一旦字符串内容超过varchar2的最大限度就会报会报ora-01704（字符串太长）错误。

正确操作

?
1
2
3
4
5
6
7
8
9
10
11
12
--使用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 方法总结

?
1
2
3
4
5
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。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
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将待插入字符串写入进去。

　　　　　　重点：这两步操作要放在同一个事务里面。具体增加的方法如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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方法:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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中定位到对应的字段后，往外读：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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

?
1
<result column = "temp_clob" property= "content" jdbcType= "Clob" typeHandler= "org.apache.ibatis.type.ClobTypeHandler" />

　　貌似Hibernate5内部做了对应的处理，可以直接当string类型一样处理即可。

总结

以上所述是小编给大家介绍的Oracle的CLOB大数据字段类型操作方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！

风儿吹花儿美
关注
oracle插入CLOB类型超过4000个字符解决方案
刘浩宇的博客
04-07 2047
利用Oracle函数进行更新 content字段。在此我分享一下对于这个问题解决方案。
Oracle的CLOB大数据字段类型
08-18 2888
一、Oracle中的varchar2类型 我们在Oracle数据库存储的字符数据一般是用VARCHAR2。VARCHAR2既分PL/SQL Data Types中的变量类型，也分Oracle Database中的字段类型，不同场景的最大长度不同。 在Oracle Database中，VARCHAR2 字段类型，最大值为4000；PL/SQL中 VARCHAR2 变量类...
Oracle 数据库新手指南(二) 数据类型
最新发布
a494312616的博客
08-17 839
在中，我们探讨了 Oracle 数据库的基础知识，包括 Oracle 数据库的概述、与 MySQL 的主要区别，以及为什么选择使用 Oracle。现在，让我们更深入地了解 Oracle 数据库中的数据类型。数据类型是构建数据库表和存储数据的基础。正确选择数据类型对于保证数据的准确性、提高查询效率以及优化存储空间至关重要。在本文中，我们将介绍 Oracle 数据库中常用的几种数据类型，并探讨它们的特点和适用场景。
ORACLE 操作 CLOB 字段
lijunlinlijunlin的专栏
11-11 1571
方法一： 在Oracle中，LOB（Large Object，大型对象）类型的字段现在用得越来越多了。因为这种类型的字段，容量大（最多能容纳4GB的数据），且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域（如图象、档案等）。而LONG、LONG RAW等类型的字段，虽然存储容量也不小（可达2GB），但由于一个表中只能有一个这样类型的字段的限制，现在已很少使用了。
oracle将其他字段类型改为clob类型方法
weixin_45418325的博客
05-16 3797
oracle将其他字段类型改为clob类型方法
oracle数据库字段改为clob,Oracle 数据库表 varchar2 类型改成 CLOB
weixin_42149145的博客
04-11 4621
在之前的表设计中，是使用的 varchar2，由于字段内容太长了，导致生产报错了，varchar2 仅支持 4000 ，我们需要改成更大的 CLOB注意事项1. 在Oracle JDBC中采用流机制对 BLOB/CLOB 进行读写操作，所以要注意不能在批处理中读写 BLOB/CLOB字段，否则将出现Stream type cannot be used in batching 异常。2. Oracl...
详解Oracle修改字段类型方法总结
12-16
此外，对于大数据类型的字段，如`CLOB`，处理起来更为复杂，涉及到大对象的读取、写入和更新，通常需要使用专门的方法或函数进行操作。例如，处理`CLOB`字段的查询性能问题、PHP处理`CLOB`的实例、插入和更新`CLOB`...
用OO4O操作Oracle数据库的大数据字段.pdf
10-10
【Oracle 数据库与大数据字段操作】Oracle 数据库是关系型数据库的一种，被广泛应用于存储和管理各种类型的数据，包括图像、音频、视频等大数据量的信息。这些数据通常以大数据字段的形式存储，如 BFile、BLOB...
oracle clob介绍
Shliesce的专栏
11-07 4109
参考了DAVID的文档：http://blog.csdn.net/tianlesoftware/article/details/6905406 varchar2类型的对象最大只能存放4000字节的内容，要想存放更多的字符，可以选择用lob字段。lob分clob、blob。 clob是存储字符的，这种类型用于存储大量的文本信息。 blob这种类型用于存储二进制信息，如字处理文档，图像。它不
Oracle数据库中的CLOB字段类型，映射到Map＜String，Object＞中异常问题解决
Mr_Dracy的博客
08-11 647
Oracle数据库中的CLOB字段类型，映射到Map中异常问题解决
oracle数据库中如何处理clob字段方法介绍
12-16
在知识库的建立的时候，用普通VARCHAR2存放文章是显然不够的，只有区区4000的字节，放不了多少字， 而CLOB数据类型，则能最多存放8G的数据。但是这个字段处理起来有比较多的特殊性，记录一下。 插入： 直接写在SQL里面是不行的，一来SQL脚本有字符数限制，而来文章内容包含许多特殊字符，如换行，引号， 之类的东西，很麻烦。网上流行通用做法是先插入一个空CLOB字段，用empty_clob()方法来创建空字段，如： 代码如下: INSERT INTO T_TOPIC(TOPIC_ID,TOPIC_CONTENT) VALUES(‘0000001′,empty_clob()); 然后再用
java 插入oracle中clob字段
xinjirufen1的专栏
10-16 885
import java.io.BufferedReader; import java.io.File; import java.io.FileReader; import java.io.IOException; import java.io.Reader; import java.io.StringReader; import java.sql.Connection; import
Oracle 如何处理CLOB字段
weixin_33862993的博客
04-21 2522
1. 必须保证在一个事务内处理：JDBC connection autocommit conn.setAutoCommit(false); 2. 插入记录用empty_clob()函数带入空CLOB字段：Insert into table with CLOB column as empty_clob(); 3. 用s...
oracle中clob字段的使用
09-18 2465
oracle中定义了一个字段是clob的，由于用的是ssh的框架，结果在面向对象存取的时候出现clob类型字段和String类型字段的转换问题。开始查阅了clob字段和String字段的相互转换的方法进行处理结果在运行的时候出现can not reset reader的问题，查了百度，但还是没解决，也许是运行环境不同。后来就使用了另外一种方法： 一：在hibernate.cfg.xml文件中加一
Oracle数据库中，BLOB、CLOB 和 NCLOB的区别和使用场景及注意事项
kiingking的博客
07-03 1449
BLOB主要用于存储二进制数据。CLOB用于存储单字节字符集的文本数据。NCLOB用于存储需要多字节字符集编码的文本数据，如Unicode。
oracle数据库clob字段处理
bdksf42444的博客
08-20 704
1、指定待插入字符串类型为clob，可以使用过程或存储过程例子：DECLAREREALLYBIGTEXTSTRING CLOB := '待插入的海量字符串';BEGIN INSERT INTO test_table VALUES('test', REALLYBIGTEXTSTRING, '0'); commit;end ;2、blob字段数据转换为clob类型CRE...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值