存储过程 例子

USE [rtcard]
GO
/****** Object:  StoredProcedure [dbo].[prcd_cw_balance_query]    Script Date: 10/12/2009 09:46:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-- =============================================
-- Author:  wuxiaoyong
-- Create date: 20081231
-- Description: 余额查询
-- =============================================
ALTER PROCEDURE [dbo].[prcd_cw_balance_query]
 @date varchar(20),
 @employeeId varchar(10),
 @clientId varchar(10),
 @cardType varchar(3),
 @cardNo varchar(25),
 @supplyBatchNo varchar(10),
 @sysAreaId varchar(6),
 @count bigint output,
 @balance_all bigint output
AS
declare @sql_str nvarchar(1000)
 declare @balance bigint
 declare @employee_name varchar(100)
 declare @title varchar(25)
 declare @account_net_type varchar(3)
 declare @account_id varchar(10)
 declare @pay_account_id varchar(10)
 declare @pay_money bigint
 declare @pay_account_balance bigint
 declare @rcv_account_id varchar(10)
 declare @rcv_money bigint
 declare @rcv_account_balance bigint 
 DECLARE @var_card varchar(25)
BEGIN
 SET NOCOUNT ON
 set @sql_str=""

 if(@supplyBatchNo is not null and @supplyBatchNo!="")--按充值批次ID查询模式
  set @sql_str=" select distinct card_no from card_supply cs,supply_card_log scl where cs.supply_card_id=scl.supply_card_id and scl.supply_batch_no="+@supplyBatchNo+" and scl.sys_area_id='"+@sysAreaId+"'"
 else
  begin
   set @sql_str=" select card_no from card_info ci,account_info ai
         where ci.account_id=ai.account_id and ci.sys_area_id='"+@sysAreaId+"'"
   if(@employeeId is not null and @employeeId!="")
    set @sql_str=@sql_str+" and sales_id="+@employeeId
   if(@clientId is not null and @clientId!="")
    set @sql_str=@sql_str+" and buy_card_user_id="+@clientId
   if(@cardType="0")
    set @sql_str=@sql_str+" and (account_net_type='2' or account_net_type='0')"
   if(@cardType="1")
    set @sql_str=@sql_str+" and account_net_type='1'"
   if(@cardNo is not null and @cardNo!="")
    set @sql_str=@sql_str+" and card_no='"+@cardNo+"'"
  end
 --查询符合条件卡号存入临时表
 create table #card_table(card_no varchar(25))
 exec("insert into #card_table "+@sql_str)
 --定义临时表 功能：保存请求所须记录
 --create table #temp_table(supply_card_id varchar(10),employee_name varchar(100),title varchar(100),account_net_type varchar(3),card_no varchar(25),balance bigint)

 delete temp_table
 --查询符合条件卡号
 set @var_card=""
 DECLARE card_cursor CURSOR LOCAL FOR
    select card_no from #card_table
 open card_cursor
 fetch next from card_cursor into @var_card
 while(@@fetch_status=0)
 BEGIN
  --查询余额
  exec prcd_cw_getBalance @date,@var_card,@balance output
  --查询业务员，客房名称，卡类
  select @employee_name=employee_name,@title=title,@account_net_type=account_net_type from account_info ai,card_info ci
   left join employee_info ei on ci.sales_id=ei.employee_id
            left join buy_card_user bcu on ci.buy_card_user_id=bcu.buy_card_user_id
   where ci.account_id=ai.account_id   and ci.card_no=@var_card
  --将查询结果存入临时表#temp_table
  --insert into #temp_table(supply_card_id,employee_name,title,account_net_type,card_no,balance)
  -- values(@supplyBatchNo,@employee_name,@title,@account_net_type,@var_card,@balance)
  --test
  insert into temp_table(supply_card_id,employee_name,title,account_net_type,card_no,balance)
   values(@supplyBatchNo,@employee_name,@title,@account_net_type,@var_card,@balance)

  fetch next from card_cursor into @var_card
 END
 close card_cursor

 --select * from temp_table order by card_no
 
--test
 --select card_no,balance from #temp_table order by card_no

 select @balance_all=sum(balance) from temp_table

 set @sql_str=" from temp_table"
 set @sql_str = 'set @temp = (select  count(*)'+@sql_str+')'
 exec sp_executesql @sql_str,N'@temp bigint out',@count out

 if(@balance_all is null)set @balance_all=0;
END

 

 

USE [rtcard]
GO
/****** Object:  StoredProcedure [dbo].[prcd_cw_getBalance]    Script Date: 10/12/2009 09:48:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/**
 wuxiaoyong
**/
--计算每张卡的余额
ALTER PROCEDURE [dbo].[prcd_cw_getBalance]
@date varchar(20),
@card_no varchar(22),
@balance bigint output
AS
declare @account_id varchar(10)
declare @pay_account_id varchar(10)
declare @pay_money bigint
declare @pay_account_balance bigint
declare @rcv_account_id varchar(10)
declare @rcv_money bigint
declare @rcv_account_balance bigint 
declare @card_balance bigint
declare @account_balance bigint
BEGIN
 SET NOCOUNT ON;
 --计算卡的帐户ID
 select @account_id=ci.account_id,@account_balance=account_balance from card_info ci,account_info ai where ci.account_id=ai.account_id and card_no=@card_no

 --计算卡余额--
 set @card_balance=0
 --按时间查询期限内的最后一笔交易记录
 select top 1
  @pay_account_id=to1.pay_account_id,  
  @pay_money=pay_money,
  @pay_account_balance=pay_account_balance,
  @rcv_account_id=to1.rcv_account_id,
  @rcv_money=rcv_money,
  @rcv_account_balance=rcv_account_balance
 from trade_log tl,trade_order to1 where (to1.pay_account_id=@account_id or to1.rcv_account_id=@account_id)
     and to1.order_id=tl.order_id and to1.order_status=1 and (pay_status=3 or pay_status=4) and to1.pay_date<@date order by to1.pay_date desc  
  --根据帐户在交易中所处的帐户角色类型计算在期限末期的卡帐户余额
 if(@account_id=@pay_account_id and @account_id!=@rcv_account_id) --是支付帐户
  set @card_balance=@pay_account_balance-@pay_money
 else if(@account_id!=@pay_account_id and @account_id=@rcv_account_id) --是接收帐户
  set @card_balance=@rcv_account_balance+@rcv_money
 else --既是支付帐户又是接收帐户
  set @card_balance=@pay_account_balance-@pay_money+@rcv_money
  
  --如果在该期限内，该卡账户没有交易记录，则要重新取交易记录，取无期限限制的第一笔交易记录来计算之前的帐户余额
  if(@card_balance is null)
  begin
   --按时间查询期限内的最后一笔交易记录
   select top 1
    @pay_account_id=to1.pay_account_id,  
    @pay_money=pay_money,
    @pay_account_balance=pay_account_balance,
    @rcv_account_id=to1.rcv_account_id,
    @rcv_money=rcv_money,
    @rcv_account_balance=rcv_account_balance
   from trade_log tl,trade_order to1 where (to1.pay_account_id=@account_id or to1.rcv_account_id=@account_id)
       and to1.order_id=tl.order_id and to1.order_status=1 and (pay_status=3 or pay_status=4) order by to1.pay_date asc
    --根据帐户在交易中所处的帐户角色类型计算在期限末期的卡帐户余额
   if(@account_id=@pay_account_id) --是支付帐户
    set @card_balance=@pay_account_balance
   else
    set @card_balance=@rcv_account_balance
  end

 if(@card_balance is null) set @card_balance=@account_balance
 set @balance = @card_balance
END

 

 

 

java 调用

   conn = (Connection) pool.requestObject();
   cstmt = conn.prepareCall("{call prcd_cw_balance_query(?,?,?,?,?,?,?,?,?)}");
   cstmt.setString(1, date);
   cstmt.setString(2, employee);
   cstmt.setString(3, client);
   cstmt.setString(4, cardType);
   cstmt.setString(5, cardNo);
   cstmt.setString(6, supplyCardId);
   cstmt.setString(7, sysAreaID);
   cstmt.registerOutParameter(8, java.sql.Types.BIGINT);
   cstmt.registerOutParameter(9, java.sql.Types.BIGINT);
   // 取总记录数
   cstmt.execute(); //获取结果集
   cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);//第二个结果--记录数
   //金额合计
   pageContext.setAttribute("balance_all", cstmt.getObject(9));

   long recordCount = cstmt.getLong(8);