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.getString(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去使用，还可加入学期、班级的动态变量，即可获得全动态的的数据了。