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三个数据项信息，并且数据项的信息是混乱的

思路：使用存储过程，不断的遍历获取所需的数据项，一旦没有找到数据则不再执行。

分析：

1. 删除原有临时表的数据，保证临时表记录的数据是最新的

2. 使用dbms_instr函数来定位关键字的位置，获取关键字对应键的值

3. 修改dbms_instr函数下一次关键字的起始位置，因为数据量的个数不确定

4. 使用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/