用Oracle写一个动态行转列的存储过程
时间: 2024-05-22 21:17:18 浏览: 93
好的,我明白。关于您的问题,以下是一个简单的示例存储过程,可以将一列中的值转换为行,并将结果存储在一个表中。
CREATE OR REPLACE PROCEDURE dynamic_pivot_procedure
(IN_TABLE_NAME IN VARCHAR2,
IN_ROW_COL_NAME IN VARCHAR2,
IN_COL_COL_NAME IN VARCHAR2,
OUT_TABLE_NAME IN VARCHAR2,
OUT_COL_NAMES IN VARCHAR2)
IS
COL_NAMES_ARRAY DBMS_SQL.VARCHAR2_TABLE;
DYNAMIC_SQL VARCHAR2(4000);
BEGIN
-- Extract column names from input parameter string
SELECT REGEXP_SUBSTR(OUT_COL_NAMES, '[^,]+', 1, LEVEL) BULK COLLECT
INTO COL_NAMES_ARRAY
FROM DUAL
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(OUT_COL_NAMES, '[^,]+')) + 1;
-- Build dynamic SQL statement
DYNAMIC_SQL := 'SELECT DISTINCT ' || IN_ROW_COL_NAME || ',';
FOR I IN 1..COL_NAMES_ARRAY.COUNT LOOP
DYNAMIC_SQL := DYNAMIC_SQL || ' MAX(CASE WHEN ' || IN_COL_COL_NAME || ' = ''' || COL_NAMES_ARRAY(I) || ''' THEN ' || IN_COL_COL_NAME || ' ELSE NULL END) AS ' || COL_NAMES_ARRAY(I) || ',';
END LOOP;
DYNAMIC_SQL := RTRIM(DYNAMIC_SQL, ',') || ' FROM ' || IN_TABLE_NAME || ' GROUP BY ' || IN_ROW_COL_NAME;
-- Execute dynamic SQL statement
EXECUTE IMMEDIATE 'CREATE TABLE ' || OUT_TABLE_NAME || ' AS (' || DYNAMIC_SQL || ')';
END dynamic_pivot_procedure;
/
说明:该存储过程使用动态 SQL 生成一个包含转换后数据的表。您需要提供以下参数:
1. IN_TABLE_NAME:要转换为行的表名。
2. IN_ROW_COL_NAME:要用作每行标识符的列名。
3. IN_COL_COL_NAME:要转换为列的值所在的列名。
4. OUT_TABLE_NAME:用于存储转换后数据的表名。
5. OUT_COL_NAMES:用逗号分隔的转换后表的列名称。
该存储过程基于 Oracle,使用 DBMS_SQL.VARCHAR2_TABLE 和动态 SQL 生成转换后的表。请根据您的实际要求进行修改,以便满足您的具体需求。
阅读全文