Jsp+Oracle调用存储过程

来源：互联网发布：编程社区论坛编辑：程序博客网时间：2024/06/10 05:13

一、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 blocke.printStackTrace();} finally {if (cs != null) {try {cs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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>");     }        %>

0 0