jsp中调用oracle存储过程的实例

       前段时间上传了如何在jsp、java中调用oracle存储过程的例子，现在把其中有用的部分摘录出来以做参考。错误之处望见谅！

      上传见：http://down.51cto.com/data/219597  

--存储过程：
--包：
create or replace package pck_recond
as

------------------------------声明游标变量
  type t_recond_cursor is ref cursor return icdmain.t_pub_commoninfo%rowtype; --弱游标类型
  type y_recond_cursor is ref cursor; --强游标类型

-----------------------------返回录音的存储过程
  procedure   p_getrecond(i_serialno     in  icdmain.t_pub_commoninfo.serialno%type,
                          i_callerno     in  icdmain.t_pub_commoninfo.callerno%type,
                          i_accepterno   in  icdmain.t_pub_commoninfo.accepterno%type,
                          i_checkflag    in  icdmain.t_pub_commoninfo.checkflag%type,
                          o_dataset      out  y_recond_cursor --返回数据集
                          );

----------------------------更新质检状态的存储过程

procedure p_updaterecond(i_serialno  in  icdmain.t_pub_commoninfo.serialno%type,
                         i_remark    in  icdmain.t_pub_commoninfo.remark%type
                         );

---------------------------存储过程分页
PROCEDURE p_query
       (i_tableName        in  varchar2,   --表名
        i_curPage          in out Number,  --当前页 in out
        i_pageSize         in out Number,  --每页显示记录条数  in out
        i_totalRecords     out Number,     --总记录数
        i_totalPages       out Number,     --总页数
        o_cur              out y_recond_cursor --返回数据集
        );

 ------------------------登陆
PROCEDURE p_login
       (i_staffno       in   icdmain.t_pub_staff.staffno%type,   --用户no
        i_password      in   icdmain.t_pub_staff.password%type,    --密码
        o_staffno       out  icdmain.t_pub_staff.staffno%type,
        o_msg           out  varchar2, ----返回错误消息
        o_login         out  number,
        o_staffstatus   out  number
        );  
                  
----------------------退出
PROCEDURE P_LOGOUT
       (i_staffno       in   icdmain.t_pub_staff.staffno%type  --用户no
        );  
                  
end pck_recond;

------------------------------------------------------
--包体，仅写出分页的过程，因信息安全其余不便写出：

create or replace package body pck_recond
as

------------------------------分页存储过程
PROCEDURE p_query
       (i_tableName        in  varchar2,   --表名或试图名
        i_curPage          in out Number,  --当前页
        i_pageSize         in out Number,  --每页显示记录条数
        i_totalRecords     out Number,     --总记录数
        i_totalPages       out Number,     --总页数
        o_cur              out y_recond_cursor)   --返回的结果集
IS
   v_sql VARCHAR2(1000) := '';      --sql语句
   v_startRecord Number(4);         --开始显示的记录条数
   v_endRecord Number(4);           --结束显示的记录条数
BEGIN
   --记录中总记录条数
   v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || i_tableName || ' WHERE 1=1';

   EXECUTE IMMEDIATE v_sql INTO i_totalRecords;

   --验证页面记录大小
   IF i_pageSize < 0 THEN
       i_pageSize := 0;
   END IF;

   --根据页大小计算总页数
   IF MOD(i_totalRecords,i_pageSize) = 0 THEN
       i_totalPages := ceil(i_totalRecords / i_pageSize);
   ELSE
       --i_totalPages := i_totalRecords / i_pageSize + 1;
       i_totalPages := ceil(i_totalRecords / i_pageSize);
   END IF;

   --验证页号
   IF i_curPage < 1 THEN
       i_curPage := 1;
   END IF;

   IF i_curPage > i_totalPages THEN
       i_curPage := ceil(i_totalPages);
   END IF;

  --实现分页查询
   v_startRecord := (i_curPage - 1) * i_pageSize + 1;
   v_endRecord := i_curPage * i_pageSize;
   v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||
            '(SELECT * FROM ' || i_tableName;

   v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '|| v_startRecord;
   DBMS_OUTPUT.put_line(v_sql);

   OPEN o_cur FOR v_sql;

