java调用SQLServer2005的存储过程

1、在java向SQLServer2005的SP传入一个参数。

2、写SQLServer2005的SP，此SP有返回结果。

3、java获取SQLServer2005的SP的返回结果。

具体实现步聚和代码:

1、java代码，以下代码是调用一个名叫P_ARAgingSummaryForJSPCal的SP，有五个参数，其中第一个是java端输入的，第二，三，四，五个是SP返回的结果。所以你会看第一个参数用c.setString(1, customer);来set,而其它几个是c.registerOutParameter（）来获取。

     在c.execute()之后，表示java已执行了SP，接着就可以获取SP的返回结果了。这个并不难理解。 

package oa.soinfo;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Vector;

public class SAPInfo {
	
	public Vector getAmountByCustomer(String customer, Connection conn) throws Exception{
		Vector vt = new Vector();
		CallableStatement c =null;
		try{
			c=conn.prepareCall("{call P_ARAgingSummaryForJSPCal(?,?,?,?,?)}");  
			c.setString(1, customer);
			c.registerOutParameter(2,java.sql.Types.NUMERIC); 
			c.registerOutParameter(3,java.sql.Types.NUMERIC); 
			c.registerOutParameter(4,java.sql.Types.NUMERIC); 
			c.registerOutParameter(5, java.sql.Types.NUMERIC);
			c.execute();
			BigDecimal creditLimit = c.getBigDecimal(2);
			BigDecimal balance = c.getBigDecimal(3);
			BigDecimal notYetDue = c.getBigDecimal(4);
			BigDecimal unRcdAmt = c.getBigDecimal(5);
			vt.add(creditLimit);
			vt.add(balance);
			vt.add(notYetDue);
			vt.add(unRcdAmt);
			
		}catch(Exception e){
			System.out.println("getAmountByCustomer occur error: "+ e.getMessage());
		}finally{
			if(c==null)
				c.close();
		}
		return vt;		
	}
}

 2、SQLServer2005的SP，这里你只需关心开始和结尾部分，看它是怎么定义，接收传过来的值，和如何返回结果的就行了。至于其中的程序的逻辑无需太关心。

       稍稍讲解下这个SP的定义部分，

@BPCODE varchar(10) ,  ---这个是java端刚才定义的c.setString(1, customer);也就是@BPCODE是用来接收这个customer的值

@creditLimit numeric(18,2) output,---这是到时返回的结果值，看到了没，它定义是用关键字output, 以下都差不多是这个意思。

@balance numeric(18,2) output,

@notYetDue numeric(18,2) output,

@UnRcdAmt numeric(18,2) output

再看看它是如何返回结果的，用的就是这种方法，也很容易明白的

select @creditLimit=creditLine, @balance= 0, @notYetDue=0, @UnRcdAmt=0 from ocrd where cardcode =@BPCODE

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		Lin Wu
-- Create date: 2011-07-27
-- Description:	供JSP页面调用，提供客户代码计算此客户的Credit Limit, Balance和Not Yet Due Amt
-- =============================================
ALTER PROCEDURE [dbo].[P_ARAgingSummaryForJSPCal]
@BPCODE varchar(10) ,
@creditLimit numeric(18,2) output,
@balance numeric(18,2) output,
@notYetDue numeric(18,2) output,
@UnRcdAmt numeric(18,2) output 

AS
declare @cutoff datetime
declare @cnt int
BEGIN
SET NOCOUNT ON;
set @cutoff = getdate()
set @cnt =0;
create table #tmptable( customerCode varchar(15), creditLimit numeric(19,6),balance numeric(19,6), TCurrent numeric(19,6), UnRcdAmt numeric(18,2)) 



