sybase语法备忘

最新推荐文章于 2021-04-15 16:16:34 发布

ITWIT

最新推荐文章于 2021-04-15 16:16:34 发布

阅读量239

点赞数 1

分类专栏： DB 文章标签： sybase float date insert output table

本文链接：https://blog.csdn.net/itwit/article/details/7193795

版权

DB 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

create procedure aa_set_rate(
                               @p_company varchar(50),
                               @p_period  varchar(50),
                               @p_step_id varchar(50),
                               @p_from_business_code varchar(50),
                               @p_to_business_code varchar(50), 
                               @p_rate float,
                               @p_source varchar(50))
as
   declare  @v_count int,
            @v_from_business_code varchar(50),
            @v_new_rate float,
            @v_rate_id  varchar(50)

   declare c_rate cursor for
          SELECT from_business_code,
                 new_rate = SUM(rate) * @p_rate 
            FROM aa_rates
           WHERE company_code = @p_company
             AND period = @p_period
             AND to_business_code = @p_from_business_code
           GROUP BY From_business_code 
  BEGIN

    if @p_from_business_code = @p_to_business_code
        print 'p_to_business_code = %1! ',  @p_from_business_code
       return 0 
       
    if @v_count > 0 
       begin 
          open c_rate
          fetch /*next from*/ c_rate into @v_from_business_code,@v_new_rate
            while @@sqlstatus  = 0 
                begin 
                    exec aa_get_next_secquence  @p_table_name = 'AA_RATES',@p_next_sequence =@v_rate_id  output

                    INSERT INTO aa_rates
                      (rate_id,
                       company_code,
                       period,
                       from_business_code,
                       to_business_code,
                       rate,
                       SOURCE,
                       step_id,
                       creation_date,
                       created_by,
                       last_update_date,
                       last_updated_by)
                       VALUES(
                             @v_rate_id ,                            
                             @p_company,
                             @p_period,
                             @v_from_business_code,
                             @p_to_business_code,
                             @v_new_rate,
                             'INDIRECT',
                             @p_step_id,
                             getdate(),
                             '0',
                             getdate(),
                             '0')
                    fetch /*next from*/ c_rate into @v_from_business_code,@v_new_rate
               end           
               close c_rate
               deallocate cursor c_rate
       end  
END