Dynamic Oracle cross tabulation

The ORACLE tutorial is: Dynamic Oracle cross tabulation. Oracle is the most widely used large database, while Oracle Database Design in the paradigm can greatly reduce data redundancy, making database maintenance more convenient. Unfortunately, data tables in the paradigm generally cannot be directly output. Today, we will discuss how to generate dynamic cross-tabulation for data tables under the paradigm.

Oracle Database Design in the paradigm

The complexity of the data relationship leads to the existence of data redundancy in the table. data redundancy increases the burden of maintaining the database and occupies a large amount of disk space, which directly leads to performance degradation. To eliminate these negative effects, we should standardize database tables so that they comply with certain rules, especially the database design paradigm.

The relationship must be normalized. Simply put, it is to eliminate redundancy and uncoordinated subordination during the design of the structure table. That is, each component must be an inseparable data item, but this is only the most basic normalization. The Standardization Theory is to study how to transform a bad relationship model into a good relationship model. The Standardization Theory is built around the paradigm. Standardization theory holds that all the relationships in a relational database must meet certain standards (constraints ). Standardization Theory divides the normative requirements that the relationship should meet into several levels. The level that meets the minimum requirements is called 1NF. Based on the first paradigm, the second Paradigm (2NF) is proposed ), based on the second paradigm, the third paradigm (3NF) is proposed, and then the BCNF paradigm, 4NF, 5NF, and domain/keyword paradigm are proposed. The higher the level of the paradigm, the stricter the condition of the attention set. Each level of a Specification depends on its previous level. For example, if a relational model meets 2NF, 1NF must be met.

When designing a database on Oracle, it must comply with the requirements of the paradigm. If a non-conforming database is put in Oracle, it will not highlight the performance of Oracle, or even be very bad.

For example, we generally require that the student's sheet be clearly printed.

This is also in line with 1NF, but if the table structure defined in the database is the same, it is not perfect and there is a potential conflict. If you want to add more exam subjects, you have to change the table structure, especially for universities with many majors and subjects. Some subjects are optional, which will make the table structure quite complex, the number of subjects is the number of subject fields, and some of the field values must be blank. Does this table refer to the results of a certain test or a mid-term or final exam? The results cannot be identified. Therefore, a similar table must be created for each score. Therefore, there must be many tables. It not only wastes a lot of disk space, but also brings great difficulties to programming.

Under the guidance of the Data paradigm theory, standardize database tables to make their structures more reasonable, eliminate storage exceptions, minimize data redundancy, and facilitate insertion, deletion, and update, data integrity is further maintained. After exploration, I have adopted the following table structure in the design of the score management system. This table structure can be applied without changing changes, regardless of the increase in subjects or changes in teachers, comply with data specifications.

It can be seen that, although the standardized data makes the data redundant and easy to insert, delete, and update, if the direct output does not meet people's viewing habits, the output must be in the format of Table 1 above. This is the problem of generating a horizontal table from a column to a table, that is, generating a cross table.

Dynamic cross tabulation

For the sake of briefing, only two fields, student ID and name, and other fields such as gender and Subject Code, are created in the basic student information table. The mid-shift, instructor code library, and exam count indicators (I .e., the first quiz, or the mid-term or end-time exam) are also omitted. Only the following data structure is retained to illustrate the process of cross tabulation.

The table structure is simplified as follows:

Student basic info table: JBXX

Xh char (13) // student ID

Xm char (8) // name. variable-length characters are available for different situations.

Account code table: KMDM

No number (3) // subject code. Currently, more than 900 subjects are available. If not, you can define four subjects.

Mc varchar (20) // Chinese name of the subject.

Partition Table: CJ

Xh char (13) // student ID, associated with the XH of JBXX.

Xq number (2) // The term of the student's school.

Km number (3) // subject code.

Cj number (3) // score of this subject.

At this point, the data table structure has been fully created. In this case, the task is to generate a cross table for the data in table 3 below, table 4.
　　
Cross tabulation can be implemented using 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 and cj. km = 3) as km3 from jbxx where <class or professional conditions> order by jbxx. xh

The Java language provides the cross-platform capability of "writing once, running anywhere" and powerful network capabilities. Oracle is a relational large database that can run on multiple hardware platforms. It supports multiple operating systems and high-performance transaction processing for large databases and multiple users, it is famous for its powerful functions and stability. Therefore, we recommend that you use Java in combination with Oracle to write programs. The following describes the specific implementation process in Java.

NOTE: For the sake of convenience, the following program has been simplified. In practice, many problems need to be considered, and it is generally used as a bean.

The procedure is as follows:

Import java. SQL. *; // import the 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"; in the future, you need to add work or level or professional conditions.
ResultSet rs_km = stmt.exe cuteQuery (SQL _km );
String title = "student ID ";
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 ="; // Add the class conditions in actual use.
SQL _sum = SQL _sum + rs_km.get

[1] [2] Next page

The ORACLE tutorial is: Dynamic Oracle cross tabulation. String (1); // calculates the total line of the course.
ResultSet rs_sum = stmt1.executeQuery (SQL _sum );
Rs_sum.next ();
// Calculate the total score CJ that meets the class criteria. If it is 0, it is deemed that this course is not opened and omitted.
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) + ",";
// Construct a dynamic statement.
}
Rs_sum.close ();
} // Obtain the dynamic subject and name
SQL = SQL. substring (1, SQL. length ()-1); // remove the last comma.
SQL = SQL + "from jbxx order by jbxx. xh"; // Add the class conditions in actual use.
ResultSet rs1_stmt.exe cuteQuery (SQL );
ResultSetMetaData data = rs. getMetaData ();
Int col = data. getColumnCount (); // obtain all generated fields and implement 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 ("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 and Oracle 8.1.7. In applications, it is generally used as a bean. You can also add dynamic variables for the semester and class, you can get all the dynamic data.

Previous Page [1] [2]