EXCEPTION
      WHEN OTHERS THEN
          OPEN o_cur FOR SELECT '1' FROM dual;

END p_query;

---package body  结束

end;

---------------------------------------------------------------------------------
--jsp页面中调用（jsp、java写的一般望谅解）：

--登陆页面：
 <jsp:useBean id="Data"  scope="request"  class="javabean.DataBean"/>
<%  request.setCharacterEncoding("GB2312");
   Connection cn = Data.setConnectionOracle();
   if(request.getParameter("staffno")!=null && request.getParameter("password2")!=null)
     {      
     String staffno     = new String(request.getParameter("staffno"));
     String password2     = new String(request.getParameter("password2"));
                                                           
     CallableStatement  stmt=cn.prepareCall("{call icdmain.pck_recond.p_login(?,?,?,?,?,?)}"); 
       stmt.setString(1,staffno);  //输入参数
       stmt.setString(2,password2);//输入参数
       stmt.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR); //输出参数
       stmt.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR); //输出参数，错误消息
       stmt.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER); //输出参数
       stmt.registerOutParameter(6,oracle.jdbc.OracleTypes.INTEGER); //输出参数
       stmt.execute();
      
       String v_staffno = stmt.getString(3);
       session.setAttribute("staffno",v_staffno);
       String msg = stmt.getString(4);
       int login = stmt.getInt(5);
      
       if (login==0)
          out.println(msg);
       else   
         response.sendRedirect("record/recordindex.jsp");
      }
   else
       {
       out.println("<tr><td colspan=4 align=center height=30>"); 
        out.println("请输入用户名或密码！");
        out.println("</td>");
       }              
  %>
 

----显示页面：
 <jsp:useBean id="Data"  scope="request"  class="javabean.DataBean"/>
<%  request.setCharacterEncoding("GB2312");
   Connection cn = Data.setConnectionOracle();
   if(request.getParameter("serialno")!=null || request.getParameter("callerno")!=null || request.getParameter("accepterno")!=null || request.getParameter("checkflag")!=null)
    {      
   String serialno     = new String(request.getParameter("serialno"));
   String callerno     = new String(request.getParameter("callerno"));
   String accepterno   = new String(request.getParameter("accepterno"));
   String checkflag    = new String(request.getParameter("checkflag"));
                                                           
  CallableStatement  stmt=cn.prepareCall("{call icdmain.pck_recond.p_getrecond(?,?,?,?,?)}"); 
    stmt.setString(1,serialno);  //输入参数
    stmt.setString(2,callerno);  //输入参数
    stmt.setString(3,accepterno); //输入参数
    stmt.setString(4,checkflag);  //输入参数
    stmt.registerOutParameter(5,oracle.jdbc.OracleTypes.CURSOR);  //返回游标的参数
    stmt.execute();
    ResultSet   rs=(ResultSet)stmt.getObject(5);
   
     %>

--更新状态页面：
<%
  Connection cn = Data.setConnectionOracle();
  if(request.getParameter("remark")!=null)
    {
    String remark  = new String( request.getParameter("remark"));
    String serialno   = (String)session.getValue("serialno");

   CallableStatement  stmt=cn.prepareCall("{call icdmain.pck_recond.p_updaterecond(?,?)}");    //2个输入参数
    stmt.setString(1,serialno);  //送入参数
    stmt.setString(2,remark);    //送入参数
    stmt.execute();
     
    response.sendRedirect("recordindex.jsp");
     }
else
 out.println("<a href='upformrecord.jsp'>重填</a>");
%>

----分页显示页面：
 <jsp:useBean id="Data"  scope="request"  class="javabean.DataBean"/>
          <%
             int pagey;  //从用户那里得到的目标页
             String pages  = request.getParameter("page");
                    if(pages==null)
                       { pages="1";}
             pagey = Integer.parseInt(pages);
                     //如果页数小于1，那么把页数设置成1
                    if(pagey<1)
                       {  pagey=1; }
                          
            try{ 
                Connection cn = Data.setConnectionOracle();
 
                 //分页存储过程
                 OracleCallableStatement stmt = (OracleCallableStatement)cn.prepareCall("{call icdmain.pck_recond.p_query(?,?,?,?,?,?)}");
                 stmt.setString(1,"v_recordfile"); //输入参数,是个试图
                 stmt.setInt(2,pagey);             //输入输出参数
                 stmt.registerOutParameter(2,oracle.jdbc.OracleTypes.INTEGER); //输入输出参数 --当前页
                 stmt.setInt(3,8); //输入输出参数，8是每页显示8条
                 stmt.registerOutParameter(3,oracle.jdbc.OracleTypes.INTEGER); //输入输出参数 --每页显示记录条数
                 stmt.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER); //输出参数 --总记录数
                 stmt.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER); //输出参数  --总页数
                 stmt.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR); //返回结果集  --返回的结果集
                                
                 stmt.execute();
                 ResultSet rs = (ResultSet)stmt.getObject(6);
               
                     ////////////////////////分页
                     int  currentPage = stmt.getInt(2);
                     int pageSize = stmt.getInt(3);
                     int recondSize = stmt.getInt(4);
                     int pageCount = stmt.getInt(5);
                     // 如果页数大于总页数，那么设置成最大页数
                     if(pagey>pageCount)
                          {  pagey=pageCount; }
                    
             %>
   
            共 <font color=red><%=recondSize%></font> 条记录，        
          每页 <font color=red><%=pageSize%></font> 条，
            共 <font color=red><%=pageCount%></font> 页，
    当前第 <font color=red><%=currentPage%></font> 页&nbsp;
           <a href="?page=1">首页</a>&nbsp;&nbsp;
           <a href="?page=<%=(currentPage-1)%>">上一页</a>&nbsp;&nbsp;
           <a href="?page=<%=(currentPage+1)%>">下一页</a>&nbsp;&nbsp;
           <a href="?page=<%=pageCount%>">尾页</a> &nbsp;&nbsp;&nbsp;&nbsp;
           <a href="../r_login.jsp" onclick="javascript:window.location.href="../r_login.jsp"; return false;"> </a>

