CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS
TYPE refc IS REF CURSOR;
PROCEDURE p_print_sql(p_txt VARCHAR2);
FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2;
PROCEDURE p_rows_column(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc);
PROCEDURE p_rows_column_real(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc);
PROCEDURE p_rows_column_grouping(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_group IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc);
END;
/
CREATE OR REPLACE PACKAGE BODY PKG_DYNAMIC_ROWS_COLUMN AS
--行列转包的内容
---====================================================================================
--存储过程:p_print_sql 打印语句(is 和 as 没有区别)打印输入的SQL语句
PROCEDURE P_PRINT_SQL(P_TXT VARCHAR2) IS
V_LEN INT;
BEGIN
V_LEN := LENGTH(P_TXT); --设置长度为我们输入的p_txt的长度
FOR I IN 1 .. V_LEN / 250 + 1 LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTRB(P_TXT, (I - 1) * 250 + 1, 250));
END LOOP;
END;
---=========================================================================================
--函数:f_split_str 用某些字符在固定的几个数据以后进行分隔开(截取P_DIVISION前一个与当前查询夹的字符串)
-- P_STR :要进行查找的原始字符串
-- P_DIVISION :要查找的字符串
-- P_SEQ : 查找几次要查找的字符串
FUNCTION F_SPLIT_STR(P_STR VARCHAR2, P_DIVISION VARCHAR2, P_SEQ INT)
RETURN VARCHAR2 IS
V_FIRST INT;
V_LAST INT;
BEGIN
--如果组数量小于1,那么返回null
IF P_SEQ < 1 THEN
RETURN NULL;
END IF;
--如果组数量等于1
IF P_SEQ = 1 THEN
/*
insert函数 从p_str字符串中找到p_divsion字符的位置,
开始查找的位置为1(默认为1),结束的位置p_seq,如果没有找到那么返回0
*/
IF INSTR(P_STR, P_DIVISION, 1, P_SEQ) = 0 THEN
RETURN P_STR;
ELSE
--如果查找到,那么进行截取
RETURN SUBSTR(P_STR, 1, INSTR(P_STR, P_DIVISION, 1) - 1);
END IF;
ELSE
--如果p_seq 大于1
V_FIRST := INSTR(P_STR, P_DIVISION, 1, P_SEQ - 1);
V_LAST := INSTR(P_STR, P_DIVISION, 1, P_SEQ);
IF (V_LAST = 0) THEN
IF (V_FIRST > 0) THEN
RETURN SUBSTR(P_STR, V_FIRST + 1);
ELSE
RETURN NULL;
END IF;
ELSE
RETURN SUBSTR(P_STR, V_FIRST + 1, V_LAST - V_FIRST - 1);
END IF;
END IF;
END F_SPLIT_STR;
----===================================执行的结果是转换为列名和列值两列==============================================
--存储过程:P_ROWS_COLUMN
PROCEDURE P_ROWS_COLUMN(P_TABLE IN VARCHAR2,--表
P_KEEP_COLS IN VARCHAR2,--不变的列
P_PIVOT_COLS IN VARCHAR2,--以某个列进行旋转
P_WHERE IN VARCHAR2 DEFAULT NULL,--条件
P_REFC IN OUT REFC) IS--返回值
V_SQL VARCHAR2(4000);
--声明一种表类型数组
TYPE V_KEEP_IND_BY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
--固定列数组(表)
V_KEEP V_KEEP_IND_BY;
--定义一种旋转表类型数组
TYPE V_PIVOT_IND_BY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
--旋转列数组(表)
V_PIVOT V_PIVOT_IND_BY;
V_KEEP_CNT INT;---固定列的个数
V_PIVOT_CNT INT;---旋转列的个数
V_MAX_COLS INT;---固定字段重复的最大列数
V_PARTITION VARCHAR2(4000);---最终的SQL语句
V_PARTITION1 VARCHAR2(4000);---固定字段拼接的SQL
V_PARTITION2 VARCHAR2(4000);---旋转字段拼接的SQL
BEGIN
-- 统计固定列的逗号个数,(用来统计一共有多少个固定列)
V_KEEP_CNT := LENGTH(P_KEEP_COLS) - LENGTH(REPLACE(P_KEEP_COLS, ',')) + 1;
-- 统计旋转列的逗号个数,(用来统计一共有多少个旋转列)
V_PIVOT_CNT := LENGTH(P_PIVOT_COLS) - LENGTH(REPLACE(P_PIVOT_COLS, ',')) + 1;
---取得固定的列
FOR I IN 1 .. V_KEEP_CNT LOOP
V_KEEP(I) := F_SPLIT_STR(P_KEEP_COLS, ',', I);
END LOOP;
---取得要旋转的列
FOR J IN 1 .. V_PIVOT_CNT LOOP
V_PIVOT(J) := F_SPLIT_STR(P_PIVOT_COLS, ',', J);
END LOOP;
---拼接SQL语句查询最大的记录重复合计
V_SQL := 'select max(count(*)) from ' || P_TABLE || ' group by ';
FOR I IN 1 .. V_KEEP.LAST LOOP
V_SQL := V_SQL || V_KEEP(I) || ',';
END LOOP;
V_SQL := RTRIM(V_SQL, ',');--去掉最后一个逗号
--==============================执行SQL得到结果
EXECUTE IMMEDIATE V_SQL
INTO V_MAX_COLS;
---===========================
V_PARTITION := 'select ';
----拼接固定字段
FOR X IN 1 .. V_KEEP.COUNT LOOP
V_PARTITION1 := V_PARTITION1 || V_KEEP(X) || ',';
END LOOP;
----拼接翻转字段
FOR Y IN 1 .. V_PIVOT.COUNT LOOP
V_PARTITION2 := V_PARTITION2 || V_PIVOT(Y) || ',';
END LOOP;
---去掉多余的逗号
V_PARTITION1 := RTRIM(V_PARTITION1, ',');
V_PARTITION2 := RTRIM(V_PARTITION2, ',');
---添加合计分组函数
V_PARTITION := V_PARTITION || V_PARTITION1 || ',' || V_PARTITION2 ||
', row_number() over (partition by ' || V_PARTITION1 ||
' order by ' || V_PARTITION2 || ') rn from ' || P_TABLE;
V_PARTITION := RTRIM(V_PARTITION, ',');
---最终执行SQL语句
V_SQL := 'select ';
FOR I IN 1 .. V_KEEP.COUNT LOOP
V_SQL := V_SQL || V_KEEP(I) || ',';
END LOOP;
FOR I IN 1 .. V_MAX_COLS LOOP
FOR J IN 1 .. V_PIVOT.COUNT LOOP
V_SQL := V_SQL || ' max(decode(rn,' || I || ',' || V_PIVOT(J) ||
',null))' || V_PIVOT(J) || '_' || I || ',';
END LOOP;
END LOOP;
---添加查询WHERE条件
IF P_WHERE IS NOT NULL THEN
V_SQL := RTRIM(V_SQL, ',') || ' from (' || V_PARTITION || ' ' ||
P_WHERE || ') group by ';
ELSE
V_SQL := RTRIM(V_SQL, ',') || ' from (' || V_PARTITION ||
') group by ';
END IF;
---添加分组字段
FOR I IN 1 .. V_KEEP.COUNT LOOP
V_SQL := V_SQL || V_KEEP(I) || ',';
END LOOP;
V_SQL := RTRIM(V_SQL, ',');
---输出SQL语句
P_PRINT_SQL(V_SQL);
---执行SQL语句并返回值
OPEN P_REFC FOR V_SQL;
---捕捉异常信息
EXCEPTION
WHEN OTHERS THEN
OPEN P_REFC FOR
SELECT 'x' FROM DUAL WHERE 0 = 1;
END;
-----=================================执行结果是列名对应下边的列值=========================================
PROCEDURE P_ROWS_COLUMN_REAL(P_TABLE IN VARCHAR2,--表名称
P_KEEP_COLS IN VARCHAR2,--查询的固定的列
P_PIVOT_COL IN VARCHAR2,--要转换为行标题的列
P_PIVOT_VAL IN VARCHAR2,--要转换为行值的列
P_WHERE IN VARCHAR2 DEFAULT NULL,--查询的限制条件
P_REFC IN OUT REFC) IS--返回值
V_SQL VARCHAR2(4000);--拼接的执行的SQL语句变量
TYPE V_KEEP_IND_BY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
V_KEEP V_KEEP_IND_BY;--固定的列的数组
TYPE V_PIVOT_IND_BY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
V_PIVOT V_PIVOT_IND_BY;--转换的列的数组
V_KEEP_CNT INT;--固定的列的字段数
V_GROUP_BY VARCHAR2(2000);--新的固定字段的字符串.
BEGIN
--固定字段的个数
V_KEEP_CNT := LENGTH(P_KEEP_COLS) - LENGTH(REPLACE(P_KEEP_COLS, ',')) + 1;
--拆分固定字段
FOR I IN 1 .. V_KEEP_CNT LOOP
V_KEEP(I) := F_SPLIT_STR(P_KEEP_COLS, ',', I);
END LOOP;
---拼接查询的要转换为列名的SQL语句
V_SQL := 'select ' || 'cast(' || P_PIVOT_COL ||
' as varchar2(200)) as ' || P_PIVOT_COL || ' from ' || P_TABLE ||
' group by ' || P_PIVOT_COL;
--列名封装到数组中.
EXECUTE IMMEDIATE V_SQL BULK COLLECT
INTO V_PIVOT;
--拼接固定字段到字符串
FOR I IN 1 .. V_KEEP.COUNT LOOP
V_GROUP_BY := V_GROUP_BY || V_KEEP(I) || ',';
END LOOP;
V_GROUP_BY := RTRIM(V_GROUP_BY, ',');
V_SQL := 'select ' || V_GROUP_BY || ',';
--组装转换为列的字段
FOR X IN 1 .. V_PIVOT.COUNT LOOP
V_SQL := V_SQL || ' NVL(max(decode(' || P_PIVOT_COL || ',' || CHR(39) ||
V_PIVOT(X) || CHR(39) || ',' || P_PIVOT_VAL ||
',null)),0) as "' || V_PIVOT(X) || '",';
END LOOP;
V_SQL := RTRIM(V_SQL, ',');
---添加where条件
IF P_WHERE IS NOT NULL THEN
V_SQL := V_SQL || ' from ' || P_TABLE || P_WHERE || ' group by ' ||
V_GROUP_BY;
ELSE
V_SQL := V_SQL || ' from ' || P_TABLE || ' group by ' || V_GROUP_BY;
END IF;
---输出SQL语句
P_PRINT_SQL(V_SQL);
---执行SQL语句
OPEN P_REFC FOR V_SQL;
--异常处理
EXCEPTION
WHEN OTHERS THEN
OPEN P_REFC FOR
SELECT 'x' FROM DUAL WHERE 0 = 1;
END;
-----================================执行结果是列名下面列值,但进行了统计分组==========================================
PROCEDURE P_ROWS_COLUMN_GROUPING(P_TABLE IN VARCHAR2,--表名称
P_KEEP_COLS IN VARCHAR2,--查询的固定的列
P_PIVOT_COL IN VARCHAR2,--要转换为行标题的列
P_PIVOT_VAL IN VARCHAR2,--要转换为行值的列
P_WHERE IN VARCHAR2 DEFAULT NULL,--查询的限制条件
P_GROUP IN VARCHAR2 DEFAULT NULL,--分组变量.
P_REFC IN OUT REFC) IS--返回值
V_SQL VARCHAR2(4000);--拼接的执行的SQL语句变量
TYPE V_KEEP_IND_BY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
V_KEEP V_KEEP_IND_BY;--固定的列的数组
TYPE V_PIVOT_IND_BY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
V_PIVOT V_PIVOT_IND_BY;--转换的列的数组
V_KEEP_CNT INT;--固定的列的字段数
V_GROUP_BY VARCHAR2(2000);--新的固定字段的字符串.
BEGIN
--固定字段的个数
V_KEEP_CNT := LENGTH(P_KEEP_COLS) - LENGTH(REPLACE(P_KEEP_COLS, ',')) + 1;
--拆分固定字段
FOR I IN 1 .. V_KEEP_CNT LOOP
V_KEEP(I) := F_SPLIT_STR(P_KEEP_COLS, ',', I);
END LOOP;
---拼接查询的要转换为列名的SQL语句
V_SQL := 'select ' || 'cast(' || P_PIVOT_COL ||
' as varchar2(200)) as ' || P_PIVOT_COL || ' from ' || P_TABLE ||
' group by ' || P_PIVOT_COL;
--列名封装到数组中.
EXECUTE IMMEDIATE V_SQL BULK COLLECT
INTO V_PIVOT;
--拼接固定字段到字符串
FOR I IN 1 .. V_KEEP.COUNT LOOP
V_GROUP_BY := V_GROUP_BY || V_KEEP(I) || ',';
END LOOP;
V_GROUP_BY := RTRIM(V_GROUP_BY, ',');
V_SQL := 'select ' || V_GROUP_BY || ',';
--组装转换为列的字段
FOR X IN 1 .. V_PIVOT.COUNT LOOP
V_SQL := V_SQL || ' NVL(SUM(decode(' || P_PIVOT_COL || ',' || CHR(39) ||
V_PIVOT(X) || CHR(39) || ',' || P_PIVOT_VAL ||
',null)),0) as "'|| V_PIVOT(X) || '",';
END LOOP;
V_SQL := RTRIM(V_SQL, ',');
---添加where条件
IF P_WHERE IS NOT NULL THEN
V_SQL := V_SQL || ' from ' || P_TABLE || P_WHERE ;
ELSE
V_SQL := V_SQL || ' from ' || P_TABLE ;
END IF;
---添加group函数
IF P_GROUP IS NOT NULL THEN
V_SQL := V_SQL || ' group by grouping sets (' || P_GROUP || ')';
ELSE
V_SQL := V_SQL || ' group by ' || V_GROUP_BY;
END IF;
---输出SQL语句
P_PRINT_SQL(V_SQL);
---执行SQL语句
OPEN P_REFC FOR V_SQL;
--异常处理
EXCEPTION
WHEN OTHERS THEN
OPEN P_REFC FOR
SELECT 'x' FROM DUAL WHERE 0 = 1;
END;
END;
/
======================存储过程的执行=============
/*
在SQLplus中的执行语句
SET SERVEROUTPUT ON;
declare
tt pkg_dynamic_rows_column.refc ;
begin
pkg_dynamic_rows_column.p_rows_column_real('VI_GYS_PERFEE','P_NAME, P_CERT, COM_NAME','PARAVALUE','FEE_TOTAL',null,tt);
end;
*/
===========================在JAVA中执行的函数=====================
public void testPro_out() {
try {
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//IP:1521/orcl", "user", "psd");
CallableStatement state = conn.prepareCall("{call pkg_dynamic_rows_column.p_rows_column_grouping(?,?,?,?,?,?,?)}");
state.setString(1, "VI_GYS_PERFEE");
state.setString(2, " COM_NAME ,NVL(P_NAME,'合计') , NVL(P_CERT,'合计') ");
state.setString(3, "PARAVALUE");
state.setString(4, "FEE_TOTAL");
state.setString(5, null);
state.setString(6, "(COM_NAME ,P_NAME , P_CERT),(COM_NAME),()");
state.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
state.execute();
ResultSet rs = (ResultSet)state.getObject(7);
//取得列名
ResultSetMetaData metaData = rs.getMetaData();
int cols = metaData.getColumnCount();
String name = "";
for (int i = 0; i < cols; i++) {
name += metaData.getColumnName(i + 1).toLowerCase()+">>";
}
System.out.println(name);
//取得数据
while(rs.next()) {
String value = "";
for (int i = 0; i < cols; i++) {
value += rs.getString(i+1)+">>";
}
System.out.println(value);
}
rs.close();
if (conn != null) {
try {
conn.close();
conn = null;
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
static {
try {
// Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}