在JSP页面中进行测试,代码如下:
<%
@ page contentType
=
"
text/html;charset=gb2312
"
%>
<% @ page import = " java.sql.* " %>
< html >
< body >
<%
Class.forName( " com.microsoft.jdbc.sqlserver.SQLServerDriver " )
.newInstance();
String url = " jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs " ;
// pubs为你的数据库的
String user = " sa " ;
String password = "" ;
Connection conn = DriverManager.getConnection(url, user, password);
// 不带参数的存储过程,并且返回结果集
CallableStatement stmt = conn.prepareCall( " {call ghy_proc} " );
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
out.println(rs.getString(1));
}
out.println( " <br> " );
// 带参数的存储过程,并且返回值
stmt = conn.prepareCall( " {call ghy_proc_return(?,?)} " );
stmt.registerOutParameter( 1 , Types.INTEGER);
stmt.registerOutParameter( 2 , Types.INTEGER);
stmt.setInt( 1 , 10 );
stmt.setInt( 2 , 10 );
stmt.execute();
out.println( " 加1的值是: " + stmt.getString( 1 ) + " <br> " );
out.println( " 减1的值是: " + stmt.getString( 2 ) + " <br> " );
// 带参数的存储过程,并且返回结果集
stmt = conn.prepareCall( " {call ghy_proc_var(?)} " );
stmt.setInt( 1 , 14 );
stmt.execute();
rs = stmt.getResultSet();
while (rs.next()) {
out.println("job_id value is:" + rs.getString(1) + "<br>");
out.println("job_desc value is:" + rs.getString(2) + "<br>");
}
%>
</ body >
</ html >
<% @ page import = " java.sql.* " %>
< html >
< body >
<%
Class.forName( " com.microsoft.jdbc.sqlserver.SQLServerDriver " )
.newInstance();
String url = " jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs " ;
// pubs为你的数据库的
String user = " sa " ;
String password = "" ;
Connection conn = DriverManager.getConnection(url, user, password);
// 不带参数的存储过程,并且返回结果集
CallableStatement stmt = conn.prepareCall( " {call ghy_proc} " );
stmt.execute();
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
out.println(rs.getString(1));
}
out.println( " <br> " );
// 带参数的存储过程,并且返回值
stmt = conn.prepareCall( " {call ghy_proc_return(?,?)} " );
stmt.registerOutParameter( 1 , Types.INTEGER);
stmt.registerOutParameter( 2 , Types.INTEGER);
stmt.setInt( 1 , 10 );
stmt.setInt( 2 , 10 );
stmt.execute();
out.println( " 加1的值是: " + stmt.getString( 1 ) + " <br> " );
out.println( " 减1的值是: " + stmt.getString( 2 ) + " <br> " );
// 带参数的存储过程,并且返回结果集
stmt = conn.prepareCall( " {call ghy_proc_var(?)} " );
stmt.setInt( 1 , 14 );
stmt.execute();
rs = stmt.getResultSet();
while (rs.next()) {
out.println("job_id value is:" + rs.getString(1) + "<br>");
out.println("job_desc value is:" + rs.getString(2) + "<br>");
}
%>
</ body >
</ html >
三个SQL Server 2000存储过程如下:
CREATE
PROCEDURE
ghy_proc
AS
select * from jobs
GO
AS
select * from jobs
GO
CREATE
PROCEDURE
ghy_proc_return (
@max
int
output,
@min
int
output)
AS
select @max = @max + 1
select @min = @min - 1
GO
AS
select @max = @max + 1
select @min = @min - 1
GO
CREATE
PROCEDURE
ghy_proc_var (
@id
int
)
AS
select * from jobs where job_id = @id
GO
AS
select * from jobs where job_id = @id
GO
告诉自己:标准SQL语法和概念很重要,以后要强化这些。