关闭

sybase 存储过程

标签: sybase存储insertdate
888人阅读 评论(0) 收藏 举报
分类:

 

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

 

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

sybase杀掉“僵死”链接

sybase是一个收费的数据库。如果是测试版,会限制链接数。比较奇葩的是,它释放死掉的链接很慢(不知道是不是设置问题),一个小组的人开发的时候,很快链接就满了,sybase很久都不会主动回收。浪费大量的时间在等待数据库链接资源上。 我们能够根据系统进程表sysprocesses中的status(re...
  • mahycsdn
  • mahycsdn
  • 2015-08-27 20:10
  • 1054

sybase数据库函数基础示例

表:student(学生表) 有三列,分别为:S_NAME(学生姓名),S_AGE(学生年龄),S_SEX(学生性别); 需求:查出给定年龄和性别的所有学生姓名,并用“;”拼接返回。 函数代码: CREATE FUNCTION GET_ALL_NAME_BY_PARA(@SEX VARCHA...
  • skyfred
  • skyfred
  • 2016-07-11 15:43
  • 666

sybase数据库的两种简单迁移方法

新上了一套sybase数据库,需要做数据迁移工作,由于可以申请周末停机时间,因此可以选择如下两种方案,方法都比较简单,同sqlserver很类似 方法1:dump和load方法,本质上是备份和恢复 迁移的简单过程如下 1, 数据库数据备份(老库)    使...
  • hijk139
  • hijk139
  • 2014-02-28 10:52
  • 7079

linux下sybase数据库的常用操作

常用操作: 1.su - sybase -c "isql -Usa -STANK" 2.修改SA的密码(SA默认密码为空): 用ISQL连接上后   sp_password null,"新密码" or sp_password &q...
  • qq_29663071
  • qq_29663071
  • 2016-07-12 17:12
  • 2480

学习总结:sybase中bcp命令批量导出和导入

1.编辑一个文件selectout.sql: set nocount on use databasename go select 'bcp databasename..' + name + ' out d:\temp\' + name + '...
  • colinmok
  • colinmok
  • 2014-07-17 11:17
  • 3671

Sybase常见日期函数使用说明

Sybase常见日期函数使用说明
  • rcom10002
  • rcom10002
  • 2014-03-11 13:23
  • 4688

sybase审计功能

1、创建审计所需的数据库设备和数据库     use master     go     --创建审计数据库sybsecurity     disk init name =...
  • lileizhang
  • lileizhang
  • 2013-12-05 17:42
  • 855

解决Sybase数据库死锁的方法

人民银行吉林市中心支行科技处 刘志明   在联机事务处理(OLTP)的数据库应用系统中,多用户、多任务的并发性是系统最重要的技术指标之一。为了提高并发性,目前大部分RDBMS都采用加锁技术。然而由于现实环境的复杂性,使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加...
  • lileizhang
  • lileizhang
  • 2015-05-18 10:40
  • 1458

sybase复制服务

Sybase复制服务器表的单向复制和双向复制定义语句 单向复制 一、在hn01_db上新建表tab1 isql -Usa -P -Shn01 1> use hn01_db 2> create table tab10(a int, b char(10)...
  • u010562865
  • u010562865
  • 2017-04-17 13:43
  • 509

Sybase IQ,你需要知道的基础

Sybase IQ,你需要知道的基础  Sybase IQ,你需要知道的基础: 第一,知道IQ跟其它的关系型数据库相比,它的主要特征是什么?包括查询快、数据压缩比高、Load快,但是插入更新慢,不太适合数据老是变化,它是按列存储的。这时候你就知道它适做DSS(决策支持系统),数...
  • u014774781
  • u014774781
  • 2016-07-15 11:49
  • 2131
    个人资料
    • 访问:3389936次
    • 积分:29820
    • 等级:
    • 排名:第200名
    • 原创:347篇
    • 转载:561篇
    • 译文:7篇
    • 评论:342条
    艺术设计类