字符串长度
语法: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/