关于oracle行列转换

 最近公司项目涉及到统计报表，有关交叉报表部分差不多都有行列转换需求，根据个人开发中的需求，总结了一些例子

 

1.创建表

create table T1
(
  PRODUCTID VARCHAR2(100),
  NUM       NUMBER
)
;

2.插入测试数据

insert into T1 (PRODUCTID, NUM)
values ('1', 3);
insert into T1 (PRODUCTID, NUM)
values ('2', 12);
insert into T1 (PRODUCTID, NUM)
values ('3', 24);
insert into T1 (PRODUCTID, NUM)
values ('4', 11);
insert into T1 (PRODUCTID, NUM)
values ('5', 51);
insert into T1 (PRODUCTID, NUM)
values ('6', 511);
commit;

3.编写转换函数

CREATE OR REPLACE FUNCTION fn_rs_1
    RETURN pkg_getrecord.myrctype
IS
    s VARCHAR2 (4000);
    CURSOR c1 IS
    SELECT ',sum(case when productid!='|| productid || ' then num else 0 end)' || ' "产品' || productid|| '"' c2
    FROM t1
    group by productid;
    r1 c1%ROWTYPE;
    list_cursor pkg_getrecord.myrctype;
BEGIN
    s := 'select  1 ';
    OPEN c1;
    LOOP
        FETCH c1 INTO r1;
        EXIT WHEN c1%NOTFOUND;
        s := s || r1.c2;
    END LOOP;
    CLOSE c1;
    s := s || ' from t1 g ';
    dbms_output.put_line(s);
    OPEN list_cursor FOR s;
    RETURN list_cursor;
END fn_rs_1;

 

4.java测试代码

public class Test {
 private String db_url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
 private String db_username = "test";
 private String db_passwd = "test";

 /**
  * @param args
  */
 public static void main(String[] args) {
  Test test=new Test();
  try {
   test.exec();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }

 public void exec() throws SQLException {
  Connection conn = getConnection();
  if(conn==null){
   throw new SQLException("database not connectioned");
  }
  CallableStatement cstmt = conn.prepareCall("{?=call fn_rs_1}");
  cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
  cstmt.execute();
  ResultSet rs=(ResultSet)cstmt.getObject(1);
  if(rs.next()){
   for(int j=1;j<=rs.getMetaData().getColumnCount();j++){
    System.out.print(rs.getMetaData().getColumnName(j)+"/t");
   }
  }
  System.out.println("");
  cstmt.execute();
  rs=(ResultSet)cstmt.getObject(1);
  while(rs.next()){
   System.out.print(rs.getString(1)+"/t");
   System.out.print(rs.getString(2)+"/t");
   System.out.print(rs.getString(3)+"/t");
   System.out.print(rs.getString(4)+"/t");
   System.out.print(rs.getString(5)+"/t");
   System.out.print(rs.getString(6)+"/t");
   System.out.print(rs.getString(7)+"/t");
  }
  cstmt.close();
 }

 protected Connection getConnection() {
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   return DriverManager.getConnection(db_url, db_username, db_passwd);
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return null;
 }

}