第一種情況：不帶輸出參數(shù)的
1編寫(xiě)存儲(chǔ)過(guò)程
create procedure getsum
@n int =0
as
declare @sum int
declare @i int
set @sum=0
set @i=0
while @i<=@n begin
set @sum=@sum+@i
set @i=@i+1
end
print 'the sum is '+ltrim(rtrim(str(@sum)))
2.在SQL中執(zhí)行測(cè)試存儲(chǔ)過(guò)程
exec getsum 100
3. 在JAVA中調(diào)用存儲(chǔ)過(guò)程（北京北大青鳥(niǎo)）
import java.sql.*;
public class ProcedureTest
{
public static void main(String args[]) throws Exception
{
//加載驅(qū)動(dòng)
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
//獲得連接
Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");
//創(chuàng)建存儲(chǔ)過(guò)程的對(duì)象
CallableStatement c=conn.divpareCall("{call getsum(?)}");
//給存儲(chǔ)過(guò)程的參數(shù)設(shè)置值
c.setInt(1,100); //將第一個(gè)參數(shù)的值設(shè)置成100
//執(zhí)行存儲(chǔ)過(guò)程
c.execute();
conn.close();
}
}
第二種情況：帶輸出參數(shù)的（北京北大青鳥(niǎo)）
1.帶參數(shù)的帶輸出參數(shù)的
alter procedure getsum
@n int =0,
@result int output
as
declare @sum int
declare @i int
set @sum=0
set @i=0
while @i<=@n begin
set @sum=@sum+@i
set @i=@i+1
end
set @result=@sum
2.在查詢(xún)分析器中執(zhí)行
declare @myResult int
exec getsum 100,@myResult output
print @myResult
3.在JAVA中調(diào)用
import java.sql.*;
public class ProcedureTest
{
public static void main(String args[]) throws Exception
{
//加載驅(qū)動(dòng)
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
//獲得連接
Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");
//創(chuàng)建存儲(chǔ)過(guò)程的對(duì)象
CallableStatement c=conn.divpareCall("{call getsum(?,?)}");
//給存儲(chǔ)過(guò)程的第一個(gè)參數(shù)設(shè)置值
c.setInt(1,100);
//注冊(cè)存儲(chǔ)過(guò)程的第二個(gè)參數(shù)
c.registerOutParameter(2,java.sql.Types.INTEGER);
//執(zhí)行存儲(chǔ)過(guò)程
c.execute();
//得到存儲(chǔ)過(guò)程的輸出參數(shù)值
System.out.println (c.getInt(2));
conn.close();
}
}
（北京北大青鳥(niǎo)）
