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: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
--建立表格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
-