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:存储过程GROUP

--版本1 IF OBJECT_ID ('dbo.STATISTICS_FOR_SHITANG') IS NOT NULL DROP PROCEDURE dbo.STATISTICS_FOR_S...

Sysbase数据库存储过程

近日不得不学习Sybase数据库的相关内容,以前只是接触过mysql的基本操作,基本的增删改查之类的。没有接触过Sybase,不过Sybase的操作和mysql很相似。所以写起来也不甚麻烦,特写篇博客...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

sybase存储过程一 存储过程介绍

存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化  后存储在数据库服务器中,使用时只要调用即可。在ORACLE中,若干个  有联系的过程可以组合在一起构成程序包。  使用存储过程有...

Sybase与Oracle存储过程的区别

Sybase Oracle 传...

Sybase存储过程的使用

导读:存储过程p_RsGz_JiNeng_Rtn向调用者返回一个存储在变量@ErrCode里的值,这个值被称为状态值,它向调用者反映存储过程执行的成败状态(三)。

存储过程

oracle 存储过程的基本语法 2011-03-06 11:56 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 (     参数...

存储过程

转载自博客园,仅供学习参考 ---------------------------------------- Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。...

存储过程

存储过程存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数来调用执行它。一、存储过程的优点 存储过程增强了SQL语言的功能和灵活性。存储过程可以...

存储过程

1、创建存储过程 create or replace procedure test(var_name_1 in type,var_name_2 out type) as --声明变量(变量名 变...

存储过程

我们在进行pl/sql编程时打交道最多的就是存储过程了。存储过程的结构是非常的简单的,我们在这里除了学习存储过程的基本结构外,还会学习编写存储过程时相关的一些实用的知识。如:游标的处理,异常的处理,集...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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