SQL -- 存储过程（详细）

一 、概述

1-1 存储过程基本概念

什么是存储过程

存储过程是事先编好的、存储在数据库中的一组被编译了的T-SQL命令集合，这些命令用来完成对数据库的指定操作。

---

存储过程的优点

1. 减少网络流量

• 存储过程是数据库服务器端完成的，只向客户端返回执行的结果。
• 实现相同功能时，不使用存储过程，需要将数百行代码传送到网络中；使用存储过程，只需要向服务器端传送存储过程中的名称与参数。可以看出节省了网络流量，提高了执行效率。
• 存储过程的执行结果是在服务器中完成，则避免了服务器端与客户端之间的多次往返。存储过程只需要将最终结果通过网络传输到客户端。

2. 提高系统性能

• 一般T-SQL语句都是执行一次编译一次，但是存储过程是在执行第一次时进行编译，编译后存放在数据库服务器的过程高速缓存中，之后使用不需要再次分析和编译。
• 对数据库进行复杂操作时（多表执行Insert、Update 或 Delete时），可以将这些复杂操作用存储过程封装起来并且与事务处理结合在一起使用，节省了分析、解析、和优化代码所需要的CPU资源和时间。

3. 安全性高

• 使用存储过程可以完成所有数据库操作，并且可以授予没有直接执行存储过程中语句的权限用户，也可以执行该存储过程的权限。
• 可以防止用户直接访问表，强制用户使用存储过程执行特定的任务。

4. 可重用性

• 存储过程只需要创建并存储在数据库中，可以重复使用，并且存储过程的修改也是独立于程序的源代码，故减少了数据库开发人员的工作量。

5. 可自动完成需要预先执行的任务

• 存储过程可以在系统启动时自动执行，完成一些需要预先执行的任务，而不必在系统启动后再进行人工操作。

1-2 存储过程的类型

1. 系统存储过程

• 系统存储过程是微软内置在SQL Server中的存储过程。在SQL Server 中，系统存储过程位于master数据库中,以sp_前缀开头，并标记为System。

2. 扩展存储过程

• 扩展存储过程是可以在SQL Server环境外执行的动态链接库来实现，以xp_前缀开头。

3. 用户存储过程

• 用户存储过程用于用户数据库中的创建，通常用于与数据库的交互，用于完成特定数据库操作任务，可以接受和返回用户提供的参数，名称不能以 sp_ 开头

二、语法 T-SQL

1-1 创建存储过程

CREATE PROC[EDURE] <存储过程名称>     -- 定义存储过程名称
[ @参数名称 参数类型]                 -- 定义传值参数及类型
[=default][OUTPUT][,...n1]           -- 定义参数的属性：默认值，返回值
AS
SQL 语句[,...n2]                     --执行的操作

举例一：查询 所有用户的信息

CREATE PROC proc_student_select
AS
	SELECT * FROM student;

---

举例二：通过学生学号和学生名称来查询学生姓名、性别和所属院系。
这里讲一下 “@Name nvarchar=‘张莉’”的用法 ，给@Name参数赋了默认值“张莉”。若Name参数传值，则Name=传入的参数，若没有传值，则Name参数=“张莉”。

CREATE PROC proc_student_ByIDAndName_select
@ID char(10),@Name nvarchar(5)='张莉'
as
	select sname,ssex,sdepartment from student where s_id=@ID and sname=@Name

---

举例三：通过用户学号查询用户姓名并返回

CREATE PROC proc_student_ByIDAndName_Output
@ID char(10),@Name nvarchar(5) OUTPUT
as
	select @Name=sname from student where s_id=@ID

1-2 查看存储过程

EXEC[UTE] {存储过程名称}                                    -- 查看存储过程名
[[@参数名称=]value | @variable [OUTPUT]|[DEFAULT][,...n1]}  --参数名称

举例一：查看proc_student_select存储过程----获取所有用户的信息

EXEC proc_student_select

---

**举例二：通过学生学号和学生名称来查询学生姓名、性别和所属院系。** 1. Name参数不传值 ```sql EXEC proc_student_ByIDAndName_select @ID= '20070101' ``` 2. 不写参数名称并只传一个参数 ```sql EXEC proc_student_ByIDAndName_select '20070101' ``` 其中1、2执行结果： 

3. Name传值

EXEC  proc_student_ByIDAndName_select @ID= '20070102',@Name='张建'

4. 参数修改位置

EXEC  proc_student_ByIDAndName_select @Name='张建',@ID= '20070102'

