SQL server 存储过程和标量值自定义函数

最新推荐文章于 2021-07-04 18:10:48 发布

原创最新推荐文章于 2021-07-04 18:10:48 发布 · 1.2k 阅读

1 ·

CC 4.0 BY-SA版权

DataBase之SqlServer 专栏收录该内容

15 篇文章

订阅专栏

本文介绍如何使用 SQL Server 的存储过程进行循环查询，并通过自定义函数实现业务逻辑统计，包括累计学习时长、答题次数等。同时展示了如何创建存储过程和函数，以及如何将统计结果保存到指定表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL server 存储过程和标量值自定义函数：

-- 1：创建临时表
select * from member_temp;

if (exists (select * from sys.objects where name = 'member_temp'))
    drop table member_temp
go
CREATE TABLE   member_temp(
         ID INT IDENTITY(1,1),
         userName [varchar](50) NOT NULL,
         userID INT,
         status INT,
         PRIMARY KEY(id)
    )

-- 2：根据用户代码   查询用户的userID 和 userName 插入到临时表中

delete from member_temp

insert into member_temp
SELECT userName,userID,0 as status FROM dbo.ADVC_MEMBER
WHERE userName in (
'15666660235','15666660327','18615202531','15666660245','15969675559')

-- 1: 创建主存储过程--循环查询
--先执行以下，看看是否存在
    EXEC proc_getMembertRecord;

-- 第一种循环方法：
if (object_id('proc_getMembertRecord', 'P') is not null)
    drop proc proc_getMembertRecord
go
   create proc proc_getMembertRecord
as

DECLARE @TOTAL_COUNT INT, --临时表记录条数
          @NOW_COUNT INT,    --现在遍历到哪条记录
          @uID VARCHAR(30)             --当前遍历的 userID


      SELECT @TOTAL_COUNT=COUNT(*) FROM member_temp;
      SET @NOW_COUNT=1;

   WHILE(@NOW_COUNT<=@TOTAL_COUNT)
   BEGIN
          SELECT @uID=userID FROM member_temp   WHERE ID=@NOW_COUNT    -- 关键就在这（主键的值必须是从1开始）
          EXEC proc_get_member_message @uID -- 传给另一个存储过程：这个是含有业务统计函数的存储过程
          SET @NOW_COUNT=@NOW_COUNT+1
   END

-- 第2 种循环方法：下面修改存储过程
if (object_id('proc_getMembertRecord', 'P') is not null)
    drop proc proc_getMembertRecord
go
   create proc proc_getMembertRecord
as

DECLARE @TOTAL_COUNT INT, --临时表记录条数
          @NOW_COUNT INT,    --现在遍历到哪条记录
          @uID VARCHAR(30),             --当前遍历的 userID
          @uName VARCHAR(30);             --当前遍历的 userName

      SELECT @TOTAL_COUNT = COUNT(*) FROM member_temp where status = 0; -- 统计需要更新总数 (0 ：表示未更新的)
      SET @NOW_COUNT=0;

   WHILE(@TOTAL_COUNT > @NOW_COUNT)
   BEGIN
           SELECT top 1 @uID=userID,@uName=userName FROM member_temp   WHERE status = 0; -- 一次查询一条
           EXEC proc_get_member_message @uID,@uName ; -- 传给另一个存储过程：这个是含有业务统计函数的存储过程

           UPDATE member_temp SET   status = 1 where userID = @uID; -- 将状态改为1
           SELECT @TOTAL_COUNT = COUNT(*) FROM member_temp where status = 0
   END


   --2：执行：最后统计结果

       EXEC proc_getMembertRecord;


   --3   查询结果：
       select * from dbo.member_temp -- 查看临时表变化

       update member_temp set status = 0; -- 更新临时表状态

       select * from dbo.member_temp_result -- 统计后结果

       delete from dbo.member_temp_result


新建标量值函数：
--1 -- 累计学习时长

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getStudayTimeSum]')
     and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getStudayTimeSum] -- 删除函数
GO

create FUNCTION [dbo].[getStudayTimeSum](@userID INT)
RETURNs INT
AS
BEGIN
   DECLARE @stuTimesum INT
   select @stuTimesum = SUM(StudyTime) from   dbo.Cware_TimeDay_User WHERE userID = @userID
   RETURN ISNULL(@stuTimesum ,0)
END

-- 2 试卷中心测试记录答题次数 (试卷提交次数) (需要去除--模拟试卷辅导majorID = 15的数据)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getSubmitSum]')
     and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getSubmitSum] -- 删除函数
GO

