--Íá äçìéïõñãçèåß ìéá function ðïõ èá Ý÷åé óáí ðáñÜìåôñï ôïí êùäéêü åíüò ÷ñÞóôç
--êáé èá õðïëïãßæåé ôï óýíïëï ôùí ùñþí ðïõ åñãÜóôçêå óå üëá ôï project
create function EmpHours (@id char(8)) returns decimal(3,1) as
begin
declare @hr decimal(3,1)
set @hr = (select isnull(sum(hours), 0) from works_on where emp_id = @id )
return @hr
end
----------------
declare @h decimal(3,1)
begin
exec @h=emphours 'G276548'
print convert(char(10), @h)
end
--Íá ãñÜøåôå Ýíá ìðëïê êþäéêá ðïõ èá åìöáíßæåé üëïõò ôïõò åñãáæüìåíïõò (êùäéêü, ðëÞñåò üíïìá).
-- Ãéá êÜèå åñãáæüìåíï èá ÷ñçóéìïðïéåß ôçí function EmpHours ãéá íá õðïëïãßóåé êáé åìöáíßóåé ôéò þñåò ðïõ åñãÜæåôáé
-- óôá projects
declare c1 cursor for
select emp_id, lastname + ' ' + firstname
from employee
declare @id char(8), @fullname char(30), @hours decimal(3,1)
begin
open c1
fetch c1 into @id, @fullname
while @@fetch_status = 0
begin
exec @hours = EmpHours @id
print @id + ' ' + @fullname + ' '+ convert(char(10), @hours)
fetch c1 into @id, @fullname
end
close c1
deallocate c1
end
--Íá äçìéïõñãçèåß ìéá procedure ShowDept ìå ðáñÜìåôñï ôïí êùäéêü åíüò ôìÞìáôïò (dnumber)
--êáé èá åìöáíßæåé ôï üíïìá ôìÞìáôïò, ôï ðëÞñåò üíïìá ôïõ ìáíáôæåñ êáé ôá
--ïíüìáôá ôùí project óôá ïðïßá óõììåôÝ÷åé.
create procedure ShowDept @dn int as
declare @dnm varchar(15), @mname varchar(30)
declare pr cursor for
select pname from project where dnumber=@dn
declare @pn varchar(30)
begin
select @dnm=d.dname, @mname=e.lastname+' '+e.firstname
from department d, employee e
where d.dnumber=e.dnumber
and d.dnumber = @dn
print @dnm + ' ' + @mname
print '--------------------'
open pr
fetch pr into @pn
while @@fetch_status = 0
begin
print @pn
fetch pr into @pn
end
close pr
deallocate pr
end
exec ShowDept 6
--procedure EmpSalray
--Íá õðïëïãéóôåß ôï ðëÞèïò ôùí projects ðïõ óõììåôÝ÷åé ï êÜèå åñãáæüìåíïò.
--Áí åßíáé ðÜíù áðü 2 ôüôå íá áõîÜíåôáé ï ìéóèüò ôïõ êáôÜ 2%
create procedure EmpSalary as
set nocount on
declare e1 cursor for
select emp_id, count(*)
from works_on
group by emp_id
declare @id char(8), @p1 int
begin
open e1
FETCH e1 INTO @id, @p1
WHILE @@FETCH_STATUS = 0 --Ýëåã÷ïò ãéá ôï ôÝëïò ôïõ cursor
Begin
print @id + convert(varchar(10),@p1)
if @p1>2
begin
update employee
set salary=1.02*salary
where emp_id=@id
print '------¸έãéíå áýîçóç óôïí' + @id
end
FETCH e1 INTO @id, @p1
End
CLOSE e1
DEALLOCATE e1
end
--exec EmpSalary
--Íá ãñáöåß ôìÞìá êþäéêá ðïõ èá åìöáíßæåé ôïí êùäéêü êáé ôï ïíïì/ìï ôïõ supervisor
--êáèþò êáé ôï ðëÞèïò ôùí õðáëëÞëùí ðïõ ðñïßóôáôáé
declare e1 cursor for
select e1.emp_id, e1.lastname+' ' +e1.firstname supname, count(*)
plithos_emp
from employee e1, employee e2
where e1.emp_id = e2.supervisor_id
group by e1.emp_id, e1.lastname+' ' +e1.firstname
declare @id char(8), @sn char(20), @pl int
begin
open e1
FETCH e1 INTO @id, @sn, @pl
WHILE @@FETCH_STATUS = 0 --Ýëåã÷ïò ãéá ôï ôÝëïò ôïõ cursor
Begin
print @id + ' ' + @sn + ' ' + convert(varchar(10), @pl)
FETCH e1 INTO @id, @sn, @pl
end
close e1
deallocate e1
end
Υπολογίζοντας τον μέσο μισθό όλων των εργαζομένων και όσο είναι κάτω από 900 να κάνετε συνολική
αύξηση μισθών κατά την τιμή της μεταβλητής @extra_money. Η ενέργεια θα επαναλαμβάνεται όσο ο
μέγιστος μισθός είναι πάνω από 3000.
Declare @extra_money money, @max_salary float
set @extra_money=100
WHILE (SELECT AVG(Salary) FROM employee) < 900
BEGIN
UPDATE employee
SET Salary = Salary + @extra_money
set @max_salary = (SELECT MAX(salary) FROM employee)
IF (@max_salary) > 3000
BREAK
ELSE
CONTINUE
END
