Oracle動態交叉表產生

正在看的ORACLE教程是:Oracle動態交叉表產生。Oracle是應用最廣的大型資料庫，而在範式下進行Oracle資料庫設計則可以大大減少資料冗餘，使資料庫維護更方便，可惜範式下的資料表一般不能直接輸出。今天我們就來探討一下範式下的資料表的動態交叉表產生的方法。

　　範式下的Oracle資料庫設計

　　資料關係的複雜性導致了表中資料冗餘的存在，資料冗餘增加了維護資料庫的負擔，也佔用了大量的磁碟空間,直接造成效能下降。為了消除這些負面影響，就應該對資料庫表格進行正常化，使其遵守一定的規則的，尤其是資料庫設計範式。

　　關係必須是正常化的，簡單說來，就是在結構表設計時，消除冗餘性和不協調的從屬關係。即每一個分量必須是不可分的資料項目，但是這隻是最基本的正常化。正常化理論就是研究如何將一個不好的關係模式轉化為好的關係模式的理論，正常化理論是圍繞範式而建立的。正常化理論認為，一個關聯式資料庫中所有的關係，都應滿足一定的規範(約束條件)。正常化理論把關係應滿足的規範要求分為幾級，滿足最低要求的一級叫做第一範式(1NF)，在第一範式的基礎上提出了第二範式(2NF)，在第二範式的基礎上又提出了第三範式(3NF)，以後又提出了BCNF範式，4NF，5NF，以及“域/關鍵字”範式。範式的等級越高，應滿足的約束集條件也越嚴格。規範的每一層級都依賴於它的前一層級，例如若一個關係模式滿足2NF，則一定滿足1NF。

　　在Oracle上設計資料庫時更要符合範式的要求，如果把一個不符合規範的資料庫放在Oracle中，是不會突出Oracle的效能的，甚至是非常糟糕。

　　例如：學生的成績表，我們一般都要求列印一目瞭然。

　　這也是符合1NF的，但如果是在資料庫中定義的表結構也這樣，則是不完善的，是有潛在衝突的。如要增加考試科目，就得更改表結構，特別是大學，專業多、科目多，而有些科目是選學的，這將會使表結構變得相當複雜，有多少科目就得有多少個科目的欄位，有部分欄位值必然為空白；這個表是指某次測驗的還是期中或期末考試的成績呢？分辨不出，於是每一次成績都要造一張類似的表，必然表格較多。不僅浪費大量的磁碟空間，還會給程式的編寫帶來極大的困難。

　　在資料範式理論的指導下，對資料庫表格進行正常化，使其結構更合理，消除儲存異常，使資料冗餘盡量最小，便於插入、刪除和更新，進一步保持了資料的完整性。經過探索，我在成績管理系統的設計上採用了如下的表結構，這個表結構能以不變應用多變，不管是科目的增加，還是教師的變動，都能適應，符合資料的規範要求。　

由此看出，經資料正常化的資料雖然使資料冗餘小，便於插入、刪除和更新，但如果直接輸出是不符合人們觀看習慣的，必需要把其輸出為上面表1的格式才行，這就是列向表產生橫向表的問題，即交叉表的產生。

動態交叉表的產生

　　為了簡述起見，在學生基本資料表中，只建兩個欄位，學號、姓名，其他的諸如性別、科代碼等則略。其中班、教師程式碼程式庫、考試次數標誌（即第幾次測驗，還是期中、期末考試）等也略，只保留下面資料結構足以能說明交叉表產生的過程。

　　各表結構簡化如下：

　　學生基本資料表：JBXX

　　xh char(13) //學號

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

　　科目代碼錶：KMDM

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

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

　　成績表： CJ

　　xh char(13) //學號，關聯JBXX的XH。

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

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

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

　　至此，資料表結構已全部建好，此時的任務是把下面表3的資料進行產生交叉表，表4。
　　
　　交叉表的產生，在Oracle中可以用SQL語句實現。

　　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 <班級或專業條件> order by jbxx.xh

　　Java語言有“編寫一次，隨處運行”的跨平台能力，具有強大的網路能力。Oracle是一種關係型的大型資料庫，可在多種硬體平台上運行，支援多種作業系統，支援大資料庫、多使用者的高效能的交易處理，以其強大的功能和穩定性而著稱。因此建議用Java結合Oracle編寫程式。下面給出在Java語言中的具體實現過程。

　　註：為了簡述方便，下面的程式已簡略，在實踐應用中，還要考慮很多問題，並且一般把它做成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";以後實際使用要加上班或級或專業條件.
　　 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.get

[1] [2] 下一頁

正在看的ORACLE教程是:Oracle動態交叉表產生。String(1);//統計該班該科目的總成線。
　　 ResultSet rs_sum = stmt1.executeQuery(sql_sum);
　　 rs_sum.next();
　　 //統計符合班級條件的成績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)+" ,";
　　//構造動態語句.
　　 }
　　 rs_sum.close();
　　 } //擷取動態科目及名稱
　　 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(); //擷取所有曾產生的欄位，實行動態輸出。
　　 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("資料已列印完成！");
　　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去使用，還可加入學期、班級的動態變數，即可獲得全動態的資料了。

上一頁 [1] [2]