--如果存在数据库PRogrammerPay 就删除
if exists (select * from sysdatabases where name='programmerPay')
drop database programmerPay
go
--创建数据库programmerPay
create database programmerPay
on primary
(
name ='programmerPay_data',
filename='D:/programmerPay/programmerPay_data.mdf',
maxsize=3mb,
filegrowth=15%
)
log on
(
name ='programmerPay_log',
filename='D:/programmerPay/programmerPay_log.ldf',
maxsize=1mb,
filegrowth=15%
)
go
use programmerPay
go
--创建表prowage
create table prowage
(
id int identity(1,1) not null,--主键 标识列
Pname char(10) not null,--程序员姓名
wage int not null--程序员工资
)
go
--为表prowage id 字段添加主键约束
alter table prowage
add constraint PK_id primary key(id)
--插入测试数据
insert into prowage (pname,wage)
values ('张三',5000)
insert into prowage (pname,wage)
values ('李四',1200)
insert into prowage (pname,wage)
values ('二月',1700)
insert into prowage (pname,wage)
values ('蓝天',5700)
insert into prowage (pname,wage)
values ('阳光',8700)
insert into prowage (pname,wage)
values ('神州',1100)
insert into prowage (pname,wage)
values ('曾经藏',1300)
insert into prowage (pname,wage)
values ('ruo',1200)
insert into prowage (pname,wage)
values ('chend',1400)
--如果存在存储过程proc_addWage1 就删除
if exists (select * from sysobjects where name='proc_addWage1')
drop procedure proc_addWage1
go
--创建存储过程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--定义两个变量 没达到2200的人数和总人数
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'一共加薪'+convert(varchar(5),@endwage-@firstwage)
print'加薪后的程序员工资列表：'
select ID ,Pname,wage from prowage
go
--如果存在存储过程proc_addWage2 就删除
if exists (select * from sysobjects where name='proc_addWage2')
drop procedure proc_addWage2
go
--创建存储过程proc_addWage2
create procedure proc_addWage2
as
set nocount on
while(1=1)
begin
declare @avgwage int --定义变量 平均工资
select @avgwage =avg(wage) from prowage
if(@avgwage<4500)
update prowage set wage=wage+200
else
break
end
go
exec proc_addWage1 --调用存储过程 proc_addWage1
exec proc_addwage2 --调用存储过程 proc_addwage2
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/yyoanlp/archive/2009/12/21/5047942.aspx
