我有一個包含如下字串的 Oracle 18c 表：
select
'((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' as multipart_lines
--There are more rows in the actual table.
from
dual
MULTIPART_LINES
-------------------------------------------------------------
((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))
-- v1 v2 v3 v4 v5
-- | part 1 | | part 2 |
- 各個坐標由空格分隔。
- 頂點（XYZ 坐標）用逗號分隔。
- 行部分用括號括起來并用逗號分隔。
在查詢中，我想為每個頂點生成行：
PART_NUM VERTEX_NUM X Y Z
---------- ---------- ---------- ---------- ----------
1 1 0 5 0
1 2 10 10 11.18
1 3 30 0 33.54
2 1 50 10 33.54
2 2 60 10 43.54
- 我想在查詢中執行此操作。我不想在表中插入行。
- 不幸的是，我在資料庫中沒有 CREATE TYPE 權限。但我可以創建函式（當然，行內函式也是一種選擇）。
如何從字串中的數字（頂點）生成行？
相關：Oracle Ideas - generate_series() 函式
uj5u.com熱心網友回復：
如果輸入采用某種標準格式（例如 JSON）會簡單得多。那么任務將是微不足道的。你對此有任何權力嗎？
如果沒有，您可以將輸入轉換為正確的 JSON（或類似的），或者您可以直接解決問題。假設 Oracle 版本 12.1 或更高，我將在下面說明后者。
with
inputs (id, multipart_lines) as (
select 2810,
'((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))'
from dual union all
select 7284, '((-2.3 0.2 3))' from dual
)
select id, part_num, vertex_num, x, y, z
from inputs
cross join lateral
( select level as part_num,
regexp_substr(multipart_lines,
'\(([^()] )\)', 1, level, null, 1) as part
from dual
connect by level <= regexp_count(multipart_lines, '\(') - 1
)
cross join lateral
(
select level as vertex_num,
regexp_substr(part, '[^,] ', 1, level) as vertex
from dual
connect by level <= regexp_count(part, ',') 1
)
cross join lateral
(
select to_number(regexp_substr(vertex, '[^ ] ', 1, 1)) as x,
to_number(regexp_substr(vertex, '[^ ] ', 1, 2)) as y,
to_number(regexp_substr(vertex, '[^ ] ', 1, 3)) as z
from dual
)
order by id, part_num, vertex_num -- if needed
;
輸出（來自我在查詢中包含的示例輸入）：
ID PART_NUM VERTEX_NUM X Y Z
---------- ---------- ---------- ---------- ---------- ----------
2810 1 1 0 5 0
2810 1 2 10 10 11.18
2810 1 3 30 0 33.54
2810 2 1 50 10 33.54
2810 2 2 60 10 43.54
7284 1 1 -2.3 .2 3
uj5u.com熱心網友回復：
作為替代方案 - 這是您如何處理輸入字串以將它們轉換為正確的 JSON 字串的方法；然后任務變得微不足道。首先單獨顯示 JSON 化，因為它確實是該解決方案的有意義的部分；然后在顯示查詢和結果后，我將通過添加 JSON 操作來完成解決方案。
with
inputs (id, multipart_lines) as (
select 2810,
'((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))'
from dual union all
select 7284, '((-2.3 0.2 3))' from dual
)
, j (id, ml) as (
select id,
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(multipart_lines
, '\(\s*\(\s*', '[[[')
, '\s*\)\s*\)', ']]]')
, '\s*\)\s*,\s*\(\s*', '],[')
, '\s*,\s*', '],[')
, '\s ', ',')
from inputs
)
select * from j;
ID ML
----- --------------------------------------------------------------------
2810 [[[0,5,0],[10,10,11.18],[30,0,33.54]],[[50,10,33.54],[60,10,43.54]]]
7284 [[[-2.3,0.2,3]]]
您的輸入應該看起來像ml我的子查詢中的列中的字串j- 然后您可以像這樣處理它們：
with
inputs (id, multipart_lines) as (
........
)
, j (id, ml) as (
........
)
select id, part_num, vertex_num, x, y, z
from j,
json_table(ml, '$[*]'
columns (
part_num for ordinality,
nested path '$[*]'
columns (
vertex_num for ordinality,
x number path '$[0]',
y number path '$[1]',
z number path '$[2]'
)
)
)
order by id, part_num, vertex_num -- if needed
;
輸出與我的其他答案相同。
uj5u.com熱心網友回復：
我看到 mathguy 已經到了那里。我正在使用，from json_table但我不能一次取消嵌套 2 行，否則我基本上會在那里第二次使用 row_number() over（由 Paru_num 磁區）。
create table sample(value varchar(100));?
insert into sample values ('((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))')
1 行受影響
with a as ( select '["'|| regexp_replace(value,'\(|\)','') ||'"]' a from sample ), b as ( select regexp_replace(a,', ?','","') b from a ), c as ( SELECT value c FROM json_table( (select b from b) , '$[*]' COLUMNS (value PATH '$') ) ), d as ( SELECT c d, instr(c,' ') s1, instr(c,' ',instr(c,' ') 1) s2 from c) select substr(d,0,s1) x, substr(d,s1 1,s2-s1) y, substr(d,s2 1) z from d
X | 是 | Z :-- | :-- | :---- 0 | 5 | 0 10 | 10 | 11.18 30 | 0 | 33.54 50 | 10 | 33.54 60 | 10 | 43.54
with a as ( select '["'|| regexp_replace(value,'\( |\) ','"') ||'"]' a from sample ), b as( select replace(a,'""','"')b from a ), c as ( SELECT row_number() over (order by 'zero') pn, value c FROM json_table( (select b from b) , '$[*]' COLUMNS (value PATH '$') ) ), d as ( select '["'|| pn ||' '|| regexp_replace(c,', ?','","'||pn||' ')||'"]' d from c ) select * from d| D | | :-------------------------------------------------------- | | ["1 0 5 0","1 10 10 11.18","1 30 0 33.54"] | | ["2 50 10 33.54","2 60 10 43.54"] |
with a as ( select '["'|| regexp_replace(value,'\( |\) ','"') ||'"]' a from sample ), b as( select replace(a,'""','"')b from a ), c as ( SELECT row_number() over (order by 'zero') pn, value c FROM json_table( (select b from b) , '$[*]' COLUMNS (value PATH '$') ) ), d as ( select '["'|| pn ||' '|| regexp_replace(c,', ?','","'||pn||' ')||'"]' d from c ), e as ( SELECT row_number() over (order by 'zero') pn, value c FROM json_table( (select d from d) , '$[*]' COLUMNS (value PATH '$') ) ) select * from eORA-01427: 單行子查詢回傳多于一行
db<>在這里擺弄
uj5u.com熱心網友回復：
@SolomonYakobson 在Oracle 社區帖子中提供了這個答案。
with sample as (
select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' as multipart_lines
--There are more rows in the actual table.
from dual
)
select part_num,
vertex_num,
to_number(regexp_substr(vertex,'[^ ] ')) x,
to_number(regexp_substr(vertex,'[^ ] ',1,2)) y,
to_number(regexp_substr(vertex,'[^ ] ',1,3)) z
from sample,
lateral(
select level part_num,
regexp_substr(multipart_lines,'\(([^()] )',1,level,null,1) part
from dual
connect by level < regexp_count(multipart_lines,'\(')
),
lateral(
select level vertex_num,
regexp_substr(part,'[^,] ',1,level) vertex
from dual
connect by level <= regexp_count(part,',') 1
)
/
PART_NUM VERTEX_NUM X Y Z
---------- ---------- ---------- ---------- ----------
1 1 0 5 0
1 2 10 10 11.18
1 3 30 0 33.54
2 1 50 10 33.54
2 2 60 10 43.54
以及 Oracle 19c 解決方案：
create or replace
function split_multipart_line(
p_line varchar2
)
return varchar2
sql_macro
is
begin
return q'[
select part_num,
vertex_num,
to_number(regexp_substr(vertex,'[^ ] ')) x,
to_number(regexp_substr(vertex,'[^ ] ',1,2)) y,
to_number(regexp_substr(vertex,'[^ ] ',1,3)) z
from dual,
lateral(
select level part_num,
regexp_substr(p_line,'\(([^()] )',1,level,null,1) part
from dual
connect by level < regexp_count(p_line,'\(')
),
lateral(
select level vertex_num,
regexp_substr(part,'[^,] ',1,level) vertex
from dual
connect by level <= regexp_count(part,',') 1
)]';
end;
/
Function created.
SQL> with sample as (
2 select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' multipart_lines from dual union all
3 select '((1 2 3, 4 5 6, 7 8 9, 10 11 12),(22 33 44, 55 66 77))' multipart_lines from dual
4 )
5 select l.*
6 from sample,
7 lateral(
8 select *
9 from split_multipart_line(multipart_lines)
10 ) l
11 /
PART_NUM VERTEX_NUM X Y Z
---------- ---------- ---------- ---------- ----------
1 1 0 5 0
1 2 10 10 11.18
1 3 30 0 33.54
2 1 50 10 33.54
2 2 60 10 43.54
1 1 1 2 3
1 2 4 5 6
1 3 7 8 9
1 4 10 11 12
2 1 22 33 44
2 2 55 66 77
11 rows selected.
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/467736.html
上一篇：未定義的陣列鍵；試圖訪問null型別值的陣列偏移量；foreach()引數必須是陣列|物件型別
下一篇：<>與多個值相比如何作業？