• SqlServer函数，存储过程的创建和使用

    1.函数

    在SQLserer中，函数是经常要用到的，不过大多数时候只是使用一些系统函数。

    函数的分类：

    MS将函数类型分为三类

    1. 标量函数
    2. 表值函数
    3. 系统函数

    标量函数的解释为
    用户定义标量函数返回在 RETURNS 子句中定义的类型的单个数据值。 对于内联标量函数，返回的标量值是单个语句的结果。 对于多统计信息标量函数，函数正文可以包含返回单个值的一系列 Transact-SQL 语句。 返回类型可以是除 text、 ntext、 image、 cursor和 timestamp外的任何数据类型。（大致可以理解为返回一个具体的值）
    表值函数的介绍为
    用户定义表值函数 (TVF) 返回 表 数据类型。 对于内联表值函数，没有函数主体；表是单个 SELECT 语句的结果集（就是返回一张表了）
    系统函数则是SQLServer提供的可以直接使用的函数。

    自定义函数

    标量函数

    自定义标量函数的公式为

    -- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
 [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    标量函数的定义比较简单。主要分为两个部分，第一部分

    CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
 [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
) //定义函数名和参数，例如
// Create function  CountRectangleSize(@len int,@wid int) //当然你也可以设置默认值和readonly属性
// return int

RETURNS return_data_type

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    第二部分则比较灵活

    	[ WITH <function_option> [ ,...n ] ]
	//函数选项------在BEGIN开始之前，可以做一些设置，像开启事务什么的，下面是MS的解释
	// NATIVE COMPILATION
    //|  SCHEMABINDING
    //| [ EXECUTE AS Clause ]
    //| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
    [ AS ]
    //BEGIN 和 END 中是函数的主体，在其中执行相关的操作（定义变量，赋值，判断。。。。）
    BEGIN
        function_body
        RETURN scalar_expression
    END

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    一个完整的标值函数

    CREATE FUNCTION GetReactangleSize(@len int,@wid int)
returns int
BEGIN
		DECLARE @Res int;
		SET @Res = ( SELECT @len * @wid )
		RETURN @Res
END		

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    执行：
    在这里插入图片描述

    表值函数

    表值函数的公式为

    CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [READONLY] }
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    只是在声明返回值上和标量函数不一样，因为返回的表结构需要自定义
    这里直接用例子来演示

    在这里插入图片描述
    不愿自己定义表的可以使用下面的导出sql文件

    -- ----------------------------
-- Table structure for Student
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type IN ('U'))
	DROP TABLE [dbo].[Student]
GO

CREATE TABLE [dbo].[Student] (
  [ID] int  IDENTITY(1,1) NOT NULL,
  [Name] varchar(200) COLLATE Chinese_Taiwan_Stroke_CI_AS  NOT NULL,
  [Age] int  NOT NULL,
  [Birth] datetime  NOT NULL
)
GO

ALTER TABLE [dbo].[Student] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of Student
-- ----------------------------
SET IDENTITY_INSERT [dbo].[Student] ON
GO

INSERT INTO [dbo].[Student] ([ID], [Name], [Age], [Birth]) VALUES (N'1', N'?建雄', N'22', N'1999-07-22 00:00:00.000')
GO

INSERT INTO [dbo].[Student] ([ID], [Name], [Age], [Birth]) VALUES (N'2', N'万天', N'22', N'2022-06-21 00:00:00.000')
GO

INSERT INTO [dbo].[Student] ([ID], [Name], [Age], [Birth]) VALUES (N'3', N'李承?', N'19', N'2022-07-01 16:01:48.880')
GO

INSERT INTO [dbo].[Student] ([ID], [Name], [Age], [Birth]) VALUES (N'4', N'金武', N'18', N'2022-07-01 16:02:23.850')
GO

SET IDENTITY_INSERT [dbo].[Student] OFF
GO


-- ----------------------------
-- Auto increment value for Student
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[Student]', RESEED, 4)
GO


-- ----------------------------
-- Triggers structure for table Student
-- ----------------------------
CREATE TRIGGER [dbo].[T1]
ON [dbo].[Student]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
	SELECT * FROM Student
END
GO


