SQL函数和存储过程模板示例

数据库 SQL Server
函数和存储过程在SQL中都是很重要的,下文将为您介绍SQL函数和存储过程模板,供您参考,希望对您有所帮助。

学习SQL数据库,函数和存储过程都是非常重要的,下面就将为您示例SQL函数和存储过程模板,供您参考,希望对您学习SQL函数和存储过程能有所启迪。

--标量值函数
 

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:   <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN#p#
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

-- Add the T-SQL statements to compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>

END
GO

--////////////////////////////////////////////////////////////////////////////////////////////////////////////

--内联表值函数

-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:   <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE #p#
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO


--//////////////////////////////////////////////////////////////////////////////////////////////////////////////

--多语句表值函数

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:   <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS #p#
<@Table_Variable_Name, sysname, @Table_Var> TABLE
(
-- Add the column definitions for the TABLE variable here
<Column_1, sysname, c1> <Data_Type_For_Column1, , int>,
<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
-- Fill the table variable with the rows for your result set

RETURN
END
GO

--//////////////////////////////////////////////////////////////////////////////////////////////////////////////

--多语句表值函数

DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DuplicateId Int

SELECT @MergeDate = GetDate()


DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0

OPEN merge_cursor

FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC MergeDuplicateCustomers @MasterId, @DuplicateId

UPDATE DuplicateCustomers
SET
IsMerged = 1,
MergeDate = @MergeDate
WHERE
MasterCustomerId = @MasterId AND
DuplicateCustomerId = @DuplicateId

FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
END

CLOSE merge_cursor
DEALLOCATE merge_cursor

 

 

【编辑推荐】

SQL中表变量是否必须替代临时表

SQL中表变量的不足

详解SQL中循环结构的使用

SQL循环执行while控制

SQL循环语句的妙用

 

 

责任编辑:段燃 来源: 互联网
相关推荐

2009-08-06 16:18:38

C#调用SQL存储过程

2011-09-01 15:40:42

SQL Server存储过程和存储函数的加

2011-08-22 10:15:39

数据库存储过程

2023-02-28 11:29:09

存储函数MySQL

2010-09-25 16:00:38

sql存储过程

2011-08-16 16:59:58

PLSQL分页存储过程Java

2010-10-09 17:08:15

MySQL存储过程

2010-09-14 17:20:39

SQL函数

2010-10-12 10:48:02

mysql存储过程

2011-09-01 17:25:03

SQL Server 查看死锁存储过程

2011-08-12 14:01:21

SQL Server存储过程

2011-03-31 10:38:28

SQL Server编写优化

2011-07-11 13:22:28

存储过程

2018-03-16 08:41:16

Oracle存储函数

2010-07-15 12:38:14

SQL Server存

2010-09-07 11:41:24

SQL语句

2009-08-06 16:44:06

2010-10-29 16:22:37

SQL存储过程

2010-09-06 15:41:34

SQL函数存储过程

2010-09-06 16:52:17

SQL函数
点赞
收藏

51CTO技术栈公众号