JSP调用ＯＲＡＬＣＥ存储过程显示简单的查询结果（以表格形式显示）

来源：互联网发布：淘宝改后台是什么意思编辑：程序博客网时间：2024/05/01 09:20

oralce存储过程

create or replace package gradetest
as
type g_test is ref cursor;
procedure page_test
(in_xuehao in number,rc out g_test);
end;
/

程序包已创建。
create or replace package body gradetest
as
procedure page_test(in_xuehao in number,rc out g_test) is
begin
open rc for
select * from grade_1 where xuehao=in_xuehao;
end;
end;
/

程序包体已创建。

ＪＳＰ调用

<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%@ page import="oracle.jdbc.driver.OracleTypes"%>
<%@ page import="java.sql.*,
java.sql.Connection,
java.sql.Statement,
java.sql.ResultSet,
java.util.Properties,
java.io.*,;"
%>

<%
Connection conn = null;
CallableStatement cs = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ce){
out.println(ce.getMessage());
}
try{
String url="jdbc:oracle:thin:@localhost:1521:orcl";
conn=DriverManager.getConnection(url,"system","system");
cs = conn.prepareCall("{call gradetest.page_test(? )}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.setInt(1, 14);
cs.execute();
ResultSet rs=(ResultSet)cs.getObject(1);

out.print("<TABLE BORDER='1'>");
        if(rs.next()){
  out.print("<TR><TD>"+rs.getString("xuehao")+"</TD>");
  out.print("<TD>"+rs.getString("xingming")+"</TD>");
  out.print("<TD>"+rs.getString("yumen")+"</TD>");
  out.print("<TD>"+rs.getString("shuxue")+"</TD>");
  out.print("<TD>"+rs.getString("yingyu")+"</TD></TR>");
}

out.print("</TABLE>");

}
catch(SQLException e){
out.print(e.getMessage());
}
finally{
cs.close();
conn.close();
}
%>