-- ----------------------------
-- Primary Key structure for table Student
-- ----------------------------
ALTER TABLE [dbo].[Student] ADD CONSTRAINT [PK_Student_ID] PRIMARY KEY CLUSTERED ([ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    Create Function WhoAgeIsBelow(@limit int)
returns @tb  TABLE(
	Name varchar(20),
	Birth datetime
)    //表值函数需要自定义返回的表结构，当然还有一种简化的方式等下再说
AS
BEGIN
	 //然后把查询的表结构插入@tb即可
	 insert @tb SELECT Name,Birth from Student where Age < @limit
	 //这里的return是固定的，但不许有后面的值
	 return
END

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    测试,输入20，得到如下
    在这里插入图片描述
    当然，如果你需要的返回表非常简单，那么也可以使用简化的方法。
    这种简化的方法被称为内联表值函数

    //内联表值函数不需要定义返回表结构，直接使用return返回查询到的表
Create Function FindAgeStudent(@limit int)
returns table
as 
return (select * from Student where Student.Age = @limit)

    • 1
    • 2
    • 3
    • 4
    • 5

    请添加图片描述
    请添加图片描述
    补充：
    在自定义的函数中，不可以去修改表的记录，即不可以使用insert,update,delete语句。
    同样，你也无法在自定义函数中去执行存储过程。
    例：

    ALTER FUNCTION GetEmpNo()
returns int

BEGIN
		DECLARE @wdnmd int,@result varchar(20);
		set @wdnmd = (SELECT COUNT(*) FROM t_role_permission)
		EXEC @result = plusplus  //在函数中定义执行存储过程不会报错，但在使用函数时会报错
		return @wdnmd
END

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    请添加图片描述

    2.存储过程

    相比于函数，存储过程友更灵活。它在微软文档里的定义如下

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

    相比于函数，它可以说是标量函数的一个超集，可以实现函数的功能，也能够完成编程，修改记录等操作。
    它的语法如下

    -- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    存储过程的也可以带返回值，返回值必须为int,
    当然可以设置OUT/OUTPUT实现多个返回值

    create proc SetStuAge(@age int)
AS
BEGIN
	Update Student set Age = @age
	SELECT * from Student
END

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    执行：
    请添加图片描述

    存储过程的流程控制

    流程控制在自定义函数也是能使用的，但是因为在自定义函数中无法做到改变记录，所以在用到不多。而在存储过程中则经常使用。

    1.判断

    语法：

    IF search_condition 
	BEGIN
		statement_list
	END
ELSE 
	BEGIN
		statement_list
	END

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    判断的结构比较简单，只需要在IF/ELSE下各用BEGIN和END声明区域即可
    例

    CREATE PROC panduan(@sign varchar(5))
AS
BEGIN
	
	IF @sign = 'A'
		BEGIN
			SELECT * from Student
		END	
	ELSE
		BEGIN
			SELECT 8*8
		END
END
EXEC panduan 'A'			

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    请添加图片描述
    请添加图片描述

    2.循环

    存储过程的循环控制比较复杂
    一般来说，SqlServer的循环也和编程语言一样使用While关键字

    WHILE loop_condition BEGIN
	statement_list
END

    • 1
    • 2
    • 3

    WHILE关键字单独使用的用法比较简单，但是一般都和Cursor游标结合使用

    Create proc WhileDemo 
AS
BEGIN
	While (select Age from Student where ID = 1) <= 200
		BEGIN 
			PRINT 'while循环测试'
			update  Student set Age = Age + 10 where ID = 1
		END
END	

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    请添加图片描述

    CURSOR游标循环

    cursor是存储过程的游标类型，用来遍历查询到的记录，类似于编程语言中的foreach循环
    cursor的使用方法非常的灵活，语法大致如下

    --声明游标
ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
--打开游标
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
--读取游标
FETCH 
          [ [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
               ] 
               FROM 
          ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ]
--关闭游标
CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }
--删除游标
DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    这里推荐查看微软的文档
    微软的文档
    这里也推荐一篇文章
    SQL游标详解

    例：

    Create proc LoopTest
AS
BEGIN
	DECLARE @S_name varchar(20),@S_id int
	--定义游标
	DECLARE DemoCursor cursor for        
		Select Name,ID from Student
	--打开游标	
	Open DemoCursor
	--读取游标
	FETCH NEXT FROM DemoCursor	into @S_name,@S_id
	While @@FETCH_STATUS = 0
		BEGIN
			PRINT 'wdnmd' + @S_name
			FETCH NEXT FROM DemoCursor INTO @S_name,@S_id
		END
--关闭游标		
CLOSE DemoCursor
--删除游标
DEALLOCATE DemoCursor
END

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    请添加图片描述

  • 相关阅读:
    启动微服务，提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接
    Python 机器学习入门之C4.5决策树算法
    Postman接口测试 —— 设置断言和集合运行
    神经网络正则化技术防过拟合和R语言CNN卷积神经网络手写数字图像数据MNIST分类
    上手之Python之异常
    Android常用的延迟执行任务及轮询定时任务的几种方式
    分布式系统中如何实现临界资源的互斥访问？
    对象的相等和引用相等的区别
    UE4 材质多张图片拼接成一张图片（此处用2×2拼接）
    JVM 内存模型
  • 原文地址：https://blog.csdn.net/jamenu/article/details/125557142