一、Oracle的存储过程
--存储过程 (根据条件来删除员工信息)
create or replace procedure proc_delEmp(eno emp.empno%type,msg out number)
as
v_count number; --声明变量存放结果
begin
select count(1) into v_count from emp where empno=eno;
if v_count>0 then
delete from emp where empno=eno;
msg:=1;
else
msg:=0;
end if;
end;
--pl/sql执行
declare
msg number;
begin
proc_delEmp(11191,msg);
dbms_output.put_line(msg);
end;
二、业务Bean调用存储过程
/**
* 底层数据继承基类(BaseDao)
*
* @author Administrator
*
*/
public class EmpDao extends BaseDao {
// 声明对象
private String sql = "";
private Connection con;
private PreparedStatement ps;
private ResultSet rs;
private CallableStatement cs;
/**
* --(1)删除
*
* @return
*/
public int delEmpByNo(int no) {
sql = "{call proc_delEmp(?,?)}";
// 1)获得连接
con = this.getConnection();
// 2)获得cs对象
try {
CallableStatement cs = con.prepareCall(sql);
// 设置输入参数
cs.setInt(1, no);
// 将存储过程的输出参数转换成Java识别的参数类型 --->输出参数
cs.registerOutParameter(2, Types.INTEGER);
// 执行
cs.execute();
// 获得输出参数--对应输出的编号
int count = cs.getInt(2);
System.out.println("count=" + count);
// 5)获得值
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (cs != null) {
try {
cs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
this.closeAll(null, null, con); // 关闭
}
return 0;
}
三、在JSP页面实现
<body>
<form action="delEmp.jsp" method="post">
员工编号:<input type="text" name="eno"/><input type="submit" value="删除">
</form>
</body>
四、处理页面
<jsp:useBean id="dao" class="com.hlx.dao.EmpDao"></jsp:useBean>
<%
String no = request.getParameter("eno");
int count = dao.delEmpByNo(Integer.parseInt(no));
if(count>0){
// response.sendRedirect("index.jsp");
out.println("<script>alert('删除成功!');document.location.href='index.jsp';</script>");
}else{
out.println("<script>alert('删除失败!');document.location.href='index.jsp';</script>");
}
%>