Sqlserver存储过程快速上手分享

一、前言

1.1 什么是Sqlserver

根据百度百科介绍：美国Microsoft公司推出的一种关系型数据库系统。SQL Server是一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的数据库管理系统，实现了与WindowsNT的有机结合，提供了基于事务的企业级信息管理系统方案。

1.2 什么是存储过程

根据Microsoft介绍: SQL Server 中的存储过程是由一个或多个 Transact-SQL 语句或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用构成的一个组。 过程与其他编程语言中的构造相似，这是因为它们都可以：

接受输入参数并以输出参数的格式向调用程序返回多个值。
包含用于在数据库中执行操作的编程语句。 这包括调用其他过程。
向调用程序返回状态值，以指明成功或失败（以及失败的原因）。

1.3 使用场景

Sqlserver在一些小型的场景还是非常有用的。一般CS架构，如果没有单独架设服务器，只有一个客户端+一个数据库，这种结构来说，Sqlserver就承担着服务器的功能，Sqlserver存储过程可以用于大部分逻辑的编写，比如报表、逻辑判断、数据采集、监控等。

注意1：由于存储过程，可以看成为作为服务器编程使用，该功能具备增删改查的功能，增删改查就满足了大部分使用场景了。但是由于数据库本身的原因，如果是非常大量的操作，建议还是使用服务端程序进行操作，存储过程的维护和扩展的功能，真的不是很强大，建议评估后再使用

注意2：由于sqlserver

1.4 Sqlserver存储过程编写过程

大概步骤

编写基础结构、并且进行必要的注释
1、确定输入参数
1.1 对入参进行判断
2、编写过程
2.1 定义变量
2.2 写for循环
2.3 if判断（根据条件创建表、根据条件执行不同逻辑）
2.3 写中间表（创建表、移除表）
2.4 编写函数
3、确定返回结果
3.1 确定返回的字段
3.2 确定返回表的数量

问题思考：

问题1：如何调用其他存储过程

1.5 使用工具

目前连接 Sqlserver的工具有 SSMS、Navicat、DBeaver。其实对于编写存储过程来，这些都大同小异，主要是看人的使用情况。

我更偏向于使用 Navicat

二、创建一个sqlserver的存储过程

2.1 编写一个基础的结构

CREATE PROCEDURE [dbo].[Can_getScore]
AS
BEGIN
	-- routine body goes here, e.g.
	-- SELECT 'Navicat for SQL Server'
END

以上是最简单的结构，dbo为数据库名称、Can_getScore为存储过程名称，但是目前该存储过程不能保存，因为最少需要写一个 DML 语句

在最顶部编写注释

/*
	主要功能：xxx
	开发者：xxxx
	开发时间：xxxx

	测试：
	EXEC Can_getScore xxxxx
*/

最终结果为

/*
	主要功能：按一个月时间段获取学生成绩、按天获取学生成绩
	开发者：Can
	开发时间：2024-08-09

	测试：
	EXEC Can_getScore xxx
*/
CREATE PROCEDURE [dbo].[Can_getScore]
AS
BEGIN
	SELECT * FROM Student_Score
END

2.2 确定输入参数

根据需求：按一个月时间段（1-5 6-10 11-15 16-20 21-25 26-月底）获取学生成绩或者按一个月30天获取学生成绩
那么参数1，按时间段的参数为1 按天为2
参数2：指定是哪个月的范围
参数3：可以是指定获取某个人的数据

注意，存储过程是根据顺序来判断对应的入参的

例如
EXEC Can_getScore 1,‘2024-08-01’,‘2024-08-05’,‘小明’ — 按8月份时间段获取该学生数据

CREATE PROCEDURE [dbo].[Can_getScore]
@STYLE INT=1,
@StartDate DATETIME,
@EndDate DATETIME,
@Username VARCHAR(50) = ''
AS
BEGIN
	IF @STYLE=1
	BEGIN
		SELECT * FROM Student_Score
	END

    IF @STYLE=2
	BEGIN
		SELECT * FROM Student_Score
	END
END

• 1.1 对入参进行判断
  例如 @STYLE 只能输入 1 和 2

@StartDate @EndDate的开始时间和结束时间只能同一个月份

	--- 前置条件
	IF @STYLE != 1 AND @STYLE != 2
	BEGIN
			RAISERROR('类型只能输入1或2.', 16, 1)
			RETURN
	END

	IF MONTH(@StartDate) != MONTH(@EndDate) OR YEAR(@StartDate) != YEAR(@EndDate) 
	BEGIN
			RAISERROR('只能查找同个年月份的数据.', 16, 1)
			RETURN
	END

2.3 编写过程

思考：天数进行for循环

例如 2024-08-09 ~ 2024-08-19，天数进行循环，并且获取当天早上00:00:00 到晚上 23:59:59

    DECLARE @MiddleDate DATE = @StartDate;

 	WHILE @MiddleDate <= @EndDate  
 	BEGIN
		
		
		SELECT @MiddleDate=DATEADD(DAY, 1, @MiddleDate)
 	END

