十二、存储过程
存储过程是一组欲编译的SQL语句的集合,它可以作为一个独立的数据库对象,也可以作为一个单元被用户的应用程序使
用,以实现某一功能。
按定义的文体可分为三种:
1、系统存储过程:SQL SERVER内置的存储过程,以“sp_”开头,用于进行系统设置或执行相关操作。
2、扩展存储过程:多数由传统的设计语言编写,其内容不保存在SQL SERVER中,而是以DLL形式单独存在.
以“xp_”开头,用于扩展SQL Server服务器功能的存储过程。
3、用户自定义存储过程:用户通过SQL语句自己创建的存储过程,用于实现某一功能。
创建存储过程:
CREATE PROCEDURE procedure_name [ ; number] [{@parameter data_type} [VARYING] [=default][OUTPUT]][ ,...N]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }]
[ FOR REPLICATION] AS sql_statement
procedure_name:要创建的存储过程名称
number:对存储过程进行分组,以后可以用一条DROP PROCEDURE将同组的过程一起删除。
@parameter:存储过程的参数,可声明一个或多个,用户必须在执行过程时提供每个所声明参数的值。
data_type:参数的数据类型。
varying:指定作为输出参数支持的结果集,仅适用于游标参数。
default:为参数设置默认值。
output:表明参数是返回参数
n:表示可定义多个参数。
sql_statement:存储过程中的过程体。
例:
create procedure p_selectUser as select * from user
exec p_selectUser
create procddure p_selectGoods @goodsID int =1 as select * from goods where id=@goodsID
exec p_selectGoods 13 或 exec p_selectGoods @goodsID=13
在SQL Server中使用Execute语名招待存储过程: exec procedure_name
修改存储过程:
ALTER PROCEDURE procedure_name [; number] [{@parameter data_type} [VARYING][=default][OUTPUT]][ ,...n]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION }]
[FOR REPLICATION] AS sql_statement
删除存储过程:
DROP PROCEDURE procedure_name [ , ...n]
十三、触发器
1、它是一种特殊类型的存储过程,在插入、删除或修改特定的数据时触发执行。通常可以强制执行一定的业务规则,以
保持 数据完整性、检查数据有效性、实现数据库管理任务和一些附加的功能。
2、它不能通过名称被直接调用,更不允许设置参数。
3、分类:
1)AFTER触发器:会在触发INSERT , UPDATE, DELETE动作之后执行,它也可以在已插入一行或多行和所有约束已被
处理且通过后才触发。
2)INSTEAD OF触发器:它是在激活触发器的语句执行前,系统执行触发器中的内容,并取代激活触发器语句的执行
4、创建
CREATE TRIGGER trigger_name ON {table | view} [ WITH ENCRYPTION ] {{{FOR | AFIER | 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){comparsion_operator}
column_bitmask[...n]}] sql_statement[...n]}}
5、注意事项
1)CREATE TRIGGER必须是批处理中的第一个语句,若该语句前有其他语句,应该使用批处理结束符GO隔开。
2)不能在临时表或系统表上创建触发器,但在触发器中可以引用临时表,不能引用系统表。
3)创建触发器的权限默认分配给表的所有者,且不能将该权限转给其他用户。
4)触发器可以引用当前数据库以外的对象,但只能在当前数据库中创建触发器。
5)在含有用DELETE或UPDATE操作定义外键的表中,不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。
6)WRITETEXT语句不会引发INSERT或UPDATE触发器。
7)TRUNCATE TABLE语句不能引发DELETE触发器,因为该语句没有记录。
6、删除触发器
DROP TRIGGER {trigger}[,...n]
十四、游标
1、游标是映射结果集并在结果集内的单行上建立一个位置的实例,使用它,用户可以访问结果集中任意行的数据。
2、使用游标的步骤:
1)声明(DECLARE):定义游标的名称、类型和属性。
DECLARE cursor_name CURSOR [LOCAL | GLOBAL][ FORWARD_ONLY | SCROLL][STATIC | KEYSET |
DYNAMIC | FAST_FORWARD][READ_ONLY | SCROLL_LOCKS | OPTIMISTIC][TYPE_WARNING]
FOR select_statement [FOR UPDATE [OF column_name[,...n]]]
2)调用(OPEN):打开和直充数据。
OPEN {{[GLOBAL] cursor_name} | cursor_variable_name}
3)提取(FETCH):从结果集中读取数据并进行处理。
FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] FROM ]
{{[GLOBAL] cursor_name} | @cursor_variable_name} [ INTO @variable_name[,...n]]
4)关闭(CLOSE):停止游标使用的查询,但并不删除游标定义,可以使用OPEN语句再次打开。
CLOSE {{[GLOBAL] cursor_name} | cursor_variable_name}
5)释放(DEALLOCATED):删除游标并释放其占用的所有资源。
DEALLOCATE {{[GLOBAL] cursor_name | @cursor_variable_name}