中南大学大型数据库实验报告课程名称大型数据库技术指导教师姓名学号专业班级《大型数据库技术》实验三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{String url = "jdbc:oracle:thin:@localhost:1521:ORCL";String user = "jelly";String pwd = "csusoft";DBOpers db = new DBOpers(url, user, pwd);db.selectQTYList();}测试结果：(输入的ID为空或空格时推出)4．在PROC程序中创建一个PL/SQL函数qty_check，以一笔订单的订货数量为参数，判断该订单的订货数量是否超过了被订购商品的库存数量。
如果没有超过，返回TRUE，否则返回FALSE。
由于在Java中不能创建PL/SQL的函数，所以这个题目直接用Java代码实现，Java代码如下所示：public boolean qtyCheck(String pid, int qty) throws Exception {boolean flag = false;Connection conn = this.getConnection();Statement stmnt = conn.createStatement();ResultSet set = stmnt.executeQuery("SELECT p.quantity from PRODUCTS p where p.pid ='"+ pid + "'");if (set.next()) {int storage = set.getInt(1);if (storage >= qty) {flag = true;}}set.close();stmnt.close();conn.close();return flag;}测试代码：public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@localhost:1521:lee";String user = "system";String pwd = "evefish";DBOpers db = new DBOpers(url, user, pwd);System.out.println(db. qtyCheck("P07",100501));System.out.println(db. qtyCheck("P01",1000));}测试结果：5．写一个PROC程序接收代理商输入的订单信息，并把它加入到CAP数据库中。