如何在Oracle中将单行拆分为多行并插入到表中? [英] How do I split a single row into multiple rows and Insert into a table in Oracle?

问题描述

我使用以下查询从表Employee_Master中选择数据

I select data from table Employee_Master using the below query

SELECT EMP_ID
, EMP_NAME
, EMP_LOC1
, EMP_LOC2
, EMP_LOC3
, EMP_DESG
, EMP_ADDR1
, EMP_ADDR2
, EMP_ADDR3
, EMP_PHNO
, EMP_STATUS
FROM Employee_Master
WHERE EMP_STATUS = 'A'

结果集如下所示.

    EMP_ID          EMP_NAME         EMP_LOC1      EMP_LOC2    EMP_LOC3   EMP_DESG  EMP_ADDR1   EMP_ADDR2   EMP_ADDR3   EMP_PHNO    EMP_STATUS
     23       Covey, Stephen J, Mr       1            2            4       Writer       3         3            3          11          A
     24       Jhon, Abraham A, Mr        1            2            4       Actor        1         1            1          22          A

现在，我必须将一个记录分成三个记录，并像这样插入到Emp_history表中. EMP_SAL_ID是从另一个可以处理的表中提取的.

Now I have to split the one record into three records and insert in into Emp_history table like this. EMP_SAL_ID is fetched from a different table which I can take care of.

    EMP_SAL_ID  First_Name  Middle_Initial  Last_Name   Title   Designation Location    Address Phone_No    Status
       3456      Stephen          J           Covey      Mr       Writer       1           3        11         A
       3456      Stephen          J           Covey      Mr       Writer       2           3        11         A
       3456      Stephen          J           Covey      Mr       Writer       4           3        11         A

是否可以使用PL/SQL块或任何其他具有性能的简单方法来实现这一目标?

Is it possible to achieve this using PL/SQL block or any other simple method with performance.?

推荐答案

您可以使用 UNION 为每个地址值使用单独的行.

You could use UNION to have separate rows for each address value.

例如，假设您在with子句中建立了一行:

For example, let's say you have a row as built in the with clause:

SQL> WITH DATA(EMP_NAME,EMP_ADDR1,EMP_ADDR2,EMP_ADDR3) AS(
  2  SELECT 'Covey, Stephen J, Mr', 1, 2, 4 FROM DUAL UNION ALL
  3  SELECT 'Jhon, Abraham A, Mr ', 1, 2, 4 FROM DUAL
  4  )
  5  SELECT * FROM DATA;

EMP_NAME              EMP_ADDR1  EMP_ADDR2  EMP_ADDR3
-------------------- ---------- ---------- ----------
Covey, Stephen J, Mr          1          2          4
Jhon, Abraham A, Mr           1          2          4

SQL>

现在，您可以使用 UNION 将上面的行分成多行.只需做额外的工作即可使用 SUBSTR 和 INSTR 从emp_name中提取名称.

Now you could split the above row into multiple rows using UNION. Just an additional effort is to use SUBSTR and INSTR to extract the name from emp_name.

例如，

SQL> WITH DATA(EMP_NAME,EMP_ADDR1,EMP_ADDR2,EMP_ADDR3) AS(
  2  SELECT 'Covey, Stephen J, Mr', 1, 2, 4 FROM DUAL UNION ALL
  3  SELECT 'Jhon, Abraham A, Mr ', 1, 2, 4 FROM DUAL
  4  )
  5  SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)) AS "ename",
  6    emp_addr1                                                                                                  AS "addr"
  7  FROM DATA
  8  UNION ALL
  9  SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)),
 10    emp_addr2
 11  FROM DATA
 12  UNION ALL
 13  SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)),
 14    emp_addr3
 15  FROM DATA
 16  /

ename                      addr
-------------------- ----------
 Stephen                      1
 Abraham                      1
 Stephen                      2
 Abraham                      2
 Stephen                      4
 Abraham                      4

6 rows selected.

SQL>

注意:

WITH 子句仅用于构建示例数据以进行演示.在实际情况下，您只需要在表上使用 SELECT 语句即可.

The WITH clause is only used to build sample data for demonstration purpose. In real case, you just need to use the SELECT statement on your table.

INSERT INTO hist_table SELECT statement as shown above...

这篇关于如何在Oracle中将单行拆分为多行并插入到表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！