oracle 将表列转换为键值对
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2629653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting table columns to key value pairs
提问by Tom Donohue
I am writing a PL/SQL procedure that loads some data from Schema A into Schema B. They are both very different schemas and I can't change the structure of Schema B.
我正在编写一个 PL/SQL 过程，将一些数据从模式 A 加载到模式 B。它们都是非常不同的模式，我无法更改模式 B 的结构。
Columns in various tables in Schema A (joined together in a view) need to be inserted into Schema B as key=>value pairs in 2 columns in a table, each on a separate row. For example, an employee's first name might be present as employee.firstname in Schema A, but would need to be entered in Schema B as:
模式 A 中各种表中的列（在视图中连接在一起）需要作为表中 2 列中的键 => 值对插入到模式 B 中，每列在单独的行上。例如，员工的名字可能在模式 A 中显示为 employee.firstname，但需要在模式 B 中输入为：
id=>1, key=>'A123', value=>'Smith'
There are almost 100 keys, with the potential for more to be added in future. This means I don't really want to hardcode any of these keys.
有近 100 个键，将来可能会添加更多键。这意味着我真的不想对这些键中的任何一个进行硬编码。
Sample code:
示例代码：
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)
);
I thought an elegant solution would most likely involve a lookup table to determine what value to insert for each key, and doesn't involve effectively hardcoding dozens of similar statements like....
我认为一个优雅的解决方案很可能涉及一个查找表来确定为每个键插入什么值，并且不涉及有效地硬编码几十个类似的语句，例如......
insert into schema_b_values ( 1, 'A123', v_firstname );
insert into schema_b_values ( 1, 'B123', v_surname );
What I'd like to be able to do is have a local lookup table in Schema A that lists all the keys from Schema B, along with a column that gives the name of the column in the table in Schema A that should be used to populate, e.g. key "A123" in Schema B should be populated with the value of the column "firstname" in Schema A, e.g.
我希望能够做的是在模式 A 中有一个本地查找表，其中列出了模式 B 中的所有键，以及一个列，该列给出了模式 A 中表中列的名称，该列应该用于填充，例如模式 B 中的键“A123”应填充模式 A 中列“名字”的值，例如
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' );
But I'm not sure how I could dynamically use values from the lookup table to tell Oracle which columns to use.
但我不确定如何动态使用查找表中的值来告诉 Oracle 使用哪些列。
So my question is, is there an elegant solution to populate schema_b_values table with the data from schema_a_employees without hardcoding for every possible key (i.e. A123, B123, etc)?
所以我的问题是，是否有一种优雅的解决方案可以使用来自 schema_a_employees 的数据填充 schema_b_values 表，而无需对每个可能的键（即 A123、B123 等）进行硬编码？
Cheers.
干杯。
采纳答案by Vincent Malgrat
I sincerely hope that your Schema B is not the dreadedkey-value pairdesign. While some dynamic attribute-value table may be useful in some situation, you will find that all but the most basic queries are nearly impossible to write in the EAV design (even a simple query like "find all employees that are named John Smith" is hard to write -- and impossible to tune).
我真诚地希望您的 Schema B 不是可怕的键值对设计。虽然某些动态属性值表在某些情况下可能有用，但您会发现在 EAV 设计中几乎不可能编写除最基本的查询之外的所有查询（即使是像“查找所有名为 John Smith 的员工”这样的简单查询也是如此）很难写——而且不可能调整）。
Anyway, in your case you want to write a dynamic query that will look like this:
无论如何，在您的情况下，您想编写一个如下所示的动态查询：
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
You can use the following query to generate the statement:
您可以使用以下查询来生成语句：
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
You can then use EXECUTE IMMEDIATE or DBMS_SQL to execute that statement.
然后您可以使用 EXECUTE IMMEDIATE 或 DBMS_SQL 来执行该语句。
回答by APC
I like INSERT ALL as an approach because it offers an encapsulated transaction: either all rows are inserted or none are. My experience of data migration is that it tends to be a highly iterative process, so anything which assists in clean up and regression is a distinct boon.
我喜欢 INSERT ALL 作为一种方法，因为它提供了一个封装的事务：要么插入所有行，要么都不插入。我的数据迁移经验是它往往是一个高度迭代的过程，所以任何有助于清理和回归的东西都是一个明显的好处。
SQL> declare
2 l_src_name varchar2(30) := 'SCHEMA_A_EMPLOYEES';
3 l_tgt_name varchar2(30) := 'SCHEMA_B_VALUES';
4 stmt varchar2(32767);
5 begin
6 for pk_rec in ( select cc.table_name, cc.column_name
7 from user_cons_columns cc
8 , user_constraints c
9 where c.table_name = l_src_name
10 and c.constraint_type = 'P'
11 and cc.table_name = l_src_name )
12 loop
13 stmt := 'insert all';
14 for col_rec in ( select * from schema_a_lookup )
15 loop
16 stmt := stmt||' into '||l_tgt_name||' values ('
17 ||pk_rec.column_name
18 ||', '''||col_rec.the_key||''','
19 ||col_rec.the_local_field_name
20 ||')';
21 end loop;
22 stmt := stmt||' select * from '||l_src_name;
23 end loop;
24 execute immediate stmt;
25 end;
26 /
PL/SQL procedure successfully completed.
SQL>
How many rows?
几行？
SQL> select * from schema_b_values;
EMP_ID THE_K THE_VALUE
---------- ----- ---------------
1 A123 James
2 A123 Fred
1 B123 Smith
2 B123 Jones
SQL>
I have wrapped the queries in PL/SQL because it points the way towards further automation. You could add a table to hold the SOURCE and TARGET table names. Obviously there's scope for some fun if the source table has a composite primary key.
我已将查询封装在 PL/SQL 中，因为它为进一步自动化指明了方向。您可以添加一个表来保存 SOURCE 和 TARGET 表名称。显然，如果源表有一个复合主键，就会有一些乐趣。
