自定义函数中返回表的例子

最新推荐文章于 2024-01-24 13:49:09 发布
原创 最新推荐文章于 2024-01-24 13:49:09 发布 · 568 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

该博客介绍了如何使用SQL自定义函数来查询不同日期的逾期量，包括今天、下周的工作日数据。函数根据输入日期判断是星期一、星期二还是星期五，然后查询相应的逾期量数据并返回到一个表格中。

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

USE [作业数据库]
GO

SET QUOTED_IDENTIFIER ON
GO
 ALTER function [dbo].[FK_baogaof1](@time datetime)  --  drop function FK_baogaof1
 returns @mytable TABLE(委托报告量 varchar(100),CECR1 int,CECR11 int,CECR3 int,CECR16 int,CECR6 int,CECR7 int,OTHERS int,总量 int)
 as
 begin

 declare @CECR1Num int
 declare @CECR11Num int
 declare @CECR3Num int
 declare @CECR16 int
 declare @CECR6 int
 declare @CECR7 int
 declare @OtherNum int
 declare @total int
 declare @time1 datetime
 set @time1=null
 
 
 --------------如果是星期一，查询：逾期量 今天 星期二三四五 和下星期一。
 if datename(weekday, @time)= '星期一'
 begin
 -----------------------逾期量--------------------------------------------------------------------------------------------
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',1)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',1)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',1)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',1)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',1)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',1)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
  insert  @mytable select '逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

  --今天
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 1, @time) --星期二
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 2, @time) --星期三
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 3, @time) --星期四
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 4, @time) --星期五
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 7, @time) --下周一
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 end
 
  --------------如果是星期二，查询：逾期量 今天 星期三四五 和下星期一二。
 if datename(weekday, @time)= '星期二'
 begin
  -----------------------逾期量--------------------------------------------------------------------------------------------
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',1)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',1)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',1)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',1)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',1)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',1)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
  insert  @mytable select '逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

  --今天
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 1, @time) --星期三
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 2, @time) --星期四
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 3, @time) --星期五
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
  insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 6, @time) --下周一
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 7, @time) --下周二
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 end
   --------------如果是星期三，查询：逾期量 今天 星期四五 和下星期一二三。
 if datename(weekday, @time)= '星期三'
 begin
   -----------------------逾期量--------------------------------------------------------------------------------------------
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',1)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',1)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',1)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',1)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',1)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',1)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
  insert  @mytable select '逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

  --今天
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 1, @time) --星期四
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 2, @time) --星期五
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 5, @time) --下周一
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 6, @time) --下周二
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 7, @time) --下周三
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 end
    --------------如果是星期四，查询：逾期量 今天 星期五 和下星期一二三四。
 if datename(weekday, @time)= '星期四'
 begin
   -----------------------逾期量--------------------------------------------------------------------------------------------
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',1)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',1)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',1)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',1)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',1)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',1)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
  insert  @mytable select '逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

  --今天
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 1, @time) --星期五
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 4, @time) --下周一
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 5, @time) --下周二
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 6, @time) --下周三
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 7, @time) --下周四
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 end
     --------------如果是星期五，查询：逾期量 今天 下星期一二三四五。
 if datename(weekday, @time)= '星期五'
 begin
   -----------------------逾期量--------------------------------------------------------------------------------------------
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',1)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',1)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',1)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',1)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',1)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',1)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
  insert  @mytable select '逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

  --今天
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 3, @time) --下周一
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 4, @time) --下周二
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 5, @time) --下周三
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 6, @time) --下周四
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 7, @time) --下周五
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 end
 
  return
 end
 
 
 
 USE [作业数据库]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------自定义函数FK_baogaof2   drop function FK_baogaof2

