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;
/
程序包体已创建。
JSP调用
<%@ 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();
}
%>