Oracle的clob数据类型
字符串长度
语法：DBMS_LOB.GETLENGTH(field)
作用：获取filed字段的总长度，和length函数类似
字符串截取
场景：使用substr函数有时会无法解决clob数据类型的数据，我们可以使用DBMS_LOB.substr函数，作用和substr函数类似。
语法：
-- 从start位置在field截取长度为length的字符串
DBMS_LOB.SUBSTR(field,length,start)
-- 获取field的所有数据
DBMS_LOB.SUBSTR(field)
注意：如果filed的字段很长的话还是会出现"字符串截断"的情况，但是可以根据数据的实际情况调整length的长度，效果会比substr函数好点
查询字符串索引位置
语法：
-- 查询field中keyword从startPosition开始第number次出现的索引
dbms_lob.instr(field,keyword,startPosition,number)
-- 查询field中keyword从startPosition开始出现的索引
dbms_lob.instr(field,keyword,startPosition)
-- 从左往右查询field中符合keyword的索引。
dbms_lob.instr(field,keyword)
作用：和instr类似，不过dbms_lob.instr可以解决大数据字段的问题
注意：
- 如果
field中没有找到keyword，则返回值为0 - 如果
startPosition的值为0，则返回值为NULL
案例
clob数据类型的数据提取
有的时候我们会在数据库中使用clob来存储json的数据，后续需要从clob中读取对应的数据项。
提取数据，需要分析对应的数据结构，不同的数据结构处理的形式不同。
假设在user表中的info字段使用的是clob数据类型，其中存储如下数据：
{
"employees":[
{"firstName":"Bill","lastName":"Gates","creation_time":"2021-01-01","age":"30"},
{"firstName":"George","lastName":"Bush","creation_time":"2021-06-01","age":"26"},
{"firstName":"Thomas","lastName":"Carter","creation_time":"2020-03-01","age":"23"},
...
]
}
需求：提取出employees中firstName、lastName、age三个数据项信息，并且数据项的信息是混乱的
思路：使用存储过程，不断的遍历获取所需的数据项，一旦没有找到数据则不再执行。
分析：
-
删除原有临时表的数据，保证临时表记录的数据是最新的
-
使用
dbms_instr函数来定位关键字的位置，获取关键字对应键的值 -
修改
dbms_instr函数下一次关键字的起始位置，因为数据量的个数不确定 -
使用
while来进行死循环，然后当关键字没找到，直接退出死循环，遍历下一行
以下代码在达梦数据库下验证通过
①创建测试数据
create table test001 (
info clob
);
insert into test001 values ('
{
"employees":[
{"firstName":"Bill","lastName":"Gates","creation_time":"2021-01-01","age":"30"},
{"firstName":"George","lastName":"Bush","creation_time":"2021-06-01","age":"26"},
{"firstName":"Thomas","lastName":"Carter","creation_time":"2020-03-01","age":"23"}
]
}
')
②创建临时表存储数据
create table t_employees (
firstname varchar2(20),
lastname varchar2(20),
age number
)
③创建存储过程
create or replace procedure p_employees() is
declare
cursor datas is select info from test001;
-- 定义关键字的位置来获取关键字的信息
firstname_index number;
firstname_start_index number;
firstname_end_index number;
firstname_value varchar2(100);
lastname_index number;
lastname_start_index number;
lastname_end_index number;
lastname_value varchar2(100);
age_index number;
age_start_index number;
age_end_index number;
age_value varchar2(100);
v_sql varchar2(1000);
begin
-- 删除原有的数据，保证临时表存储新的数据
v_sql:= 'delete from t_employees';
execute immediate v_sql;
-- 遍历游标，即遍历每行
for data in datas loop
firstname_start_index:= 1;
lastname_start_index:= 1;
age_start_index:= 1;
while 1>0 loop
-- 根据关键字定位位置，然后获取 firstName": "XXX" 中XX的值
firstname_index:= dbms_lob.instr(data.info,'firstName',firstname_start_index);
if firstname_index=0 then
goto next1;
end if;
firstname_start_index:= firstname_index+length('firstName":"');
firstname_end_index:= dbms_lob.instr(data.info,'"',firstname_start_index);
firstname_value:= dbms_lob.substr(data.info,firstname_end_index-firstname_start_index,firstname_start_index);
lastname_index:= dbms_lob.instr(data.info,'lastName',lastname_start_index);
lastname_start_index:= lastname_index+length('lastName":"');
lastname_end_index:= dbms_lob.instr(data.info,'"',lastname_start_index);
lastname_value:= dbms_lob.substr(data.info,lastname_end_index-lastname_start_index,lastname_start_index);
age_index:= dbms_lob.instr(data.info,'age',age_start_index);
age_start_index:= age_index+length('age":"');
age_end_index:= dbms_lob.instr(data.info,'"',age_start_index);
age_value:= dbms_lob.substr(data.info,age_end_index-age_start_index,age_start_index);
v_sql:= 'insert into t_employees values ('''||firstname_value||''','''||lastname_value||''','||age_value||')';
execute immediate v_sql;
end loop;
<<next1>>
null;
end loop;
end;
④调用存储过程
call p_employees()
⑤查询结果
参考资料
- DBMS_LOB包的使用：http://blog.itpub.net/23065269/viewspace-630417/