5. 不写参数名称

EXEC  proc_student_ByIDAndName_select '20070102','张建'

其中3、4、5执行结果：

---

举例三：通过用户学号查询用户姓名并返回

DECLARE @NAME_1 nvarchar(5)
EXEC proc_student_ByIDAndName_Output @ID='20070101' ,@Name=@NAME_1 OUTPUT
PRINT @NAME_1

1-3 修改存储过程

ALTER PROC[EDURE] <存储过程名称>   -- 定义修改存储过程名称
[ @参数名称 参数类型]              -- 定义传值参数及类型
[=default][OUTPUT][,...n1]        -- 定义参数的属性：默认值，返回值
AS
SQL 语句[,...n2]                  -- 执行的操作

举例：
修改与创建存储过程 相比，只是定义存储过程的关键字不同（创建：CREATE，修改：ALTER）

ALTER PROC proc_student_ByIDAndName_Output
@Name nvarchar(5) OUTPUT
as
	select @Name=sname from student

1-4 删除存储过程

DROP PROC[EDUTE] <存储过程名称>  --删除存储过程名
[,...n]}                        --参数名称

举例:
删除 proc_student_select 触发器

Drop PROC proc_student_select

1-5 存储过程命名规则

存储过程命名规则：
pro_[小写表名][操作参数][操作关键字]

1. 以 ‘pro_’ 为前缀
2. 小写的表名
3. 操作参数：举例，通过ID查询student，则为ByID
4. 操作关键字：

• 如果存储过程返回一条记录那么后缀是：Select
• 如果存储过程插入数据那么后缀是：Insert
• 如果存储过程更新数据那么后缀是：Update
• 如果存储过程有插入和更新那么后缀是：Save
• 如果存储过程删除数据那么后缀是：Delete
• 如果存储过程更新表中的数据 (ie. drop and create) 那么后缀是：Create
• 如果存储过程返回输出参数或0，那么后缀是：Output

三、创建举例用的数据库及表

创建举例用的数据

1. 创建StuInfo数据库，若存在则直接使用
2. 创建student表，若已存在删除后创建
3. 插入数据

可以将下方SQL语句 复制到 SSMS 工具上直接执行，如果有时间，建议自己敲一遍。扩展一点，SQL语句关键字最好使用大写。如果使用小写，数据库会将关键字从小写转换为大写，增加这一过程则性能会降低，所以最好直接使用大写。

use master
go
-- 判断数据库是否已经存在，若存在不删除直接使用，反之新建
IF EXISTS(SELECT * FROM sysdatabases WHERE name='StuInfo')	
	PRINT 'StuInfo数据库已存在'
ELSE
BEGIN
	--创建数据库
	CREATE DATABASE StuInfo
	ON
	(
		NAME=StuInfo,
		FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StuInfo.mdf',
		SIZE=3MB,
		MAXSIZE=UNLIMITED,
		FILEGROWTH=10%
	)
	LOG ON
	(
		NAME=StuInfo_log,
		FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StuInfo_log.ldf',
		SIZE=1MB,
		MAXSIZE=100MB,
		FILEGROWTH=10%
	)
END
GO

USE StuInfo
GO
-- 判断数据表是否已经存在，若存在删除后创建
IF OBJECT_ID(N'StuInfo..student',N'U') IS NOT NULL
	DROP TABLE student

CREATE TABLE student
(
[s_id][char](10)NOT NULL,
[sname][nvarchar](5)NULL,
[ssex][nvarchar](1)NULL,
[sbirthday][date]NULL,
[sdepartment][nvarchar](10)NULL,
[smajor][nvarchar](10)NULL,
[spoliticalStatus][nvarchar](4)NULL,
[phoneName][varchar](100)NULL,
[photo][varchar](max)NULL,
[smemo][nvarchar](max)NULL,
CONSTRAINT[PK_student]PRIMARY KEY CLUSTERED
(
	[s_id] ASC
))on [PRIMARY]
GO

-- 向数据库添加数据
USE StuInfo
INSERT INTO 
student  ([s_id],[sname],[ssex],[sbirthday],[sdepartment],[smajor],[spoliticalStatus],[photo],[smemo])
VALUES
('20070101',N'张莉',N'女','1/30/1998',N'信息工程学院',N'计算机',N'党员',NULL,NULL)
,
('20070102',N'张建',N'男','1/30/1998',N'信息工程学院',N'计算机',N'党员',NULL,NULL)
GO