SQL Serever学习14——存储过程和触发器
存储过程 在数据库中很多查询都是大同小异，编写他们费时费力，将他们保存起来，以后执行就很方便了，把SQL语句“封装”起来。 存储过程的概念 存储过程是一组SQL语句集，经过编译存储，可以”一次编译，多次执行“。除了第一次调用需要编译，后面都可以直接执行，执行速度更快，而不是普通SQL语句一样，每一次执行都要编译。 提供一种安全机制，如果某用户满意特定视图的使用权限，但是有使用存储过程的权限，通过执行存储过程，依旧可以获取存储过程中的表。 存储过程的优点 改善系统性能，一次编译，多次执行，而普通SQL是每一次都编译执行 安全机制 重用性，可以反复调用 共享性 减少网络流量，存储过程是服务器上编译好的T-sql代码，对一个可能需要几百行的T-sql操作，在客户端只需要通过一条执行语句完成，而不是发送几百行代码 存储过程分类 分为3类： 系统存储过程 通常以sp_开头，有sp_helpdb查看数据库名称和大小 所在位置 还有sp_helptext用于小时规则，默认值，触发器 sp_renamedb重命名数据库 sp_rename 重命名表，列，用户定义的类型 sp_helplogins看用户登录的数据 用户存储过程 用户编写的存储过程（主要是的的就是这个部分） 扩展存储过程 为扩展sqlserver提供的方法，可以动态的加载和执行动态链接库的函数，以xp_开头 存储过程的创建语句 CREATE PROC[EDURE] 存储过程名 [@参数名 参数类型 =默认值 ] OUTPUT WITH RECOMPILE FOR REPLICATION AS SQL语句组 说明：OUTPUT是输出参数， RECOMPILE表示sqlserver不对存储过程计划进行高速缓存，每一次重新编译， FOR REPLICATION表示存储过程只能在复制过程中使用，而且不和WITH RECOMPILE一起使用。 存储过程执行语法格式 EXEC[UTE] [@状态值=] 存储过程名 [@参数名=] 参数值 存储过程重新编译 sqlserver强制重新编译存储过程3种方法： 系统存储过程 ，EXEC sp_recompile 存储过程名 可以创建的时候使用WITH RECOMPILE 指定WITH RECOMPILE选项，EXEC 存储过程 WITH RECOMPILE 存储过程修改 就是把创建语句的CREATE 换成ALTER ，仅此而已。 ALTER PROC[EDURE] 存储过程名 [@参数名 参数类型 =默认值 ] OUTPUT WITH RECOMPILE FOR REPLICATION AS SQL语句组 存储过程删除 可以一次删除多个存储过程 DROP PROCEDURE PROC_3 , PROC_4 创建一个简单的存储过程 CREATE PROC 简单的存储过程 AS BEGIN SELECT 商品编号 ,商品名称,销售价 FROM 商品表 WHERE 商品名称='笔记本' END GO 存储过程中输入输出参数在AS之前，申明，局部变量在AS之后，申明。 创建带参数的存储过程 CREATE PROC 带参数的存储过程 @SPM VARCHAR(20) ='笔记本' AS BEGIN DECLARE @JG SMALLMONEY SELECT @JG=销售价 FROM 商品表 WHERE 商品名称=@SPM RETURN @JG END GO 使用带参数，有返回值的存储过程 DECLARE @MAXJG SMALLMONEY EXEC @MAXJG=带参数的存储过程 SELECT @MAXJG AS 最高的实际销售价格 数据库的触发器 触发器的概念 触发器是特殊的存储过程，只不过它是在操作数据表（增删改）之前或者之后，自动执行的，不能铜鼓名称来调用。 优点 自动，操作表之后立即激活 实施更为复杂的数据约束 级联修改数据库中相关表，自动触发其他与之相关操作 跟踪变化，撤销和回滚 返回自定义错误信息（一般的约束是无法返回信息的） 可以调用更多的存储过程 分类 触发器分为2类： DML DDL DML触发器 进行了增删改之后自动激活，有分为AFTER触发（操作后触发，执行优先级在约束检查之后），和INSTEAD OF 触发（不会执行增删改操作，而是执行INSTEAD OF指定操作，执行优先级在约束检查之前）。 DDL触发器 在执行CREATE, ALTER , GRANT , DENY , REVOKE, UPDATE STATISTICS语句触发。 触发器专用临时表 有2个触发器专用临时表INSERTED 和DELETED 表存在INSERT触发器（DDL触发器），插入数据，系统自动创建一个与表一样结构的INSERTED临时表，新的记录同时，添加到触发器表和INSERTED中，INSERTED保存的副本，方便用户查找当前插入数据。 表存在DELETE触发器，删除数据，系统自动创建一个DELETED临时表，用来保存被删除记录。 修改表，就是删除一条记录，然后插入一条记录，删除记录添加到DELETED表，新增记录添加到INSERTED表。 触发器的创建和触发 DML触发器 CREATE TRIGGER 触发器 ON 表名 FOR | AFTER | INSTEAD OF INSERT ,UPDATE , DELETE AS SQL语句 DDL触发器 CREATE TRIGGER 触发器 ON ALL SERVER | DATABASE FOR | AFTER 事件种类 AS SQL语句 触发器的修改 和创建类似，就是把CREATE 改为ALTER 触发器的删除 DROP TRIGGER 触发器, 触发器2 触发器的启用与禁用 禁用触发器 ALTER TABLE 表名 DISABLE TRIGGER 触发器名 DISABLE TRIGGER 触发器名 禁用数据库级别触发器 DISABLE TRIGGER 触发器名 ON DATABASE 启用触发器 ALTER TABLE 表名 ENABLE TRIGGER 触发器名 ENABLE TRIGGER 触发器 ON 表名 简单的触发器 USE 销售管理 GO CREATE TRIGGER tr_insert_mj ON 买家表 FOR INSERT --指定触发类型 AS BEGIN PRINT '有新买家插入到买家表' END GO 验证，插入一条数据 INSERT INTO 买家表 VALUES('M05','MARS',12232323,'J01') GO 看消息，触发器运行了 创建一个DDL触发器，当删除数据库中的表时，就撤销该操作，并提示：禁止删除数据库表！ USE 销售管理 GO CREATE TRIGGER tr_drop_dll ON DATABASE FOR DROP_TABLE AS BEGIN ROLLBACK TRANSACTION PRINT '禁止删除数据表！' END GO 验证一下，删除表 DROP TABLE 买家表 GO 看信息提示 禁用触发器 ALTER TABLE 买家表 DISABLE TRIGGER tr_insert_mj 或者 DISABLE TRIGGER tr_insert_mj ON 买家表 启用触发器 ALTER TABLE 买家表 ENABLE TRIGGER tr_insert_mj 或者 ENABLE TRIGGER tr_insert_mj ON 买家表