ALTER function [dbo].[FK_baogaof2](@time datetime,@报告种类 varchar(20),@type int) returns int
as
begin
 declare @baogaoNum int
 declare @changeTime datetime
 set @changeTime= CONVERT(varchar(100), @time, 23)+' 00:00:00'
 if @type=1
   begin
   if @报告种类='其他'
    begin
      select @baogaoNum=COUNT(*) from 国内订单表 where  预计完成时间<@changeTime and 预计完成时间>dateadd(day, -15, @changeTime)
      and 报告种类 <> 'CECR1' and 报告种类 <> 'CECR1-1' and 报告种类 <> 'CECR3' and 报告种类 <> 'CECR16' and 报告种类 <> 'CECR6' and 报告种类 <> 'CECR7' and 完成情况<>'已暂停' and 完成情况<>'已取消' and 完成情况='未完成' and 委托公司名称 not like '%同业%' and 委托公司名称 not like '%住友%'
    end
   else
    begin
       select @baogaoNum=COUNT(*) from 国内订单表 where  预计完成时间<@changeTime and 预计完成时间>dateadd(day, -15, @changeTime)  and 报告种类 =@报告种类 and 完成情况<>'已暂停' and 完成情况<>'已取消' and 完成情况='未完成' and 委托公司名称 not like '%同业%' and 委托公司名称 not like '%住友%'
    end

   end
 else
  begin
   if @报告种类='其他'
    begin
     select @baogaoNum = COUNT(*) from 国内订单表 where  CONVERT(varchar(100), 预计完成时间, 23)= CONVERT(varchar(100), @time, 23)
     and 报告种类 <> 'CECR1' and 报告种类 <> 'CECR1-1' and 报告种类 <> 'CECR3' and 报告种类 <> 'CECR16' and 报告种类 <> 'CECR6' and 报告种类 <> 'CECR7' and 完成情况<>'已暂停' and 完成情况<>'已取消' and 完成情况<>'已暂停' and 完成情况<>'已取消' and 完成情况='未完成' and 委托公司名称 not like '%同业%' and 委托公司名称 not like '%住友%'
    end
   else
    begin
     select @baogaoNum=COUNT(*) from 国内订单表 where  CONVERT(varchar(100), 预计完成时间, 23)= CONVERT(varchar(100), @time, 23) and 报告种类 = @报告种类 and 完成情况<>'已暂停' and 完成情况<>'已取消' and 完成情况='未完成' and 委托公司名称 not like '%同业%' and 委托公司名称 not like '%住友%'
    end
  end
  
 return @baogaoNum
end

 

