• 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()
    

    ⑤查询结果

    参考资料

  • 相关阅读:
    [转发]深入浅出EventSourcing和CQRS
    [转载]缓存与数据库双写一致性问题
    关于com.microsoft.sqlserver.jdbc.SQLServerException: 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“java.lang.RuntimeException: Could not generate DH keypair”
    [转载]安装sql2016时提示Polybase 要求安装Oracle JRE 7更新51 (64位)或更高版本”规则失败
    windows安装配置压缩版mysql
    virtualbox虚拟机centeros7系统桥接网卡网络配置
    oracle导入dmp文件(恢复数据)
    解决PL/SQL DEVELOPER12查询中文乱码问题(本地没装Oracle)
    JAVA线程Thread
    深入构造器
  • 原文地址:https://www.cnblogs.com/it774274680/p/15305661.html
Copyright © 2020-2023  润新知