创建存储过程:
1、在企业管理器中新建存储过程
2、在查询分析器中编辑存储过程(带有参数和返回值的)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure insertUserinfo @name varchar ( 50 ), @sex varchar ( 50 ), @birthday varchar ( 50 ), @returnValue int = 0 output
as
insert into userinfo (name,sex,birthday) values ( @name , @sex , @birthday )
set @returnValue = 1 ;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Java 调用存储过程
1.数据库连接
package com.hujuan.conn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConn {
public static Connection getConnection(){
String className= "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=login";
String name = "sa";
String password = "sasa";
try {
Class.forName(className);
return DriverManager.getConnection(url,name,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
2.通过连接调用存储过程
package com.hujuan.dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import com.hujuan.conn.DatabaseConn;
public class SqlServerProc {
public static void main(String[] args) {
Connection conn = null;
CallableStatement call = null;
conn = DatabaseConn.getConnection();
try {
call = conn.prepareCall("{call insertUserinfo(?,?,?,?)}");
call.setString(, "hujuan");
call.setString(, "女");
call.setString(, "1985-06-16");
call.registerOutParameter(, Types.INTEGER);
call.executeUpdate();
int value = call.getInt();
System.out.println(value);
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(call != null)call.close();
if(conn != null)conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
返回结果集
package com.hujuan.dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import com.hujuan.conn.DatabaseConn;
public class SqlServerProc {
public static void main(String[] args) {Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
conn = DatabaseConn.getConnection();
try {
call = conn.prepareCall("{?=call selectUserinfo()}");
call.registerOutParameter(, Types.REAL);
rs = call.executeQuery();
while(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(rs != null)rs.close();
if(call != null)call.close();
if(conn != null)conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}