Oracle動態(tài)交叉表生成

2010-08-28 10:48:44來源:西部e網(wǎng)作者:

    Oracle是應用最廣的大型數(shù)據(jù)庫,而在范式下進行Oracle數(shù)據(jù)庫設計則可以大大減少數(shù)據(jù)冗余,使數(shù)據(jù)庫維護更方便,可惜范式下的數(shù)據(jù)表一般不能直接輸出。今天我們就來探討一下范式下的數(shù)據(jù)表的動態(tài)交叉表生成的方法。

  范式下的Oracle數(shù)據(jù)庫設計

  數(shù)據(jù)關系的復雜性導致了表中數(shù)據(jù)冗余的存在,數(shù)據(jù)冗余增加了維護數(shù)據(jù)庫的負擔,也占用了大量的磁盤空間,直接造成性能下降。為了消除這些負面影響,就應該對數(shù)據(jù)庫表格進行規(guī)范化,使其遵守一定的規(guī)則的,尤其是數(shù)據(jù)庫設計范式。

  關系必須是規(guī)范化的,簡單說來,就是在結(jié)構(gòu)表設計時,消除冗余性和不協(xié)調(diào)的從屬關系。即每一個分量必須是不可分的數(shù)據(jù)項,但是這只是最基本的規(guī)范化。規(guī)范化理論就是研究如何將一個不好的關系模式轉(zhuǎn)化為好的關系模式的理論,規(guī)范化理論是圍繞范式而建立的。規(guī)范化理論認為,一個關系數(shù)據(jù)庫中所有的關系,都應滿足一定的規(guī)范(約束條件)。規(guī)范化理論把關系應滿足的規(guī)范要求分為幾級,滿足最低要求的一級叫做第一范式(1NF),在第一范式的基礎上提出了第二范式(2NF),在第二范式的基礎上又提出了第三范式(3NF),以后又提出了BCNF范式,4NF,5NF,以及“域/關鍵字”范式。范式的等級越高,應滿足的約束集條件也越嚴格。規(guī)范的每一級別都依賴于它的前一級別,例如若一個關系模式滿足2NF,則一定滿足1NF。

  在Oracle上設計數(shù)據(jù)庫時更要符合范式的要求,如果把一個不符合規(guī)范的數(shù)據(jù)庫放在Oracle中,是不會突出Oracle的性能的,甚至是非常糟糕。

  例如:學生的成績表,我們一般都要求打印一目了然。

  這也是符合1NF的,但如果是在數(shù)據(jù)庫中定義的表結(jié)構(gòu)也這樣,則是不完善的,是有潛在沖突的。如要增加考試科目,就得更改表結(jié)構(gòu),特別是大學,專業(yè)多、科目多,而有些科目是選學的,這將會使表結(jié)構(gòu)變得相當復雜,有多少科目就得有多少個科目的字段,有部分字段值必然為空;這個表是指某次測驗的還是期中或期末考試的成績呢?分辨不出,于是每一次成績都要造一張類似的表,必然表格較多。不僅浪費大量的磁盤空間,還會給程序的編寫帶來極大的困難。

  在數(shù)據(jù)范式理論的指導下,對數(shù)據(jù)庫表格進行規(guī)范化,使其結(jié)構(gòu)更合理,消除存儲異常,使數(shù)據(jù)冗余盡量最小,便于插入、刪除和更新,進一步保持了數(shù)據(jù)的完整性。經(jīng)過探索,我在成績管理系統(tǒng)的設計上采用了如下的表結(jié)構(gòu),這個表結(jié)構(gòu)能以不變應用多變,不管是科目的增加,還是教師的變動,都能適應,符合數(shù)據(jù)的規(guī)范要求!

    由此看出,經(jīng)數(shù)據(jù)規(guī)范化的數(shù)據(jù)雖然使數(shù)據(jù)冗余小,便于插入、刪除和更新,但如果直接輸出是不符合人們觀看習慣的,必需要把其輸出為上面表1的格式才行,這就是列向表生成橫向表的問題,即交叉表的生成。

    動態(tài)交叉表的生成

  為了簡述起見,在學生基本信息表中,只建兩個字段,學號、姓名,其他的諸如性別、科代碼等則略。其中班、教師代碼庫、考試次數(shù)標志(即第幾次測驗,還是期中、期末考試)等也略,只保留下面數(shù)據(jù)結(jié)構(gòu)足以能說明交叉表生成的過程。

  各表結(jié)構(gòu)簡化如下:

  學生基本信息表:JBXX

  xh char(13) //學號

  xm char(8) //姓名,針對不同情況,可用變長字符。

  科目代碼表:KMDM

  no  number(3) //科目代號,現(xiàn)可用900多科目可用,若不夠,可定義四位。

  mc varchar(20) //科目中文名稱。

  成績表: CJ

  xh char(13) //學號,關聯(lián)JBXX的XH。

  xq  number(2) //學期,指該學生所在校的學期。

  km  number(3) //科目代號。

  cj  number(3) //該科成績。

  至此,數(shù)據(jù)表結(jié)構(gòu)已全部建好,此時的任務是把下面表3的數(shù)據(jù)進行生成交叉表,表4。
  
  交叉表的生成,在Oracle中可以用SQL語句實現(xiàn)。

  select jbxx.xh,jbxx.xm , (select cj.cj from cj where cj.xh=jbxx.xh and cj.xq=1 and cj.km=1) as km1 , (select cj.cj from cj where cj.xh=jbxx.xh and cj.xq=1 and cj.km=2) as km2 , (select cj.cj from cj where cj.xh=jbxx.xh and cj.xq=1 and cj.km==3) as km3 from jbxx where <班級或?qū)I(yè)條件> order by jbxx.xh

  Java語言有“編寫一次,隨處運行”的跨平臺能力,具有強大的網(wǎng)絡能力。Oracle是一種關系型的大型數(shù)據(jù)庫,可在多種硬件平臺上運行,支持多種操作系統(tǒng),支持大數(shù)據(jù)庫、多用戶的高性能的事務處理,以其強大的功能和穩(wěn)定性而著稱。因此建議用Java結(jié)合Oracle編寫程序。下面給出在Java語言中的具體實現(xiàn)過程。

  注:為了簡述方便,下面的程序已簡略,在實踐應用中,還要考慮很多問題,并且一般把它做成bean來用。

  程序如下:

  import java.sql.*;//導入類庫
  public class sjk{
    public static void main(String[] args)  throws Exception {
  Connection conn;
     try
   {
       Class.forName("oracle.jdbc.driver.OracleDriver");
       String sourceURL="jdbc:oracle:thin:@server:1521:orcl";
       String user="scott";
       String password="tiger";
       conn=DriverManager.getConnection(sourceURL,user,password);
       Statement stmt = conn.createStatement();
       Statement stmt1 = conn.createStatement();
       String sql_km="select no,mc from km";
      // String bb_tj="0441010101";以后實際使用要加上班或級或?qū)I(yè)條件.
       ResultSet rs_km = stmt.executeQuery(sql_km);
       String title="  學號     姓名   ";
       String sql1="( select cj.cj from cj where cj.xh=jbxx.xh and cj.xq=1 and cj.km=";
       String sql=" select jbxx.xh,jbxx.xm ,";
        while (rs_km.next())
        {
         String sql_sum=" select sum(cj) as s1 from cj where "+
            " cj.xq=1 and cj.km="; //在實際使用中要加上班級條件
        sql_sum=sql_sum+rs_km.getString(1);//統(tǒng)計該班該科目的總成線。
        ResultSet rs_sum = stmt1.executeQuery(sql_sum);
        rs_sum.next();
         //統(tǒng)計符合班級條件的成績CJ總和,如果為0則認為該班不開設該科目,略掉。
          if (rs_sum.getInt(1)>0)
          {
                title = title + rs_km.getString(2);
       sql = sql + sql1 + rs_km.getString(1) + ") as km" + rs_km.getString(1)+" ,";
  //構(gòu)造動態(tài)語句.
             }
             rs_sum.close();
           } //獲取動態(tài)科目及名稱
       sql=sql.substring(1,sql.length()-1); //去掉最后一個逗號。
       sql=sql+"from jbxx order by jbxx.xh"; //在實際使用中要加上班級條件
       ResultSet rs=stmt.executeQuery(sql);
    ResultSetMetaData data = rs.getMetaData();
    int col=data.getColumnCount(); //獲取所有曾生成的字段,實行動態(tài)輸出。
    System.out.println(title);
  
       while (rs.next())
       {
         for  (int i=1;i<=col;i++)
         {
       if (i==col)
       System.out.println(rs.getString(i));
       else
        System.out.print(rs.getString(i)+"  ");
         }
       }
  System.out.println("數(shù)據(jù)已打印完成!");
  rs_km.close();
  rs.close();
  stmt1.close();
  stmt.close();
  conn.close();
  
   ///////////////////////////
   }
        catch (Exception  e) {
          System.err.println(e);
   }
  }
  }
  
  以上代碼已在j2sdk1.4.2,Oracle 8.1.7編譯通過,在應用中,一般需要把其做成bean去使用,還可加入學期、班級的動態(tài)變量,即可獲得全動態(tài)的的數(shù)據(jù)了。

關鍵詞:Oracle

贊助商鏈接: