前段时间上传了如何在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> 页
<a href="?page=1">首页</a>
<a href="?page=<%=(currentPage-1)%>">上一页</a>
<a href="?page=<%=(currentPage+1)%>">下一页</a>
<a href="?page=<%=pageCount%>">尾页</a>
<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文件完
©著作权归作者所有：来自51CTO博客作者srsunbing的原创作品，如需转载，请注明出处，否则将追究法律责任
oracle职场jsporacle开发
随时随地看视频
