Oracle Dynamic Cross Table generation _oracle

The Oracle tutorial that you are looking at is Oracle Dynamic Cross table generation. Oracle is the most widely used large database, and in the paradigm of Oracle database design can greatly reduce data redundancy, so that the database maintenance more convenient, unfortunately, the normal form of the data table can not be directly output. Today we are going to explore the method of generating dynamic crosstab tables for data tables under the paradigm.

Oracle database design under the paradigm

The complexity of data relationship leads to the existence of data redundancy in the table, the data redundancy increases the burden of maintaining the database, and consumes a lot of disk space, which directly results in performance degradation. In order to eliminate these negative effects, we should standardize the database tables so that they can obey certain rules, especially the database design paradigm.

Relationships must be normalized, in short, redundant and uncoordinated dependencies are eliminated in the design of the structure table. That is, each component must be an irreducible data item, but this is only the most basic normalization. The theory of normalization is the theory of how to transform a bad relationship model into a good one, and the normalization theory is built around the paradigm. The theory of normalization holds that all the relationships in a relational database should meet certain criteria (constraints). The normative theory divides the standard requirements that the relationship should meet into a few levels, the first level that satisfies the minimum requirement is called the primary normal form (1NF), the second normal form (2NF) is proposed on the basis of the first normal form, and the third normal form (3NF) is proposed on the basis of the second normal form, and then the BCNF paradigm, 4NF,5NF, and The domain/keyword paradigm. The higher the rank of the paradigm, the stricter the constraint set conditions should be met. Each level of the specification depends on its previous level, for example, if a relational pattern satisfies the 2NF, it must satisfy 1NF.

When designing a database on Oracle, it is better to conform to the paradigm, and if a database that is not compliant is placed in Oracle, it does not highlight Oracle's performance or even worse.

For example: A student's score sheet, we generally require print at a glance.

This is also 1NF compliant, but if the table structure defined in the database is the same, it is imperfect and potentially conflicting. If you want to increase the test subjects, you have to change the table structure, especially the university, professional, many subjects, and some subjects are selected, which will make the table structure is very complex, how many subjects have to have the number of subjects of the field, some of the field value is bound to be empty; This table refers to a test or midterm or final exam results? Can not distinguish, so every time the results are to create a similar table, must be more forms. Not only waste a lot of disk space, but also bring great difficulties to the programming.

Under the guidance of the data paradigm theory, the database table is normalized to make its structure more reasonable, eliminate the storage anomaly, minimize the data redundancy, facilitate inserting, deleting and updating, and keep the data integrality. After exploring, I use the following table structure in the performance management system design, this table structure can change with invariable application, no matter is the increase of the subject, or the change of the teacher, all can adapt, conforms to the data specification request.

From this, the data normalized data, although the data redundancy is small, easy to insert, delete, and update, but if the direct output is not in line with people's viewing habits, it must be output to the table 1 above the format, which is the column to the table to generate a horizontal table problem, that is, the generation of cross tables.

Generation of dynamic cross-table

For the sake of the brief, in the Student basic information table, only constructs two fields, the study number, the name, other such as gender, the section Code and so on is slightly. The class, the teacher code base, the number of test marks (that is, the first test, or midterm, final examination), and so on, only the following data structure is sufficient to explain the process of cross table generation.

The table structure is simplified as follows:

Student Basic information table: JBXX

XH char (13)//School Number

XM char (8)//name, variable length characters are available for different situations.

Account code table: KMDM

No number (3)//Subject code, now available in more than 900 subjects available, if not enough, can be defined four-bit.

MC varchar (20)//Subject Chinese name.

Score Sheet: CJ

XH char (13)//school number, associated Jbxx XH.

XQ Number (2)/term, which is the semester of the student's school.

KM number (3)//Subject code.

CJ Number (3)//The results of the section.

Now that the datasheet structure has been built, the task is to generate a crosstab table of data from table 3 below, table 4.
　　
Cross-table generation, which can be implemented with SQL statements in Oracle.

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) As km3 from JBXX where < class or professional conditions > ORDER by JBXX.XH

The Java language has a "write once, run anywhere" cross-platform capabilities, with strong network capabilities. Oracle is a relational large-scale database, can operate on a variety of hardware platforms, support a variety of operating systems, support large databases, multi-user high-performance transaction processing, with its strong features and stability is known. Therefore, it is recommended to write programs in Java with Oracle. The following is a detailed implementation process in the Java language.

Note: In order to briefly describe the convenience, the following procedures have been abbreviated, in practice, but also to consider a lot of problems, and generally make it into a bean to use.

The procedure is as follows:

Import java.sql.*;//Class Library
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", followed by a class or level or a professional condition for actual use.
ResultSet rs_km = Stmt.executequery (sql_km);
String title= "Learn number name";
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="; In the actual use to add class conditions
Sql_sum=sql_sum+rs_km.get

[1] [2] Next page

The Oracle tutorial that you are looking at is Oracle Dynamic Cross table generation. A String (1);//The assembly line that counts the account for that class.
ResultSet rs_sum = Stmt1.executequery (sql_sum);
Rs_sum.next ();
The statistics meet the class conditions of the results CJ Sum, if 0 is considered that the class does not open the subject, slightly off.
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) + ",";
Constructs a dynamic statement.
}
Rs_sum.close ();
//Get dynamic account and name
Sql=sql.substring (1,sql.length ()-1); Remove the last comma.
sql=sql+ "from Jbxx order by Jbxx.xh"; In the actual use to add class conditions
ResultSet rs=stmt.executequery (SQL);
ResultSetMetaData data = Rs.getmetadata ();
int Col=data.getcolumncount (); Gets all the fields that have been generated and implements dynamic output.
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 ("The data has been printed!") ");
Rs_km.close ();
Rs.close ();
Stmt1.close ();
Stmt.close ();
Conn.close ();
　　
///////////////////////////
}
catch (Exception e) {
System.err.println (e);
}
}
}
　　
The above code has been compiled in J2sdk1.4.2,oracle 8.1.7, in the application, the general need to make it into a bean to use, but also to join the semester, class dynamic variables, you can get the full dynamic of the data.

prev [1] [2]