----完

--------------------------------------------------------
---连接数据库的通用java文件：
 
package   javabean;
import java.sql.*;

public class DataBean {
    public Connection cn = null;
    public ResultSet rs = null;
    public PreparedStatement ps=null;
    public String dbs = null;
    public String user = null;
    public String pwd = null;

    public DataBean() {}

    public Connection setConnectionOdbc(String db) {
        dbs = "jdbc:odbc" + db;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            cn = DriverManager.getConnection(dbs);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return cn;

    }

    public Connection setConnectionOracle() {

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            cn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:ora10", "scott", "tiger");
                } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return cn;

    }

    public   Connection setConnectionSqlServer(String dbName)
    {
        dbs = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=" + dbName;
        user="sa";
        pwd="";

     try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            cn = DriverManager.getConnection(dbs,user,pwd);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return cn;

    }

    public  ResultSet getQuery(String sql) {
         ResultSet    rs=null;
        try {
           ps = cn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,
                                              ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return rs;
    }

    public  void setModify(String sql) {
       ps=null;
        try {
            ps = cn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,
                                              ResultSet.CONCUR_UPDATABLE);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void showResultSet(ResultSet rs) {
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            int col = rsmd.getColumnCount();
            while (rs.next()) {
                System.out.println("<tr>");
                for (int i = 1; i <= col; i++) {
                    System.out.println("<td>" + rs.getString(i) + "</td>");
                }
                System.out.println("</tr>");

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public  void  stClose()
    {
    try
    {
     ps.close();
    }
    catch(SQLException e)
    {
     e.toString();
    }
    }

    public  void  cnClose()
       {
       try
       {
       cn.close();
       }
       catch(SQLException e)
       {
       e.toString();
       }
    }

    public static String convert(String str)
    {
    try
    {
       byte[] bytesstr=str.getBytes("ISO-8859-1");
       return new String(bytesstr,"gb2312");
    }
    catch(Exception e )
    {
      e.toString();
    }
    return str;
    }
}
 

---java文件完

本文出自 “srsunbing” 博客，请务必保留此出处http://srsunbing.blog.51cto.com/3221858/611403