复习点
一、存储过程
1.一些基本知识
（1）存储过程是一种数据库对象，是一组为了完成特定功能、可以接收和返回用户参数的T-SQL语句预编译集合，经编译后存储在数据库中，以某个名称存储并作为一个单元处理。
（2）存储过程在第一次执行时会进行语法检查和变异，执行后它的执行计划就驻留在高速缓存中，用于后续调用。
2.存储过程的特点
（1）存储程序创建后在程序中可被多次调用，不必重新编写。
（2）所有的客户端可以使用相同的存储过程来确保数据访问和修改的一致性。
（3）存储过程可以独立于应用程序而进行修改，大大提高了程序的可移植性。
（4）存储过程在服务器端进行，执行速度快。
（5）可以大大减少网络流量。
（6）存储过程可以作为一种安全机制充分利用。
3.存储过程的分类
- 系统存储过程：主要存储在master数据库中，一般以’sp_'为前缀。
- 用户自定义存储过程：分为T-SQL存储过程和CLR存储过程（针对微软）。此外还有临时存储过程，用’#‘和’##'命名，分为局部临时存储过程（当前会话结束时自动删除且权限不能授予其他用户）和全局临时存储过程（该过程的最后一个会话结束时才会被删除且所有用户都可以访问该过程）。
- 扩展存储过程：是指用户使用外部程序设计语言编写的存储过程。一般以’xp_'为前缀。一定要存放在master数据库中。
4.存储过程的常用操作
（1）创建存储过程
a. SQL语句
CREATE POPROCETURE
procedure_name
[ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ]
[ OUTPUT ]] [ ,…n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }]
[ FOR REPLICATION ]
AS sql_statement [ …n ]
参数含义如下：
- procedure_name：存储过程的名称。要创建局部临时过程的话，在前面加一个编号符（#procedure_name），要创建全局临时过程的话，在前面加两个编号符（##procedure_name）。一个完成的名称（包括#和##）不能超过128个字符。
- number：可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句时就能将同组的过程一起删除。
- @parameter。存储过程的参数，参数名称必须符合标识符的规则。一个CREATE中可以声明一个或多个参数。除非定义了该参数的默认值，否则用户必须在执行过程时提供每个所声明参数的值。
- data-type：参数的数据类型，所有数据类型都可以用作存储过程的参数。
- VARYING：指定作为输出参数支持的结果集。
- default：参数的默认值。
- OUTPUT：表名该参数是返回参数，可将该参数的值返回给EXECUTE调用语句
- RECOMPILE：表名SQL Server不会缓存该过程的计划。该存储过程在运行时重新会被编译。
- ENCRYPTION：表示加密存储过程的文本。
- FOR PEPLICATION：指定不能再订阅服务器上执行为复制创建的存储过程。
- AS：指定过程要执行的操作。
- sql_statement，过程中包含的任意数目和类型的T_SQL语句。
b.SSMS
然后设置数据：
说明：上述图片中，设置了存储过程的名字为Proc_Q_stugrade，第一个参数名为@stuname，类型为varchar(10)，默认值为’ ‘，第二个参数名为@kcname，类型为varchar(50)，默认值为’ '。
点击确认之后，会发现代码已经自动修改
然后在“Insert statements for procedure here”插入SELECT语句：
SELECT 姓名,SC.课程号,成绩
FROM STUDENT INNER JOIN
SC ON STUDENT.学号 = SC.学号
WHERE STUDENT.姓名 = @stuname AND SC.成绩 = @kcname
（INEER JOIN是一个内连接，简单讲就是将两个表连接起来，语法格式为 <表名><连接类型><表名> on <条件表达式>）
然后点击执行。
（2）查看存储过程
a.SSMS
b.使用系统存储过程查看（注意，这个查看的是未加密的存储过程）
SP_HELPTEXT
[@objename=] ‘name’
[, [@columnname = ]‘computed_column_name’ ]
参数含义如下：
- [@objename=] ‘name’：存储过程的名称。
- [@columnname = ] ‘computed_column_name’：显示其定义信息的计算列的名称。。column_name的数据类型为sysname，无默认值。
（3）修改存储过程
a.SSMS
b.SQL语句
ALTER {PROC | PROCEDURE}
procedure_name
[;number]
[{@parameter data_type}
[VARYING][=default]
[OUTPUT]][,…n]
[WITH] {RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}]
[FOR REPLICATION]
ASsql_statement […n]
所有的参数同创建存储过程完全一致，这里不再赘述。
（4）重命名存储过程
a.SSMS
b.语句
SP_RENAME
[@objname=] ‘object_name’,
[@newname=]’new_name’
[,[@objtype=] ‘object_type’]
参数说明如下：
- [@objname=] ‘object_name’：存储过程当前的名称。
- [@newname=]’new_name’：新名称。
- [,[@objtype=]‘object_type’]：要重命名对象的类型。对象类型为存储过程或触发器时，值为OBJECT。
（5）删除存储过程
a.SSMS
b.语句
PROP PROCEDURE 存储过程的名称
二、触发器
1.一些基本知识
- 它是一个特殊的存储过程，它的执行不是通过程序调用或手工启动，而是通过某些事件来触发。
- 与存储过程相比，触发器更多的是维护数据的完整性。
- 分类：分为DDL触发器（数据库发生数据定义语言DDL事件时，调用DDL触发器）和DML触发器（数据库发生数据操作语言DML事件时，调用DML触发器）
- 触发器通常由3个部分组成：事件（插入、删除、修改等操作） + 条件（触发器测试条件是否成立，若成立就执行相应的动作，否则什么也不做） + 动作（一系列对数据库的操作或其他操作）
- 触发器可以嵌套执行，比如一个触发器执行激发另一个触发器的操作。最多可以进行32层嵌套。
2.触发器的常用操作
（1）创建触发器
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ] {
{ { FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ] [ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ …n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ …n ]
} ]
sql_statement [ …n ]
} }
语句格式有点复杂，参数的说明如下：
- trigger_name：触发器的名称。必须要复合标识符规则，不能以#或##开头。
- table | view ：在其上执行的触发器的表或视图。
- WITH ENCRYPTION：加密syscomments表中包含CREATE TRIGGER语句文本的条目。
- FOR | AFTER ：AFTER指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。 如果仅指定 FOR 关键字，则 AFTER 是默认设置。视图上不能定义AFTER触发器。
- INSTEAD OF ：指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。（意思就是INSTEAD OF 触发器用来代替通常的触发动作，即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，INSTEAD OF 触发器的动作要早于表的约束处理。）
- { [DELETE] [,] [INSERT] [,] [UPDATE] }：指定在表或视图上执行哪些数据修改语句时将会激活触发器的关键字。要求至少选定一个选项。指定选项多于一个时，用逗号分隔。
- WITH APPEND ：指定应该添加现有类型的其它触发器，不能和INSTEAD OF 一起使用，如果显式声明 AFTER 触发器，也不能使用该子句。
- NOT FOR REPLICATION ：表示当复制进程更改触发器所涉及的表时，不应该执行触发器。
- AS：是触发器要执行的操作。
- sql_statement ：是触发器的条件和操作。触发器条件指定其它准则，以确定 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。
CREATE TRIGGER 触发器
ON STUDENT
AFTER INSERT
AS
RAISERROR('正在向表中插入数据', 16, 10)
执行之后就创建了一个名为触发器的触发器。每次向表中插入数据时都会显示如下的内容：
（2）修改触发器
ALTER TRIGGER
ON { table | view }
[ WITH ENCRYPTION ] {
{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ] [ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ …n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ …n ]
} ]
sql_statement [ …n ]
}
参数同创建触发器的相同。
（3）重命名触发器
SP_RENAME OLDNAME，NEWNAME
SP_RENAME 触发器,触发器1
（4）查看触发器
a.使用系统存储过程查看触发器信息
SP_HELPTRIGGER < 触发器所属表名称> [类型]
如果不指定触发器类型的话，将会列出所有的触发器。
SP_HELPTRIGGER 'STUDENT','INSERT'
b.使用系统存储过程查看触发器代码
SP_HELPTEXT <触发器名称>
SP_HELPTEXT '触发器1'
c.使用系统存储过程查看触发器其他信息
SP_HELP <触发器名称>
SP_HELP '触发器1'
（5）禁用/启用触发器
ALTER TABLE 触发器所属表名称
{ENABLE|DISABLE}TRIGGER {ALL|触发器名称[,…n] }
参数说明如下：
- {ENABLE|DISABLE}TRIGGER：指定启用或禁用TRIGGER_NAME。
- ALL：不指定触发器名称的话，指定ALL则启用或禁用所有触发器。
扫一扫
5520
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
