--如果存在數據庫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
