oracle - 将表列转换为键值对

Question

我正在编写一个 PL/SQL 过程，将一些数据从架构 A 加载到架构 B。它们都是非常不同的架构，我无法更改架构 B 的结构。

架构 A 中各个表中的列(在 View 中连接在一起)需要作为表中 2 列中的键 => 值对插入到架构 B 中，每列位于单独的行上。例如，员工的名字可能在架构 A 中显示为 employee.firstname，但需要在架构 B 中输入为:

id=>1, key=>'A123', value=>'Smith'

有近 100 个键，将来可能会添加更多键。这意味着我真的不想对这些键进行硬编码。

示例代码:

create table schema_a_employees (
    emp_id number(8,0),
    firstname varchar2(50),
    surname varchar2(50)
);
insert into schema_a_employees values ( 1, 'James', 'Smith' );
insert into schema_a_employees values ( 2, 'Fred', 'Jones' );

create table schema_b_values (
    emp_id number(8,0),
    the_key varchar2(5),
    the_value varchar2(200)
);

我认为一个优雅的解决方案很可能涉及一个查找表来确定为每个键插入什么值，并且不涉及有效地硬编码数十个类似的语句，例如......

insert into schema_b_values ( 1, 'A123', v_firstname );
insert into schema_b_values ( 1, 'B123', v_surname );

我想要做的是在模式 A 中有一个本地查找表，其中列出了模式 B 中的所有键，以及一个列，该列给出了模式 A 中的表中应该出现的列的名称用于填充，例如架构 B 中的键“A123”应使用架构 A 中“firstname”列的值进行填充，例如

create table schema_a_lookup (
    the_key varchar2(5),
    the_local_field_name varchar2(50)
);
insert into schema_a_lookup values ( 'A123', 'firstname' );
insert into schema_a_lookup values ( 'B123', 'surname' );

但我不确定如何动态使用查找表中的值来告诉 Oracle 使用哪些列。

所以我的问题是，是否有一个优雅的解决方案可以使用 schema_a_employees 中的数据填充 schema_b_values 表，而无需对每个可能的键(即 A123、B123 等)进行硬编码？

干杯。

Answer 1

我真诚地希望您的架构 B 不是 dreaded key-value pair设计。虽然某些动态属性值表在某些情况下可能有用，但您会发现，除了最基本的查询之外，几乎不可能在 EAV 设计中编写所有查询(即使是像“查找名为 John Smith 的所有员工”这样的简单查询也是如此)很难编写——而且不可能调整)。

无论如何，在您的情况下，您想要编写一个如下所示的动态查询:

SQL> INSERT ALL
  2     INTO schema_b_values VALUES (emp_id, 'A123', firstname)
  3     INTO schema_b_values VALUES (emp_id, 'B123', surname)
  4     SELECT emp_id, firstname, surname
  5       FROM schema_a_employees;

4 rows inserted

您可以使用以下查询来生成语句:

SQL> SELECT 'INSERT ALL ' sql_lines FROM dual
  2  UNION ALL
  3  SELECT 'INTO schema_b_values VALUES (emp_id, '''
  4            || dbms_assert.simple_sql_name(the_key)
  5            || ''', '
  6            || dbms_assert.simple_sql_name(the_local_field_name)
  7            ||')'
  8    FROM schema_a_lookup
  9  UNION ALL
 10  SELECT 'SELECT * FROM schema_a_employees' FROM dual;

SQL_LINES
--------------------------------------------------------------------------------
INSERT ALL
INTO schema_b_values VALUES (emp_id, 'A123', firstname)
INTO schema_b_values VALUES (emp_id, 'B123', surname)
SELECT * FROM schema_a_employees

然后您可以使用 EXECUTE IMMEDIATE 或 DBMS_SQL 来执行该语句。