在java中存储过程的使用
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1 使用不带参数的存储过程
public static void executeStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con.prepareCall("{call dbo.GetContactFormalNames}");
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("FormalName"));
}
}
rs.close();
cstmt.close();
catch (Exception e) {
e.printStackTrace();
}
}
2 使用带有输入参数的存储过程
public static void executeStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con.prepareCall("{call pGetEmployeeManagers(?)}");
cstmt.setInt(1, 50);
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
System.out.println("EMPLOYEE:");
System.out.println(rs.getString("LastName") + ", " +
rs.getString("FirstName"));
System.out.println("MANAGER:");
System.out.println(rs.getString("ManagerLastName") + ", " + rs.getString("ManagerFirstName"));
System.out.println();
}
}
rs.close();
cstmt.close();
catch (Exception e) {
e.printStackTrace();
}
}
3 使用带有输出参数的存储过程
public static void executeStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2));
}
catch (Exception e) {
e.printStackTrace();
}
}
4 使用带有返回状态的存储过程
作为示例，在SQL Server 2005 AdventureWorks 示例数据库中创建以下存储过程：CREATE PROCEDURE CheckContactCity @cityName CHAR(50)ASBEGIN IF ((SELECT COUNT(*) FROM Person.Address WHERE City = @cityName) > 1) RETURN 1ELSE RETURN 0END
public static void executeStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, "Atlanta");
cstmt.execute();
System.out.println("RETURN STATUS: " + cstmt.getInt(1));
}
cstmt.close();
catch (Exception e) {
e.printStackTrace();
}
}
注：在sql中执行该存储过程的方法：
declare @c int
exec @c=CheckContactCity 'Atlanta'
print @c
5 使用带有更新记数的存储过程
作为示例，在SQL Server 2005 AdventureWorks 示例数据库中创建以下表和存储过程：CREATE TABLE TestTable
(Col1 int IDENTITY,
Col2 varchar(50),
Col3 int);
CREATE PROCEDURE UpdateTestTable
@Col2 varchar(50),
@Col3 int
AS
BEGIN
UPDATE TestTable
SET Col2 = @Col2, Col3 = @Col3
END;
public static void executeUpdateStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con.prepareCall("{call dbo.UpdateTestTable(?, ?)}");
cstmt.setString(1, "A");
cstmt.setInt(2, 100);
cstmt.execute();
int count = cstmt.getUpdateCount();
cstmt.close();
System.out.println("ROWS AFFECTED: " + count);
}
catch (Exception e) {
e.printStackTrace();
}
}