存储过程-2 基本操作

官方文档查看

1 定义

Sql Server的存储过程是由一个或多个Transact-SQL语句组成
一个存储过程中可以包含查询、插入、删除、更新等操纵的一系列SQL语句，当这个存储过程被调用执行时，这些操作也会同时执行。

1. (接受参数、输出参数)接受输入参数并输出参数的格式向调用程序返回多个值
2. (可嵌套使用)包含用于在数据库中执行操作的编程语句(可以调用表、视图、存储过程、函数)
3. (可返回值)向调用程序返回状态值，以指明成功或失败

2 存储过程的好处

1. 减少服务器/客户端网络流量
2. 更强安全性: 
	对于表结构的封装，存储过程有授权权限要求
3. 代码的重复使用
	 存储过程可以嵌套使用，支持代码重用
4. 存储过程可以接受与使用`动态参数执行其中的SQL语句`
5. 更容易维护
	客户端不需要知道数据库的布局、关系、进程额任何更改情况
6. 提高性能
	存储过程比一般SQL语句执行速度快，存储过程在创建时就已经被编译，每次执行不需要重新编译，但是SQL语句每次执行需要重新编译
	在首次执行存储过程时，将编译存储过程，并且创建一个执行计划，供以后的重复使用
	因为查询处理器不需要创建新计划，所以通常用更少的时间来处理过程
	

3 存储过程的类型

• 用户定义

用户定义的过程可在用户定义的数据库中创建
可以使用Transact-SQL(T-SQL)开发或可视化工具开发

• 临时

临时存储过程是用户定义存储过程的一种形式
临时存储过程与永久存储过程相似，但临时存储过程存储与 tempdb中

• 系统

系统存储过程包含在SQL Server,它们物理上存储在内部隐藏的 Resource 数据库中，但逻辑上出现在每个系统定义数据库和用户定义数据库的 sys 架构中。
msdb 数据库还在 dbo 架构中包含用于计划警报和作业的系统存储过程。 因为系统过程以前缀 sp_ 开头

4 存储过程管理

4.1 创建存储过程

• 创建方式

• 可视化工具: SQL Server Management Studio

• Transact-SQL

• T-SQL创建存储过程

-- 创建存储过程
-- 对HumanDataBase数据库的EmployeesTable表创建一个名为HumanDataBase.EmployeesProc的存储过程
CREATE PROCEDURE HumanDataBase.EmployeesProc
	@LastName nvarchar(50),
	@FirstName nvarchar(50)
AS
	SET NOCUNT ON;
	SELECT FirstName, LastName, Department
	FROM HumanDataBase.EmployeesTable
	WHERE FirstName = @FirstName AND LastName = @LastName
	AND EndDate IS NULL;
GO

-- 执行存储过程
EXECUTE HumanDataBase.EmployeesProc N'Rose',N'Pilar';
或
EXEC HumanDataBase.EmployeesProc @LastName = N'Ackerman', @FirstName = N'Pilar';  

4.2 修改存储过程

• 修改方案

• 1.先删除存储过程，在创建同名存储过程(会删除已显示授予该存储过程的权限:不建议)

• 2.使用ALTER覆盖存储过程(可以保证存储过程已有权限不丢失)

• DROP and CREATE PROC

-- 1.先DROP存储过程
IF OBJECT_ID('HumanDataBase.EmployeesProc') IS NOT NUU
	DROP PROCEDURE HumanDataBase.EmployeesProc;
GO 
-- 2.创建存储过程
-- 继续CREATE PROC就行

• ALTER

-- 就一个覆盖操作
ALTER PROCEDURE HumanDataBase.EmployeesProc
	@Product varchar(25)
AS
	SET NOCUNT ON;
	SELECT * FROM HumanDataBase.EmployeesTable
	WHERE Product = @Product;
GO

4.3 删除存储过程

删除存储过程，可能会对引用这个存储过程的其他有影响

-- 语法:
DROP PROCEDURE [<stored procedure name];

-- 示例
DROP PROCEDURE HumanDataBase.EmployeesProc;
或
DROP PROC HumanDataBase.EmployeesProc;

4.4 执行存储过程

--语法:
EXECUTE PROCEDURE [<stored procedure name];
或
EXEC PROCEDURE [<stored procedure name];

-- 示例
EXEC sys.sp_who;  -- 执行系统存储过程 

EXECUTE HumanDataBase.EmployeesProc N'Rose',N'Pilar'; --执行用户自定义存储过程

4.5 指定参数

