如何使用光标计算员工的总薪水 [英] how to calculate total salary of an employee using cursor

问题描述

嗨..

我有以下2个表格，

Hi..
I have following 2 tables,

create table Employee_Details
(
EmpId	int,
EmpName	varchar(20),
EmpLocation	varchar(50),
BasicSalary	int,
HraDetails	int,
TotalSalary	int
)

create table Department_Details
(
EmpId	int,
DeptId	int,
DeptName	varchar(50)
)

我必须用23％的基本工资和总工资来计算hra ✍。

我是存储过程的新手，所以请指导我...



Ok..i已经提到了2个表，每个表有2个记录。我尝试用光标来计算hra和总薪水如下

I have to calculate hra with 23% of basic salary and totalsalary using cursor.
Am new to stored procedures,so guide me...

Ok..i have mentioned 2 tables with each having 2 records. I tried with cursor to calculate hra and total salary as below

declare @empname	varchar(50)
declare @emplocation	 varchar(50)
declare @deptname	varchar(50)
declare	@basicsalary	int
declare	@hra	int
declare	@total	int
declare @hradetails	int
declare @totaldetails	int
declare cur_sample4	cursor
static for
select emp.EmpName,emp.EmpLocation,dep.DeptName,emp.BasicSalary,emp.HraDetails,emp.TotalSalary
from Employee_Details emp inner join Department_Details dep on emp.EmpId=dep.EmpId
open cur_sample4
begin
fetch next from cur_sample4 into @empname,@emplocation,@deptname,@basicsalary,@hradetails,@totaldetails
while @@FETCH_STATUS=0
begin
set @hradetails=@basicsalary*23/100
set @totaldetails=@basicsalary + @hradetails
fetch next from cur_sample4 into @empname,@emplocation,@deptname,@basicsalary,@hradetails,@totaldetails
end
end
close cur_sample4
deallocate cur_sample4
--set nocount off
select @empname as EmpName,@emplocation as EmpLocation,@deptname as Dept
,@basicsalary as BasicSalary,@hradetails as HRA,@totaldetails as Total

但我只得到了secon d行。指导我查询中的错误

But am getting only the second row. Guide me what is the mistake in query

推荐答案

好的...使用你目前所拥有的东西只需移动显示循环内新细节的选择

Ok ... using what you have so far just move the select showing the new details inside the loop

while @@FETCH_STATUS=0
begin
    set @hradetails=@basicsalary*23/100
    set @totaldetails=@basicsalary + @hradetails
     -- display the results as you go along
select @empname as EmpName,@emplocation as EmpLocation,@deptname as Dept      ,@basicsalary as BasicSalary,@hradetails as HRA,@totaldetails as Total 
    fetch next from cur_sample4 into @empname,@emplocation,@deptname,@basicsalary,@hradetails,@totaldetails
end

请注意，它位于 fetch next 之前，以确保我们不要搞砸@@ FETCH_STATUS



另请注意，如果我这样做，我会将BasicSalary，HraDetails和TotalSalary声明为数字 <无线电通信/>
并得到这样的结果...

Note that it is before the fetch next to ensure we don''t mess up @@FETCH_STATUS

Also note that if I was doing this I would have BasicSalary, HraDetails and TotalSalary declared as numeric
and would get the results like this ...

update #Employee_Details set HraDetails = BasicSalary * 23.0 / 100.0, TotalSalary = BasicSalary + (BasicSalary * 23.0 / 100.0)
select * from #Employee_Details

while @@ FETCH_STATUS = 0

begin

set @ hradetails = @ basicsalary * 23/100

set @ totaldetails = @ basicsalary + @hradetails

从cur_sample4获取下一个到@empname，@ emplocation，@ deptname，@ basicsalary，@ hradetails，@ littledetails

end



在以下循环中你只计算当前行的数据





选择@empname作为EmpName，@ emplocation作为EmpLocation，@ deptname作为Dept

，@ basicsalary作为BasicSalary， @hradetails作为HRA，@ totaldetails作为总计

以上行您选择的最后一个记录





使用以下查询来选择计算数据或更新

SELECT emp.EmpName，emp.EmpLocation，dep.DeptName，emp.BasicSalary，

（emp.BasicSalary * 23/100）AS HraDetails，（emp.BasicSalary，（emp.BasicSalary * 23/100） ））AS emp.TotalSalary

FROM Employee_Details emp

INNER JOIN Department_Details dep

ON emp.EmpId = dep.EmpId



用于更新Hra和总薪水

UPDATE emp SET HraDetails =（BasicSalary * 23/100），TotalSalary =（BasicSalary +（BasicSalary * 23/100） ）

FROM Employee_Details emp

INNER JOIN Department_Details dep

ON emp.EmpId = dep.EmpId

while @@FETCH_STATUS=0
begin
set @hradetails=@basicsalary*23/100
set @totaldetails=@basicsalary + @hradetails
fetch next from cur_sample4 into @empname, @emplocation, @deptname, @basicsalary, @hradetails, @totaldetails
end

in following loop you are only calculating the data for current row


select @empname as EmpName,@emplocation as EmpLocation,@deptname as Dept
,@basicsalary as BasicSalary,@hradetails as HRA,@totaldetails as Total
in above line your selecting the last fectched record


use following queries to select calculated data or updating
SELECT emp.EmpName, emp.EmpLocation, dep.DeptName, emp.BasicSalary,
(emp.BasicSalary * 23 / 100) AS HraDetails, (emp.BasicSalary, (emp.BasicSalary * 23 / 100)) AS emp.TotalSalary
FROM Employee_Details emp
INNER JOIN Department_Details dep
ON emp.EmpId = dep.EmpId

For updating Hra and total salary
UPDATE emp SET HraDetails = (BasicSalary * 23 / 100), TotalSalary = (BasicSalary + (BasicSalary * 23 / 100))
FROM Employee_Details emp
INNER JOIN Department_Details dep
ON emp.EmpId = dep.EmpId

这篇关于如何使用光标计算员工的总薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！