[分享]一個復雜的存儲過程

HG

create trigger utri_wage on wage after insert
as
begin
declare @wage_ym varchar(6),@emp_sn smallint,@wage_day tinyint,@wage_overtime_hour decimal(5,2),@wage_lack_daily tinyint,@wage_lack_hour as decimal(5,2),@wage_alimony as tinyint,@wage_bonus as tinyint,@wage_compensation as tinyint
declare @wage_hour smallmoney,@wage_daily smallmoney,@wage_base smallint,@emp_manage tinyint
declare @emp_full tinyint,@wage_redeem_daily tinyint,@wage_redeem_cost smallmoney,@wage_deduct_daily tinyint,@wage_deduct_cost smallmoney,@wage_overtime_cost smallmoney,@wage_lack_cost smallmoney
declare  @wage_yield as smallmoney,@wage_receive smallmoney,@wage_out_tex smallmoney,@wage_sum smallmoney

select @wage_ym = inserted.wage_ym,@emp_sn = inserted.emp_sn,@wage_day = inserted.wage_day,@wage_overtime_hour = inserted.wage_overtime_hour,@wage_lack_daily = inserted.wage_lack_daily,@wage_lack_hour=inserted.wage_lack_hour,@wage_alimony=inserted.wage_out_alimony,@wage_bonus=inserted.wage_in_bonus,@wage_compensation=inserted.wage_compensation  from inserted
execute usp_wage_hour_daily_base @emp_sn,@wage_ym,@wage_hour output,@wage_daily output,@wage_base output,@emp_manage output
execute usp_comput_base @emp_sn,@wage_ym,@wage_day,@emp_full output,@wage_redeem_daily output,@wage_redeem_cost output,@wage_deduct_daily output,@wage_deduct_cost output,@wage_base output
set @wage_overtime_cost = @wage_hour * @wage_overtime_hour
set @wage_lack_cost = @wage_lack_daily * @wage_daily + @wage_lack_hour * @wage_hour
set @wage_yield = hrm.dbo.ufn_wage_ext(@emp_sn,@wage_ym)
set @wage_receive = (@wage_base+@wage_compensation+@wage_bonus+@wage_overtime_cost+@wage_redeem_cost+@emp_full+@wage_yield-@wage_deduct_cost-@wage_lack_cost)
set @wage_out_tex=hrm.dbo.ufn_personal_income_tax(@wage_receive)
set @wage_sum = @wage_receive-@wage_out_tex-@emp_manage-@wage_alimony

update wage set wage_deduct_daily = @wage_deduct_daily,wage_deduct_cost=@wage_deduct_cost,wage_redeem_daily = @wage_redeem_daily,wage_redeem_cost=@wage_redeem_cost,wage_in_full=@emp_full,wage_overtime_cost=@wage_overtime_cost,wage_lack_cost=@wage_lack_cost,wage_yield=@wage_yield,wage_receive=@wage_receive,wage_out_tex=@wage_out_tex,wage_sum=@wage_sum where wage_ym=@wage_ym and emp_sn =@emp_sn
end
------------------------------------------------------------------------------
此過程所運用的技術介紹
1。觸發器2.在觸發器出調用帶軟入輸出參數的存儲過程。3。在觸發器中調用有返回值自定子函數4。精確定位到某一記錄
-----------------------------------------------------------------------

zhengjialon

好！ 堪称技术典型。

zhuyiwen

一看见这么一满版的英文字母,就呆了![em26]

goodidea

ACCESS-CN.COM对于代码不如ACCXP.COM好看，没有缩格。

yuab

也提供一個在sql中怎樣使用指針的例子.
USE PROMIS

Begin Tran

declare @docid varchar(10)                                        --宣告文件編號
declare @ToDate datetime                                        --宣告日期

set @ToDate=Getdate()
set @docid='IR0000001'

declare RawminDetail Scroll cursor                                --宣告指針及指針來源
for
select LotID,DocID,PODetailID,Item_No,Quantity,Weight,Result,Result_Confirm,
          To_LocID,Area
from tbl_Store_Rawmin_Detail
where docid='IR0000001'  and Result_Confirm=0  and Result>0

open RawminDetail                                                --打開指針
declare @Lotid varchar(10),@Docids varchar(10),@PODetailID int,@Item_NO varchar(30),@Quantity numeric(18,4),
            @weight numeric(18,4),@Result int,@Result_Confirm bit,@To_LocID varchar(5),@Area varchar(6)

Fetch  next  from RawminDetail                                --一筆一筆地向后讀取記錄
into @Lotid,@Docids,@PODetailID,@Item_NO,@Quantity,@weight ,@Result ,@Result_Confirm ,@To_LocID ,@Area

WHILE (@@Fetch_Status=0)                                        --循環指標集直到指針為零時
       Begin
        --查找該指針的當筆記錄在tbl_Store_QOH中的記錄數, 如果不存在記錄則將該指標插入. 存在則更改數量.
        Select Locid,Item_NO from tbl_Store_QOH where (Locid=@To_Locid) and (Item_NO=@Item_NO)
        if @@rowcount =0
                begin
                insert tbl_Store_QOH (Item_NO,Locid,QOH,Lotid)
                values(@Item_NO,@To_Locid,@Quantity,@Lotid)
                end
        else        
                begin
                update dbo.tbl_Store_QOH
                set QOH=QOH+@Quantity,LotID=@LotID
                Where (LocID=@To_LocID) and (Item_NO=@Item_NO)
                end

        --將記錄插入到tbl_Store_TranDtl表中
        declare @QOH numeric(18,4)
        Set @QOH=(Select QOH from tbl_Store_QOH where (Locid=@To_Locid) and (Item_NO=@Item_NO))
        insert tbl_Store_TranDtl(TranDtlID,Lotid,LocID,DocID,TransactType,Item_NO,Quantity,QOH,Weight,Tran_Time,Area)
        values(@Lotid,@Lotid,@To_Locid,@DocID,'IN' ,@Item_NO,@Quantity, @QOH, @Weight,@ToDate,@Area)

        --更新tbl_Purch_PO_Detail采購單細節表中的ReceQuantity已收數量, Recedate最后收貨日期, Finish是否完成
        update tbl_Purch_PO_Detail
        set ReceDate=@ToDate,ReceQuantity=ReceQuantity+@Quantity,
                      Finish=Case  when ReceQuantity+@Quantity=Quantity then 1 else 0 end
        where DetailID=@PODetailID
       
        --更新本筆指針記錄Result_Confirm是否批核過帳
        update tbl_Store_RawMin_Detail
        set Result_Confirm=1
        Where Current of RawminDetail

        Fetch  next  from RawminDetail                        --一筆一筆地向后讀取記錄
        into @Lotid,@Docids,@PODetailID,@Item_NO,@Quantity,@weight ,@Result ,
               @Result_Confirm ,@To_LocID ,@Area
       End

if @@Error >0
Rollback Tran
else
COMMIT TRAN

close RawminDetail                                                --關閉指針
deallocate RawminDetail                                        --移除指針

--Fetch Next from RawminDetail                        --到下一筆
--Fetch  Prior  from RawminDetail                        --到前一筆
--Fetch  Absolute 1  from RawminDetail                --到指定的筆
--Fetch  Relative 3  from RawminDetail                --目前位置向后跳躍位置
--Fetch  Last  from RawminDetail                        --到最后一筆
--Select @@cursor_rows                        --讀取指標筆數(測試用)

HG

服務端游標編程，也是典型技術。但是游標對性能上有所影響，所以要慎重使用。