sybase 存储过程

原创 2007年09月12日 14:13:00

 

CREATE PROCEDURE dbo.month_proc
AS
    BEGIN
    declare @gy_count                       decimal(38,2)
    declare @jrncb                          int
    declare @jrntl_id                       varchar(10)
    declare @jrntl_id_name                  varchar(100)
    declare @jrnbr                          varchar(10)
    declare @jrnbr_name                     varchar(100)
    declare @jrntr_cod                      varchar(10)
    declare @jrntr_cod_name                 varchar(100)
    declare @scvvhamt0                      decimal(38,2)
    declare @jrnac_dat                      varchar(20)
    declare @scvvhccy0                      varchar(10)
    declare @scvvhccy0_name                 varchar(100)
    declare @fh_work                        decimal(38,2)
    declare @zh_work                        decimal(38,2)
    declare @wd_work                        decimal(38,2)
    declare @organ_zh_id                    varchar(10)
    declare @organ_zh_name                  varchar(100)
    declare @jrntl_count                    int
    declare @wd_count                       decimal(38,2)
    declare @wd_money                       decimal(38,2)
    declare @wd_fh_work                     decimal(38,2)
    declare @wd_zh_work                     decimal(38,2)
    declare @wd_wd_work                     decimal(38,2)
    declare @percent_count                  varchar(10)
    declare @percent_money                  varchar(10)
    declare @percent_fh                     varchar(10)
    declare @percent_zh                     varchar(10)
    declare @percent_wd                     varchar(10)
    declare @wd                             varchar(10)
    declare @date                           varchar(20)
    declare @year                           varchar(10)
    declare @month                          varchar(10)
    declare @day                            varchar(10)
    declare @rn                             int
    declare @busi_count                     decimal(38,2)
    declare @wd_busi_count                  decimal(38,2)
    declare @percent_busi                   varchar(10)
    declare @busi                           varchar(10)
   
    --取得临时表gy_work_day的数据的日期
    select top 1 @date=substring(jrnac_dat,1,6) from gy_business_day
    select @year=substring(@date,1,4)
    select @month=substring(@date,5,6)
    select top 1 @day=substring(jrnac_dat,7,8) from gy_business_day
    --判断是否到月底并处理数据
    if @month='01' or @month='03' or @month='05' or @month='07' or @month='08' or @month='10' or @month='12'
    begin
    if @day='31'
    begin
    declare month_cur cursor for select gy_count, jrncb, jrntl_id, jrntl_id_name, jrnbr, jrnbr_name, jrntr_cod, jrntr_cod_name, scvvhamt0, jrnac_dat, scvvhccy0, scvvhccy0_name, fh_work, zh_work, wd_work, organ_zh_id, organ_zh_name, busi_count from gy_business_month where jrnac_dat=@year+@month order by jrnbr,jrntr_cod
    open month_cur
    --循环读取游标
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    while @@sqlstatus=0
    begin
    if @wd<>@jrnbr or @busi<>@jrntr_cod
    select @wd_count=wd_count, @wd_money=scvvhamt0, @wd_fh_work=fh_work, @wd_zh_work=zh_work, @wd_wd_work=wd_work, @wd_busi_count=busi_count from wd_business_month where jrnbr=@jrnbr and jrntr_cod=@jrntr_cod and jrnac_dat=@jrnac_dat
   
    if @wd_count=0
    select @percent_count='0.00%'
    else
    select @percent_count=convert(varchar,convert(decimal(19,2),round(@gy_count*1.0*100/@wd_count,2)))+'%'
   
    if @wd_money=0
    select @percent_money='0.00%'
    else
    select @percent_money=convert(varchar,convert(decimal(19,2),round(@scvvhamt0*100/@wd_money,2)))+'%'
   
    if @wd_fh_work=0
    select @percent_fh='0.00%'
    else
    select @percent_fh=convert(varchar,convert(decimal(19,2),round(@fh_work*100/@wd_fh_work,2)))+'%'
   
    if @wd_zh_work=0
    select @percent_zh='0.00%'
    else
    select @percent_zh=convert(varchar,convert(decimal(19,2),round(@zh_work*100/@wd_zh_work,2)))+'%'
   
    if @wd_wd_work=0
    select @percent_wd='0.00%'
    else
    select @percent_wd=convert(varchar,convert(decimal(19,2),round(@wd_work*100/@wd_wd_work,2)))+'%'
   
    if @wd_busi_count=0
    select @percent_busi='0.00%'
    else
    select @percent_busi=convert(varchar,convert(decimal(19,2),round(@busi_count*100/@wd_busi_count,2)))+'%'
   
    insert into gy_business_percent_month values(@percent_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @percent_money, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @percent_fh, @percent_zh, @percent_wd, @organ_zh_id, @organ_zh_name, @percent_busi)
    select @wd=@jrnbr
    select @busi=@jrntr_cod
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    end
    --关闭游标
    close month_cur
    --释放游标
    deallocate cursor month_cur
    end
    end
   
    if @month='04' or @month='06' or @month='09' or @month='11'
    begin
    if @day='30'
    begin
    declare month_cur cursor for select gy_count, jrncb, jrntl_id, jrntl_id_name, jrnbr, jrnbr_name, jrntr_cod, jrntr_cod_name, scvvhamt0, jrnac_dat, scvvhccy0, scvvhccy0_name, fh_work, zh_work, wd_work, organ_zh_id, organ_zh_name, busi_count from gy_business_month where jrnac_dat=@year+@month order by jrnbr,jrntr_cod
    open month_cur
    --循环读取游标
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    while @@sqlstatus=0
    begin
    if @wd<>@jrnbr or @busi<>@jrntr_cod
    select @wd_count=wd_count, @wd_money=scvvhamt0, @wd_fh_work=fh_work, @wd_zh_work=zh_work, @wd_wd_work=wd_work, @wd_busi_count=busi_count from wd_business_month where jrnbr=@jrnbr and jrntr_cod=@jrntr_cod and jrnac_dat=@jrnac_dat
   
    if @wd_count=0
    select @percent_count='0.00%'
    else
    select @percent_count=convert(varchar,convert(decimal(19,2),round(@gy_count*1.0*100/@wd_count,2)))+'%'
   
    if @wd_money=0
    select @percent_money='0.00%'
    else
    select @percent_money=convert(varchar,convert(decimal(19,2),round(@scvvhamt0*100/@wd_money,2)))+'%'
   
    if @wd_fh_work=0
    select @percent_fh='0.00%'
    else
    select @percent_fh=convert(varchar,convert(decimal(19,2),round(@fh_work*100/@wd_fh_work,2)))+'%'
   
    if @wd_zh_work=0
    select @percent_zh='0.00%'
    else
    select @percent_zh=convert(varchar,convert(decimal(19,2),round(@zh_work*100/@wd_zh_work,2)))+'%'
   
    if @wd_wd_work=0
    select @percent_wd='0.00%'
    else
    select @percent_wd=convert(varchar,convert(decimal(19,2),round(@wd_work*100/@wd_wd_work,2)))+'%'
   
    if @wd_busi_count=0
    select @percent_busi='0.00%'
    else
    select @percent_busi=convert(varchar,convert(decimal(19,2),round(@busi_count*100/@wd_busi_count,2)))+'%'
   
    insert into gy_business_percent_month values(@percent_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @percent_money, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @percent_fh, @percent_zh, @percent_wd, @organ_zh_id, @organ_zh_name, @percent_busi)
    select @wd=@jrnbr
    select @busi=@jrntr_cod
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    end
    --关闭游标
    close month_cur
    --释放游标
    deallocate cursor month_cur
    end
    end
   
    select @rn=convert(int,@year)
    if @rn/4=0 and @month='02'
    begin
    if @day='29'
    begin
    declare month_cur cursor for select gy_count, jrncb, jrntl_id, jrntl_id_name, jrnbr, jrnbr_name, jrntr_cod, jrntr_cod_name, scvvhamt0, jrnac_dat, scvvhccy0, scvvhccy0_name, fh_work, zh_work, wd_work, organ_zh_id, organ_zh_name, busi_count from gy_business_month where jrnac_dat=@year+@month order by jrnbr,jrntr_cod
    open month_cur
    --循环读取游标
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    while @@sqlstatus=0
    begin
    if @wd<>@jrnbr or @busi<>@jrntr_cod
    select @wd_count=wd_count, @wd_money=scvvhamt0, @wd_fh_work=fh_work, @wd_zh_work=zh_work, @wd_wd_work=wd_work, @wd_busi_count=busi_count from wd_business_month where jrnbr=@jrnbr and jrntr_cod=@jrntr_cod and jrnac_dat=@jrnac_dat
   
    if @wd_count=0
    select @percent_count='0.00%'
    else
    select @percent_count=convert(varchar,convert(decimal(19,2),round(@gy_count*1.0*100/@wd_count,2)))+'%'
   
    if @wd_money=0
    select @percent_money='0.00%'
    else
    select @percent_money=convert(varchar,convert(decimal(19,2),round(@scvvhamt0*100/@wd_money,2)))+'%'
   
    if @wd_fh_work=0
    select @percent_fh='0.00%'
    else
    select @percent_fh=convert(varchar,convert(decimal(19,2),round(@fh_work*100/@wd_fh_work,2)))+'%'
   
    if @wd_zh_work=0
    select @percent_zh='0.00%'
    else
    select @percent_zh=convert(varchar,convert(decimal(19,2),round(@zh_work*100/@wd_zh_work,2)))+'%'
   
    if @wd_wd_work=0
    select @percent_wd='0.00%'
    else
    select @percent_wd=convert(varchar,convert(decimal(19,2),round(@wd_work*100/@wd_wd_work,2)))+'%'
   
    if @wd_busi_count=0
    select @percent_busi='0.00%'
    else
    select @percent_busi=convert(varchar,convert(decimal(19,2),round(@busi_count*100/@wd_busi_count,2)))+'%'
   
    insert into gy_business_percent_month values(@percent_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @percent_money, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @percent_fh, @percent_zh, @percent_wd, @organ_zh_id, @organ_zh_name, @percent_busi)
    select @wd=@jrnbr
    select @busi=@jrntr_cod
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    end
    --关闭游标
    close month_cur
    --释放游标
    deallocate cursor month_cur
    end
    end
   
    if @rn/4<>0 and @month='02'
    begin
    if @day='28'
    begin
    declare month_cur cursor for select gy_count, jrncb, jrntl_id, jrntl_id_name, jrnbr, jrnbr_name, jrntr_cod, jrntr_cod_name, scvvhamt0, jrnac_dat, scvvhccy0, scvvhccy0_name, fh_work, zh_work, wd_work, organ_zh_id, organ_zh_name, busi_count from gy_business_month where jrnac_dat=@year+@month order by jrnbr,jrntr_cod
    open month_cur
    --循环读取游标
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    while @@sqlstatus=0
    begin
    if @wd<>@jrnbr or @busi<>@jrntr_cod
    select @wd_count=wd_count, @wd_money=scvvhamt0, @wd_fh_work=fh_work, @wd_zh_work=zh_work, @wd_wd_work=wd_work, @wd_busi_count=busi_count from wd_business_month where jrnbr=@jrnbr and jrntr_cod=@jrntr_cod and jrnac_dat=@jrnac_dat
   
    if @wd_count=0
    select @percent_count='0.00%'
    else
    select @percent_count=convert(varchar,convert(decimal(19,2),round(@gy_count*1.0*100/@wd_count,2)))+'%'
   
    if @wd_money=0
    select @percent_money='0.00%'
    else
    select @percent_money=convert(varchar,convert(decimal(19,2),round(@scvvhamt0*100/@wd_money,2)))+'%'
   
    if @wd_fh_work=0
    select @percent_fh='0.00%'
    else
    select @percent_fh=convert(varchar,convert(decimal(19,2),round(@fh_work*100/@wd_fh_work,2)))+'%'
   
    if @wd_zh_work=0
    select @percent_zh='0.00%'
    else
    select @percent_zh=convert(varchar,convert(decimal(19,2),round(@zh_work*100/@wd_zh_work,2)))+'%'
   
    if @wd_wd_work=0
    select @percent_wd='0.00%'
    else
    select @percent_wd=convert(varchar,convert(decimal(19,2),round(@wd_work*100/@wd_wd_work,2)))+'%'
   
    if @wd_busi_count=0
    select @percent_busi='0.00%'
    else
    select @percent_busi=convert(varchar,convert(decimal(19,2),round(@busi_count*100/@wd_busi_count,2)))+'%'
   
    insert into gy_business_percent_month values(@percent_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @percent_money, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @percent_fh, @percent_zh, @percent_wd, @organ_zh_id, @organ_zh_name, @percent_busi)
    select @wd=@jrnbr
    select @busi=@jrntr_cod
    fetch month_cur into @gy_count, @jrncb, @jrntl_id, @jrntl_id_name, @jrnbr, @jrnbr_name, @jrntr_cod, @jrntr_cod_name, @scvvhamt0, @jrnac_dat, @scvvhccy0, @scvvhccy0_name, @fh_work, @zh_work, @wd_work, @organ_zh_id, @organ_zh_name, @busi_count
    end
    --关闭游标
    close month_cur
    --释放游标
    deallocate cursor month_cur
    end
    end
    END

 

