SOURCE CODE

From: ALEX LEE (anonymous_at_discussions.microsoft.com)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 07:11:03 -0700

create proc PROC_NAME
(

)
as
  ...
  ...
  create table #INS_DETAIL
  (
   SN int not null identity, -- 序号
   
   COMMODITY_CODE varchar(10) not null , -- 商品代码
   LIMIT_PRICE decimal(10, 3) not null default 0.00, -- 极限价格
   LIMIT_QTY decimal(15, 0) not null default 0.00, -- 极限数量
  )

  insert into #INS_DETAIL (COMMODITY_CODE, LIMIT_PRICE, LIMIT_QTY)
         select COMMODITY_CODE, LIMIT_PRICE, REF_QTY
         from KPMS_BASE..INS_DETAIL
         where ..

  -- 风险控制项
  create table #RISK_SCHEME_DETAIL
  (
    SN int not null identity, -- 序号
    RISK_ID varchar(32) not null, -- 风险标识
    RISK_STOP_VALUE numeric(18,5) not null default 0.00, -- 禁止值
    RISK_CONFIRM_VALUE numeric(18,5) not null default 0.00, -- 审批值
    RISK_WARN_VALUE numeric(18,5) not null default 0.00 -- 警告值
  )
        
  begin transaction
  save transaction tran_save_point
         
  -- 以下逐个判断商品的库存风险
  select @iCurrentRecord_I = 1, @vstrCommodityCode_I = null
  select @vstrCommodityCode_I = COMMODITY_CODE,
         @ncLimitPrice = LIMIT_PRICE,
         @ncLimitQty = LIMIT_QTY
         from #INS_DETAIL
         where SN = @iCurrentRecord_I
  while (@vstrSecuCode_I is not null)
  begin
    --
    insert into #RISK_SCHEME_DETAIL(RISK_ID, RISK_STOP_VALUE, RISK_CONFIRM_VALUE, RISK_WARN_VALUE)
            select RISK_ID, RISK_STOP_VALUE, RISK_CONFIRM_VALUE, RISK_WARN_VALUE
            from RISK_SCHEME_DETAIL
            where ...
    select @iCurrentRecord_R = 1, @vstrRiskID = null
    select @vstrRiskID = RISK_ID,
           @ncStopValue = RISK_STOP_VALUE,
           @ncConfirmValue = RISK_CONFIRM_VALUE,
           @ncWarnValue = RISK_WARN_VALUE
           from #RISK_SCHEME_DETAIL
           where SN = @iCurrentRecord_R
    while (@vstrRiskID is not null)
    begin
      ...
      ...
      -- 风险判断
      ...
      
      if exists 风险
        insert RISK_LOG ...
      ...
      -- fetch the next record
      ...
    end
  end
  
  commit transaction

  ...

go

Relevant Pages