create FUNCTION [dbo].[getSubmitSum](@userID INT)
RETURNs INT
AS
BEGIN
   DECLARE @submitSum INT
   select @submitSum= COUNT(1) from dbo.QZ_MEMBER_PAPER_SCORE mps
       inner join QZ_SITE_COURSE sc on mps.siteCourseID = sc.siteCourseID
       inner join QZ_COURSE qc on sc.courseID = qc.courseID
      WHERE UserID = @userID    and qc.majorID != 15
   RETURN ISNULL(@submitSum ,0)
END

--3 ：知识点测试记录总测试题数

create FUNCTION [dbo].[getPointSum](@userID INT)
RETURNs INT
AS
BEGIN
   DECLARE @pointSum INT
   select @pointSum= COUNT(distinct questionID )    from dbo.QZ_MEMBER_POINT_SCORE   WHERE UserID = @userID
   RETURN ISNULL(@pointSum ,0)
END
--4： -- 模拟试卷测试套数 : 模拟试卷辅导id = 15

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getMoniSum]')
     and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getMoniSum] -- 删除函数
GO

create FUNCTION [dbo].[getMoniSum](@userID INT)
RETURNs INT
AS
BEGIN
   DECLARE @moniSum INT
   select @moniSum= COUNT(1) from dbo.QZ_MEMBER_PAPER_SCORE mps
       inner join QZ_SITE_COURSE sc on mps.siteCourseID = sc.siteCourseID
       inner join QZ_COURSE qc on sc.courseID = qc.courseID
       WHERE UserID = @userID   and qc.majorID = 15
   RETURN ISNULL(@moniSum ,0)
END

--5： -- 模拟试卷平均成绩 convert(verchar(10),@CountOfOrders)--将变量转换为字符串型打印

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getMoniAvgScore]')
     and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getMoniAvgScore] -- 删除函数
GO

create FUNCTION [dbo].[getMoniAvgScore](@userID INT)
RETURNs numeric(20,2)
AS
BEGIN
   DECLARE @moniAvgScore numeric(20,2)
   select @moniAvgScore = cast(round(AVG(mps.lastScore),2) as   numeric(20,2))
       from dbo.QZ_MEMBER_PAPER_SCORE mps
       inner join QZ_SITE_COURSE sc on mps.siteCourseID = sc.siteCourseID
       inner join QZ_COURSE qc on sc.courseID = qc.courseID
      WHERE UserID = @userID    and qc.majorID = 15
   RETURN ISNULL(@moniAvgScore ,0)
END

--6： -- 预约回访次数

create FUNCTION [dbo].[getMemberCallSum](@userID INT)
RETURNs INT
AS
BEGIN
   DECLARE @memberCallSum INT
   select @memberCallSum= COUNT(1) from dbo.ADVC_MEMBER_CALL amc
   WHERE amc.userID = @userID and reservedDate is not null
   RETURN ISNULL(@memberCallSum ,0)
END




测试自定义函数：

select dbo.getStudayTimeSum(28082942) AS stuTimeSum,
       dbo.getSubmitSum(28082942) as submitSum,
       dbo.getPointSum(28082942) as pointSum,
       dbo.getMoniSum(28082942) as stuTimeSum,
       dbo.getMoniAvgScore(28082942) as moniAvgScore,
       dbo.getMemberCallSum(28082942) as memberCallSum;


-- 1：创建保存结果表:

if (exists (select * from sys.objects where name = 'member_temp_result'))
    drop table member_temp_result
go
CREATE TABLE [dbo].[member_temp_result](
    [userName] [varchar](50) NOT NULL,
   [userID] [int] NULL,
   [stuTimeSum] [int] NULL,
   [submitSum] [int] NULL,
   [pointSum] [int] NULL,
   [moniSum] [int] NULL,
   [moniAvgScore] [numeric](20, 2) NULL,
   [memberCallSum] [int] NULL
) ON [PRIMARY]


-- 2：创建从存储过程(参数是userID)：作用: 将统计的结果保存到结果表中

if (exists (select * from sys.objects where name = 'proc_get_member_message'))
    drop proc proc_get_member_message
go

create proc proc_get_member_message(@userId int,@userName varchar(20))
as

insert into dbo.member_temp_result
select @userName as userName,@userId as userID,
        dbo.getStudayTimeSum(@userId) AS stuTimeSum,
        dbo.getSubmitSum(@userId) as submitSum,
        dbo.getPointSum(@userId) as pointSum,
        dbo.getMoniSum(@userId) as moniSum,
        dbo.getMoniAvgScore(@userId) as moniAvgScore ,
        dbo.getMemberCallSum(@userId) as memberCallSum;

--3：测试存储过程

          exec proc_get_member_message userID, userName;

          exec proc_get_member_message 27536917, 27536917;

-- 4：查询结果：
       select * from dbo.member_temp_result

       delete from dbo.member_temp_result