Thinkphp模板中使用自定义函数的方法
10-27
### ThinkPHP模板中使用自定义函数的方法 #### 一、概述 ThinkPHP是一款非常流行的PHP框架，它提供了强大的内置模板引擎来帮助开发者高效地构建Web应用程序。为了增强模板的灵活性和可扩展性，ThinkPHP允许在模板...
python调用自定义函数的实例操作
09-19
在这个例子中，我们定义了一个名为`sum_of_two_numbers`的函数，它接受两个参数`a`和`b`，计算它们的和并返回结果。 #### 三、调用自定义函数 一旦定义了自定义函数，就可以在程序的任何地方调用它，只需要使用...
返回一个表的自定义函数
weixin_33912453的博客
07-02 179
ALTER function [dbo].[F_GetOrderData](    @OrderNO varchar(30))RETURNS @ContentData TABLE(    OrderNO varchar(20),    type_C_P  varchar(200) default ('') --类别，成份，百分比合在一起)   as    begin        declare ...
oracle 自定义函数 返回一个表类型(转)
PolyAngle2011的博客
03-08 404
oracle 中的函数可以返回表类型 但，这个表类型实际上是集合类型（与数组类似） 这个类型不能直接作为 from 的宾语 从oracle 9i 开始，提供了一个叫做“管道化表函数”的概念，来解决这个问题 这种类型的函数，必须返回一个集合类型，且标明 pipelined 这个函数不能返回具体变量，必须以一个空 return 返回 ...
达梦数据库从入门到放弃（四）自定义函数
qq_35570434的博客
01-23 2972
达梦数据库中自定义函数的使用
达梦存储过程中返回结果
岳麓丹枫
07-20 1843
达梦存储过程中返回结果。
create a function returns a table (using cursor)
zexu82 博客
09-30 963
ALTER FUNCTION [dbo].[GetWeighingInfoByTransactionId] ( -- Add the parameters for the function here @transaction_id UniqueIdentifier
Mysql中实现提取字符串中的数字的自定义函数分享
09-10
在MySQL数据库中，有时我们需要从字符...这个自定义函数`GetNum`能有效地从任意字符串中提取连续或非连续的数字，对于处理含有数字的复杂字符串非常有用，尤其适用于需要在数据库层面直接进行数据清洗和预处理的情况。
Excel-VBA宏编程实例源代码-自定义函数-返回指定列中最后单元格.zip
03-18
在Excel中，VBA（Visual Basic for Applications）是一种强大的编程工具，可以用来自动化各种任务，创建自定义函数，以及扩展Excel的功能。这个压缩包“Excel-VBA宏编程实例源代码-自定义函数-返回指定列中最后...
MySQL自定义函数简单用法示例
09-09
在这个例子中，`DELIMITER $$`用来更改SQL语句的结束符，因为函数内部可能包含分号，为了避免冲突，我们需要指定一个不同的结束符。`DROP FUNCTION IF EXISTS`用于确保在创建函数之前，如果函数已经存在，则先删除。...
自定义函数实现字符串分割，返回集合类型
10-28 237
为了实现将tom,lucy,polly这种样式的字符串按照分隔符分割，可以使用下面的自定义函数： --创建工具类型 CREATE OR REPLACE TYPE VARCHAR2ARRA...
MS SQL Server 创建返回表类型的用户自定义函数
10-21 187
用户自定义函数可以返回表类型，记录T-SQL语法如下： CREATE FUNCTION <用户自定义函数名称> ( -- 函数参数 … ) RETURNS @<用...
达梦数据库table()函数使用（测试过程记录）
qr8345776的博客
01-15 964
1、创建自定义对象类型。4、table函数调用。2、创建自定义表类型。
达梦数据库学习之三:管道函数(SqlServer中的表函数)
bestyellow的专栏
11-13 2183
把SqlServer的函数或过程转为达梦数据库的函数或过程时，遇到最多的就是临时表以及表变量的问题，达梦和Oracle到是很像，微软的SqlServer的临时表这一块还是做的不错的，用起来很方便，达梦实现起来就麻烦得多。
SQL函数返回表的写法
Stephen___Qin的博客
03-14 621
1.听说SQL函数可以返回表,我今天就尝试了下,感觉用法还是挺顺手的. ALTER FUNCTION [dbo].[returnTable] ( @nTagID AS int ) RETURNS @tab TABLE( --自己定义要返回的一个Table类型的结构. TagId int, SAMSTag nvarchar(255) ) AS BEGIN -- routine ...
达梦数据库方法中使用table类型
El Shaddai.plus
01-24 692
【代码】达梦数据库方法中使用table类型。
达梦数据库中如何用jdbc获取存储过程中引用游标返回的结果集
龙山溪笔记
09-11 2368
1.创建一个包 CREATE OR REPLACE PACKAGE PKG_TEST IS TYPE CURSOR_TYPE IS REF CURSOR; --定义游标 PROCEDURE TEST_CURSOR(INPUT varchar, CURSOR_BACK OUT CURSOR_TYPE); END PKG_TEST; CREATE OR REPLACE PACKAGE BODY PKG_TEST IS PROCEDURE TEST_CURSOR(INPUT v
oracle 管道函数 执行计划,达梦7的管道表函数(PIPELINED TABLE FUNCTION)
weixin_31199559的博客
04-08 542
前一阵，我们在为客户移植ORACLE应用到达梦7时， 碰到了PL/SQL的PIPELINED TABLE FUNCTION，经过努力现在达梦7也支持了这个特性。简单介绍一下其用法：先创建一个嵌套表类型s100_t：SQL>create or replace type s100_t is table of varchar(100);2 /操作已执行已用时间: 119.854(ms) clo...
达梦数据库动态SQL之SELECT...INTO单行数据返回及多行数据返回
a406716524的博客
12-18 7233
1、给普通类型变量赋单个值 CREATE OR REPLACE PROCEDURE T_1208(ID INT) AS Z INT; BEGIN EXECUTE IMMEDIATE 'SELECT TOP 1 ID FROM D_I WHERE ID>? ' INTO Z USING ID; PRINT Z; END; 执行结果 [执行语句1]: CALL T_1208(15); 执行成功, 执行耗时2毫秒. 执行号:671 63 2、给普通变量赋多个值 2.1、SELECT INTO 中包
如何在自定义函数中返回数组
最新发布
10-22
在C语言中，如果你想从一个自定义函数中返回数组，有两种主要的方式： 1. **通过指针传递**： - 函数接收数组的地址作为参数，然后操作并修改数组内容。 ```c void function(int *array, int size) { // 对数组进行操作 array[0] = 5; array[1] = 10; } int main() { int my_array[2]; function(my_array, 2); // my_array现在已经被改变了 printf("%d %d", my_array[0], my_array[1]); } ``` 2. **通过动态内存分配**： - 如果数组大小在运行时确定，可以在函数内部动态地分配内存，然后返回新分配的数组地址。 ```c int* function(int n) { int *new_array = malloc(n * sizeof(int)); // 分配n个整数的空间 if (new_array != NULL) { for (int i = 0; i < n; i++) { new_array[i] = i * 2; // 初始化数组 } return new_array; // 返回数组地址 } else { printf("Memory allocation failed.\n"); return NULL; } } int main() { int size = 5; int *result = function(size); // 使用result，记得处理结果和释放内存 free(result); } ``` 在上述例子中，你需要确保在结束函数之前正确地释放分配的内存，防止内存泄漏。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值