Oracle dynamic crosstab generation

  • 2020-05-14 05:14:53
  • OfStack

The ORACLE tutorial you are looking at is :Oracle dynamic crosstab generation. Oracle is the most widely used large database, and the design of Oracle database under the normal form can greatly reduce the data redundancy and make the database maintenance more convenient. Unfortunately, the data table 1 under the normal form cannot be directly output. Today we will discuss the method of dynamic crosstab generation of data tables under the 1 normal form.

Oracle database design under the paradigm

The complexity of data relations leads to the existence of data redundancy in the table, which increases the burden of maintaining the database and occupies a large amount of disk space, directly resulting in performance degradation. In order to eliminate these negative effects, the database tables should be normalized to conform to the 1 set of rules, especially the database design paradigm.

Relationships must be normalized, which simply means eliminating redundant and inconsistent dependencies when designing structural tables. That is, each component must be an indivisible data item, but this is only the most basic normalization. Normalization theory is a theory that studies how to transform a bad relationship model into a good one. According to the theory of normalization, all the relationships in a relational database should satisfy 1 definite specification (constraint condition). Standardization theory the relationship shall meet the specification of the requirements are divided into several levels, meet the minimum requirements for grade 1 is called paradigm NF (1), 1 in the first model is proposed on the basis of 2 paradigm NF (2), on the basis of the second paradigm and put forward the third paradigm (3 NF), then put forward the BCNF paradigm, 4 NF, 5 NF, as well as the "domain/keyword" paradigm. The higher the rank of the paradigm, the more stringent the constraint set conditions that should be satisfied. Each level of the specification depends on its previous level; for example, if a relational schema satisfies 2NF, 1 must satisfy 1NF.

The design of the database on Oracle is more in line with the requirements of the paradigm. If you put a non-compliant database in Oracle, the performance of Oracle will not be highlighted, or even very bad.

For example, we usually require the students to print their grade sheets.

This is also 1NF compliant, but if the table structure is defined in the database as well, it is incomplete and potentially conflicting. If you want to add test subjects, you have to change the structure of the table, especially in universities, more majors, more subjects, and some subjects are selected to learn, this will make the structure of the table become quite complex, how many subjects will have to have how many subjects of the field, some of the field value must be empty; Does this chart refer to a test or a midterm or final? Distinguish not to be able to tell, then every time result wants to make 1 similar table, inevitable table is more. Not only does it waste a lot of disk space, but it also makes it extremely difficult to write programs.

Under the guidance of the data paradigm theory, the database table is normalized to make its structure more reasonable, the storage exception is eliminated, the data redundancy is minimized as far as possible, and it is easy to insert, delete and update. The integrity of the data is kept in step 1. After exploration, I adopted the following table structure in the design of the score management system. This table structure can adapt to the changing application with invariable application, no matter it is the increase of subjects or the change of teachers. It meets the standard requirements of data.

It can be seen from this that although the normalized data makes data redundant and easy to insert, delete and update, if the direct output is not in line with people's viewing habits, it must be output in the format of table 1 above. This is the problem of column to table to generate horizontal table, that is, the generation of cross table.

Generation of dynamic crosstab

For the sake of brevity, in the basic information table of students, only two fields are set up: student number and name, and other fields such as gender and subject code are omitted. The class, the teacher's code base, the test times mark (i.e. the number of tests, or mid-term, final exams) are also omitted, and only the following data structure is enough to illustrate the process of cross-table generation.

The structure of each table is simplified as follows:

Basic information for students: JBXX

xh char(13) // student id

xm char(8) // name, variable length characters can be used for different situations.

Subject code table: KMDM

no number(3) // subject code, now available in more than 900 subjects, if not enough, you can define 4 bits.

mc varchar(20) // subject name in Chinese.

Score sheet: CJ

xh char(13) // student number associated with XH for JBXX.

xq number(2) // term means the term in which the student is in school.

km number(3) // subject code.

cj number(3) //

At this point, the data table structure has been established. At this time, the task is to generate a cross table from the data in table 3 below, and table 4.

The generation of a crosstab can be implemented in Oracle using the SQL statement.

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 where cj xh= jbxx. xh and cj. xq=1 and cj. km=2) as km2, xh= 1 and cj km==3) km from jbxx where < Class or professional qualifications > order by jbxx.xh

The Java language has the cross-platform capability of "write once, run anywhere" and has powerful network capabilities. Oracle is a large relational database, which can run on a variety of hardware platforms, support a variety of operating systems, support large databases, multi-user high-performance transaction processing, and is known for its powerful function and stability. Therefore, it is recommended to use Java in combination with Oracle to write programs. The implementation in the Java language is given below.

Note: for the sake of convenience, the following procedure has been abbreviated. In practice, many problems should be considered, and it should be used as 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"; After the actual use to add class or level or professional conditions.
ResultSet rs_km = stmt. executeQuery (sql_km);
String title=" student name ";
String sql1="(select cj from cj cj cj xh jbxx cj xq= ";
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 ="; // class conditions should be included in the actual use
sql_sum = sql_sum + rs_km get

[1] [2] next page

The ORACLE tutorial you are looking at is :Oracle dynamic crosstab generation. String (1); // statistics of the class, the subject of the assembly line.
ResultSet rs_sum = stmt1. executeQuery (sql_sum);
rs_sum. next ();
// the total score of CJ that meets the requirements of the class shall be counted. If it is 0, it shall be considered that the class does not offer this subject, so it shall be 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 dynamic statements.
}
rs_sum. close ();
} // get dynamic subject and name
sql = sql. substring (1, sql length () - 1); // remove the last comma.
sql=sql+"from jbxx order by jbxx "; // class conditions should be included in the actual use
ResultSet rs = stmt. executeQuery (sql);
ResultSetMetaData data = rs. getMetaData ();
int col = data. getColumnCount (); // get all the generated fields, and execute 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 is printed! ") );
rs_km. close ();
rs. close ();
stmt1. close ();
stmt. close ();
conn. close ();

///////////////////////////
}
catch (Exception e) {
System. err. println (e);
}
}
}

The above code has been j2sdk1. 4.2 Oracle 8.1.7 compiled through, in applications, the need to make it a bean to use 1, still can add term, class's and grade's dynamic variables, can obtain the dynamic data.

Previous page [1] [2]


Related articles: