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中有了。
祝大家成功