通过指定过程参数，调用程序可以将值传递给过程的主体
如果将参数标记为 OUTPUT 参数，则存储过程参数还可以将值返回给调用程序


每个参数都有 名称、数据类型、方法、参数指定默认值(可选)
使用过程调用提供的参数必须为常量或变量，不能将函数梦作为参数值
变量可以是用户定义变量或系统标量

-- 创建一个存储过程
CREATE PROC TestProc 
	@Name nvarchar(50),
	@Age int,
	@Sex nvarchar(2) = '男'  -- 默认Sex为男
	-- 使用OUT关键字，当存储过程退出时，向调用程序返回输出参数的当前值
AS 
	SELECT NOCUNT ON;  -- 阻止结果集返回受T-SQL语句影响的行计数信息
	SELECT * FROM USER
	WHERE Name like @Name AND Age > @Age AND Sex = @Sex;
	SET @MaxUser = (
		SELECT Name FROM USER WHERE Age = 
		(SELECT max(Age) as 'Age' FROM User) LIMIT 1;
	);
GO	

-- 隐式传参，必须与过程定义的变量顺序一致
EXEC TestProc '张三',18,'男'; 

-- 同上
EXEC TestProc N'张三',N'18',N'男';

-- 指定参数名称: 没有顺序的区别
EXEC TestProc @Name='张三',@Age=18,@Sex='男';

-- 使用参数的默认值(不为Name传递参数，使用默认参数值)
EXEC TestProc @Name='张三',@Age=18;

4.6 参数

参数用于存储过程以及调用存储过程或函数的应用程序或工具之间交换数据

• 输入参数允许调用方法将数据值传递到存储过程或函数
• 输出参数运行存储过程将数据值或游标变量传递到回调方
• 每个存储过程向调用方法返回一个整数返回代码(如果存储过程没有显示设置返回代码的值，默认返回代码为0)

CREATE PROC SampleProc 
	@Sno INT,
	@Total INT OUTPUT
AS
	DECLARE @Error INT
	SET @ErrorSave = 0 
	SELECT Sno,Sname,Sage,Ssex from Student 
	WHERE Sno = @Sno;
	
	IF ( @ERROR <> 0 ) -- 如果上一条T-SQL没有错误(错误就是0)
		SET @ERRORSave = @@ERROR
		
	SELECT @Total = SUM(sno) FROM Student;
	
	IF ( @@ERROR <> 0 )
		SET @ERRORSave = @@Error
	RETURN @ErrorSave
	
	
-- 执行存储过程
DECLARE @ReturnCode INT -- 定义变量用来接受存储过程返回值
DECLARE @TotalValue INT -- 定义变量用来做输出参数OUTPUT值
EXEC @ReturnCode = SampleProc @Sno=1,@Total=@TotalValue OUTPUT

4.7 从存储过程中返回数据

可通过三种方法将数据从过程返回到调用程序:
1.结果集
2.输出参数
3.返回代码

• 使用结果集返回数据

-- 此存储过程将返回所有 行数据
CREATE PROC TestProc
AS
	SET NOCUNT ON;
	SELECT Sno,Sname,Sage,Ssex FROM Student;
	
	RETURN;
GO

• 使用输出参数返回数据

-- SELECT语句将值赋值给@Total　　
CREATE PROC TestProc
	@Total INT OUTPUT  -- OUTPUT将值输出到变量中
AS
	SET NOCUNT ON;
	SELECT @Total=SUM(Sno) FROM Student;
	RETURN;
GO

• 返回代码

CREATE PROC TestProc
	@Sno INT = NULL -- 不传参，就是默认NULL
AS 
	IF @Sno IS NULL 
		BEGIN
			PRINT 'ERROR: 必须传递一个学号';
			RETURN (-1)
		END
	
	SELECT * FROM Student WHERE Sno = @Sno;
	
	IF @@ERROR <> 0 -- 如果查询报错
		BEGIN
			PRINT '参数不合法';
			RETURN (-1)
		END
	ELSE
		RETURN (1)

4.8 重命名存储过程

CREATE PROC TestProc
AS
	SELECT * FROM Student;
GO
-- 将名为 "TestProc" 的存储过程更名为"NewProc"
EXEC sp_rename 'TestProc','NewProc';

4.9 查看存储过程的定义

-- 系统函数 OBJECT_DEFINITION
SELECT OBJECT_DEFINITION(OBJECT_ID(N'TestProc'));

4.10 查看相关性

存储过程之间的相互依赖

官方文档:查看相关性