Oracle PLSQL -如何插入管道分隔的字符串值到表中

Question

需求如下:

我有一个字符串与|(管道)分开。我需要拉出每个字符串值并插入到表中。

例如:字符串像A,B,C,D,E,F,L,R,X,Z

Table has all the columns from A to Z 

如果给定的字符串有一个值，该列应该插入'Yes'否则'No'。像这样，如果字符串有值，表中对应的列应该变成'Yes'，否则就变成'No'。

我写了一个程序，它插入不同的行。

---

DECLARE 
LV_VUSR VARCHAR2(1000);
BEGIN
FOR J IN (SELECT COLUMN_VALUE FROM TABLE(MS_APPS_UTILITIES.SPLIT_STRING('A,B,D',','))) 
LOOP
  LV_VUSR := J.COLUMN_VALUE;
     INSert into dummy_v 
     values ('1', decode(J.column_value,'A','Y','N'),decode(J.column_value,'B','Y','N'),decode(J.column_value,'C','Y','N'),decode(J.column_value,'D','Y','N'));
END LOOP;
END;

我已经写了这个块，其中多行插入相同的id。有谁能帮忙吗?

Answer 1

这可能过于简化了，但是如果您可以依赖输入数据的质量，为什么不这样呢:

INSERT INTO table_name
WITH data ( id, value ) AS (
  SELECT 1, 'A,B,E' FROM DUAL UNION ALL
  SELECT 2, 'B,E' FROM DUAL
)
SELECT d.id,
       CASE WHEN INSTR(d.value,'A') > 0 THEN 'Y' ELSE 'N' END A,
       CASE WHEN INSTR(d.value,'B') > 0 THEN 'Y' ELSE 'N' END B,
       CASE WHEN INSTR(d.value,'C') > 0 THEN 'Y' ELSE 'N' END C,
       CASE WHEN INSTR(d.value,'D') > 0 THEN 'Y' ELSE 'N' END D,
       CASE WHEN INSTR(d.value,'E') > 0 THEN 'Y' ELSE 'N' END E
FROM   data d

REGEXP_INSTR可以用来覆盖输入数据中的一些小变化。

Answer 2

5列的小示例(您应该能够将其扩展到全部26列):

Oracle安装

:

CREATE TABLE table_name (
  id INT,
  A CHAR(1),
  B CHAR(1),
  C CHAR(1),
  D CHAR(1),
  E CHAR(1)
);

查询

:

INSERT INTO table_name
WITH data ( id, value ) AS (
  SELECT 1, 'A,B,E' FROM DUAL UNION ALL
  SELECT 2, 'B,E' FROM DUAL
)
SELECT d.id, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'A' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'B' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'C' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'D' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'E' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END
FROM   data d,
       TABLE(
        CAST(
          MULTISET(
            SELECT REGEXP_SUBSTR( d.value, '[^,]+', 1, LEVEL )
            FROM   DUAL
            CONNECT BY LEVEL <= REGEXP_COUNT( d.value, '[^,]+' )
          ) AS SYS.ODCIVARCHAR2LIST
        )
       ) t
GROUP BY d.id;

:

SELECT * FROM table_name;
        ID A B C D E
---------- - - - - -
         1 Y Y N N Y 
         2 N Y N N Y 

或使用短存储函数的解决方案:

Oracle安装

:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(20);
/
CREATE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
  p_result       stringlist := stringlist();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

查询

:

INSERT INTO table_name
WITH data ( id, value ) AS (
  SELECT 1, 'A,B,E' FROM DUAL UNION ALL
  SELECT 2, 'B,E' FROM DUAL
)
SELECT id,
       CASE WHEN 'A' MEMBER OF vs THEN 'Y' ELSE 'N' END,
       CASE WHEN 'B' MEMBER OF vs THEN 'Y' ELSE 'N' END,
       CASE WHEN 'C' MEMBER OF vs THEN 'Y' ELSE 'N' END,
       CASE WHEN 'D' MEMBER OF vs THEN 'Y' ELSE 'N' END,
       CASE WHEN 'E' MEMBER OF vs THEN 'Y' ELSE 'N' END
FROM  (
  SELECT id,
         split_String( value ) AS vs
  FROM   data
);

:

SELECT * FROM table_name;
        ID A B C D E
---------- - - - - -
         1 Y Y N N Y 
         2 N Y N N Y