以下描述可能会很繁琐,但本人尽量整理说得清楚点,盼CSDN友给支持。-------------------------------------------------
表gongzi --存放职工工资具体数据,其结构:(没列出的省略)
-------------------------------------------------
yingfa --工资应发 float型
zf_accu --公积金 float型
yanglao_bx --养老保险 float型
yiliao_bx --医疗保险 float型
shiye_bx --失业保险 float型
gzzhanghao --工资帐号 char(18)型(用与奖金表匹配的唯一条件)
gzyear --年份 char(4)型
gzmonth --月份 char(2)型
gr_tax --个人所得税 float型
lv --税局要求上报的税率 float型
kou --税局要求上报的速算扣除额 float型
shifa --实发工资 float型-------------------------------------------------
表jiangjinall --存放职工奖金具体数据,其结构:(没列出的省略)
-------------------------------------------------gzzhanghao ----工资帐号 char(18)型(用与工资表匹配的唯一条件)
kh_jj ----奖金实发金额 float型
leibie ----奖金的类别 char(2)型(每位职工可能有多种类别的奖金)***************************************************
***************************************************
现在要写一个存储过程(假设名为count_tax),作用是
1、向count_tax传入2个参数@gzyear,@gzmonth,表明需要计算是当前年月的数据;
2、update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth;
(此条目的是计算应发工资,a b c为字段名,省略)
3、计算每位职工的sum(kh_jj) as kh_jj;
4、计算个人所得税的临时变量 Declare @Num float
@Num = yingfa + sum(kh_jj) - zf_accu - yanglao_bx - shiye_bx - yiliao_bx - 1600
5、根据税局的要求判断计算以下3项(Declare)
@gr_tax --个人所得税
@lv --税率
@kou --速算扣除额 其中判断方法为以下(很长省略了一些)
If @Num<=0 Then
@Gr_Tax=0
@lv=0.05
@kou=0
End If
If @Num>0 and @Num<=500 Then
@Gr_Tax=@Num*5/100
@lv=0.05
@kou=0
End If
If @Num>500 and @Num<=2000 Then
@Gr_Tax=@Num*10/100-25
@lv=0.1
@kou=25
End If
If @Num>2000 and @Num<=5000 Then
@Gr_Tax=@Num*15/100-125
@lv=0.15
@kou=125
End If
If @Num>5000 and @Num<=20000 Then
@Gr_Tax=@Num*20/100-375
@lv=0.2
@kou=375
End If
--.......(省略,太长了。可能用case会好点)
6、计算完毕后,保存数据到工资表gongzi里头
update gongzi set gr_tax=@Gr_Tax,lv=@lv,kou=@kou where gzyear=@gzyear and gzmonth=@gzmonth
7、计算实发数
update gongzi set shifa=yingfa-zf_accu-yiliao_bx-shiye_bx-gr_tax where gzyear=@gzyear and gzmonth=@gzmonth
8、返回出现实发数<0的数据
return shifa<0
请问以上功能要做成存储过程如何做?不够分再加。
表gongzi --存放职工工资具体数据,其结构:(没列出的省略)
-------------------------------------------------
yingfa --工资应发 float型
zf_accu --公积金 float型
yanglao_bx --养老保险 float型
yiliao_bx --医疗保险 float型
shiye_bx --失业保险 float型
gzzhanghao --工资帐号 char(18)型(用与奖金表匹配的唯一条件)
gzyear --年份 char(4)型
gzmonth --月份 char(2)型
gr_tax --个人所得税 float型
lv --税局要求上报的税率 float型
kou --税局要求上报的速算扣除额 float型
shifa --实发工资 float型-------------------------------------------------
表jiangjinall --存放职工奖金具体数据,其结构:(没列出的省略)
-------------------------------------------------gzzhanghao ----工资帐号 char(18)型(用与工资表匹配的唯一条件)
kh_jj ----奖金实发金额 float型
leibie ----奖金的类别 char(2)型(每位职工可能有多种类别的奖金)***************************************************
***************************************************
现在要写一个存储过程(假设名为count_tax),作用是
1、向count_tax传入2个参数@gzyear,@gzmonth,表明需要计算是当前年月的数据;
2、update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth;
(此条目的是计算应发工资,a b c为字段名,省略)
3、计算每位职工的sum(kh_jj) as kh_jj;
4、计算个人所得税的临时变量 Declare @Num float
@Num = yingfa + sum(kh_jj) - zf_accu - yanglao_bx - shiye_bx - yiliao_bx - 1600
5、根据税局的要求判断计算以下3项(Declare)
@gr_tax --个人所得税
@lv --税率
@kou --速算扣除额 其中判断方法为以下(很长省略了一些)
If @Num<=0 Then
@Gr_Tax=0
@lv=0.05
@kou=0
End If
If @Num>0 and @Num<=500 Then
@Gr_Tax=@Num*5/100
@lv=0.05
@kou=0
End If
If @Num>500 and @Num<=2000 Then
@Gr_Tax=@Num*10/100-25
@lv=0.1
@kou=25
End If
If @Num>2000 and @Num<=5000 Then
@Gr_Tax=@Num*15/100-125
@lv=0.15
@kou=125
End If
If @Num>5000 and @Num<=20000 Then
@Gr_Tax=@Num*20/100-375
@lv=0.2
@kou=375
End If
--.......(省略,太长了。可能用case会好点)
6、计算完毕后,保存数据到工资表gongzi里头
update gongzi set gr_tax=@Gr_Tax,lv=@lv,kou=@kou where gzyear=@gzyear and gzmonth=@gzmonth
7、计算实发数
update gongzi set shifa=yingfa-zf_accu-yiliao_bx-shiye_bx-gr_tax where gzyear=@gzyear and gzmonth=@gzmonth
8、返回出现实发数<0的数据
return shifa<0
请问以上功能要做成存储过程如何做?不够分再加。
declare @id int, @code char(6)declare cur_list cursor for
select id, code
from tb
where condition = 'true'
open cur_list
fetch next from cur_list into @id, @code
while @@fetch_status!=-1 begin
/* .. 在这部分添加你的处理逻辑脚本
*/
fetch next from cur_list into @id, @code
end
close cur_list
deallocate cur_list
--1、向count_tax传入2个参数@gzyear,@gzmonth,表明需要计算是当前年月的数据;
@gzyear varchar(4),
@gzmonth varchar(2)
as
set nocount on--2、update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth;
update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth--3、计算每位职工的sum( kh_jj) as kh_jj;
--4、计算个人所得税的临时变量 Declare @Num float -->用shifa字段暂存@Num
update gongzi set shifa = a.yingfa + b.kh_jj - a.zf_accu - a.yanglao_bx - a.shiye_bx - a.yiliao_bx - 1600
from gongzi as a inner join (select gzzhanghao, kh_jj=sum(kh_jj) from jiangjinall group by gzzhanghao) as b
on a.gzzhanghao = b.gzzhanghao
where gzyear=@gzyear and gzmonth=@gzmonth--5、根据税局的要求判断计算以下3项(Declare)
--6、计算完毕后,保存数据到工资表gongzi里头
update gongzi set
gr_tax = case
when shifa<=0 then 0
when shifa>0 and shifa<=500 then shifa*5/100
when shifa>500 and shifa<=2000 then shifa*10/100-25
when shifa>2000 and shifa<=5000 then shifa*15/100-125
when shifa>5000 and shifa<=20000 then shifa*20/100-375 end,
lv = case
when shifa<=500 then 0.05
when shifa>500 and shifa<=2000 then 0.1
when shifa>2000 and shifa<=5000 then 0.15
when shifa>5000 and shifa<=20000 then 0.2 end,
kou = case
when shifa<=500 then 0
when shifa>500 and shifa<=2000 then 25
when shifa>2000 and shifa<=5000 then 125
when shifa>5000 and shifa<=20000 then 375 end
where gzyear=@gzyear and gzmonth=@gzmonth--7、计算实发数
update gongzi set shifa=yingfa-zf_accu-yiliao_bx-shiye_bx-gr_tax where gzyear=@gzyear and gzmonth=@gzmonth--8、返回出现实发数 <0的数据
select * from gongzi where gzyear=@gzyear and gzmonth=@gzmonth and shifa<0set nocount off
go
declare @id int, @code char(6)
declare cur_list cursor for
select id, code
from tb
where condition = 'true'
open cur_list
fetch next from cur_list into @id, @code
while @@fetch_status!=-1 begin
/* .. 在这部分添加你的处理逻辑脚本
*/
fetch next from cur_list into @id, @code
end
close cur_list
deallocate cur_list
对于我这种情况,如何定义游标?你的@id int, @code char(6)代表什么意思?TO Limpire
辛苦你了.向你致敬!
你的方法虽然没有我想象的使用游标或循环,但你把计算税的临时变量先存到shifa里,再判断shifa,此时是同一张表里头判断
避免了使用游标,是好的方法.但是难免以后有类似的情况我还是想搞明白如何使用游标或循环?希望再给我这只笨猪解释解释.谢谢.解释完马上结贴.
update gongzi set shifa = a.yingfa + b.kh_jj - a.zf_accu - a.yanglao_bx - a.shiye_bx - a.yiliao_bx - 1600
from gongzi as a inner join (select gzzhanghao, kh_jj=sum(kh_jj) from jiangjinall group by gzzhanghao) as b
on a.gzzhanghao = b.gzzhanghao
where gzyear=@gzyear and gzmonth=@gzmonth
应该改为这样吧?
update gongzi set shifa = a.yingfa + b.kh_jj - a.zf_accu - a.yanglao_bx - a.shiye_bx - a.yiliao_bx - 1600
from gongzi as a inner join (select gzzhanghao, kh_jj=sum(kh_jj) from jiangjinall group by gzzhanghao) as b
on a.gzzhanghao = b.gzzhanghao
where a.gzyear=@gzyear and a.gzmonth=@gzmonth and b.gzyear=@gzyear and b.gzmonth=@gzmonth
如果要用游标循环,可以取循环计算每个员工的实发工资:
create proc wsp
@gzyear char(4)
@gzmonth char(2)
as
update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth
declare @gzzhanghao char(18)
declare @num float
declare cur cursor for select gzzhanghao from gongzi where gzyear=@gzyear and gzmonth=@gzmonth
open cur
fetch next from cur into @gzzhanghao
while(@@fetch_status=0)
begin
select @num=yingfa+sum(b.kh_jj)-zf_accu-yanglao_bx-shiye_bx-yiliao_bx-1600
from gongzi a ,jiangjinall b where a.gzzhanghao=b.gzzhanghao and a.gzzhanghao=@ gzzhanghao
-----这里再加上楼主那些判断方法,就不写了。好长
----最后算出来的@num。就直接去更新
update gongzi set shifa=@num where gzzhanghao=@gzzhanghao and gzyear=@gzyear and gzmonth=@gzmonth
end
close cur
deallocate cur
--1、向count_tax传入2个参数@gzyear,@gzmonth,表明需要计算是当前年月的数据;
@gzyear varchar(4),
@gzmonth varchar(2)
as
set nocount on--2、update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth;
update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth--3、计算每位职工的sum( kh_jj) as kh_jj;
--4、计算个人所得税的临时变量 Declare @Num float -->用shifa字段暂存@Num
update gongzi set shifa = a.yingfa + b.kh_jj - a.zf_accu - a.yanglao_bx - a.shiye_bx - a.yiliao_bx - 1600
from gongzi as a inner join (select gzzhanghao, kh_jj=sum(kh_jj) from jiangjinall group by gzzhanghao) as b
on a.gzzhanghao = b.gzzhanghao
where gzyear=@gzyear and gzmonth=@gzmonth--> 游标
declare @num float,@gr_tax float, @lv float, @kou int
declare cur_test cursor for select shifa from gongzi where gzyear=@gzyear and gzmonth=@gzmonth
open cur_test
fetch next from cur_test into @num
while @@fetch_status = 0
begin
--5、根据税局的要求判断计算以下3项(Declare)
If @Num <=0
begin
select @Gr_Tax=0, @lv=0.05, @kou=0
end
If @Num> 0 and @Num <=500
begin
select @Gr_Tax=@Num*5/100, @lv=0.05, @kou=0
end
If @Num> 500 and @Num <=2000
begin
select @Gr_Tax=@Num*10/100-25, @lv=0.1, @kou=25
end
If @Num> 2000 and @Num <=5000
begin
select @Gr_Tax=@Num*15/100-125, @lv=0.15, @kou=125
end
If @Num> 5000 and @Num <=20000
begin
select @Gr_Tax=@Num*20/100-375, @lv=0.2, @kou=375
end
--6、计算完毕后,保存数据到工资表gongzi里头
update gongzi set gr_tax=@Gr_Tax, lv=@lv, kou=@kou where current of cur_test
end
close cur_test
deallocate cur_test--7、计算实发数
update gongzi set shifa=yingfa-zf_accu-yiliao_bx-shiye_bx-gr_tax where gzyear=@gzyear and gzmonth=@gzmonth--8、返回出现实发数 <0的数据
select * from gongzi where gzyear=@gzyear and gzmonth=@gzmonth and shifa<0set nocount off
go
--1、向count_tax传入2个参数@gzyear,@gzmonth,表明需要计算是当前年月的数据;
@gzyear varchar(4),
@gzmonth varchar(2)
as
set nocount on--2、update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth;
update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth--3、计算每位职工的sum( kh_jj) as kh_jj;
--4、计算个人所得税的临时变量 Declare @Num float -->用shifa字段暂存@Num
update gongzi set shifa = a.yingfa + b.kh_jj - a.zf_accu - a.yanglao_bx - a.shiye_bx - a.yiliao_bx - 1600
from gongzi as a inner join (select gzzhanghao, kh_jj=sum(kh_jj) from jiangjinall group by gzzhanghao) as b
on a.gzzhanghao = b.gzzhanghao
where gzyear=@gzyear and gzmonth=@gzmonth--> 游标
declare @num float,@gr_tax float, @lv float, @kou int
declare cur_test cursor for select shifa from gongzi where gzyear=@gzyear and gzmonth=@gzmonth
open cur_test
fetch next from cur_test into @num
while @@fetch_status = 0
begin
--5、根据税局的要求判断计算以下3项(Declare)
If @Num <=0
begin
select @Gr_Tax=0, @lv=0.05, @kou=0
end
If @Num> 0 and @Num <=500
begin
select @Gr_Tax=@Num*5/100, @lv=0.05, @kou=0
end
If @Num> 500 and @Num <=2000
begin
select @Gr_Tax=@Num*10/100-25, @lv=0.1, @kou=25
end
If @Num> 2000 and @Num <=5000
begin
select @Gr_Tax=@Num*15/100-125, @lv=0.15, @kou=125
end
If @Num> 5000 and @Num <=20000
begin
select @Gr_Tax=@Num*20/100-375, @lv=0.2, @kou=375
end
--6、计算完毕后,保存数据到工资表gongzi里头
update gongzi set gr_tax=@Gr_Tax, lv=@lv, kou=@kou where current of cur_test
end
close cur_test
deallocate cur_test--7、计算实发数
update gongzi set shifa=yingfa-zf_accu-yiliao_bx-shiye_bx-gr_tax where gzyear=@gzyear and gzmonth=@gzmonth--8、返回出现实发数 <0的数据
select * from gongzi where gzyear=@gzyear and gzmonth=@gzmonth and shifa<0set nocount off
go
汗。游标掉了个东西,变成死游标了。
如果要用游标循环,可以取循环计算每个员工的实发工资:
create proc wsp
@gzyear char(4)
@gzmonth char(2)
as
update gongzi set yingfa=a+b+c where gzyear=@gzyear and gzmonth=@gzmonth
declare @gzzhanghao char(18)
declare @num float
declare cur cursor for select gzzhanghao from gongzi where gzyear=@gzyear and gzmonth=@gzmonth
open cur
fetch next from cur into @gzzhanghao
while(@@fetch_status=0)
begin
select @num=yingfa+sum(b.kh_jj)-zf_accu-yanglao_bx-shiye_bx-yiliao_bx-1600
from gongzi a ,jiangjinall b where a.gzzhanghao=b.gzzhanghao and a.gzzhanghao=@ gzzhanghao
-----这里再加上楼主那些判断方法,就不写了。好长
----最后算出来的@num。就直接去更新
update gongzi set shifa=@num where gzzhanghao=@gzzhanghao and gzyear=@gzyear and gzmonth=@gzmonth
fetch next from cur into @gzzhanghao
end
close cur
deallocate cur