SQL基础学习5-存储过程与触发器

存储过程

存储过程的概述

概念：
存储过程是一组能够完成特定功能的Transact-SQL语句集，经编译后存储在数据库中，用用户通过过过程名和给出的参数来调用它。有两种方法存储和执行代码

1. 在客户端存储代码，然后创建向 SQL Server 发送SQL命令（或SQL语句）并处理返回结果的应用程序；
2. 将发送的SQL语句存储在服务器端的数据库中，然后由客户端应用程序调用执行这些代码。这些存储在服务器端数据库中供客户端调用执行的SQL语句就是存储过程。

功能

1. 接受输入参数并以输出参数的形式将多个值返回给调用过程。
2. 包含执行数据库操作（包括调用其它过程）的多句编程语句。
3. 向调用过程返回状态值，以表明成功或失败（以及失败原因）。

存储过程的特点

1. 允许模块化程序设计
2. 改善性能，执行速度快
3. 能够有效减少网络流量
4. 可作为安全机制使用，提高数据库的安全性

存储过程的分类
分为三类：系统存储过程，用户自定义存储过程，扩展存储过程。

创建和执行用户存储过程

创建存储过程

CREATE PROC[ EDURE ] 存储过程名 
[ { @参数名  数据类型 } [ = default ] [OUTPUT] 
] [ , ... n ]
[WITH ENCRYPTION]
[WITH RECOMPILE]
  AS  
    SQL语句 [ ... n ]

@参数名称：存储过程中可以没有参数，也可以声明一个或多个参数。参数名称必须以@作为第一字符。参数后面带OUTPUT,表示为输出参数
WITH ENCRYPTION:对存储过程加密，其他用户无法查看存储过程的定义。
WITH RECOMPILE:每次执行该存储过程时都重新编译。
default：表示参数的默认值。如果定义了默认值，则在执行存储过程时，可以不必指定该参数的值。
OUTPUT：表明参数是输出参数。使用 OUTPUT 参数可将信息返回给调用者。

按定义中的参数形式，可以把存储过程分为不带参数的存储过程、带输入参数的存储过程和带输出参数的存储过程3类。

不带参数的存储过程

1. 创建与执行不带参数的存储过程

CREATE PROCEDURE 存储过程名  --定义过程名
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
  sql_statement    --过程体

--执行存储过程
EXECUTE  存储过程名

存储过程创建成功后，用户可以执行存储过程来检查存储过程的返回结果。

带输入参数的存储过程

2. 创建与执行带输入参数的存储过程

CREATE PROCEDURE 存储过程名
@参数名称  参数数据类型 [=参数默认值]
AS
  sql_statement
--为测试过程体的正确性  
--如在过程体sql_statement中
SELECT Sname,Sdept
FROM Student
WHERE Sno=@sno --假定输入参数(参数对应的属性=@定义的参数) 

--执行存储过程
--1）使用参数名传递参数值
EXECUTE 存储过程名  @参数名=参数值
--2）按位置传递参数值
EXECUTE [参数值