动态交叉表

 PROCEDURE cross_table_test(DATACUR OUT SYS_REFCURSOR,L_QUERY_OUT OUT LONG) IS
  
   
    L_QUERY LONG;
    L_DYNAMIC_COLUMN  LONG;
    NUM_MAX_YEAR NUMBER;
    NUM_MIN_YEAR NUMBER;
  BEGIN
  
    SELECT MAX(NUM_YEAR) INTO NUM_MAX_YEAR FROM CROSS_TABLE_TEST;
  
    SELECT MIN(NUM_YEAR) INTO NUM_MIN_YEAR FROM CROSS_TABLE_TEST;
    --DBMS_OUTPUT.put_line(NUM_MAX_YEAR);
    --DBMS_OUTPUT.put_line(NUM_MIN_YEAR);
    
    /*
    动态构造交叉表
    */
    FOR i in NUM_MIN_YEAR..NUM_MAX_YEAR
    LOOP
      --DBMS_OUTPUT.put_line(i);
      L_DYNAMIC_COLUMN := L_DYNAMIC_COLUMN || ' SUM(DECODE(NUM_YEAR, ' || i ||  ' , NUM_COST, 0)) ' || 'AS cost_'|| i || ',' ;
      
      IF i = NUM_MAX_YEAR THEN 
           L_DYNAMIC_COLUMN := L_DYNAMIC_COLUMN || ' SUM(DECODE(NUM_YEAR, ' || i ||  ' , NUM_SALES, 0)) ' || 'AS sales_'|| i /*|| ','*/;              
      ELSE
           L_DYNAMIC_COLUMN := L_DYNAMIC_COLUMN || ' SUM(DECODE(NUM_YEAR, ' || i ||  ' , NUM_SALES, 0)) ' || 'AS sales_'|| i || ','; 
      END IF;
    END LOOP;
  
   L_QUERY := ' SELECT ID_PART, TXT_PART, ';
  
   L_QUERY := L_QUERY || ' SUM(NUM_COST) AS TOT_COST , SUM(NUM_SALES) AS TOT_SALES ,';
   L_QUERY :=  L_QUERY || L_DYNAMIC_COLUMN ;
   L_QUERY := L_QUERY || '  FROM CROSS_TABLE_TEST GROUP BY ID_PART, TXT_PART' ;
   
   /*
   输出动态SQL ,调试用
   */
   L_QUERY_OUT := L_QUERY; 
    /*
    非动态的交叉表
    OPEN DATACUR FOR
      SELECT ID_PART,
              TXT_PART,
              SUM(DECODE(NUM_YEAR, 2004, NUM_COST, 0)) AS cost_2004,
              SUM(DECODE(NUM_YEAR, 2004, NUM_SALES, 0)) AS sales_2004
         FROM CROSS_TABLE_TEST
        GROUP BY ID_PART, TXT_PART;
     */
  
  OPEN DATACUR FOR L_QUERY;
  
  
  END cross_table_test;

 

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;


public class Test {

	/**
	 * @param args
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();  
		String url="jdbc:oracle:thin:@3.242.164.94:1521:xxxx"; //xxxx为数据库的SID  
		String user="xxxx";  
		String password="xxxx";  
	    Connection conn= DriverManager.getConnection(url,user,password);
	    OracleCallableStatement proc = (OracleCallableStatement)conn.prepareCall("{call cross_table_test(?,?)}");
        proc.registerOutParameter(1, OracleTypes.CURSOR);
        proc.registerOutParameter(2, OracleTypes.LONGVARCHAR);
        
       
        proc.execute();
        ResultSet rs = (ResultSet)proc.getObject(1);
        String str = proc.getObject(2).toString();
        //System.out.println(str);
        
        ResultSetMetaData rsMetaData = rs.getMetaData();
        int intColumn = rsMetaData.getColumnCount();
        
        for (int i =1;i<=intColumn;i++){
        	String strColumn =rsMetaData.getColumnName(i);
        	System.out.print(  (strColumn.length()>7?strColumn.substring(0,7):strColumn) +"\t\t");
    	}
        System.out.println();
        
        while(rs.next()) {
        	for (int i =1;i<=intColumn;i++){
        		System.out.print(rs.getString(i)+"\t\t");
        	}
        	System.out.println();
        }

	}

}