创建表并插入数据¶
创建表¶
学生表:
create table student(sno int primary key, sname varchar(10));
分数表:
create table grade(gsno int, gcno int, gr int ,foreign key
(gsno) references student (sno));
插入数据¶
学生表数据插入:
CREATE OR REPLACE PROCEDURE insert_into_student() AS
DECLARE
i INT;
sqlStr TEXT;
sname TEXT;
BEGIN
FOR i IN 1..10000 LOOP
BEGIN
sname = '学生'||i||'';
sqlStr = 'Insert into student values(' || i || ', '''|| sname
||''');';
execute sqlStr;
END;
END LOOP;
RETURN;
END;
/
EXEC insert_into_student();
分数表数据插入:
CREATE OR REPLACE PROCEDURE insert_into_grade() AS
DECLARE
i INT;
j INT;
sqlStr TEXT;
BEGIN
FOR j IN 1..10000 LOOP
BEGIN
FOR i IN 1..1000 LOOP
BEGIN
if (j < 100) then
sqlStr = 'insert into grade values('||i||','||j||',60);';
else
sqlStr = 'insert into grade
values('||i||','||j||','||((i/100)::INT)*10||');';
end if;
execute sqlStr;
END;
END LOOP;
END;
END LOOP;
RETURN;
END;
/
EXEC insert_into_grade();
执行语句¶
1 查询某学生的各科成绩，并按分数排序取前十条数据:
select s.sno,s.sname,g.gr from student s,grade g where s.sno=1 and
s.sno=g.gsno order by g.gr desc limit 10;
2 查询某学科前十名学生成绩:
select s.sno,s.sname,g.gcno,g.gr from student s, grade g where g.gcno=1
and s.sno=g.gsno order by gr desc limit 10;
3 查询某学生某学科的成绩:
select s.sno,s.sname,g.gr from student s,grade g where g.gsno=1 and
g.gcno=1 and s.sno=g.gsno;
为了模拟查分系统客户端，运行以下java代码，可建立多个会话同时并且随机的查询这三条语句。 若要更改当前连接数据库，可以在代码中更改：
private static String dbName = "shi";
private static String host = "localhost";
private static String port = "2007";
java代码：
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
public class Threads {
private static String dbName = "shi";
private static String host = "localhost";
private static String port = "2007";
private static boolean isDispose = false;
private static JLabel displayLabel = new
JLabel("当前会话数:0");
public static void main(String[] args) {
JPanel mainPanel = new JPanel( new GridLayout(4, 1));
JLabel connectLabel = new JLabel("当前链接数据库:" + dbName + "@"
+ host +":" + port);
JButton addThrs = new JButton("添加会话");
JButton disposeThrs = new JButton("销毁会话");
mainPanel.add(connectLabel);
mainPanel.add( displayLabel);
mainPanel.add(addThrs);
mainPanel.add(disposeThrs);
addThrs.addActionListener( new ActionListener() {
public void actionPerformed(ActionEvent paramActionEvent) {
// TODO Auto-generated method stub
if (isDispose) {
isDispose = false;
}
T t = new T();
t.start();
String sessions = displayLabel.getText().split(":")[1];
displayLabel.setText("当前会话数:" +
(Integer.valueOf (sessions)+1));
}
});
disposeThrs.addActionListener( new ActionListener() {
public void actionPerformed(ActionEvent paramActionEvent) {
// TODO Auto-generated method stub
isDispose = true;
displayLabel.setText("当前会话数:0");
}
});
JFrame frame = new JFrame("多会话模拟");
Dimension screenSize =
Toolkit. getDefaultToolkit ().getScreenSize();
Dimension frameSize = frame.getSize();
if (frameSize.height > screenSize.height) {
frameSize.height = screenSize.height;
}
if (frameSize.width > screenSize.width) {
frameSize.width = screenSize.width;
}
frame.getContentPane().add(mainPanel);
frame.setLocation((screenSize.width - frameSize.width) / 2,
(screenSize.height - frameSize.height) / 2);
frame.setSize(250,250);
frame.setVisible( true);
frame.setDefaultCloseOperation(JFrame. EXIT_ON_CLOSE);
}
public static class T extends Thread {
public void run() {
String[] sqls = {"select s.sno,s.sname,g.gr from student s,grade g where
s.sno=? and s.sno=g.gsno order by g.gr desc limit 10;",
"select s.sno,s.sname,g.gcno,g.gr from student s, grade g where g.gcno=?
and s.sno=g.gsno order by gr desc limit 10;",
"select s.sno,s.sname,g.gr from student s,grade g where g.gsno=? and
g.gcno=! and s.sno=g.gsno;" };
String sql;
Connection connection = getConnection();
Statement st = null;
try {
if (connection != null) {
st = connection.createStatement();
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
while (!isDispose) {
Random random = new Random();
int index = Math. abs (random.nextInt())%3;
if (index != 2) {
int no = 1 + Math. abs (random.nextInt())%1000;
sql = sqls[index].replace("?", String. valueOf (no));
} else {
int sno = 1 + Math. abs (random.nextInt())%1000;
int gcno = 1 + Math. abs (random.nextInt())%1000;
sql = sqls[index].replace("?", String. valueOf (sno)).replace("!",
String. valueOf (gcno));
}
try { if (st != null) {
st.executeQuery(sql);
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (!connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public Connection getConnection() {
String url="jdbc:oscar://"+ host + ":" + port +"/"+ dbName;
Connection con = null;
try {
Class. forName ("com.oscar.Driver");
con = DriverManager. getConnection (url, "sysdba", "szoscar55");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
}
运行该程序界面如下，可以看到当前链接的数据库信息，以及实时的显示当前会话数。点击添加会话按钮，可以新增会话，点击销毁会话按可以清除添加的所有会话。
图246 多会话模拟程序界面