大型数据库实验报告

中南大学大型数据库实验报告

课程名称大型数据库技术指导教师

姓名

学号

专业班级

《大型数据库技术》实验三

1．写一个PROC程序，查询并显示表Agents的所有记录。要求定义一个数组类型的宿主变量，一次性把所有记录从服务器端传送到客户端，然后逐行显示。

Java代码如下：

public void selectAgents() throws Exception {

Connection conn = this.getConnection();

Statement stmnt = conn.createStatement();

ResultSet set = stmnt.executeQuery("select * from Agents");

System.out.println("查询结果如下：\n");

while (set.next()) {

String id = set.getString("AID");

String name = set.getString("ANAME");

String city = set.getString("CITY");

int percent = set.getInt("PERCENT");

System.out.println("aid:"+ id + " aname:"+ name + " city:" + city + " percent:" + percent);

}

set.close();

stmnt.close();

conn.close();

}

测试代码：

public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@localhost:1521:ORCL";

String user = "jelly";

String pwd = "csusoft";

DBOpers db = new DBOpers(url, user, pwd);

db.selectAgents();

}

测试结果：

2．写一个PROC程序，根据用户输入的城市，查询并逐行显示该城市所有顾客的编号、名称和折扣。如果该城市中不存在任何顾客，则调用一个错误处理函数，函数中显示错误信息：“该城市中不存在顾客”。

Java代码如下：

public void selectCustomerByCity(String city) throws Exception {

Connection conn = this.getConnection();

Statement stmnt = conn.createStatement();

ResultSet set = stmnt

.executeQuery("select * from Customers where city='" + city

+ "'");

boolean isEmpty = true;

System.out.println("查询结果如下：");

while (set.next()) {

String cid = set.getString("CID");

String cname = set.getString("CNAME");

double discnt = set.getDouble("DISCNT");

String ct = set.getString("CITY");

System.out.println("cid:" + cid + " cname:" + cname + " discnt:"

+ discnt + " city:" + ct);

isEmpty = false;

}

if (isEmpty) {

System.out.println("该城市不存在客户。");

}

set.close();

stmnt.close();

conn.close();

}

测试代码：（查询在Duluth的用户）

public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@localhost:1521:ORCL";

String user = "jelly";

String pwd = "csusoft";

DBOpers db = new DBOpers(url, user, pwd);

db.selectCustomerByCity("Duluth");

}

测试结果：

3．写一个在循环中提示用户输入一个顾客ID（cid）和一个商品ID（pid）(各占一行)的PROC程序。该程序应该逐行显示每一个提供pid给cid的代理商的aid和由每个代理商提供的qty总数的列表。如果提供的cid或pid 的值在Customers表或Products表中不

存在，则程序应该不返回任何行。当用户输入一个空行后，程序终止。

Java代码如下：

public void selectQTYList() throws Exception {

while (true) {

System.out.print("请输入客户ID：");

BufferedReader reader = new BufferedReader(new InputStreamReader(

System.in));

String cid = reader.readLine();

if (cid == null || "".equals(cid.trim())) {

break;

}

System.out.print("请输入商品ID：");

String pid = reader.readLine();

if (pid == null || "".equals(pid.trim())) {

break;

}

Connection conn = this.getConnection();

Statement stmnt = conn.createStatement();

String sql = "select o.aid,sum(o.qty)"+ " from Orders o"

+ " where o.cid= '" + cid + "' and o.pid= '" + pid + "' "

+ " and exists ( select * from Customers c where c.cid='"

+ cid + "' )"

+ " and exists ( select * from Products p where p.pid='"

+ pid + "' )" + " group by o.aid";

ResultSet set = stmnt.executeQuery(sql);

System.out.println("查询结果如下：");

while (set.next()) {

String aid = set.getString(1);

int sum = set.getInt(2);

System.out.println("代理商ID:" + aid + " QTY总和:" + sum);

}

set.close();

stmnt.close();

conn.close();

}

}

测试代码：

public static void main(String[] args) throws Exception