范围填充表 [英] Range Fill Table

问题描述

美好的一天！有一张桌子:

Good day! There is a table:

CREATE TABLE table
(
start_range  varcahar2(10),
end_range varcahar2(10),
val_range NUMBER(10)
);

在初始阶段，我们填写了两个字段:start_range 和 end_range.

At the initial stage, we filled in two fields: start_range and end_range.

start_range = a1;
end_range = a5;

你能在 Apex 的 a1-a5 (a1, a2, a3, a4, a5) 范围内填充一个完全不同的表格吗?

Can you fill a completely different table in the range a1-a5 (a1, a2, a3, a4, a5) in Apex?

推荐答案

看起来像一个分层查询.

Looks like a hierarchical query.

测试用例:

SQL> CREATE TABLE test
  2  (
  3     start_range  VARCHAR2 (10),
  4     end_range    VARCHAR2 (10),
  5     val_range    NUMBER (10)
  6  );

Table created.

SQL> INSERT INTO test
  2       VALUES ('a1', 'a5', NULL);

1 row created.

SQL> INSERT INTO TEST
  2       VALUES ('L4819201', 'L4819205', NULL);

1 row created.

SQL> SELECT * FROM test;

START_RANG END_RANGE   VAL_RANGE
---------- ---------- ----------
a1         a5
L4819201   L4819205

查询:

SQL> INSERT INTO test2 (val)
  2     SELECT    SUBSTR (start_range, 1, 1)
  3            || TO_CHAR (
  4                  (  TO_NUMBER (REGEXP_SUBSTR (start_range, '\d+$'))
  5                   + COLUMN_VALUE
  6                   - 1))
  7               AS val
  8       FROM test
  9            CROSS JOIN
 10            TABLE (
 11               CAST (
 12                  MULTISET (
 13                         SELECT LEVEL
 14                           FROM DUAL
 15                     CONNECT BY LEVEL <=
 16                                     TO_NUMBER (
 17                                        REGEXP_SUBSTR (end_range, '\d+$'))
 18                                   - TO_NUMBER (
 19                                        REGEXP_SUBSTR (start_range, '\d+$'))
 20                                   + 1) AS SYS.odcinumberlist))
 21      WHERE start_range = '&start_range';
Enter value for start_range: a1

5 rows created.

SQL> /
Enter value for start_range: L4819201

5 rows created.

结果:

SQL> SELECT * FROM test2 ORDER BY val;

VAL
----------
a1
a2
a3
a4
a5
L4819201
L4819202
L4819203
L4819204
L4819205

10 rows selected.

SQL>

这篇关于范围填充表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！