存儲過程
（1）創建存儲過程，使用Employees表中的員工人數來初始化一個局部變量，并調用這個存儲過程。
1. Create PROCEDURE test @number1 int output --輸出參數，可以從程序中返回信息
2. As
3. begin
4. Declare @number2 int
5. Set @number2=(Select COUNT(*) from Employees)
6. Set @number1=@number2
7. end
運行結果如下
執行該存儲過程，并查看結果。
Declare?@num?int??
EXEC?test?@num?output??
Select?@num??運行結果如下
（2）創建存儲過程，比較兩個員工的實際收入，若前者比后者高就輸出0，否則輸出1。
1. Create PROCEDURE compa @id1 char(6),@id2 char(6),@bj int output
2. as
3. Begin
4. Declare @sr1 float, @sr2 float
5. Select @sr1=income-outcome from salary Where EmployeeID=@id1
6. Select @sr2=income-outcome from salary Where EmployeeID=@id2
7. If @sr1>@sr2
8. Set @bj=0
9. Else
10. Set @bj=1
11. end
12. GO
運行結果如下
（3）創建添加職員記錄的存儲過程EmployeeAdd。
Create?PROCEDURE?EmployeeAdd???
(??
@employeeid?char(6),@name?char(10),@education?char(4),?@birthday?datetime,??
@workyear?tinyint,@sex?bit,@address?char(40),?@phonenumber?char(12),??
@departmentID?char(3))??
As??
Begin??
Insert?into?employees???
values(@employeeid,@name,@education,@birthday,@workyear,??
@sex,@address,@phonenumber,@departmentID)??
end??
return運行結果如下
（4）創建帶output游標參數的存儲過程，在employees中聲明并打開一個游標。
Create?PROCEDURE?em_cursor?@em_cursor?cursor?VARYING?output??
As??
Begin??Set?@em_curcor=CURSOR?forward_only?static??FOR??Select?*?from?Employees??open?@em_cursor??
end??運行結果如下
聲明一個局部游標變量，執行上述存儲過程，并將游標賦值給局部游標變量，然后通過該游標變量讀取記錄
Declare?@mycursor?cursor??
EXEC?em_cursor?@em_cursor=@mycursor?output??
Fetch?next?from?@mycursor??
While(@@FETCH_STATUS=0)??
begin??fetch?next?from?@mycursor??
end??
close?@mycursor??
deallocate?@mycursor?運行結果如下
（5）創建存儲過程，使用游標確定一個員工的實際收入是否排在前三名。結果為1表示是，結果為0表示否。
Create?PROCEDURE?top_three?@em_id?char(6),@ok?bit?output??
As??
Begin??Declare?@x_em_id?char(6)??Declare?@act_in?int,@seq?int??Declare?salary_ids?cursor?for??Select?EmployeeID,Income-Outcome?from?salary??order?by?income-outcome?desc??Set?@seq=0??Set?@ok=0??open?salary_ids??fetch?salary_ids?into?@x_em_id,@act_in??While?@seq<3?and?@ok=0??begin??Set?@seq=@seq+1??if?@x_em_id=@em_id??Set?@ok=1??fetch?salary_ids?into?@x_em_id,@act_in??end??close?salary_ids??deallocate?salary_ids??
end?運行結果如下
執行該存儲過程
Declare?@ok?bit??
EXEC?top_three?'108991',@ok?output??
Select?@ok?運行結果如下
要求一個員工的工作年份大于6時將其轉移到經理辦公室工作
1. Select * from dbo.Departments
2. Select * from dbo.Employees
3. Select * from dbo.Salary
4.
5. Create PROCEDURE que1 @id char(6)
6. as
7. Begin
8. Declare @workyear char(6),@dep_id char(3)
9. Select @workyear=workyear from Employees Where EmployeeID=@id
10. Select @dep_id=Departments.departmentID from employees,departments
11. Where employees.DepartmentID=departments.DepartmentID
12. and departmentName='經理辦公室'
13. If @workyear>6
14. Update Employees Set departmentID=@dep_id Where EmployeeID=@id
15. end
根據每個員工的學歷將收入提高500
1. Create Procedure que2 @id char(6)
2. As
3. Begin
4. Declare @education char(6)
5. Select @education=Education from Employees Where EmployeeID=@id
6. Update salary Set Income=income+500 Where EmployeeID=@id
7. end
使用游標計算本科及以上學歷的員工在員工總數中占的比例
1. Declare @edu varchar(10),@part_count int,@all_count int
2. Declare mycursor cursor
3. for Select distinct education,count(education) over(partition by education)as part_count,
4. count(education) over() as all_count from Employees
5. open mycursor
6. fetch next from mycursor into @edu,@part_count,@all_count
7. While @@FETCH_STATUS=0
8. Begin
9. print @edu+'占總人數比例：'+convert(varchar(100),convert(numeric(38,2),@part_count/1.0/@all_count*100)+'%'
10. fetch next from mycursor into @edu,@part_count,@all_count
11. end
12. close mycursor
13. deallocate mycorsor
觸發器
對于TGGL數據庫，Employees表的DepartmentID列與Departments表的DepartmentID列應滿足參照完整性規則。
- 向Employees表添加記錄時，該記錄的DepartmentID字段值在Departments表中應存在。
- 修改Departments表的DepartmentID字段值時，該字段在Employees表中的對應值也應修改。
- 刪除Departments表的記錄時，該記錄的DepartmentID字段值在Employees表中對應的記錄也應刪除。
對于上述參照完整性規則，在此通過觸發器實現。
（1）向Employees表插入一個記錄時，通過觸發器檢查記錄的DepartmentID在Departments中是否存在，不存在則取消插入或修改操作。
1. Create Trigger employeesIns on dbo.Employees
2. for insert,Update
3. as
4. Begin
5. IF((Select DepartmentID from inserted)not in
6. (Select DepartmentID from Departments))
7. Rollback
8. end
運行結果如下
（3）刪除Departments中記錄的同時刪除Employees中DepartmentID對應記錄.
Create?Trigger?DepartmentDelete?on?dbo.Departments??for?Delete??as??Begin??Delete?from?Employees??Where?DepartmentID=(Select?DepartmentID?from?deleted)??end?運行結果如下
（4）創建Instead of觸發器，當向salary中插入記錄時，先檢查EmployeeID列上的值在Employees中是否存在，如果存在則執行插入操作，如果不存在則提示“員工編號不存在”。
1. Create Trigger EM_EXISTS on salary
2. Instead of Insert
3. as
4. Begin
5. Declare @employeeID char(6)
6. Select @employeeID=EmployeeID from inserted
7. If (@employeeID in (Select EmployeeID from Employees))
8. Insert into salary Select * from inserted
9. Else
10. print '員工編號不存在'
11. end
運行結果如下
（5）創建DDL觸發器，當刪除數據庫的一個表時，提示‘不能刪除表’，并回滾刪除表的操作。
Create?Trigger?table_delete?on?Database??
After?drop_table??
as??print?'不能刪除表'??rollback?transaction?運行結果如下
Employees與salary的EmployeeID應滿足完整性規則，請用觸發器實現兩個表之間的參照完整性。
1. Create Trigger que1_1 on salary
2. for insert,update
3. as
4. Begin
5. If(Select employeeid from inserted)not in
6. (Select EmployeeID from Employees)
7. rollback
8. end
9. Create trigger que1_2 on Employees
10. for update
11. as
12. Begin
13. Update Salary Set zemployeeID=(Select Employeeid from inserted)
14. Where EmployeeID=(Select employeeid from deleted)
15. end
16.
17. Create Trigger que1_3 on Employees
18. for delete
19. as
20. Begin
21. Delete from salary
22. Where EmployeeID=(Select EmployeeID from deleted)
23. End
若將Employees中員工的工作時間增加到1年，則收入增加500，若增加兩年則增加1000。
1. Create Trigger que2_1 on Employees
2. After update
3. as
4. Begin
5. Declare @a int,@b int
6. Set @a=(Select workyear from inserted)
7. Set @b=(Select workyear from deleted)
8. If (@a>@b)
9. update salary
10. Set income=income+(@a-@b)*500
11. Where EmployeeID in (Select EmployeeID from inserted)
12. End
創建UPdate觸發器，當salary中income增加500時，outcome增加50。
1. Create Trigger que3_1 on salary
2. for update
3. as
4. Begin
5. If((Select income from inserted)-(Select income from deleted)=500)
6. Update salary Set outcome=outcome+50
7. Where EmployeeID=(Select EmployeeId from inserted)
8. end
創建instead of觸發器，實現向不可更新視圖插入數據。
1. Create VIEW a_view
2. as
3. Select a.EmployeeID,name,workyear,income,outcome from Employees a,salary b
4. Where Employees.employeeID=Salary.EmployeeID
5. Create Trigger que4_1 on a_view
6. Instead of insert
7. as
8. Begin
9. Declare @ei char(6),@name char(10),@wy tinyint,@ic float,@oc float
10. Select @ei=EmployeeID,@name=name,@wy=workyear,@ic=income,@oc=outcome
11. from inserted
12. insert into Employees(EmployeeID,name,workyear)values(@ei,@name,@wy)
13. insert into salary values(@ei,@ic,@oc)
14. end
創建DDl觸發器，當刪除數據庫時，提示無法刪除，并回滾刪除操作。
1. Create Trigger que5_1 on all server
2. after drop_database
3. as
4. print '不能刪除'
5. rollback transaction
6. drop database Hao
???????
