Oracle 动态行转列函数

1.函数描述

	使用函数实现动态行的值转换为列名的查询语句。

2.函数参数定义

columns_name: 返回查询的列名
sql_from: from语句
sql_where: where语句
p_str1: 动态行转列的列名称
p_str2:动态行转列的列值

3.函数

CREATE OR REPLACE FUNCTION buildPivot
(
  columns_name IN CLOB,
  sql_from     IN CLOB,
  sql_where    IN CLOB,
  p_str1       IN CLOB,
  p_str2       IN CLOB
) RETURN CLOB IS
  v_str  CLOB;
  v_sql  CLOB;
  v_data CLOB;

  j_object json_object_t;
  j_array  json_array_t := NEW json_array_t;
  
BEGIN
  v_str  := 'WITH TMP_A AS (select DISTINCT ' || p_str1 || ' ' || sql_from||') SELECT JSON_ARRAYAGG(JSON_OBJECT(* RETURNING CLOB)RETURNING CLOB) FROM TMP_A';
  EXECUTE IMMEDIATE v_str  into v_data; 
  --dbms_output.put_line(v_data);
  v_str   := 'SELECT ' || columns_name;
  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));
    v_sql := JSON_VALUE(j_object.to_clob,
                        '$.' || UPPER(SUBSTR(p_str1,
                                       INSTR(p_str1, '.', 1, 1) + 1,
                                       LENGTH(p_str1)) || ''));
    --dbms_output.put_line(v_sql);
    v_str := v_str || ',DECODE(' || p_str1 || ',''' || v_sql || ''',' ||
             p_str2 || ') as "' || v_sql || '"';
  END LOOP;
  v_str := v_str || ' ' || sql_from || ' ' || sql_where;
  dbms_output.put_line(v_str);
  RETURN(v_str);
END buildPivot;

4.调用方法

 DECLARE
  v_output CLOB;
  BEGIN
    SELECT buildPivot('id','FROM dual','WHERE id > 2','name','scope')
  INTO v_output
  FROM DUAL;
  dbms_output.put_line(v_output);
  END;