很多數據庫都支持在數據庫內部執行的函數。這種方法有幾個好處，包括更快的性能和改進的安全性。這些函數稱為存儲過程。存儲過程是用來封裝SQL語句來完成一個完整的業務功能，類似於面向對象裡面方法的概念。雖然它們通常是用SQL編寫的，但也可以用數據庫支持的任何編程語言編寫。隨著Java語言日趨流行，幾個數據庫廠商—Oracle（Oracle數據庫）和IBM（db2數據庫）都起用瞭Java語言創建存儲過程，還可以在不同數據庫之間移動存儲過程。
存儲過程可以支持三種類型的參數：IN,OUT和INOUT，這對於存儲過程在數據庫內部真正能做什麼來說，帶來瞭很大的靈活性。不管存儲過程是用什麼語言編寫的，它都能以一種標準的方式從Java應用程序調用。
首先，您需要創建一個CallableStatement對象。為瞭標識存儲過程和過程需要的參數的類型和數量，還要允許使用三種類型的調用。下面的清單說明瞭這三種類型（假定我們正在調用一個名為StudentList的存儲過程）：
n {call StudentList}如果過程不需要參數
n {call StudentList(?,?)}如果過程需要兩個參數
n {?=call StudentList(?,?)}如果參數需要兩個參數並返回一個
要想使用存儲過程，首先應該創建一個存儲過程！
代碼的實現
[java]
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
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.創建函數
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("程序執行結束！");
}
}
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
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.aiwalls.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()) {
String id = rs.getString(1);
String name = rs.getString(2);
String age = rs.getString(3);
System.out.println(id + " " + name + " " + age);
}
// 調用第 2 個存儲過程
cs = con.prepareCall(callSQL2);
cs.setString(1, "2");
cs.registerOutParameter(2, Types.CHAR);
cs.registerOutParameter(3, Types.INTEGER);
cs.execute();
String name = cs.getString(2);
int age = cs.getInt(3);
System.out.println("第二個存儲過程調用結果");
System.out.println("This student's name is " + name
+ " and age is " + age);
// 調用函數
cs = con.prepareCall(callSQL3);
cs.setString(2, "小羅");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
age = cs.getInt(1);
System.out.println("函數調用結果");
System.out.println("This student's name is " + age + ".");
cs.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("調用結束!");
}
}
摘自 Java教程