在Oracle中定界的Blob数据 [英] Delimited Blob data in oracle
本文介绍了在Oracle中定界的Blob数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在Oracle表中得到了+
分隔的BLOB数据.
I have got +
delimited BLOB data in an oracle table.
数据:
2342-34-34+83898oov+4ncjj+jdjjd11kj+20-12-2017
我想提取这些数据并将其存储在数组中
I would like to extract this data and store it in an array
预期输出:
Array[1] Array[2] Array[3]
2342-34-34 83898oov 4ncjj
我们是否有任何方法可以在oracle中实现此功能而无需在DB外部提取数据?
Do we have any way to achieve this in oracle without extracting data outside DB?
注意:每条记录的分隔符文本数都不同.
Note: Number of delimiter text varies for each record.
推荐答案
Oracle设置:
CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE TYPE cloblist IS TABLE OF CLOB;
/
CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
c_buffer CONSTANT PLS_INTEGER := 32767;
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1 .. CEIL( DBMS_LOB.GETLENGTH(blob_in) / c_buffer ) LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, c_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + c_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;
/
SHOW ERRORS;
CREATE OR REPLACE FUNCTION split_clob(
i_str IN CLOB,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN stringlist /* or cloblist */ DETERMINISTIC PIPELINED
AS
p_start PLS_INTEGER := 1;
p_end PLS_INTEGER;
c_len CONSTANT PLS_INTEGER := DBMS_LOB.GETLENGTH( i_str );
c_ld CONSTANT PLS_INTEGER := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := DBMS_LOB.INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
PIPE ROW ( DBMS_LOB.SUBSTR( i_str, p_end - p_start, p_start ) );
p_start := p_end + c_ld;
p_end := DBMS_LOB.INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
PIPE ROW ( DBMS_LOB.SUBSTR( i_str, c_len - p_start + 1, p_start ) );
END IF;
END IF;
END;
/
示例数据:
CREATE TABLE test ( data BLOB );
INSERT INTO test VALUES ( UTL_RAW.CAST_TO_RAW( '2342-34-34+83898oov+4ncjj+jdjjd11kj+20-12-2017' ) );
查询1-作为行:
SELECT x.COLUMN_VALUE AS value
FROM test t,
TABLE( split_clob( blob_to_clob( data ), '+' ) ) x;
输出:
VALUE
----------
2342-34-34
83898oov
4ncjj
jdjjd11kj
20-12-2017
查询2-作为列:
SELECT DBMS_LOB.SUBSTR( str, delimiter1 - 1, 1 ) AS A1,
DBMS_LOB.SUBSTR( str, delimiter2 - delimiter1 - 1, delimiter1 + 1 ) AS A2,
DBMS_LOB.SUBSTR( str, delimiter3 - delimiter2 - 1, delimiter2 + 1 ) AS A3
FROM (
SELECT str,
DBMS_LOB.INSTR( str, '+', 1, 1 ) AS delimiter1,
DBMS_LOB.INSTR( str, '+', 1, 2 ) AS delimiter2,
DBMS_LOB.INSTR( str, '+', 1, 3 ) AS delimiter3
FROM (
SELECT BLOB_TO_CLOB( data ) AS str
FROM test
)
);
输出:
A1 A2 A3
---------- ---------- ----------
2342-34-34 83898oov 4ncjj
这篇关于在Oracle中定界的Blob数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文