【技术总结】关于Oracle大字段的问题及总结

gxzsometime

于 2024-05-11 16:28:36 发布

阅读量824

点赞数 26

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/gxzsometime/article/details/138714518

版权

在日常工作处理中，遇到过一些关于Oracle大字段的问题，每次都是面向百度编程，导致印象不太深刻，特此记录并总结，加深对Oracle大字段的理解。

1.什么是Oracle大字段？

百度的解释： BLOB和CLOB都是大字段类型，BLOB是按二进制来存储的，而CLOB是可以直接存储文字的。其实两个是可以互换的的，或者可以直接用LOB字段代替这两个。
但是为了更好的管理ORACLE数据库，通常像图片、文件、音乐等信息就用BLOB字段来存储，先将文件转为二进制再存储进去。而像文章或者是较长的文字，就用CLOB存储，这样对以后的查询更新存储等操作都提供很大的方便。
BLOB全称为二进制大型对象（Binary Large Object)。它用于存储数据库中的大型二进制对象。可存储的最大大小为4G字节
CLOB全称为字符大型对象（Character Large Object)。它与LONG数据类型类似，只不过CLOB用于存储数据库中的大型单字节字符数据块，不支持宽度不等的字符集
可存储的最大大小为4G字节，Clob可以存储单字节字符数据，Blob可以存储无结构的二进制数据。

个人理解：日常项目用途主要是存储报文信息、工作流关联业务信息、二进制文件等，文件存储用得比较少，大多是clob字段。

2.工作中遇到的Oracle大字段相关的问题

2.1.工作流bizcase表中有relatedObject字段为blob类型，怎么输出？

由于blob存的可能是对象也可能是文件，所以没办法直接输出，使用dbms_lob.instr函数也输出不了，只能使用java程序输出；
用hibernate的查询方法查出bizcase对象之后，用blob接收，然后用如下方法读出：
InputStream inputStream = blob.getBinaryStream();
ObjectInputStream ois = new ObjectInputStream(inputStream);
HashMap amap = (HashMap) ois.readObject();
System.out.println("amap:"+amap);

2.2.财务公司承兑的票据贴现后不在系统中做票据解付，票据结清了，页面上查不到结清通知报文（043），也没有票据状态通知报文（040），怎么在数据库查询票据关联报文信息？

根据edc_bill.billid=edc_txdetail.billid，edc_txdetail.txdetailid=edc_message.txdetailid查询不到信息。

票据结清通知报文（043）可能会包含多张结清票据，在保存数据时也未与edc_bill表进行关联，所以页面无法查询到报文信息，需要直接查询edc_message表，查询sql：

SELECT * FROM edc_message t WHERE t.msgtype='043'
--AND to_char(t.createtime,'yyyy-MM-dd')='2023-02-28'
AND dbms_lob.instr(t.content ,'190752100008420221020369623960')>0;

2.3.新网银应用中心中有配置项需批量修改，配置信息保存在config_detail表的大字段中，怎么用sql查询信息并进行修改？

查询sql：

select * from config_detail t where t.pid in (select id from config where app_no='GDPS' and module_code='01') and dbms_lob.instr(t.row_value ,'907521000017')>0;

更新sql：

update config_detail t set t.row_value = replace(t.row_value,'907521000017','102521003500')
where t.pid in (select id from config where app_no='GDPS' and module_code='01') and dbms_lob.instr(t.row_value ,'907521000017')>0;

2.4.oracle表字段使用varchar2类型时字段超长无法存储，修改字段类型为clob时数据库报错，是什么原因？

varchar2类型不可以直接改成clob，需要先删除对应字段，再添加类型为clob的同名字段；
原因：Oracle不允许将字段类型修改为：object、REF、nested table、varchar、clob、blob。

2.5.待补充

遇到的Oracle大字段相关的问题不只这些，还需要更细致的收集及整理。

3.Oracle大字段处理函数及用法

从上述问题描述中，用得最多的函数是dbms_lob.instr，还有其它函数也可能会用到，具体可查询

DBMS_LOB - DBMS_LOB 概述 - 《OceanBase v3.1.1 官方教程》 - 书栈网 · BookStack

在下面只列出部分常用函数（子程序）。

3.1.dbms_lob.getlength

GETLENGTH 函数用于获取指定 LOB 的长度。返回的长度以字节或字符为单位。

长度计数中还包括 LOB 中由先前的 WRITE 等操作产生的 0 字节或空格。空的内部 LOB 的长度为 0。

示例：

select dbms_lob.getlength(t.relatedobject) from bizcase t where t.caseid=8967781;

输出：13814

3.2.dbms_lob.read

可以读取大字段中的信息，但是似乎并不能实现查看功能，示例：

DECLARE

var_clob BLOB;
var_buff VARCHAR(2);
var_len NUMBER(4);
var_start NUMBER(4);

BEGIN

--SELECT RESUME INTO var_clob FROM TLOB WHERE NO = 1;
select t.relatedobject INTO var_clob from bizcase t where t.caseid=8967781;
var_len := 100;--DBMS_LOB.GETLENGTH(varc);
var_start := 1;
DBMS_LOB.READ(var_clob, var_len, var_start, var_buff);
DBMS_OUTPUT.PUT_LINE('RETURN: '||var_buff);

END;

输出

RETURN: ACED0005737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C7708000000100000000374000A627573696E6573734E6F74000F434432

3.3.dbms_lob.substr

截取大字段输出，但是并不能按正常的字符输出。

示例：

select dbms_lob.substr(t.relatedobject,100) from bizcase t where t.caseid=8967781;

输出：ACED0005737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C7708000000100000000374000A627573696E6573734E6F74000F434432