数据库期末复习 简化操作

一、游标 Cursor

1. 从多数据记录中提取一条记录

1.1 查询结果为多条记录的SELECT语句

1.1.1 创建游标

模版1：

模版2：
Declare 游标名 [insensitive] cursor
[local | global]
[forward_only | scroll]
[static | keyset | dynamic | fast_forward]
[read_only | scroll_locks | optimistic]
[type_warning]
For SQL 语句
[for [read_only | update]]

1.1.2 打开游标

OPEN <cursor_name>;

1.1.3 推进游标并取当前数据

读取游标数据
Fatch [ next | prior | first | last ] [absolute n | relative n ] from 游标名 [ into 变量名 ]
Next----返回当前行的下一行记录
Prior----返回第一行记录
Last----返回最后一行记录
Absolute n----返回第n行记录，当n为负值则从最后一行算起。
Relative ----游标当前位置起向后第n行，若n为负值则向前第n行。

1.1.4 关闭游标

CLOSE {{[GLOBAL] cursor_name } | cursor_variable_name }

1.1.5 释放游标

DEALLOCATE {{[GLOBAL] cursor_name } | cursor_variable_name }

1.2 CURRENT形式的UPDATE/DELETE语句

1.2.1 插入子句

在UPDATE/DELETE语句中插入
WHERE CURRENT OF <游标名>
即游标指向的纪录
(当SELECT语句中带有UNION或ORDER BY子句，或相当于定义了后一个不可更新的视图时，不能用CURRENT)

二、存储过程

2.1 创建存储过程

USE [数据库名]
CREATE PROCEDURE [存储过程名] ([参数1,参数2…]) AS
<过程化SQL块>
GO(作用类似分隔符)

例：
USE teach
CREATE PROCEDURE myproc AS
SELECT * FROM table WHERE sex=‘男’
GO

USE teach
CREATE PROCEDURE InsertRecord
(
@sno char(6),
@snaem char(20)
)
AS
INSERT INTO Student VALUES(@sno,@sname)
GO

2.2 执行存储过程

EXEC myproc

EXEC InsertRecord @sno=123456,@sname=‘小红’

2.3 删除存储过程

DROP PROCEDURE [存储过程名]

三、函数

函数只能返回一个变量，可以是一个表，可以在SQL语句中使用

3.1 定义函数

e.g.:
创建：
Creat FUNCTION [dbo].[aa]
(@str varchar(20))
RETURNS int
AS
BEGIN
declare @num int
select @num= case @str
when ‘优’ then 5
when ‘通过’ then 3
when ‘不通过’ then 1
else 0
end
–(注释) Return the result of the function
RETURN @num
END
使用：
Select dbo.aa(‘优’)
输出：5

3.2 调用函数

dbo.函数名(参数)

3.3 修改函数

ALTER 代入定义函数模版

四、触发器

1. SQL Server没有行级触发器，用游标实现
2. 触发器不能建在视图上
3. 同表触发器内有Inserted和Deleted表
4. AFTER和FOR是触发事件执行后激活触发器
5. INSTEAD OF是替代触发事件

4.1 定义触发器

模版1:

模版2:

4.2 激活触发器

由触发事件激活
e.g.:
只有学生表中没有某班的学生了，才能删除该班级
CREATE TRIGGER deletecheck ON [dbo].[class]
FOR DELETE
AS
declare @num int
select @num =count(distinct cno) from student where student.cno in (select cno from deleted)
if (@num>0)
begin
raiserror(‘must empty student table first’,16,1)
rollback transaction
end
print (‘success’)

4.3 禁用触发器

ALTER TABLE 表名
ENABLE/DISABLE TRIGGER 触发器名

4.4 删除触发器

DROP TRIGGER <触发器名> ON <表名>