[计算机]java中使用存储过程实例.doc

以前用的是Statement 和 PreparedStatement调用存储过程就用到CallableStatement cstmt =conn.prepareCall(call p(?,?,?,?);声明 一个叫p的存储过程，需要4个参数 其中1，2为输入参数3输出参数，4为输入输出参数（在sqlserver里没有这样的参数）是输出参数的 我们在程序里要说明一下如：cstmt.registerOutParameter(3,Types.INTEGER)表明这个是第3个问号上面的，切类型是int 型号的cstmt.cstmt.registerOutParameter(4,Types.INTEGER)输入输出参数也必须说明cstmt.setInt(1,3);cstmt.setInt(2,3);cstmt.setInt(4,3); 把3个输入参数给值；cstmt.execute();执行存储过程。System.out.println(cstmt.getInt(3);System.out.println(cstmt.getInt(4);现在就可以用cstmt.get方法来取值了最后cstmt.close要关闭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 dbo.uspGetEmployeeManagers(?); 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 0ENDpublic 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