【TechTarget中国原创】问：我怎么合并多个varchar2行到一个CLOB类型的列？
例如：创建表A (keys number, text CLOB);
创建表B(keys number, text varchar2(100));
insert into B values (1, 'How do I '); insert into B values (2, ' merge multiple rows '); insert into B values (3, ' into one row of type CLOB? '); |
在将表B插入表A并从表A中获取之后，我想查看下列结果：
keys text
----- -------
1 How do I
merge multiple rows
into one row of type CLOB? |
我写了下面的内容，但我还不确定怎样使用dbms_lob.writeAppend()。
DECLARE
TYPE A_table_type IS TABLE OF A%ROWTYPE
INDEX BY PLS_INTEGER;
t_A A_table_type;
CURSOR c1
SELECT text FROM B WHERE keys BETWEEN 1 AND 3;
BEGIN
OPEN c1;
LOOP --Fetch a configured set of rows at once
FETCH c1
BULK COLLECT INTO t_A LIMIT l_ROW_LIMIT;
EXIT WHEN t_A.COUNT = 0;
--For each set of rows fetched...
FOR x IN 1 .. t_A.COUNT LOOP
dbms_lob.writeAppend(?????) |
答：假设你只要表A中的一行，哪一个CLOB是联结B中的行的？这里有一种方法。
我建议你将这个问题拆分成几个简单的问题。一个问题就是如何将文本值联结到单个CLOB。我写了个单机函数执行这一操作：
create or replace
function assemble_clob(p_from in number, p_to in number)
return clob
is
v_clob clob; -- inner one
begin
for rec in (
select keys, text
from b
where keys between p_from and p_to)
loop
if (rec.keys = p_from) then -- first item
v_clob := rec.text;
else -- subsequent item
v_clob := v_clob || CHR(10) || rec.text; -- prepend newline
end if;
end loop;
return (v_clob);
end assemble_clob;
/ |
现在我可以在SQL中用到它：
insert into a (keys, text) values (1, assemble_clob(1, 3)); |
注意现在插入语句变简单了。查询表A，我得到以下结果：
SQL> column text format a30
SQL> select * from a;
KEYS TEXT
---------- ------------------------------
1 How do I
merge multiple rows
into one row of type CLOB? |