使用存储过程 (二)

import javax.naming.NamingException;

public class CreateStoredProceduresofSQLServer {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
String jndiname = "jdbcPool/mydatasource";

try {
con = DBCon.getConnectionFromPooledDataSource(jndiname);
stmt = con.createStatement();

// 1.创建存储过程show_students
String createProcedure1 = "create procedure show_students " + "as " + "select id, name,age " + "from students " + "order by id";
// 删除数据库中存在的同名过程
stmt.executeUpdate("if exists(select name from sysobjects "
+ "where name='show_students'and type='p') "
+ "drop procedure show_students");
stmt.executeUpdate(createProcedure1);

// 2.创建储存过程onestudent
String createProcedure2 = "create procedure onestudent "
+ "@stu_id int = null, " + "@name varchar(20) output, "
+ "@age int output " + "as " + "if @stu_id = null "
+ "BEGIN "
+ " PRINT 'ERROR: You must specify a stu_id value.' "
+ " RETURN "
+ "END "
+
// Get the sales for the specified cof_name and " +
// assign it to the output parameter. " +
"SELECT @name = name, @age = age " + "FROM coffees "
+ "WHERE id = @stu_id " + "RETURN ";
stmt.executeUpdate("if exists(select name from sysobjects "
+ "where name='onestudent'and type='p') "
+ "drop procedure onestudent");
stmt.executeUpdate(createProcedure2);

// 3.创建函数www.2cto.com
String createProcedure3 = "CREATE FUNCTION pubuse.ageofstu "
+
// Input cof_name
"(@stu_name varchar(20)) "
+ "RETURNS int "
+ // return sales
"AS " + "BEGIN " + " DECLARE @age int "
+ " SELECT @age = age " + " FROM student "
+ " WHERE name like @stu_name " + " RETURN @age "
+ "END ";
stmt.executeUpdate("if exists(select name from sysobjects "
+ "where name='ageofstu') "
+ "drop function pubuse.ageofstu");
stmt.executeUpdate(createProcedure3);
stmt.close();
con.close();
} catch (NamingException ex) {
System.err.println("Name Not Bound : " + ex.getMessage());
} catch (SQLException ex) {
System.err.println("SQLException : " + ex.getMessage());
}
System.out.println("程序执行结束！");
}
}

下面是使用存储过程的代码：

[cpp]
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import javax.naming.NamingException;

public class InvokeStoreProcdureofSQLServer {
public static void main(String[] args) {
Connection con = null;
String jndiname = "jdbcPool/mydatasource";
// 定义调用存储过程和函数的 SQL 语句
String callSQL1 = "{call show_students}";
String callSQL2 = "{call onestudent( , , )}";
String callSQL3 = "{ = call ageofstu( )}";

try {
con = DBCon.getConnectionFromPooledDataSource(jndiname);
// 调用第 1 个存储过程
CallableStatement cs = con.prepareCall(callSQL1);
ResultSet rs = cs.executeQuery();
System.out.println("第一个存储过程调用结果");
while (rs.next()) {