在存储过程中解析自己拼装的clob字段数据

场景说明

在oracle存储过程中需要输入自己拼装的字符串，但是该字符串太长，只能通过clob字段进行存储。但是解析clob字段和解析varchar2的方式很不一样。
拼装的结构暂定 1~a~101|1~b~101|1~xa~101|4~xc~133|5~a~0|。。。每段内容使用"|"分隔，每段内容里面的内容使用"~"进行分隔。

解决方法

1、设定每次读取clob字段的最大长度；
2、读取字段内容，并找到完整的拼装数据；
3、更新下一次读取clob字段的起始节点；
4、从1开始循环。

1.定义循环次数和读取clob字段的最大长度

lv_tmp_offset := dbms_lob.getlength(v_elements);
lv_count := ceil(lv_tmp_offset / lv_limit)+1;
lv_limit := 3000;

2.读取内容。并更新下次读取的起始位置。

dbms_lob.read(v_elements, lv_limit, lv_offset, lv_elements);
lv_tmp_offset := instr(lv_elements, '|', -1, 1);
lv_elements := substr(lv_elements, 0, lv_tmp_offset);
lv_offset := lv_offset + lv_tmp_offset;

附上原过程

PROCEDURE addtemplate_p(v_name IN VARCHAR2,
                          v_expiredate IN VARCHAR2,
                          v_status IN VARCHAR2,
                          v_remark IN VARCHAR2,
                          v_storeno IN VARCHAR2,
                          v_elements IN CLOB,
                          v_x0 IN VARCHAR2,
                          v_y0 IN VARCHAR2,
                          v_x1 IN VARCHAR2,
                          v_y1 IN VARCHAR2,
                          v_operid IN VARCHAR2,
                          v_opername IN VARCHAR2,
                          v_ret OUT NUMBER,
                          v_desc OUT VARCHAR2) AS
    lv_id NUMBER;
    lv_eid NUMBER;
    lv_x0 VARCHAR2(100);
    lv_y0 VARCHAR2(100);
    lv_x1 VARCHAR2(100);
    lv_y1 VARCHAR2(100);
    lv_type CHAR(1);
    lv_style VARCHAR2(1000);
    lv_value VARCHAR2(100);
    lv_flag CHAR(1);
    lv_elements VARCHAR2(3000);
    lv_limit NUMBER;
    lv_offset NUMBER;
    lv_tmp_offset NUMBER;
    lv_count NUMBER;
    i NUMBER;
    n NUMBER;
    CURSOR cur_rec IS
      SELECT column_value rec FROM TABLE(fn_split(lv_elements, '|'));
    row_rec cur_rec%ROWTYPE;
  BEGIN
    SELECT seq_template_p_id.nextval INTO lv_id FROM dual;
    lv_limit := 3000;
    lv_offset := 1;
    lv_count := 1;
    i := 1;
    n := 0;
    IF v_elements IS NULL THEN
      RETURN;
    END IF;
    lv_tmp_offset := dbms_lob.getlength(v_elements);
    lv_count := ceil(lv_tmp_offset / lv_limit)+1;
    WHILE i < lv_count
    LOOP
      BEGIN
        IF i = lv_count - 1 THEN
          lv_limit := MOD(dbms_lob.getlength(v_elements), lv_limit);
        END IF;
        dbms_lob.read(v_elements, lv_limit, lv_offset, lv_elements);
        lv_tmp_offset := instr(lv_elements, '|', -1, 1);
        lv_elements := substr(lv_elements, 0, lv_tmp_offset);
        lv_offset := lv_offset + lv_tmp_offset;
        i := i + 1;
        OPEN cur_rec;
        LOOP
          FETCH cur_rec
            INTO row_rec;
          EXIT WHEN cur_rec%NOTFOUND OR cur_rec%NOTFOUND IS NULL;
          SELECT seq_element_id.nextval INTO lv_eid FROM dual;
          lv_x0 := rtrim(regexp_substr(row_rec.rec || '~', '.*?~', 1, 1),
                         '~');
          lv_y0 := rtrim(regexp_substr(row_rec.rec || '~', '.*?~', 1, 2),
                         '~');
          lv_x1 := rtrim(regexp_substr(row_rec.rec || '~', '.*?~', 1, 3),
                         '~');
          lv_y1 := rtrim(regexp_substr(row_rec.rec || '~', '.*?~', 1, 4),
                         '~');
          lv_type := rtrim(regexp_substr(row_rec.rec || '~', '.*?~', 1, 5),
                           '~');
          lv_style := rtrim(regexp_substr(row_rec.rec || '~', '.*?~', 1, 6),
                            '~');
          lv_value := rtrim(regexp_substr(row_rec.rec || '~', '.*?~', 1, 7),
                            '~');
          lv_flag := rtrim(regexp_substr(row_rec.rec || '~', '.*?~', 1, 8),
                           '~');
          --插入元素
          INSERT INTO pop_element
            (tid, eid, x0, y0, x1, y1, style, TYPE, VALUE, flag)
          VALUES
            (lv_id,
             lv_eid,
             lv_x0,
             lv_y0,
             lv_x1,
             lv_y1,
             lv_style,
             lv_type,
             lv_value,
             lv_flag);
        END LOOP;
        CLOSE cur_rec;
      END;
    END LOOP;
    --插入母版信息
    INSERT INTO pop_temp_p
      (id,
       status,
       expiredate,
       NAME,
       operid,
       opername,
       createdate,
       storeno,
       remark,
       x0,
       y0,
       x1,
       y1)
    VALUES
      (lv_id,
       v_status,
       to_date(v_expiredate, 'yyyy-mm-dd'),
       v_name,
       v_operid,
       v_opername,
       SYSDATE,
       v_storeno,
       v_remark,
       v_x0,
       v_y0,
       v_x1,
       v_y1);
    --记录日志
    INSERT INTO sys_log_pop
      (module, operid, opername, operdate, key, remark)
    VALUES
      ('新增母版信息', v_operid, v_opername, SYSDATE, '', '新增母版信息');
    COMMIT;
    v_ret := 1;
    v_desc := '操作成功';
  EXCEPTION
    WHEN OTHERS THEN
      v_ret := -1;
      v_desc := '操作失败,' || SQLERRM;
      ROLLBACK;
  END;