思考：然后把for循环的结果存起来

    ---创建表，并把数据取下来
    CREATE TABLE #TMP1
    (
            ID NVARCHAR(100),
            minTime DATETIME,
            maxTime DATETIME
    );

    DECLARE @MiddleDate DATE = @StartDate;

    DECLARE @StartDatefor1 DATETIME2(7), @endDatefor1 DATETIME2(7); 

 	WHILE @MiddleDate <= @EndDate  
 	BEGIN
        SET @StartDatefor1 = CAST(CAST(@MiddleDate AS VARCHAR(10)) + ' 08:00:00' AS DATETIME2(7)); 
		SET @endDatefor1 = DATEADD(SECOND, -1, CAST(CAST(DATEADD(DAY, 1, @MiddleDate) AS VARCHAR(10)) + ' 08:00:00' AS DATETIME2(7)));

        insert into #TMP1 (ID,minTime,maxTime) values (@MiddleDate,@StartDatefor1,@endDatefor1)
		
		SELECT @MiddleDate=DATEADD(DAY, 1, @MiddleDate)
 	END

    SELECT * FROM #TMP1

    DROP TABLE #TMP1

结果运行

EXEC Can_getScore 1,‘2024-07-01’,‘2024-08-05’,‘’

2024-07-01	2024-07-01 08:00:00.000	2024-07-02 07:59:59.000
2024-07-02	2024-07-02 08:00:00.000	2024-07-03 07:59:59.000
2024-07-03	2024-07-03 08:00:00.000	2024-07-04 07:59:59.000
2024-07-04	2024-07-04 08:00:00.000	2024-07-05 07:59:59.000
2024-07-05	2024-07-05 08:00:00.000	2024-07-06 07:59:59.000

关于定义变量

1、定义字符串
declare @FSELLER  VARCHAR(100)='';
2、定义Int类型
DECLARE @pavg int
3、定义时间类型
DECLARE @StartDate DATE = '2024-07-01';
4、定义Double类型
DECLARE @avg_price DECIMAL(23,10)=0

写中间表（创建表、移除表）

编写函数

案例，创建一个函数

该函数的功能，把“秒”转化为“时间”

CREATE FUNCTION [dbo].[F_SecondToString]
(
 @second int --秒
)
RETURNS NVARCHAR(100)
AS 
BEGIN
DECLARE @Result NVARCHAR(100)
Declare @h int
Declare @m int
Declare @s int
set @h=@second/3600
set @m=@second%3600/60
set @s=@second%3600%60


select @Result=right(REPLICATE('',1)+convert(varchar(50),@h),3)+':'
+right(REPLICATE('0',1)+convert(varchar(50),@m),2)+':'
+right(REPLICATE('0',1)+convert(varchar(50),@s),2)


--SET @Result=(SELECT CONVERT(VARCHAR(10),@second/3600)+'时'+CONVERT(VARCHAR(10),@second%3600/60)+'分'+CONVERT(VARCHAR(10),@second%3600%60)+'秒')

RETURN @Result
END

查看 1314 是多少小时分钟

DECLARE @secNumber int=1314

select dbo.F_SecondToString(@secNumber)

结果

0:21:54

以上可以把 函数与过程结合起来使用

三、结论

让我们再回顾一下这个过程

编写基础结构、并且进行必要的注释
1、确定输入参数
1.1 对入参进行判断
2、编写过程
2.1 定义变量
2.2 写for循环
2.3 if判断（根据条件创建表、根据条件执行不同逻辑）
2.3 写中间表（创建表、移除表）
2.4 编写函数
3、确定返回结果
3.1 确定返回的字段
3.2 确定返回表的数量

有增删改查、for循环、if等，这不妥妥可以进行编程吗？

所以sqlserver的存储过程，可以作为一个后端服务使用，类似接口一样的存在。

但是在编写存储过程的过程中，记得进行备份，还有在写update delete语句的时候，要小心。

思考：如何调用其他存储过程

方式一

目前有两个方式，一个是存储过程中调用存储过程，用变量参数，例如
存储过程1

ALTER PROCEDURE [dbo].[Can_getNow]
@NOWString VARCHAR(100)
AS
BEGIN
	-- routine body goes here, e.g.
	-- SELECT 'Navicat for SQL Server'
	SELECT 
	@NOWString = CONVERT(VARCHAR(6), GETDATE(), 112)
	
	SELECT @NOWString

END

存储过程调用存储过程1

ALTER PROCEDURE [dbo].[Can_myTest]
AS
BEGIN
	-- routine body goes here, e.g.
	-- SELECT 'Navicat for SQL Server'
	declare @NOWString  VARCHAR(100)='';
	
	EXEC Can_getNow @NOWString
	
	SELECT @NOWString
END

方式二

调用方，创建一个表
被调用放，把数据存进去
调用放，把数据获取出来，然后删除该表

本课程分享就到这里了。