--If the database programmerPay exists, delete it
if exists (select * from sysdatabases where name='programmerPay')
drop database programmerPay
go
--Create database programmerPay
create database programmerPay
on primary
(
name ='programmerPay_data',
filename='D:programmerPayprogrammerPay_data.mdf',
maxsize=3mb,
filegrowth=15%
)
log on
(
name ='programmerPay_log',
filename='D:programmerPayprogrammerPay_log.ldf',
maxsize=1mb,
filegrowth=15%
)
go
use programmerPay
go
--Create table prowage
create table prowage
(
id int identity(1,1) not null,--primary key identity column
Pname char(10) not null,--programmer name
wage int not null--programmer salary
)
go
--Add primary key constraints to table prowage id field
alter table prowage
add constraint PK_id primary key(id)
--Insert test data
insert into prowage (pname,wage)
values ('Zhang San',5000)
insert into prowage (pname,wage)
values ('李思',1200)
insert into prowage (pname,wage)
values ('February',1700)
insert into prowage (pname,wage)
values ('blue sky',5700)
insert into prowage (pname,wage)
values ('sunshine',8700)
insert into prowage (pname,wage)
values ('China',1100)
insert into prowage (pname,wage)
values ('once hidden',1300)
insert into prowage (pname,wage)
values ('ruo',1200)
insert into prowage (pname,wage)
values ('chend',1400)
--If the stored procedure proc_addWage1 exists, delete it
if exists (select * from sysobjects where name='proc_addWage1')
drop procedure proc_addWage1
go
--Create stored procedure proc_addWage1
create procedure proc_addWage1
as
set nocount on
declare @firstwage int
select @firstwage=sum(wage) from prowage
while (1=1)
begin
declare @notpass int, @count int--define two variables: the number of people who have not reached 2200 and the total number of people
select @notpass=count(*) from prowage where wage<2200
select @count =count(*) from prowage
if(@notpass*2>@count)
update prowage set wage=wage+100
else
break
end
declare @endwage int
select @endwage=sum(wage) from prowage
print'Total salary increase'+convert(varchar(5),@endwage-@firstwage)
print 'Programmer salary list after salary increase:'
select ID,Pname,wage from prowage
go
--If the stored procedure proc_addWage2 exists, delete it
if exists (select * from sysobjects where name='proc_addWage2')
drop procedure proc_addWage2
go
--Create stored procedure proc_addWage2
create procedure proc_addWage2
as
set nocount on
while(1=1)
begin
declare @avgwage int --Define variable average salary
select @avgwage =avg(wage) from prowage
if(@avgwage<4500)
update prowage set wage=wage+200
else
break
end
go
exec proc_addWage1 -- call the stored procedure proc_addWage1
exec proc_addwage2 -- call the stored procedure proc_addwage2
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/yyoanlp/archive/2009/12/21/5047942.aspx
-