SQL创建数据库、表、存储过程及调用

--如果存在数据库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