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 ONset QUOTED_IDENTIFIER ONgo-- =============================================-- 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 ASdeclare @cutoff datetimedeclare @cnt intBEGINSET 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 T0WHERE 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 ALLSELECT 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 ENDFROM CASE1 T0 */UNION ALL--LESS RECONCILIATED VALUEselect 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.reconnumwhere 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 T1000WHEREExists(Select 1 From OCRD Where T1000.ShortName = OCRD.CardCode)GROUP BY T1000.TRANSID, T1000.SHORTNAMEHAVING 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.SlpCodeUNION ALLSELECT T0.DocNum, T0.ObjType, T0.CardCode, Null, Null, Null, Null, T0.JrnlMemo, T0.DocCurrFROM ORCT T0WhereT0.DocType = 'C'UNION ALLSELECT T0.DocNum, T0.ObjType, T0.CardCode, Null, Null, Null, Null, T0.JrnlMemo, T0.DocCurrFROM OVPM T0WhereT0.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.SlpCodeINNER 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        ENDFROM 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.CARDCODEWhereIsNull(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 #tmpTableif(@cnt>0)   select @creditLimit=creditLimit, @balance= balance, @notYetDue=Tcurrent, @UnRcdAmt =UnRcdAmt  from #tmpTableelse if(@cnt=0)   select @creditLimit=creditLine, @balance= 0, @notYetDue=0, @UnRcdAmt=0 from ocrd where cardcode =@BPCODE--select * from #tmpTableEND

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

祝大家成功