使用Oracle过程将列中的字符串拆分为多个列 [英] to split the string in the column into multiple columns using oracle procedure

问题描述

我正在使用oracle过程将列中的字符串(用逗号分隔)拆分为多列.

I am looking to split the string (comma separated) in column into multiple columns by using oracle procedure.

我正在寻找动态查询，因为需要拆分的列数是未知的.

I am looking for the dynamic query as the number of columns need to be split will be unknown.

推荐答案

除非您要对最大列数做出假设，否则静态SQL不能做到这一点.

It cannot be done with static SQL, unless you want to make assumptions about the maximum number of columns.

如果可以对最大列数做出一些假设，则可以仅返回100个名为x_part1，x_part2，... x_part100的输出变量中的值.丑陋但容易得多.您还可以返回x_count告诉呼叫者有多少.

If you can make some assumptions about the maximum number of columns, you can just return the values in, say, 100 output variables named x_part1, x_part2, ... x_part100. Ugly but a lot easier. You could also return x_count to tell the caller how many there were.

否则，只能使用动态SQL(例如EXECUTE IMMEDIATE)来完成此操作，然后将SYS_REFCURSOR返回给调用方.

Otherwise, it can only be done using dynamic SQL (e.g., EXECUTE IMMEDIATE) and then return a SYS_REFCURSOR to your caller.

但是，他们必须使用DBMS_SQL将SYS_REFCURSOR转换为DBMS_SQL游标，然后使用该程序包确定有多少列，它们的名称，然后最终获取所有数据.真是痛苦.

But then, they'll have to use DBMS_SQL to convert the SYS_REFCURSOR to a DBMS_SQL cursor and then use that package to figure out how many columns are, what they are named, and then, ultimately, to fetch all the data. A real pain.

DECLARE
  i               NUMBER := 0;
  l_column_list   VARCHAR2 (32000) := NULL;
  l_sql           VARCHAR2 (32000);
  l_rc            SYS_REFCURSOR;
BEGIN
  FOR r IN (WITH test_data AS (SELECT 'a,b,ccc,ddddd,ee,,ggggg' test_string FROM DUAL),
                 parse1 AS
                   (SELECT test_string,
                           INSTR (',' || test_string || ',',
                                  ',',
                                  1,
                                  LEVEL)
                             start_pos,
                             INSTR (',' || test_string || ',',
                                    ',',
                                    1,
                                    LEVEL + 1)
                           - 2
                             end_pos
                    FROM   test_data
                    CONNECT BY ROWNUM <= LENGTH (REGEXP_REPLACE (test_string, '[^,]', '')) + 1)
            SELECT SUBSTR (test_string, start_pos, end_pos - start_pos + 1) field_part
            FROM   parse1) LOOP
    i := i + 1;

    IF l_column_list IS NOT NULL THEN
      l_column_list   := l_column_list || ',';
    END IF;

    l_column_list   := l_column_list || '''' || r.field_part || ''' as PART_' || LPAD (TO_CHAR (i), 3, '0');
  END LOOP;

  l_sql   := 'SELECT ' || l_column_list || ' FROM DUAL';
  DBMS_OUTPUT.put_line (l_sql);
  OPEN l_rc FOR l_sql;

  -- Return l_rc to the caller
END;

这篇关于使用Oracle过程将列中的字符串拆分为多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！