存储过程

请根据下面的需求提供相关的数据库存储过程

１.  查询参数-- 开始时间,结束时间,公司号

查询条件:卡的有效期在查询的时间段内将过期的该公司的员工卡信息,卡的状态应该是有效状态的卡

返回字段:公司名称,持卡人号,持卡人姓名,卡号,卡的有效期,最后一次充值的时间

drop procedure proc_onOverduce;

-----存储过程
create procedure proc_onOverduce
       @startDate varchar(10),  （输入参数）
       @endDate varchar(10),   （输入参数）
       @customerID int　　（输入参数）
as
begin
      select
             customer.customer_name as customerName,
             cardholder.cardholder_id as cardholder,
             cardholder.CARDHOLDER_NAME as cardholderName,
             cardInfo.Card_No as cardNo,
             to_date(cardInfo.Valid_Date,'yyyy-MM-dd') as cardValidDate,
             ta.txtDate as txtDate
            
      from
             TB_ENT_CUSTOMER customer,
             TB_ENT_CARDHOLDER cardholder,
             TB_ENT_CARDHOLDER_CARDLIST cardholderlist,
             TB_CARD_INFO cardInfo,
             (
               select
                       txnhis.pan as pan,
                      max(to_char(to_date(txnHis.Inst_Date||txnHis.Inst_Time,'yyyyMMddhh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')) as txtDate
                      
               from
                       tbl_txn_his txnhis
               where
                       txnhis.txn_num = '7000'
                       and txnHis.REVSAL_FLAG='0'
                       AND txnHis.TRANS_STATE!='0'
                       and txnHis.resp_code='00'
               group by   txnhis.pan     
             )ta
      where
             customer.customer_id = cardholder.customer_id
             and ta.pan = cardInfo.Card_No
             and cardholder.cardholder_id = cardholderlist.cardholder_id
             and cardInfo.Card_No = cardholderlist.card_no
             and cardholderlist.card_state = 9
             and customer.customer_id = @customerID
             and cardInfo.Valid_Date >=  @startDate
             and cardInfo.Valid_Date <=  @endDate
end;

exec proc_onOverduce('20091201','20102010',1);　（传参）
      

 

 

２.查询参数-- 开始时间,结束时间,公司号

查询条件: 在查询的时间段内该公司的每张员工卡的消费充值信息, 包括消费和消费撤销,预授权完成和预授权完成撤销.

返回字段: 公司名称,持卡人号,持卡人姓名,卡号,卡的有效期,时间段内发生的充值金额, 时间段内发生的充值次数, 时间段内发生的交易金额, 时间段内发生的交易次数

drop procedure proc_personnelTxn;

-----存储过程
create procedure proc_personnelTxn
       @startDate varchar(10), 
       @endDate varchar(10),
       @customerID int
as
begin
      
      select
             customer.customer_name as customerName,
             cardholder.cardholder_id as cardholder,
             cardholder.CARDHOLDER_NAME as cardholderName,
             cardInfo.Card_No as cardNo,
             to_date(cardInfo.Valid_Date,'yyyy-MM-dd') as cardValidDate,
             ta.AddTxnAmt as AddTxnAmt,
             ta.AddTxncnt as AddTxncnt,
             ta.txnAmt as txnAmt,
             ta.txncnt as txncnt
            
      from
             TB_ENT_CUSTOMER customer,
             TB_ENT_CARDHOLDER cardholder,
             TB_ENT_CARDHOLDER_CARDLIST cardholderlist,
             TB_CARD_INFO cardInfo,
             (
              select
                     cardstat.card_no as cardno,
                     decode(dictinfo.dict_id , 7000,cardstat.txn_amt/100,0) as AddTxnAmt,
                     decode(dictinfo.dict_id , 7000, cardstat.txn_cnt,0) as AddTxncnt,
                     decode(dictinfo.dict_id ,1105,cardstat.txn_amt/100,3105,cardstat.txn_amt/100,1095,cardstat.txn_amt/100,3095, cardstat.txn_amt/100,0) as txnAmt,
                     decode(dictinfo.dict_id ,1105,cardstat.txn_cnt,3105,cardstat.txn_cnt,1095,cardstat.txn_cnt,3095,cardstat.txn_cnt,0) as txncnt,
                     to_char(cardstat.sett_date) as settdate
              from
                     TB_BAT_CARD_STAT cardStat,
                     tb_ent_dict_info dictinfo
              where
                     dictinfo.dict_type = 999
                     and dictinfo.dict_id = cardStat.Txn_Type
                     and cardStat.Txn_Type in ( 7000,1105,3105,1095,3095)
             )ta
      where
             customer.customer_id = cardholder.customer_id
             and ta.cardno = cardInfo.Card_No
             and cardholder.cardholder_id = cardholderlist.cardholder_id
             and cardInfo.Card_No = cardholderlist.card_no
             and cardholderlist.card_state = 9
             and ta.settdate >=  @startDate
             and ta.settdate <=  @endDate
             and customer.customer_id = @customerID
end

 

exec proc_onOverduce '20091201','20102010',1