Oracle 动态行转列返回JSON

1.参数介绍

1. columns_name: 返回查询的列名
2. sql_from: 表名或者语句
3. sql_where: Condition
4. p_str1: 动态行转列的列名称
5. p_str2:动态行转列的列值的名称
6. P_json: sql_from语句字段是否有json对象和数组
7. p_type:MAX/MIN/SUM…
8. p_order_by: 行转列排序

2.语句

FUNCTION buildPivot
  (
    columns_name IN CLOB,
    sql_from     IN CLOB,
    sql_where    IN CLOB,
    p_str1       IN CLOB,
    p_str2       IN CLOB,
    P_json       IN CLOB DEFAULT 'N',
    p_type       IN CLOB DEFAULT 'MAX',
    p_order_by   IN CLOB DEFAULT ''
  ) RETURN CLOB IS
    v_str   CLOB;
    v_by    CLOB; --order by 
    v_str1  CLOB; --distinct 动态行转列的值语句
    v_sql   CLOB;
    v_data  CLOB;
    v_str2  CLOB;
    v_str3  CLOB;
    v_str4  CLOB;
    v_str5  CLOB;
    v_count NUMBER;
  
    v_where CLOB;
  
    j_object json_object_t;
    j_array  json_array_t := NEW json_array_t;
  BEGIN
  
    IF sql_where IS NULL THEN
      v_where := ' where 1 = 1';
    ELSE
      v_where := sql_where;
    END IF;
  
    v_str := 'SELECT ' || columns_name;
    IF p_str1 IS NOT NULL THEN
      --动态获取行转列结果的列名
    
      v_str1 := 'select  ' || p_str1;
      IF p_order_by IS NOT NULL THEN
        v_str1 := v_str1 || ',MAX(' || p_order_by || ') as ' || p_order_by;
      END IF;
      v_str1 := v_str1 || ' FROM  ' || sql_from || ' ' || v_where || ' and ' || p_str1 ||
                ' is not null 
               GROUP BY ' || p_str1;
    
      IF p_order_by IS NOT NULL THEN
        v_by := p_order_by;
      ELSE
        v_by := p_str1;
      
        IF INSTR(lower(p_str1), 'as', 1, 1) > 0 THEN
          v_by := SUBSTR(p_str1, INSTR(lower(p_str1), 'as', 1, 1) + 3, LENGTH(p_str1));
        END IF;
      END IF;
    
      /* DBMS_OUTPUT.PUT_LINE(v_str1);
      DBMS_OUTPUT.PUT_LINE('-------------');
      DBMS_OUTPUT.PUT_LINE(v_by);*/
    
      v_data := utils_pkg.tableToJson(v_str1, 'order by ' || v_by);
      /*dbMS_OUTPUT.PUT_LINE('v_data');
      dbms_output.put_line(v_data);*/
    
      --找不到行转列的key，跳出
      v_str5 := 'SELECT SUM(CASE
                 WHEN ' || lower(p_str1) || ' IS NULL THEN
                  0
                 ELSE
                  1
               END)
    FROM json_table(:1, ''$[*]'' columns(' || lower(p_str1) || '))';
    
      --DBMS_OUTPUT.PUT_LINE(v_str5);
      EXECUTE IMMEDIATE v_str5
        INTO v_count
        USING v_data;
    
      --如果没有值，返回空值
      IF v_count <= 0 OR v_count IS NULL THEN
        v_data := '[]';
        RETURN v_data;
      END IF;
    
      --循环遍历拼接sql语句
      j_array := NEW json_array_t(v_data);
      FOR i IN 0 .. j_array.get_size - 1 LOOP
        j_object := json_object_t(j_array.get(i));
      
        --截取字段名
        IF INSTR(lower(p_str1), 'as', 1, 1) > 0 THEN
          v_str2 := SUBSTR(p_str1, INSTR(lower(p_str1), 'as', 1, 1) + 3, LENGTH(p_str1));
        
          v_str3 := SUBSTR(p_str1, 1, INSTR(lower(p_str1), 'as', 1, 1) - 1);
        
        ELSE
        
          v_str2 := SUBSTR(p_str1, INSTR(p_str1, '.', 1, 1) + 1, LENGTH(p_str1));
          v_str3 := v_str2;
        END IF;
      
        --获取字段名的值
        v_sql := JSON_VALUE(j_object.to_clob, '$.' || lower(v_str2) || '');
        --dbms_output.put_line(v_sql);
        v_str4 := v_str4 || '''' || LOWER(v_sql) || ''' value  "' || v_sql || '"  FORMAT JSON,';
      
        v_str := v_str || ',' || p_type || '(DECODE(' || v_str3 || ',''' || v_sql || ''',' || p_str2 ||
                 ')) as "' || v_sql || '"';
      
      END LOOP;
    END IF;
    --造sql语句
  
    v_str := v_str || ' FROM ' || sql_from || ' ' || v_where || ' ' || 'GROUP BY ' || columns_name;
  
    IF P_json <> 'Y' THEN
      v_str4 := REPLACE(v_str4, 'FORMAT JSON', '');
    END IF;
  
    v_str := 'SELECT JSON_ARRAYAGG(JSON_OBJECT(' || columns_name || ',' ||
             SUBSTR(v_str4, 1, LENGTH(v_str4) - 1) || ' RETURNING CLOB ) ORDER BY ' || columns_name ||
             ' RETURNING CLOB) as str FROM (' || v_str || ')';
  
    --DBMS_OUTPUT.PUT_LINE('*****************');
    --DBMS_OUTPUT.PUT_LINE(v_str);
    EXECUTE IMMEDIATE v_str
      INTO v_data;
    --DBMS_OUTPUT.PUT_LINE(v_data); 
    IF v_data IS NULL THEN
      v_data := '[]';
    END IF;
  
    RETURN(v_data);
  
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END buildPivot;

3.执行语句

Declare
v_output  clob;
BEGIN
  
    v_output := buildPivot('group  by 名称',
                                     ' (SELECT * FROM DUAL) A/表名',
                                     'WHERER 1= 1',
                                     '动态行转列的列名称',
                                     '态行转列的列值的名称',
                                     'sql_from语句字段是否有json对象和数组');
    DBMS_OUTPUT.PUT_LINE(v_output);
END;