;with T1000 as (
 SELECT T0.TRANSID, T0.SHORTNAME, Line_ID,
        T0.DEBIT-T0.CREDIT AS [BALANCE(LC)],
        T0.SYSDEB-T0.SYSCRED AS [BALANCE(SYS)], 
        T0.FCDEBIT-T0.FCCREDIT AS [BALANCE(FC)]
FROM JDT1 T0
WHERE SHORTNAME = @BPCODE AND
        T0.REFDATE <= @CUTOFF And T0.TRANSTYPE Not In (18, 19)
		AND (CreatedBy Not In (
					Select DocEntry 
					From ORCT 
					Where Canceled = 'Y' 
						And DocType = 'C' 
						And NOt Exists (Select 1 From ITR1 Where itr1.SrcObjAbs =orct.DocEntry And itr1.SrcObjTyp = 24 ) 
					))
 /*
UNION ALL

SELECT TransId, ShortName, TransLine, 
		CASE WHEN CredDeb = 'D' THEN Amount ELSE -1 * Amount END,
		CASE WHEN CredDeb = 'D' THEN AmountSC ELSE -1 * AmountSC END,
		CASE WHEN CredDeb = 'D' THEN AmountFC ELSE -1 * AmountFC END
FROM CASE1 T0
 */

UNION ALL
--LESS RECONCILIATED VALUE
select Transid, Shortname, TransRowID,
       CASE WHEN IsCredit = 'D' THEN ReconSum * -1 ELSE ReconSum END AS [BALANCE(LC)],
       CASE WHEN IsCredit = 'D' THEN ReconSumSC * -1 ELSE ReconSumSC END AS [BALANCE(SYS)],
       CASE WHEN IsCredit = 'D' THEN ReconSumFC * -1 ELSE ReconSumFC END AS [BALANCE(FC)]
from oitr left outer join itr1 on   oitr.reconnum = itr1.reconnum
where shortname = @BPCODE AND 
      OITR.RECONDATE <= @CUTOFF And SrcObjTyp Not In (18, 19)
),
T10000 as (
SELECT 
        T1000.TRANSID, T1000.SHORTNAME, SUM(T1000.[BALANCE(LC)]) AS [BALANCE(LC)],
        SUM(T1000.[BALANCE(SYS)]) AS [BALANCE(SYS)], SUM(T1000.[BALANCE(FC)]) AS [BALANCE(FC)]
from T1000
WHERE
Exists(Select 1 From OCRD Where T1000.ShortName = OCRD.CardCode)
GROUP BY T1000.TRANSID, T1000.SHORTNAME
HAVING SUM(T1000.[BALANCE(LC)]) <> 0
),
T20000 as (
 --FIND ALL JE TO SHOW INFO LIKE LINEMEMO, TRANSTYPE ETC..
        SELECT 
                TRANSID, TRANSTYPE, SHORTNAME, 
                REFDATE, DUEDATE, LINEMEMO, REF1, 
                CASE WHEN ISNULL(FCCURRENCY,'')='' THEN 
                (Select MainCurncy From OADM ) ELSE FCCURRENCY END AS CURRENCY,
                DEBIT-CREDIT AS [AMOUNT(LC)],
                SYSDEB-SYSCRED AS [AMOUNT(SYS)], 
                FCDEBIT-FCCREDIT AS [AMOUNT(FC)]
--              CASE WHEN TRANSTYPE = 30 AND CREDIT > 0 THEN (DEBIT+CREDIT) * -1 ELSE DEBIT+CREDIT END AS [AMOUNT(LC)],
--              CASE WHEN TRANSTYPE = 30 AND CREDIT > 0 THEN (SYSDEB+SYSCRED) * -1 ELSE SYSDEB+SYSCRED END AS [AMOUNT(SYS)], 
--              CASE WHEN TRANSTYPE = 30 AND CREDIT > 0 THEN (FCDEBIT+FCCREDIT) * -1 ELSE (FCDEBIT+FCCREDIT) END AS [AMOUNT(FC)]
         FROM JDT1
),
T40000 as (
--      FIND UDF RELATED TO DOCUMENT
        SELECT 
                T0.[DocNum], T0.[ObjType], T0.CardCode, T1.[PymntGroup], T0.[NumAtCard], T3.[GroupName], 
                T4.[SlpName], T0.JrnlMemo, T0.DocCur
        FROM
        OINV T0 
        INNER JOIN OCTG  T1 ON T0.GroupNum = T1.GroupNum 
        INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode 
        INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
        INNER JOIN OSLP T4 ON T4.SlpCode = T0.SlpCode
        UNION ALL
--      FIND UDF RELATED TO DOCUMENT
        SELECT 
                T0.[DocNum], T0.[ObjType], T0.CardCode, T1.[PymntGroup], T0.[NumAtCard], T3.[GroupName], 
                T4.[SlpName], T0.JrnlMemo, T0.DocCur
        FROM ORIN T0 
        INNER JOIN OCTG T1 ON T0.GroupNum = T1.GroupNum
        INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode 
        INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
        INNER JOIN OSLP T4 ON T4.SlpCode = T0.SlpCode
		UNION ALL
		SELECT 
				T0.DocNum, T0.ObjType, T0.CardCode, Null, Null, Null, Null, T0.JrnlMemo, T0.DocCurr
		FROM ORCT T0
		Where
		T0.DocType = 'C'
		UNION ALL
		SELECT 
				T0.DocNum, T0.ObjType, T0.CardCode, Null, Null, Null, Null, T0.JrnlMemo, T0.DocCurr
		FROM OVPM T0
		Where
		T0.DocType = 'C'
),
T30000 as (
    --FIND BP INFO
        SELECT 
                T30.CARDCODE, T30.CARDNAME, T32.GroupName, T30.CNTCTPRSN, T30.CURRENCY, 
                T30.Building,T30.county,ISNULL(T30.CARDFNAME,'') AS CARDFNAME,
                T31.STREET, T31.BLOCK, T31.CITY, T30.PHONE1, T30.FAX, 
				IsNull(T30.U_BU, '') U_BU,ISNULL(T35.NAME,'') AS [NAME], T34.PymntGroup CreditTerm, T30.CreditLine, T33.SlpName ,isnull(T30.U_UnPostAmt,0) as 'UnRcdAmt'
        FROM OCRD T30 
        LEFT JOIN CRD1 T31 ON T30.CardCode = T31.CardCode AND T30.BILLTODEF = T31.ADDRESS AND T31.ADRESTYPE = 'B'
        LEFT JOIN OCRG T32 ON T30.GroupCode = T32.GroupCode
        LEFT JOIN OSLP T33 ON T33.SlpCode = T30.SlpCode
		INNER JOIN OCTG T34 On T30.GroupNum = T34.GroupNum 
		LEFT JOIN [@BUSUNIT] T35 ON T35.CODE = T30.U_BU
        --WHERE T31.ADRESTYPE = 'B'
),
Tbl as (
SELECT 
        IsNull(T30000.CardCode, T10000.Shortname) AS [Customer Code], 
		T30000.CardName As [Customer Name],
		T30000.CARDFNAME AS [Customer foreign Name],
        T30000.GroupName AS [Customer Group],
        T30000.SlpName AS [Salesman],
		T30000.CreditTerm As [Credit Term],
		T30000.CreditLine As [Credit Limit],
		T30000.U_BU As [BU],
		T30000.NAME AS [BU DESCRIPTION],
        T30000.UnRcdAmt as [UnRcdAmt],
        T20000.CURRENCY As [Currency],

		T10000.TransID,
		T20000.TransType As [TransType],
        T40000.DocNum AS [B1 Inv/CN No.],
		T40000.NumAtCard As [BPCS Inv. No.],
		T40000.JrnlMemo,
        --T30000.CardName AS [Customer Name],

        T30000.Street AS [Address Line1], 
        T30000.Block AS [Address Line2], 
        T30000.City AS [Address Line3],
        T30000.County AS [Address Line4],
        T30000.CNTCTPRSN AS [Contact Person], 
        T30000.Phone1 AS [Contact No.],
        T30000.Fax AS Fax,  
        T20000.RefDate AS Date, 
        T20000.DueDate, 
        --T40000.[NumAtCard],
        T40000.[PymntGroup] AS [Payment Term], 

	    
        CASE WHEN T20000.CURRENCY = (Select MainCurncy From OADM ) THEN
                T20000.[Amount(LC)]
        ELSE
                T20000.[Amount(FC)] 
        END AS [Amount],
        T20000.[Amount(LC)],
        CASE WHEN T20000.CURRENCY = (Select MainCurncy From OADM ) THEN
                (T20000.[Amount(LC)] - T10000.[Balance(LC)]) 
        ELSE
                (T20000.[Amount(FC)] - T10000.[Balance(FC)]) 
        END AS [Settled Amount],
        CASE WHEN T20000.CURRENCY = (Select MainCurncy From OADM ) THEN
                T10000.[Balance(LC)]
        ELSE
                T10000.[Balance(FC)] 
        END AS Balance,
T10000.[Balance(LC)],
        [Current] = 
        CASE WHEN DATEDIFF(DAY,T20000.DUEDATE, @CUTOFF) <= 0 THEN
                CASE WHEN T20000.CURRENCY = (Select MainCurncy From OADM ) THEN
                        T10000.[BALANCE(LC)]
                ELSE
                        T10000.[BALANCE(FC)] 
                END
        ELSE
                0
        END,

/**/
        [CurrentLC] = 
        CASE WHEN DATEDIFF(DAY,T20000.DUEDATE, @CUTOFF) <= 0 THEN                
                        T10000.[BALANCE(LC)]
        ELSE
                0
        END
FROM T10000 LEFT OUTER JOIN T20000 ON T10000.TRANSID = T20000.TRANSID AND T10000.SHORTNAME = T20000.SHORTNAME 
     LEFT OUTER JOIN T40000 ON CONVERT(NVARCHAR,T40000.DocNum) = T20000.REF1 AND T40000.OBJTYPE = T20000.TRANSTYPE
     LEFT OUTER JOIN T30000 ON IsNull(T40000.CardCode, T10000.SHORTNAME) = T30000.CARDCODE
Where
IsNull(T40000.CardCode, T10000.SHORTNAME)= @BPCODE
) 
 
insert into #tmpTable select [Customer Code],  max([Credit Limit]),  sum(Balance), sum([Current]), max(unRcdAmt) from Tbl group by [Customer Code]
select @cnt= count(*) from #tmpTable
if(@cnt>0)
   select @creditLimit=creditLimit, @balance= balance, @notYetDue=Tcurrent, @UnRcdAmt =UnRcdAmt  from #tmpTable
else if(@cnt=0)
   select @creditLimit=creditLine, @balance= 0, @notYetDue=0, @UnRcdAmt=0 from ocrd where cardcode =@BPCODE

--select * from #tmpTable
END

 最后由java来获取SP返回的结果，都在第一步的java中有了。

祝大家成功