Jsp+Oracle调用存储过程

深藏阁楼爱情的钟 2024-02-17 16:45 8阅读 0赞

**一、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页面实现**

**四、处理页面**

<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>");
         }
         
       %>