数据库对象管理4_1
1. 统计某个部门的平均工资、最高工资、最低工资
步骤 1 新建一个临时表 statis,存放统计数据
步骤 2 定义存储过程
步骤 3 测试存过程
步骤 4 定义 html
步骤 5 定义 jsp,调用存储过程
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8" import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String user="scott";
String password="tiger";
Connection con=DriverManager.getConnection(url,user,password);
//调用过程
Integer deptno=Integer.parseInt(request.getParameter("deptno"));
String sql="{call p_statis11(?)}";
CallableStatement cst=con.prepareCall(sql);
cst.setInt(1, deptno);
cst.executeUpdate();
Statement st =con.createStatement();
String sq12 ="select * from statis11";
ResultSet rs =st.executeQuery(sq12);
%>
<table border=1>
<tr>
<td>部门号</td>
<td>平均工资</td>
<td>最高工资</td>
<td>最低工资</td>
</tr>
<% while(rs.next()){ %>
<tr>
<td><%=rs.getInt("deptno") %></td>
<td><%=rs.getString("avg") %></td>
<td><%=rs.getInt("max") %></td>
<td><%=rs.getInt("min") %></td>
</tr>
<%}
rs.close();
st.close();%>
</table>
</body>
</html>
2. 调用存储过程,插入数据
步骤 1 创建一个过程
步骤 2 测试过程
步骤 3 新建 insert_dept.html
步骤 4 新建 insert_dept.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8" import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String user="scott";
String password="tiger";
Connection con11=DriverManager.getConnection(url,user,password);
Integer deptno =Integer.parseInt(request.getParameter("deptno"));
String dname=request.getParameter("dname");
String loc=request.getParameter("loc");
String sql ="{call p_insert11(?,?,?)}";
CallableStatement cst= con11.prepareCall(sql);
cst.setInt(1,deptno);
cst.setString(2,dname);
cst.setString(3,loc);
cst.execute();
Statement st =con11.createStatement();
String sq12 ="select * from dept";
ResultSet rs =st.executeQuery(sq12);
%>
<table border=1>
<tr>
<td>部门编号</td>
<td>部门名称</td>
<td>部门地址</td>
</tr>
<% while(rs.next()){ %>
<tr>
<td><%=rs.getInt("deptno") %></td>
<td><%=rs.getString("dname") %></td>
<td><%=rs.getString("loc") %></td>
</tr>
<%}
rs.close();
st.close();%>
</table>
</body>
</html>