相关文章推荐

Sybase存储过程

  • 2012年03月02日 11:02
  • 223KB
  • 下载

Sybase ASE存储过程大全

  • 2013年07月23日 12:02
  • 12KB
  • 下载

简单有效的SQL 存储过程移植方案(1)——从Sybase 到DB2

简单有效的SQL Stored Procedure移植方案 (1) 本文将介绍一套简单有效的移植方案,指导从Sybase到DB2 V9 FOR Z/OS 的SQL Stored Procedure的...

sybase存储过程

  • 2012年11月07日 22:14
  • 4.83MB
  • 下载

Sybase存储过程的建立和使用

  • 2009年03月11日 22:46
  • 4KB
  • 下载

Sybase存储过程的建立和使用

Sybase系统具有完备的触发器、存储过程、规则以及完整性定义,支持优化查询,具有较好的数据安全性。可见Sybase的功能性是很好的,本文将主要围绕Sybase的存储讲起。 存储过程的特点 ...

Sybase存储过程的建立和使用

  • 2011年10月19日 23:13
  • 35KB
  • 下载

sybase分页存储过程的实现

Reference: http://database.51cto.com/art/201011/235484.htm

简单有效的SQL 存储过程移植方案(2)——从Sybase 到DB2

简单有效的SQL Stored Procedure移植方案 (2) 简单有效的SQL Stored Procedure移植方案 (2) 第六步:修改 DECLARE 语句 方法:...

sybase 基本语法,存储过程,触发器

sybase据说在12.xx以后支持自定义function了,但是在Adaptive Server Enterprice版本中似乎不行, 但是函数可以通过存储过程来实现,因为存储过程也是有返回值的:...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sybase 存储过程
举报原因:
原因补充:

(最多只允许输入30个字)